|
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? |
