Postgresql 7.3.1 on Linux i386 - but from what I can see it is on all platforms

It seems pg_get_constraintdef does not remember the setting "DEFERRABLE" on a constraint. This has the effect that it does not show up in psql \d commands, and it is also *not* included in backups from pg_dump.


Reproduce:
CREATE TABLE foo.prim(i int PRIMARY KEY);
CREATE TABLE foo.for1(j int REFERENCES foo.prim(i) NOT DEFERRABLE);
CREATE TABLE foo.for2(j int REFERENCES foo.prim(i) DEFERRABLE);

"\d foo.for1" and "\d foo.for2" will then show the exact same definition of the constraint:
       Table "foo.for2"
 Column |  Type   | Modifiers
--------+---------+-----------
 j      | integer |
Foreign Key constraints: $1 FOREIGN KEY (j) REFERENCES foo.prim(i) ON UPDATE NO ACTION ON DELETE NO ACTION



Seems to me like ruleutils.c at around line 600 is the place, and that is has no concept of DEFERRABLE anywhere near that, but I'm not comfortable enough in there to produce a patch myself... 


//Magnus


Wed, 1 Jan 2003 19:41:16 +0100
"Magnus Hagander" <mha@sollentuna.net>
Bug in pg_get_constraintdef (for deferrable constraints)

I can reproduce this failure here too.  I am actually quite confused
because:

	1)  I know this deferrable stuff works or used to work
	2)  I can't find relivant references to
	condeferrable/Anum_pg_constraint_condeferrable and
	condeferred/Anum_pg_constraint_condeferred in the code.

I see the values being stored on constriant creation, but not being used
anywhere:

	$ rgrepc condefer
	./backend/catalog/pg_constraint.c:      values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable);
	./backend/catalog/pg_constraint.c:      values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred);
	./include/catalog/pg_constraint.h:      bool            condeferrable;  /* deferrable constraint? */
	./include/catalog/pg_constraint.h:      bool            condeferred;    /* deferred by default? */
	./include/catalog/pg_constraint.h:#define Anum_pg_constraint_condeferrable     4
	./include/catalog/pg_constraint.h:#define Anum_pg_constraint_condeferred       5

I am confused.

---------------------------------------------------------------------------

Magnus Hagander wrote:
> Postgresql 7.3.1 on Linux i386 - but from what I can see it is on all platforms
> 
> It seems pg_get_constraintdef does not remember the setting "DEFERRABLE" on a constraint. This has the effect that it does not show up in psql \d commands, and it is also *not* included in backups from pg_dump.
> 
> 
> Reproduce:
> CREATE TABLE foo.prim(i int PRIMARY KEY);
> CREATE TABLE foo.for1(j int REFERENCES foo.prim(i) NOT DEFERRABLE);
> CREATE TABLE foo.for2(j int REFERENCES foo.prim(i) DEFERRABLE);
> 
> "\d foo.for1" and "\d foo.for2" will then show the exact same definition of the constraint:
>        Table "foo.for2"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  j      | integer |
> Foreign Key constraints: $1 FOREIGN KEY (j) REFERENCES foo.prim(i) ON UPDATE NO ACTION ON DELETE NO ACTION
> 
> 
> 
> Seems to me like ruleutils.c at around line 600 is the place, and that is has no concept of DEFERRABLE anywhere near that, but I'm not comfortable enough in there to produce a patch myself... 
> 
> 
> //Magnus
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 1 Jan 2003 15:10:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in pg_get_constraintdef (for deferrable constraints)
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I see the values being stored on constriant creation, but not being used
> anywhere:

I believe the values that actually get inspected at runtime are the
tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
pg_constraint are just copies of these.

It is not real clear to me whether it should be allowed to alter the
deferrability status of a foreign-key constraint --- is that in the spec?

			regards, tom lane

