From: Verebes Gabor <verebes@freemail.hu>
To: pgsql-sql@postgresql.org
Subject: Re: Constraint->function dependency and dump in 7.3
Date: Fri, 9 Jul 2004 17:34:02 +0200
Message-ID: <GFEPILCFHLAKEKOGANLLCEDLCBAA.verebes@freemail.hu> (raw)
In-Reply-To: <001a01c46573$b8007a60$230a0a0a@compaq>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: verebes@freemail.hu
Subject: Re: Constraint->function dependency and dump in 7.3
In-Reply-To: <GFEPILCFHLAKEKOGANLLCEDLCBAA.verebes@freemail.hu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox