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