Horton

Thread

  1. Table versions

    Stef <svb@ucs.co.za> — 2003-10-29T10:57:52Z

    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

  2. Re: [SQL] Table versions

    Stef <svb@ucs.co.za> — 2003-10-29T11:21:33Z

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

  3. Re: Table versions

    Rod Taylor <rbt@rbt.ca> — 2003-10-29T13:50:58Z

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

  4. Re: [SQL] Table versions

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-10-29T14:30:50Z

    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

  5. Re: [SQL] Table versions

    Stef <svb@ucs.co.za> — 2003-10-29T15:36:53Z

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

  6. Re: [SQL] Table versions

    Greg Stark <gsstark@mit.edu> — 2003-10-29T17:45:35Z

    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

  7. Re: [SQL] Table versions

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-10-29T17:49:40Z

    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