[SQL] 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 test.plpgsql Description: Binary data test.trigger Description: Binary data pgp0.pgp Description: PGP signature
Re: [SQL] Table versions
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 => pgp0.pgp Description: PGP signature
Re: [SQL] Table versions
> 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. signature.asc Description: This is a digitally signed message part
Re: [SQL] Table versions
Rod Taylor <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] update from select
Hi folks, don't know if it's cos of the 17 hours I've just worked (sympathy vote please) but I can't get this one worked out I've got table names with nid as name id field and nallowfollow flag. I've got a vehicles table with vowner pointing at nid and a vallowfollow field. How can I update nallowfollow from the appropriate vallowfollow flag? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Help on update that subselects other records in table, uses joins
I need to adapt this an update statement to a general form that will iterate over multiple orderids for a given customerinvoiceid. My first concern is a form that will work for a given orderid, then an expanded version that will work on all orderids with a specific customerinvoiceid as a parameter. I'm sure appropriate joins will handle it, but I'm not making any headway, everything comes back with multiple tuple selected for update errors. Any help would be greatly appreciated. Thanks. UPDATE ordercharges INNER JOIN orders ON orders.orderid = ordercharges.orderid SET orderchargeasbilled = (SELECT .065*orderchargeasbilled FROM ordercharges WHERE ordercharges.orderid='123456' AND orderchargecode = 'SALE') WHERE ordercharges.orderchargecode='S&H' AND ordercharges.orderid = '123456' (additional join and where for customerinvoiceid omitted/not attempted yet) orders: +-orderid | customerinvoiceid | (...) | | ordercharges: | orderchargeid +---orderid orderchargeasbilled (...) To Illustrate, this is a sample table: [ordercharges]- orderchargeid | orderid | orderchargecode | orderchargeasbilled --- 1 123456SALE 10.00 2 123456S&H (update) 3 123457SALE 15.00 4 123457EXPEDITE 5.00 5 123457S&H (update) 6 123458SALE 20.00 7 123458S&H (update) 8 123459SALE 10.00 9 123459S&H (update) --- [orders]--- orderid | customerinvoiceid --- 12345654321 12345754321 12345854321 12345955543 --- (e.g. use 54321 as parameter to update 3 S&H rows in 3 orders, but not 1 S&H row in order 123459) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] update from select
On Wed, 29 Oct 2003, Gary Stainburn wrote: > Hi folks, > > don't know if it's cos of the 17 hours I've just worked (sympathy vote please) > but I can't get this one worked out > > I've got table names with nid as name id field and nallowfollow flag. > I've got a vehicles table with vowner pointing at nid and a vallowfollow > field. > > How can I update nallowfollow from the appropriate vallowfollow flag? If vehicles.vowner is unique, something like this maybe (using extensions to sql)? update names set nallowfollow=vehicles.vallowfollow from vehicles where vehicles.vowner=names.nid; I think it'd be the follwoing in straight sql: update names set nallowfollow= (select vallowfollow from vehicles where vehicles.vowner=names.nid); If it's not unique, what do you do if there are two vehicles with the same vowner and different values for vallowfollow? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] update from select
On Wednesday 29 Oct 2003 2:58 pm, Stephan Szabo wrote: > On Wed, 29 Oct 2003, Gary Stainburn wrote: > > Hi folks, > > > > don't know if it's cos of the 17 hours I've just worked (sympathy vote > > please) but I can't get this one worked out > > > > I've got table names with nid as name id field and nallowfollow flag. > > I've got a vehicles table with vowner pointing at nid and a vallowfollow > > field. > > > > How can I update nallowfollow from the appropriate vallowfollow flag? > > If vehicles.vowner is unique, something like this maybe (using extensions > to sql)? > update names set nallowfollow=vehicles.vallowfollow > from vehicles where vehicles.vowner=names.nid; > > I think it'd be the follwoing in straight sql: > update names set nallowfollow= > (select vallowfollow from vehicles where vehicles.vowner=names.nid); > > > If it's not unique, what do you do if there are two vehicles with the same > vowner and different values for vallowfollow? Thanks for this Stephan, although the vowner is not unique, the update has worked sufficantly. Gary > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Link Oracle tables in Postgre
Josh Berkus wrote: Richard, You might want to search the mailing list archives for discussion of an oracle version of dblink - I seem to remember someone saying they were working on such a thing. Yes, it'll be out sometime after 7.4. According to their posts in August, it's in alpha right now and just barely didn't make it into the 7.4 source. It's right!! at the end, I install everything! In order to install Oracle9 on RH9 (not supported linux version) it's necessary to set LD_ASSUME_KERNEL = 2.4.1 Al last I've install OCI (full Oracle client runtime) -> libsqlora8 -> dblink_ora (developed by Hans Jurgen Schonig). Best regards -- opengis (at) libero (dot) it AOL: open2gis
Re: [SQL] Help on update that subselects other records in table, uses joins
Jeff,
> UPDATE ordercharges INNER JOIN orders ON
> orders.orderid = ordercharges.orderid
> SET orderchargeasbilled =
You may only UPDATE one table at a time, you can't update a JOIN. So when
selecting from another table to filter or calculate your update, the form is:
UPDATE orderchanges
SET orderchargesbilled = {expression}
FROM orders
WHERE orders.orderid = ordercharges.orderid
AND etc.
Second, your value expression for the update is a subselect which includes a
select on the table and field you are updating! This is a recursive loop and
a very bad idea; gods only know what you'll end up with.
I suggest Joe Conway's "SQL for Smarties" or "SQL Queries for Mere Mortals"
from another author.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Help on update that subselects other records in table, uses joins
>I suggest Joe Conway's "SQL for Smarties" or "SQL Queries for Mere Mortals" >from another author. I could be mistaken, but I'm pretty sure Josh means Joe Celko's "SQL for Smarties" as I've seen him mention it before, and not Joe Conway. -b ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help on update that subselects other records in table, uses joins
Bryan, > I could be mistaken, but I'm pretty sure Josh means Joe Celko's "SQL for > Smarties" as I've seen him mention it before, and not Joe Conway. Ooops! yes, Joe Conway is a major PostgreSQL contributor and author of the tablefunc /contrib library; Joe Celko wrote the book. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Table versions
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 <[EMAIL PROTECTED]> 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 => pgp0.pgp Description: PGP signature
Re: [SQL] Table versions
Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Table versions
Greg Stark <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Help on update that subselects other records in table, uses joins
[EMAIL PROTECTED] says... > I suggest Joe Conway's "SQL for Smarties" Surely that's Joe Celko? > or "SQL Queries for Mere Mortals" > from another author. Michael J. Hernandez, John L. Viescas Paul... -- plinehan__AT__yahoo__DOT__com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] unescaped output of bytea
Good day Guru
I thought on output to input relation in general.
Manual says about scalar types that input and output functions should be
inverse to each other, because of dump problems (if output function prints
a value not in a format that input function waiting for)
That's the geat problem i thought. If so then an external representation
could not be deffer of SQL-constant representation (clear?)
Very common problem it is.
Most fields in this case should be manually (application level) transformed
both directions.
Then I try the experiment with text constant and text field dump:
INSERT INTO ttt values ('a\\b\'c''');
SELECT * FROM ttt;
fff
a\b'c'
I see the output not inverse to the input.
then I DUMP the table ttt and see into the dump file:
INSERT INTO ttt VALUES ('a\\b\'c''');
THAT'S IT !!! Dump DIFFERS to plain output !
the same result with escaping apostroph we could see in bytea type.
this GOOD, VERY GOOD fact allows us to have unescaped output of bytea
without problems with a dump.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