Wed, 01 Jan 2003 15:46:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in pg_get_constraintdef (for deferrable constraints) 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I see the values being stored on constriant creation, but not being used
> > anywhere:
> 
> I believe the values that actually get inspected at runtime are the
> tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
> pg_constraint are just copies of these.
> 
> It is not real clear to me whether it should be allowed to alter the
> deferrability status of a foreign-key constraint --- is that in the spec?

The big problem is that while pg_dump's dump_trigger() looks at
tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
at tginitdeferred, and therefore doesn't record the requirement as part
of ALTER TABLE ADD CONSTRAINT.

Attached is a dump of the supplied example, showing that the outputs are the
same.   Looks like this is a must-fix for 7.3.2.


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

--
-- PostgreSQL database dump
--

\connect - postgres

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 149751)
-- Name: prim; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE prim (
    i integer NOT NULL
);


--
-- TOC entry 3 (OID 149755)
-- Name: for1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE for1 (
    j integer
);


--
-- TOC entry 4 (OID 149761)
-- Name: for2; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE for2 (
    j integer
);


--
-- Data for TOC entry 6 (OID 149751)
-- Name: prim; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY prim (i) FROM stdin;
\.


--
-- Data for TOC entry 7 (OID 149755)
-- Name: for1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY for1 (j) FROM stdin;
\.


--
-- Data for TOC entry 8 (OID 149761)
-- Name: for2; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY for2 (j) FROM stdin;
\.


--
-- TOC entry 5 (OID 149753)
-- Name: prim_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY prim
    ADD CONSTRAINT prim_pkey PRIMARY KEY (i);


--
-- TOC entry 9 (OID 149757)
-- Name: $1; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY for1
    ADD CONSTRAINT "$1" FOREIGN KEY (j) REFERENCES prim(i) ON UPDATE NO ACTION ON DELETE NO ACTION;


--
-- TOC entry 10 (OID 149763)
-- Name: $1; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY for2
    ADD CONSTRAINT "$1" FOREIGN KEY (j) REFERENCES prim(i) ON UPDATE NO ACTION ON DELETE NO ACTION;
Wed, 1 Jan 2003 16:15:53 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in pg_get_constraintdef (for deferrable constraints)
On Wed, 1 Jan 2003, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > I see the values being stored on constriant creation, but not being used
> > > anywhere:
> >
> > I believe the values that actually get inspected at runtime are the
> > tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
> > pg_constraint are just copies of these.
> >
> > It is not real clear to me whether it should be allowed to alter the
> > deferrability status of a foreign-key constraint --- is that in the spec?
>
> The big problem is that while pg_dump's dump_trigger() looks at
> tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
> at tginitdeferred, and therefore doesn't record the requirement as part
> of ALTER TABLE ADD CONSTRAINT.

pg_get_constraintdef should probably be looking at condeferrable
and condeferred in the pg_constraint row it's looking at.  Maybe something
like the attached.
Wed, 1 Jan 2003 13:47:34 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Bug in pg_get_constraintdef (for deferrable constraints)
I think I initially forgot those options, and Stephans patch seems to be
everything required -- though the psql display is a little more
cluttered.

