At our development shop we use many different PostgreSQL databases simultaneously, each corresponding to a specific version of our software. For example, a developer might be working on v1.0 and v1.1 at the same time, while QA is busily testing/verifying version 1.0.3. All application code and SQL is managed in SVN, and we use scripts to automatically update our sandboxes from one version to another.
Each database schema maintains its version history and current version, and the update scripts can move a schema from one version to the next (all in the context of a transaction of course). In general this works well when updates are applied in order and corresponding to a specific development branch. It doesn’t work as well when updates need to be applied out of order corresponding to a different code branch.
As I see it the key to making the update process work smoothly is to make the updates themselves idempotent. For data oriented operations this is usually accomplished via ‘where not exists’ clauses. However, most of the updates are not data operations, but schema operations, for example, adding a missed unique constraint, or a column. So how can I make statements of the form:
Ø alter table only customers add constraint a_previously_missed_constraint unique (a, b, c);
Ø add column points int4 not null default 0;
idempotent?
I can always fallback on scripting to accomplish this – I can write functions to check for the ‘thing’ (column, constraint, whatever) before attempting to create it (via php, python pgdb, or via stored procedures, etc.). But am I missing something in PostgreSQL which would allow me to accomplish the same in a more direct manner?
