Thread: Constraint->function dependency and dump in 7.3
Dear Gurus, Just recently realized that our daily dump from a 7.3 (production) db to a 7.4 (development) server has problems. I have no idea where to search for an answer so please feel free to point me to the appropriate thread, doc or TFM :) Below is two portions of the dump, which seems to be in the wrong order (at least for me). I'm not sure, and not in a position to easily test it, that it's wrong order in 7.3; but 7.4 has problems creating the table without the function (which is logical): %----------------------- cut here -----------------------% CREATE TABLE cim ( -- etc etc ... orszag_kod integer, CONSTRAINT cim_orszag_kod CHECK ((hely_fajta(orszag_kod) = 7)) ); -- ... several lines later: CREATE FUNCTION hely_fajta (integer) RETURNS integer AS ' ... ' LANGUAGE sql; %----------------------- cut here -----------------------% Checked pg_depend, and constraint cim_orszag_kod refers to function hely_fajta, but noone (not even the table) refers to the constraint. I'm just wondering if it's ok... 1) is it normal that the table does not refer to its constraints? 2) if not, do you have the idea of the possible cause? 3) if so, is it normal for pg_dump to dump in this order? 4) if so, how may I change it? 5) may inserting into pg_depend solve the problem? TIA, G. %----------------------- cut here -----------------------% \end
Hi Gábor! I had the same problem and someone pointed me the right direction. I tried to define a table default clause refering a function. Reloading the dump file an error messaged raised up saying that the referred function doesn't exist. This is because dumping out the schema pg_dump pushes out the table definitions first and then the functions (I don't know why can not realize these issues.). You can keep the schema dump in a separete file and move the referred functions in front of the tble definitions. After that regulary dump out only the data. Restoing the db start with the schema file. I hope I was clear. Another advance of this method is that it is absolutely Y3K safe. :) Bye, -- Csaba Együd > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of SZŰCS Gábor > Sent: 2004. július 8. 13:10 > To: pgsql-sql@postgresql.org > Subject: [SQL] Constraint->function dependency and dump in 7.3 > > > Dear Gurus, > > Just recently realized that our daily dump from a 7.3 > (production) db to a > 7.4 (development) server has problems. I have no idea where > to search for an > answer so please feel free to point me to the appropriate > thread, doc or TFM > :) > > Below is two portions of the dump, which seems to be in the > wrong order (at > least for me). I'm not sure, and not in a position to easily > test it, that > it's wrong order in 7.3; but 7.4 has problems creating the > table without the > function (which is logical): > > %----------------------- cut here -----------------------% > CREATE TABLE cim ( > -- etc etc ... > orszag_kod integer, > CONSTRAINT cim_orszag_kod CHECK ((hely_fajta(orszag_kod) = 7)) > ); > > -- ... several lines later: > > CREATE FUNCTION hely_fajta (integer) RETURNS integer > AS ' ... ' > LANGUAGE sql; > %----------------------- cut here -----------------------% > > Checked pg_depend, and constraint cim_orszag_kod refers to function > hely_fajta, but noone (not even the table) refers to the > constraint. I'm > just wondering if it's ok... > > 1) is it normal that the table does not refer to its constraints? > 2) if not, do you have the idea of the possible cause? > 3) if so, is it normal for pg_dump to dump in this order? > 4) if so, how may I change it? > 5) may inserting into pg_depend solve the problem? > > TIA, > G. > %----------------------- cut here -----------------------% > \end > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27.
Hi! Why don't you use pg_restore. You can set the order of restoring with parameters. (I haven't tried) By, Gabor -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Együd Csaba Sent: Friday, July 09, 2004 7:15 AM To: 'SZŰCS Gábor'; pgsql-sql@postgresql.org Subject: Re: [SQL] Constraint->function dependency and dump in 7.3 Hi Gábor! I had the same problem and someone pointed me the right direction. I tried to define a table default clause refering a function. Reloading the dump file an error messaged raised up saying that the referred function doesn't exist. This is because dumping out the schema pg_dump pushes out the table definitions first and then the functions (I don't know why can not realize these issues.). You can keep the schema dump in a separete file and move the referred functions in front of the tble definitions. After that regulary dump out only the data. Restoing the db start with the schema file. I hope I was clear. Another advance of this method is that it is absolutely Y3K safe. :) Bye, -- Csaba Együd > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of SZŰCS Gábor > Sent: 2004. július 8. 13:10 > To: pgsql-sql@postgresql.org > Subject: [SQL] Constraint->function dependency and dump in 7.3 > > > Dear Gurus, > > Just recently realized that our daily dump from a 7.3 > (production) db to a > 7.4 (development) server has problems. I have no idea where > to search for an > answer so please feel free to point me to the appropriate > thread, doc or TFM > :) > > Below is two portions of the dump, which seems to be in the > wrong order (at > least for me). I'm not sure, and not in a position to easily > test it, that > it's wrong order in 7.3; but 7.4 has problems creating the > table without the > function (which is logical): > > %----------------------- cut here -----------------------% > CREATE TABLE cim ( > -- etc etc ... > orszag_kod integer, > CONSTRAINT cim_orszag_kod CHECK ((hely_fajta(orszag_kod) = 7)) > ); > > -- ... several lines later: > > CREATE FUNCTION hely_fajta (integer) RETURNS integer > AS ' ... ' > LANGUAGE sql; > %----------------------- cut here -----------------------% > > Checked pg_depend, and constraint cim_orszag_kod refers to function > hely_fajta, but noone (not even the table) refers to the > constraint. I'm > just wondering if it's ok... > > 1) is it normal that the table does not refer to its constraints? > 2) if not, do you have the idea of the possible cause? > 3) if so, is it normal for pg_dump to dump in this order? > 4) if so, how may I change it? > 5) may inserting into pg_depend solve the problem? > > TIA, > G. > %----------------------- cut here -----------------------% > \end > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Dear Fellow Countymen, I fear none of your answers are acceptable for me. Also, from Csaba's answer, this sounds to me more like a bug in pg_dump v7.3. (1) I wish to transfer the full dump for daily mirroring. Thus, data-only isn't an option. (2) Dumping functions first won't work for SQL functions that refer to tables. Separating them sounds painful. (3) Never used pg_restore, but having a look at it, it doesn't seem to be any better for me than a hand-made repair script G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "Verebes Gabor" <verebes@freemail.hu> Sent: Friday, July 09, 2004 5:34 PM Hi! Why don't you use pg_restore. You can set the order of restoring with parameters. (I haven't tried) By, Gabor -----Original Message----- From: pgsql-sql-owner@postgresql.org Sent: Friday, July 09, 2004 7:15 AM Hi Gábor! I had the same problem and someone pointed me the right direction. I tried to define a table default clause refering a function. Reloading the dump file an error messaged raised up saying that the referred function doesn't exist. This is because dumping out the schema pg_dump pushes out the table definitions first and then the functions (I don't know why can not realize these issues.). You can keep the schema dump in a separete file and move the referred functions in front of the tble definitions. After that regulary dump out only the data. Restoing the db start with the schema file. I hope I was clear. Another advance of this method is that it is absolutely Y3K safe. :) Bye, -- Csaba Együd > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > Sent: 2004. július 8. 13:10 > Subject: [SQL] Constraint->function dependency and dump in 7.3 > > > Dear Gurus, > > Just recently realized that our daily dump from a 7.3 > (production) db to a > 7.4 (development) server has problems. I have no idea where > to search for an > answer so please feel free to point me to the appropriate > thread, doc or TFM > :) > > Below is two portions of the dump, which seems to be in the > wrong order (at > least for me). I'm not sure, and not in a position to easily > test it, that > it's wrong order in 7.3; but 7.4 has problems creating the > table without the > function (which is logical): > > %----------------------- cut here -----------------------% > CREATE TABLE cim ( > -- etc etc ... > orszag_kod integer, > CONSTRAINT cim_orszag_kod CHECK ((hely_fajta(orszag_kod) = 7)) > ); > > -- ... several lines later: > > CREATE FUNCTION hely_fajta (integer) RETURNS integer > AS ' ... ' > LANGUAGE sql; > %----------------------- cut here -----------------------% > > Checked pg_depend, and constraint cim_orszag_kod refers to function > hely_fajta, but noone (not even the table) refers to the > constraint. I'm > just wondering if it's ok... > > 1) is it normal that the table does not refer to its constraints? > 2) if not, do you have the idea of the possible cause? > 3) if so, is it normal for pg_dump to dump in this order? > 4) if so, how may I change it? > 5) may inserting into pg_depend solve the problem? > > TIA, > G.
On Mon, 2004-07-12 at 08:29, SZŰCS Gábor wrote: > Dear Fellow Countymen, > > I fear none of your answers are acceptable for me. Also, from Csaba's > answer, this sounds to me more like a bug in pg_dump v7.3. It is a bug (or a missing feature) that should be solved with v7.5 for restoring to 7.5 or later database. Until then, you'll need to re-order the dump by hand (splitting data from schema can be useful).
Dear Gabor, I meant that if the schema of the given database is not altered frequently it can be a considerable solution keeping the schema in a separate file. When it is altered you must make a new one, but just once per schema modifications. I don't think that it is a painful thing to write a script which reloads the schema first and then the daily (or whatever) data-only dump file. In this approach you have to maintain two files during the restore process - or if it is a problem, you can append the data dump to a copy of the schema file. cheers, -- Csaba > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of SZŰCS Gábor > Sent: 2004. július 12. 14:30 > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Constraint->function dependency and dump in 7.3 > > > Dear Fellow Countymen, > > I fear none of your answers are acceptable for me. Also, from Csaba's > answer, this sounds to me more like a bug in pg_dump v7.3. > > (1) I wish to transfer the full dump for daily mirroring. > Thus, data-only > isn't an option. > > (2) Dumping functions first won't work for SQL functions that refer to > tables. Separating them sounds painful. > > (3) Never used pg_restore, but having a look at it, it > doesn't seem to be > any better for me than a hand-made repair script > > G. > %----------------------- cut here -----------------------% > \end > > ----- Original Message ----- > From: "Verebes Gabor" <verebes@freemail.hu> > Sent: Friday, July 09, 2004 5:34 PM > > > Hi! > > Why don't you use pg_restore. You can set the order of restoring with > parameters. > (I haven't tried) > > By, > Gabor > > > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > Sent: Friday, July 09, 2004 7:15 AM > > > Hi Gábor! > > I had the same problem and someone pointed me the right > direction. I tried > to define a table default clause refering a function. > Reloading the dump > file an error messaged raised up saying that the referred > function doesn't > exist. > This is because dumping out the schema pg_dump pushes out the table > definitions first and then the functions (I don't know why > can not realize > these issues.). You can keep the schema dump in a separete > file and move the > referred functions in front of the tble definitions. After > that regulary > dump out only the data. Restoing the db start with the schema file. > > I hope I was clear. Another advance of this method is that it > is absolutely > Y3K safe. :) > > Bye, > -- Csaba Együd > > > -----Original Message----- > > From: pgsql-sql-owner@postgresql.org > > Sent: 2004. július 8. 13:10 > > Subject: [SQL] Constraint->function dependency and dump in 7.3 > > > > > > Dear Gurus, > > > > Just recently realized that our daily dump from a 7.3 > > (production) db to a > > 7.4 (development) server has problems. I have no idea where > > to search for an > > answer so please feel free to point me to the appropriate > > thread, doc or TFM > > :) > > > > Below is two portions of the dump, which seems to be in the > > wrong order (at > > least for me). I'm not sure, and not in a position to easily > > test it, that > > it's wrong order in 7.3; but 7.4 has problems creating the > > table without the > > function (which is logical): > > > > %----------------------- cut here -----------------------% > > CREATE TABLE cim ( > > -- etc etc ... > > orszag_kod integer, > > CONSTRAINT cim_orszag_kod CHECK ((hely_fajta(orszag_kod) = 7)) > > ); > > > > -- ... several lines later: > > > > CREATE FUNCTION hely_fajta (integer) RETURNS integer > > AS ' ... ' > > LANGUAGE sql; > > %----------------------- cut here -----------------------% > > > > Checked pg_depend, and constraint cim_orszag_kod refers to function > > hely_fajta, but noone (not even the table) refers to the > > constraint. I'm > > just wondering if it's ok... > > > > 1) is it normal that the table does not refer to its constraints? > > 2) if not, do you have the idea of the possible cause? > > 3) if so, is it normal for pg_dump to dump in this order? > > 4) if so, how may I change it? > > 5) may inserting into pg_depend solve the problem? > > > > TIA, > > G. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so > that your > message can get through to the mailing list cleanly > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.717 / Virus Database: 473 - Release Date: 2004. 07. 08. > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.717 / Virus Database: 473 - Release Date: 2004. 07. 08.
Thanks Rod. Checked, and So do you say, this problem persists in dbs dumped from 7.4 to 7.4 too? i.e. upgrading to 7.4 (which we tested for quite some time now) won't help? (...) trying dump confirmed this :( Even tried adding a line to pg_depend but didn't seem to change anything. G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "Rod Taylor" <pg@rbt.ca> Sent: Monday, July 12, 2004 2:51 PM Subject: Re: [SQL] Constraint->function dependency and dump in 7.3 > On Mon, 2004-07-12 at 08:29, SZŰCS Gábor wrote: > > Dear Fellow Countymen, > > > > I fear none of your answers are acceptable for me. Also, from Csaba's > > answer, this sounds to me more like a bug in pg_dump v7.3. > > It is a bug (or a missing feature) that should be solved with v7.5 for > restoring to 7.5 or later database. > > Until then, you'll need to re-order the dump by hand (splitting data > from schema can be useful). > >
> Checked, and So do you say, this problem persists in dbs dumped from 7.4 to > 7.4 too? i.e. upgrading to 7.4 (which we tested for quite some time now) > won't help? There may have been some minor fiddling to make it easier, but I wouldn't call it fixed by any means. > trying dump confirmed this :( Even tried adding a line to pg_depend but > didn't seem to change anything. The 7.5 version of pg_dump will be the first one to use pg_depend to attempt to get the object order correct. I would hold off on an upgrade until October (or so) and goto 7.5 instead.
Dear Rod, Thanks. It'll be a pain to have two versions between the prod and devel servers, but I'll forward this info to the chief. Thanks again, G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "Rod Taylor" <pg@rbt.ca> Sent: Tuesday, July 13, 2004 7:20 PM > The 7.5 version of pg_dump will be the first one to use pg_depend to > attempt to get the object order correct. > > I would hold off on an upgrade until October (or so) and goto 7.5 > instead.
On Tue, 2004-07-13 at 13:42, SZŰCS Gábor wrote: > Dear Rod, > > Thanks. It'll be a pain to have two versions between the prod and devel > servers, but I'll forward this info to the chief. You can make this part easier on yourself. Dump the structure from production and migrate it to devel (fix the dump file). Keep this file. >From now on when applying changes to production, keep the structural changes applied as a separate SQL file (numbers work well). When building a new box to duplicate production: 1. Apply all patches in orderfor i in `ls *.sql` ; do cat $i | psql test_db ; done 2. Do a data dump of production and restore that to the testing areapg_dump --data-only prod_db | psql test_db Now you don't need to worry about whether the production schema will or will not dump properly, since you can reproduce that using external files.
Dear Rod, That sounds as good as simple ;) As for as patching, we do something like that. Developed a PHP script that compares schema files (not dumps, but source codes instead) to the actual. Say, it creates a temp table and compares it to the existing one, examining pg_attributes, pg_indexes, pg_constraints etc. then runs appropriate ALTER TABLE commands on the old table. Creating a clean double of the prod and keeping its schema sounds good to handle exceptional things. Yours, G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "Rod Taylor" <pg@rbt.ca> Sent: Tuesday, July 13, 2004 8:03 PM > When building a new box to duplicate production: > 1. Apply all patches in order > for i in `ls *.sql` ; do cat $i | psql test_db ; done > 2. Do a data dump of production and restore that to the testing area > pg_dump --data-only prod_db | psql test_db > > Now you don't need to worry about whether the production schema will or > will not dump properly, since you can reproduce that using external > files.