Thread: Table versions

Hi all,

I'm trying to create some kind of table version control
system for approximately 300 postgres databases
ranging in version from 7.1.2 to 7.3.4.

I compared the "pg_dump -s" output between
the various versions of databases, but the format is inconsistent,
and I can't do diff's to check that table structures are identical
on the various databases this way.

What I did next, is put a trigger on pg_attribute that should, in theory,
on insert and update, fire up a function that will increment a version
number on a table comment every time a table's structure is modified.
I tried to make the function update a comment on pg_description to
accomplish this.

I'm having a lot of trouble doing this and testing it, and after plenty tries
it's still not working. I've attached the trigger statement and the plpgsql function.
(There might be a few mistakes, and I haven't attempted to cater for
system columns and  multiple changes yet.)

Can somebody please tell me if what I'm trying will ever work, or
maybe an alternative (easier) way to compare a specific table's
structure amongst various databases, that are not necessarily
on the same network, nor of the same version of postgres.

Regards
Stefan

Correction on the function :
The function currently on the database did has
       select int4(description) + 1 into v_new_version from pg_description
       where objoid = NEW.attrelid;

in stead of
       select int4(description) into v_new_version from pg_description
       where objoid = NEW.attrelid;

##START##
=> Hi all,
=>
=> I'm trying to create some kind of table version control
=> system for approximately 300 postgres databases
=> ranging in version from 7.1.2 to 7.3.4.
=>
=> I compared the "pg_dump -s" output between
=> the various versions of databases, but the format is inconsistent,
=> and I can't do diff's to check that table structures are identical
=> on the various databases this way.
=>
=> What I did next, is put a trigger on pg_attribute that should, in theory,
=> on insert and update, fire up a function that will increment a version
=> number on a table comment every time a table's structure is modified.
=> I tried to make the function update a comment on pg_description to
=> accomplish this.
=>
=> I'm having a lot of trouble doing this and testing it, and after plenty tries
=> it's still not working. I've attached the trigger statement and the plpgsql function.
=> (There might be a few mistakes, and I haven't attempted to cater for
=> system columns and  multiple changes yet.)
=>
=> Can somebody please tell me if what I'm trying will ever work, or
=> maybe an alternative (easier) way to compare a specific table's
=> structure amongst various databases, that are not necessarily
=> on the same network, nor of the same version of postgres.
=>
=> Regards
=> Stefan
=>

Rod Taylor <rbt@rbt.ca> writes:
>> What I did next, is put a trigger on pg_attribute that should, in theory,
>> on insert and update, fire up a function that will increment a version

> System tables do not use the same process for row insertion / updates as
> the rest of the system. You're trigger will rarely be fired.

s/rarely/never/.  We do not support triggers on system catalogs.  The
system should have done its best to prevent you from creating one ...
I suppose you had to hack around with a "postgres -O" standalone backend?

Returning to the original problem, it seems to me that comparing "pg_dump
-s" output is a reasonable way to proceed.  The problem of inconsistent
output format across pg_dump versions is a red herring --- just use a
single pg_dump version (the one for your newest server) for all the
dumps.  Recent pg_dump versions still talk to older servers, back to 7.0
or thereabouts.

            regards, tom lane

Thanks guys,

I had a feeling this was the case, but wasn't sure.
The one-version pg_dump looks like a winner.

Regards
Stefan

##START##
=> Rod Taylor <rbt@rbt.ca> writes:
=> >> What I did next, is put a trigger on pg_attribute that should, in theory,
=> >> on insert and update, fire up a function that will increment a version
=>
=> > System tables do not use the same process for row insertion / updates as
=> > the rest of the system. You're trigger will rarely be fired.
=>
=> s/rarely/never/.  We do not support triggers on system catalogs.  The
=> system should have done its best to prevent you from creating one ...
=> I suppose you had to hack around with a "postgres -O" standalone backend?
=>
=> Returning to the original problem, it seems to me that comparing "pg_dump
=> -s" output is a reasonable way to proceed.  The problem of inconsistent
=> output format across pg_dump versions is a red herring --- just use a
=> single pg_dump version (the one for your newest server) for all the
=> dumps.  Recent pg_dump versions still talk to older servers, back to 7.0
=> or thereabouts.
=>
=>             regards, tom lane
=>

Greg Stark <gsstark@mit.edu> writes:
> This still suffers from one major deficiency. The order that objects are
> outputed isn't necessarily consistent between databases. If I add tables to
> the development server but then add them to the production server in a
> different order the schema still shows differences even though the objects in
> the two databases are identical.

Yeah.  Stef may be able to handle this by comparing single-table dumps
rather than an overall pg_dump.  In the long run pg_dump's logic for
ordering objects needs a wholesale rewrite --- maybe that will happen
for 7.5.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Returning to the original problem, it seems to me that comparing "pg_dump
> -s" output is a reasonable way to proceed.

I've actually started checking in a pg_dump -s output file into my CVS tree.

However I prune a few key lines from it. I prune the TOC OID numbers from it,
and anything not owned by the user I'm interested in.

The makefile rule I use looks like:

schema.sql:
    pg_dump -U postgres -s user | sed '/^-- TOC entry/d;/^\\connect - postgres/,/^\\connect - user/d;/^SET
search_path/d;/^$$/d;/^--$$/d'> $@ 


This still suffers from one major deficiency. The order that objects are
outputed isn't necessarily consistent between databases. If I add tables to
the development server but then add them to the production server in a
different order the schema still shows differences even though the objects in
the two databases are identical.

--
greg

> What I did next, is put a trigger on pg_attribute that should, in theory,
> on insert and update, fire up a function that will increment a version

System tables do not use the same process for row insertion / updates as
the rest of the system. You're trigger will rarely be fired.