Date: Mon, 12 Jul 2004 15:06:57 +0200
From: =?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu>
Subject: Re: Constraint->function dependency and dump in 7.3
In-reply-to: <00a001c4680b$f14c60a0$0403a8c0@fejleszt4>
To: =?iso-8859-2?Q?'SZ=DBCS_G=E1bor'?= <surrano@mailbox.hu>,
	pgsql-sql@postgresql.org
Reply-To: csegyud@vnet.hu
Message-id: <001301c46811$1d14ed60$230a0a0a@compaq>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-2
Content-transfer-encoding: 8BIT
Importance: Normal

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.