Thread: pg_dump of table including check rule fails to restore

Hi

I have a schema which includes one table with a CHECK constraint. After the
schema is dumped (with pg_dump) as ascii text attempts to recreate it using
the psql -f <filename> command cause this one table, called navigaion, to
fail to create. This is using postgresql 7.3.3 on Redhat 9.0.  The fragment
of the dump is below. Is there a cure for this problem?

regards

Lewis

--
-- TOC entry 6 (OID 154551)
-- Name: navigation; Type: TABLE; Schema: public; Owner: wallet
--

CREATE TABLE navigation (
    src_section_id integer NOT NULL,
    dst_section_id integer NOT NULL,
    rank smallint NOT NULL,
    CHECK ((parent_section_id <> child_section_id))
) WITHOUT OIDS;

Lewis Foti

e: lewis.foti@mentation.com
m: +44 (0)7771 535943
w: www.mentation.com

Further to my original message I realise that the problem was due to the
text of the rule still referring to columns, src_section_id and
dst_section_id by their original names, parent_section_id and
child_section_id respectively. So the nature of the bug changes to that
pg_dump does not correctly export CHECK rules where the name of the
column(s) referred to has changed. Now IMHO that is somewhat subtle.

regards

Lewis

-----Original Message-----
From: Lewis Foti [mailto:lewis.foti@mentation.com]
Sent: 16 December 2003 18:26
To: pgsql-bugs@postgresql.org
Subject: pg_dump of table including check rule fails to restore


Hi

I have a schema which includes one table with a CHECK constraint. After the
schema is dumped (with pg_dump) as ascii text attempts to recreate it using
the psql -f <filename> command cause this one table, called navigaion, to
fail to create. This is using postgresql 7.3.3 on Redhat 9.0.  The fragment
of the dump is below. Is there a cure for this problem?

regards

Lewis

--
-- TOC entry 6 (OID 154551)
-- Name: navigation; Type: TABLE; Schema: public; Owner: wallet
--

CREATE TABLE navigation (
    src_section_id integer NOT NULL,
    dst_section_id integer NOT NULL,
    rank smallint NOT NULL,
    CHECK ((parent_section_id <> child_section_id))
) WITHOUT OIDS;

Lewis Foti

e: lewis.foti@mentation.com
m: +44 (0)7771 535943
w: www.mentation.com

Lewis Foti wrote:
> I have a schema which includes one table with a CHECK constraint.
> After the schema is dumped (with pg_dump) as ascii text attempts to
> recreate it using the psql -f <filename> command cause this one
> table, called navigaion, to fail to create. This is using postgresql
> 7.3.3 on Redhat 9.0.  The fragment of the dump is below. Is there a
> cure for this problem?

> CREATE TABLE navigation (
>     src_section_id integer NOT NULL,
>     dst_section_id integer NOT NULL,
>     rank smallint NOT NULL,
>     CHECK ((parent_section_id <> child_section_id))
> ) WITHOUT OIDS;

Can you remember how you constructed this table?  Did you drop or alter
some columns?  Do you have your original table creation script around
or something like that?

Obviously, this table is invalid, but we need to find out how you
arrived there.

Lewis Foti wrote:
> Further to my original message I realise that the problem was due to
> the text of the rule still referring to columns, src_section_id and
> dst_section_id by their original names, parent_section_id and
> child_section_id respectively. So the nature of the bug changes to
> that pg_dump does not correctly export CHECK rules where the name of
> the column(s) referred to has changed. Now IMHO that is somewhat
> subtle.

This appears to be fixed in 7.4.  Upgrading might be your best option
unless you want to backport the pg_dump fix to 7.3.