Support for idempotent schema changes?

From: "David Lowe" <dlowe(at)airplay(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Support for idempotent schema changes?
Date: 2007-03-02 23:31:16
Message-ID: A59E91A937445645A59CEFAAA15D642646DA6C@exch.apnwi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dave Livesay 2007-03-03 00:13:05 Re: I'd love to know what the rest of this error message is.
Previous Message Bruce Momjian 2007-03-02 23:28:06 Re: Thanks to all