pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable versions
7+ messages / 4 participants
[nested] [flat]
* Table versions
@ 2003-10-29 10:57 Stef <svb@ucs.co.za>
0 siblings, 2 replies; 7+ messages in thread
From: Stef @ 2003-10-29 10:57 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; pgsql-sql
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
Attachments:
test.plpgsql (application/octet-stream, 386B) download
test.trigger (application/octet-stream, 137B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Table versions
@ 2003-10-29 11:21 Stef <svb@ucs.co.za>
parent: Stef <svb@ucs.co.za>
1 sibling, 0 replies; 7+ messages in thread
From: Stef @ 2003-10-29 11:21 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
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
=>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Table versions
@ 2003-10-29 13:50 Rod Taylor <rbt@rbt.ca>
parent: Stef <svb@ucs.co.za>
1 sibling, 1 reply; 7+ messages in thread
From: Rod Taylor @ 2003-10-29 13:50 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
> 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.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Table versions
@ 2003-10-29 14:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2003-10-29 14:30 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: Rod Taylor <rbt@rbt.ca>; pgsql-admin@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Table versions
@ 2003-10-29 15:36 Stef <svb@ucs.co.za>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Stef @ 2003-10-29 15:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rod Taylor <rbt@rbt.ca>; pgsql-admin@postgresql.org; pgsql-sql
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
=>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Table versions
@ 2003-10-29 17:45 Greg Stark <gsstark@mit.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Greg Stark @ 2003-10-29 17:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stef <svb@ucs.co.za>; Rod Taylor <rbt@rbt.ca>; pgsql-admin@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Table versions
@ 2003-10-29 17:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-10-29 17:49 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Stef <svb@ucs.co.za>; Rod Taylor <rbt@rbt.ca>; pgsql-admin@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-10-29 17:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-29 10:57 Table versions Stef <svb@ucs.co.za>
2003-10-29 11:21 ` Stef <svb@ucs.co.za>
2003-10-29 13:50 ` Rod Taylor <rbt@rbt.ca>
2003-10-29 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-29 15:36 ` Stef <svb@ucs.co.za>
2003-10-29 17:45 ` Greg Stark <gsstark@mit.edu>
2003-10-29 17:49 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox