Thread: Support for idempotent schema changes?
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?
David Lowe wrote: > 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? You just ignore the error if the object already exists. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > You just ignore the error if the object already exists. I'd advice against that or at least make sure that only "the thing already exists" errors get ignored. Because otherwise it's 100% impossible to discover any real problems with the scripts. -- Regards Flemming Frandsen - YAPH - http://dion.swamp.dk
Within the context of a script, executing: Begin Statement1 Statement2 Statement3 Commit Where I only wish to commit if the error is specific to the object already existing, and rollback for all other errors, what's the best way to accomplish that? -----Original Message----- From: Peter Eisentraut [mailto:peter_e@gmx.net] Sent: Saturday, March 03, 2007 11:45 PM To: pgsql-general@postgresql.org Cc: David Lowe Subject: Re: [GENERAL] Support for idempotent schema changes? David Lowe wrote: > 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? You just ignore the error if the object already exists. -- Peter Eisentraut http://developer.postgresql.org/~petere/
David Lowe wrote: > Within the context of a script, executing: > > Begin > Statement1 > Statement2 > Statement3 > Commit > > Where I only wish to commit if the error is specific to the object > already existing, and rollback for all other errors, what's the best way > to accomplish that? > You would have to put each statement into a savepoint, and catch each error that occured and commit or rollback to a savepoint based on that result. Joshua D. Drake > > -----Original Message----- > From: Peter Eisentraut [mailto:peter_e@gmx.net] > Sent: Saturday, March 03, 2007 11:45 PM > To: pgsql-general@postgresql.org > Cc: David Lowe > Subject: Re: [GENERAL] Support for idempotent schema changes? > > David Lowe wrote: > >> 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? >> > > You just ignore the error if the object already exists. > >
Joshua D. Drake wrote:
> David Lowe wrote:
>> Within the context of a script, executing:
>>
>> Begin
>> Statement1
>> Statement2
>> Statement3
>> Commit
>>
>> Where I only wish to commit if the error is specific to the object
>> already existing, and rollback for all other errors, what's the best way
>> to accomplish that?
>>
> You would have to put each statement into a savepoint, and catch each
> error that occured and commit or rollback to a savepoint
> based on that result.
You could write a plpgsql function that executes a text given to it
as a parameter, and catches only "already exists" errors. Then your
schema script could look like
select execute_ignoreexists('create table ...') ;
select execute_ignoreexists('create index ...') ;
...
greetings, Florian Pflug