> pg_get_constraintdef should probably be looking at condeferrable
> and condeferred in the pg_constraint row it's looking at.  Maybe something
> like the attached.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
02 Jan 2003 13:50:51 -0500
Rod Taylor <rbt@rbt.ca>
Re: Bug in pg_get_constraintdef (for deferrable

On 2 Jan 2003, Rod Taylor wrote:

> I think I initially forgot those options, and Stephans patch seems to be
> everything required -- though the psql display is a little more
> cluttered.

IIRC, theoretically only initially immediate deferrable actually
needs to specify both clauses (initially deferred guarantees deferrable
and not deferrable doesn't need an initially at all).  It seemed like that
was getting too cute though for a quick patch.


Thu, 2 Jan 2003 11:19:23 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Bug in pg_get_constraintdef (for deferrable
Rod Taylor <rbt@rbt.ca> writes:
> I think I initially forgot those options, and Stephans patch seems to be
> everything required -- though the psql display is a little more
> cluttered.

Yeah, it is.  Could we improve that by having pg_get_constraintdef add
text only when the setting isn't the default?

			regards, tom lane

Thu, 02 Jan 2003 14:37:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in pg_get_constraintdef (for deferrable 

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.  It also will be
backpatched.


---------------------------------------------------------------------------


Stephan Szabo wrote:
> 
> On Wed, 1 Jan 2003, Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > I see the values being stored on constriant creation, but not being used
> > > > anywhere:
> > >
> > > I believe the values that actually get inspected at runtime are the
> > > tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
> > > pg_constraint are just copies of these.
> > >
> > > It is not real clear to me whether it should be allowed to alter the
> > > deferrability status of a foreign-key constraint --- is that in the spec?
> >
> > The big problem is that while pg_dump's dump_trigger() looks at
> > tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
> > at tginitdeferred, and therefore doesn't record the requirement as part
> > of ALTER TABLE ADD CONSTRAINT.
> 
> pg_get_constraintdef should probably be looking at condeferrable
> and condeferred in the pg_constraint row it's looking at.  Maybe something
> like the attached.

Content-Description: 

[ Attachment, skipping... ]

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 2 Jan 2003 17:31:10 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in pg_get_constraintdef (for deferrable constraints)
OK, patch applied to HEAD and 7.3.X.  It does suppress options that are
already the default:  (patch attached)

That is:
	
	test=> CREATE TABLE a1 (x int primary key);
	NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a1_pkey'
	for table 'a1'
	CREATE TABLE
	
	test=> CREATE TABLE a2 (y int references a1 (x));
	NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
	check(s)
	CREATE TABLE

dumps out as:
	
	ALTER TABLE ONLY a2
	    ADD CONSTRAINT "$1" FOREIGN KEY (y) REFERENCES a1(x) ON UPDATE NO
	    ACTION ON DELETE NO ACTION;

However, this:
	
	test=> create table a1 (x int primary key);
	NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a1_pkey'
	for table 'a1'
	CREATE TABLE
	test=> create table a2 (y int references a1 (x) deferrable initially
	deferred);
	NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
	check(s)
	CREATE TABLE

dumps out as;
	
	ALTER TABLE ONLY a2
	    ADD CONSTRAINT "$1" FOREIGN KEY (y) REFERENCES a1(x) ON UPDATE NO
	    ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;

---------------------------------------------------------------------------

Stephan Szabo wrote:
> 
> On Wed, 1 Jan 2003, Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > I see the values being stored on constriant creation, but not being used
> > > > anywhere:
> > >
> > > I believe the values that actually get inspected at runtime are the
> > > tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
> > > pg_constraint are just copies of these.
> > >
> > > It is not real clear to me whether it should be allowed to alter the
> > > deferrability status of a foreign-key constraint --- is that in the spec?
> >
> > The big problem is that while pg_dump's dump_trigger() looks at
> > tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
> > at tginitdeferred, and therefore doesn't record the requirement as part
> > of ALTER TABLE ADD CONSTRAINT.
> 
> pg_get_constraintdef should probably be looking at condeferrable
> and condeferred in the pg_constraint row it's looking at.  Maybe something
> like the attached.

Content-Description: 

[ Attachment, skipping... ]

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.129
diff -c -c -r1.129 ruleutils.c
*** src/backend/utils/adt/ruleutils.c	14 Dec 2002 00:17:59 -0000	1.129
--- src/backend/utils/adt/ruleutils.c	8 Jan 2003 22:51:03 -0000
***************
*** 688,693 ****
--- 688,698 ----
  				}
  				appendStringInfo(&buf, " ON DELETE %s", string);
  
+ 				if (conForm->condeferrable)
+ 					appendStringInfo(&buf, " DEFERRABLE");
+ 				if (conForm->condeferred)
+ 					appendStringInfo(&buf, " INITIALLY DEFERRED");
+ 
  				break;
  			}
Wed, 8 Jan 2003 17:52:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in pg_get_constraintdef (for deferrable constraints)