Re: Constraint->function dependency and dump in 7.3 - Mailing list pgsql-sql

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.