Date: Fri, 09 Jul 2004 07:15:13 +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: <002801c464dc$196c09b0$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: <001a01c46573$b8007a60$230a0a0a@compaq>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-2
Content-transfer-encoding: 8BIT
Importance: Normal

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.