Adventures in IT

Postgres has two very useful extensions - earthdistance and postgis .  PostGIS is much more accurate but I found earthdistance to be very easy to use and accurate enough for my purpose (finding UK postcodes within a radius of a point). To install it first find your Postgres version and then install the appropriate package.  On my Debian Mint dev box it looks like the below snippet. My production machine is an Amazon RDS and you can skip this step in that environment. psql -V sudo apt-get install postgresql-contrib postgresql-contrib-9.3 sudo service postgresql restart Having done that you should launch psql and run these two commands.  Make sure that you install cube first because it is a requirement of earthdistance. CREATE EXTENSION cube; CREATE EXTENSION earthdistance; Now that the extensions are installed you have access to all of the functions they provide. If you want to check that they're working you can run SELECT earth(); as a quick wa...

I'm busy on a project which involves importing fairly large datasets of about ~3.3GB at a time.  I have to read a CSV file, process each line, and generate a number of database records from the results of that process. Users are expected to be able to rerun batches and there is overlap between different datasets.  For example: the dataset of "last year" overlaps with the dataset of "all time".  This means that we need an elegant way to handle duplicate updates. Searching if a record exists (by PK) is fine until the row count in the table gets significant.  At just over 2 million records it was taking my development machine 30 seconds to process 10,000 records.  This number steadily increased as the row count increased. I had to find a better way to do this and happened across the option of using a database rule to ignore duplicates.  While using the rule there is a marked improvement in the performance as I no longer need to search the database fo...

On my desktop machine I run my package upgrades every day.  The other day my Hiphop version got updated to 3.6.0 and suddenly my Postgres support died. Running Hiphop gave a symbol not found error in the postgres.so file ( undefined symbol: _ZTIN4HPHP11PDOResourceE\n ) exactly like the issue reported on the driver repository ( here ). I tried to recompile the postgres driver against Hiphop 3.6.0 but hit a number of problems, mostly to do with  hhvm-pgsql-master/pdo_pgsql_statement.cpp it seems. The fix for the incompatibility was unfortunately rolling back to my previous version of Hiphop.  To do this on Mint/Ubuntu just do this: Run cat /etc/*-release to get your release information Download the appropriate package for your distro from http://dl.hhvm.com/ubuntu/pool/main/h/hhvm/ Remove your 3.6.0 installation of hhvm: sudo apt-get remove hhvm Install the package you downloaded : sudo dpkg -i <deb package> After that everything should be ...

PostGres is rapidly gaining traction as a solid relational database manager.  It provides transaction reliability (ACID), standards compliance, and has a better reputation for handling large datasets than MariaDB / mySQL. Luckily installing it for Hiphop is painless and there is an externally provided extension for it. There was not a prebuilt package available for the PocketRent/hhvm-pgsql  extension for my version of Hiphop so I built it following the advice in the project readme.  Their instructions worked first time. I did not have success with using the "ini" method of including the extension so had to create a Hiphop configuration file. DynamicExtensionPath = /path/to/hhvm/extensions DynamicExtensions { * = pgsql.so } I placed the snippet they provide (above) into a file called config.hdf in my Hiphop location.  I'm using Mint on my dev box so this was /etc/hhvm/config.hdf for me. Then I edited /etc/default/hhvm and set Hiphop up to...