PostgreSQL provides us two very powerful features which are helpful with unit testing: transactional DDL and transaction savepoints. In this article I will show you how to use those with Flask-SQLAlchemy unit tests.
Transactional DDL means you can create tables inside a transaction, run tests against them, and roll back your changes after you are done. The database will be left in the same state as it was when you started. If you started with an empty database, nothing will be left in the database afterwards.
Savepoints allow you to roll back a part of a transaction without affecting what has happened before that savepoint was created. You can run a single test inside a savepoint and roll back just the changes that single test made without affecting the changes your set-up code has done before the savepoint.
That means you can create a large number of tables and other database objects in the beginning of your test suite and then run individual tests inside nested transaction using savepoints. There is no need to drop and re-create the whole database schema for each test. Continue reading Flask-SQLAlchemy and PostgreSQL Unit Testing with Transaction Savepoints
Here’s how to install the OpenStreetMap Nominatim service on your own server. It can be used to geocode and reverse geocode addresses and map coordinates. You will also get a web interface which loads map tiles from openstreetmap.org while doing geocoding requests using your own server. Continue reading OpenStreetMap Nominatim Server for Geocoding
Update: PostgreSQL 9.5 has been released with INSERT .. ON CONFLICT UPDATE support.
PostgreSQL has no “upsert” or “replace” or “insert .. on duplicate key update” or “merge into” construct to conditionally either insert a new row, or if a row with the key already exists, to either update the existing row with the new values, or first delete the old row and then insert a new one. This has been discussed many times in the posgresql developers mailing lists, and plans to implement the SQL standard MERGE operation have been devised. But there is still no such functionality in PostgreSQL as of version 9.3. Here are some examples on how to implement the functionality in different ways, using either a function, a trigger function or a rule. None of them are perfect. There are trade-offs to be made. Continue reading Upsert Methods for PostgreSQL
SQLAlchemy has a nice reflection facility which takes for example a database table name as argument and produces a Table object out of it for manipulation. However, those objects do not behave like the objects produced by declarative classes, which are easier to work with. Here’s a little class that helps to bridge that gap by reflecting proper declarative classes from database tables. It has only been tested with PostgreSQL, but it may work with other databases as well. Continue reading SQLAlchemy Declarative Class Reflector
Here’s how to do it:
ALTER ROLE myuser IN DATABASE mydb SET search_path = myschema;
The same syntax works for any configuration parameter, such as localization parameters.
I had to migrate a pretty big database cluster from one server with PostgreSQL 8.4 to a new server with 9.2 There we multiple apps using the databases, and the downtime had to be minimal. Luckily, the databases were split to multiple schemas, and there were only a few tables in a couple of schemas that were in active use at the time of the migration.
The pg_dump tool was very helpful in this operation. First, I set up ssh public key authentication between the postgres users from the source to the destination server. Then I ran the first full cluster dump to the new empty cluster. This can be done while users are actively using the source database, as long as you know which schemas, tables and sequences are in use, so that they can be copied again after shutting down applications: Continue reading PostgreSQL Database Cluster Migration Notes
Excellent article on how to extract almost any piece of metadata from PostgreSQL: Extracting META information from PostgreSQL
Top for PostgreSQL:
PostgreSQL top (pg_top): Project Home Page
sudo apt-get install ptop
To temporarily disable all triggers in a PostgreSQL session, use this:
SET session_replication_role = replica;
That disables all triggers for the current database session only. Useful for bulk operations, but remember to be careful to keep your database consistent.
SET session_replication_role = DEFAULT;
Disable a Single Trigger
To disable just a single trigger, use ALTER TABLE:
ALTER TABLE mytable DISABLE TRIGGER mytrigger;
The difference to the previous method is that ALTER TABLE will globally disable the trigger, affecting all database sessions, not just the current one.
To disable all triggers for one table:
ALTER TABLE mytable DISABLE TRIGGER ALL;
ALTER TABLE mytable ENABLE TRIGGER ALL;
This query sums total disk space used by the table including indexes and toasted data for the 20 largest tables:
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
via Disk Usage – PostgreSQL wiki.
The stock mod_auth_mysql package in Ubuntu is not able to authenticate against the phpass password hashes stored in the WordPress database.
There seems to be a patch lying around to enable phpass authentication in mod_auth_mysql. Its inclusion in mod_auth_mysql has been requested a long time ago, and again more recently, but for one reason or another it has been declined. Inclusion of the patch into the Debian package has also been requested.
Thanks to Peter Lamberg, there are good instructions around on how to apply the patch and enable it. I’ve made available a pre-compiled 64-bit package here: Continue reading Apache HTTP authentication against WordPress password database
Here’s a statically compiled version of Sqlite 3.5.6 command line interface on ARMEL with Readline support. It runs nicely on Nokia Internet Tablets with the OS 2008 (libreadline4 must be installed):
Continue reading Sqlite3 for ARMEL