Thread: Bug in pg_get_constraintdef (for deferrable constraints)
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 doesnot 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
I can reproduce this failure here too. I am actually quite confused because: 1) I know this deferrable stuff works or used to work2) I can't find relivant references tocondeferrable/Anum_pg_constraint_condeferrableandcondeferred/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:#defineAnum_pg_constraint_condeferrable 4./include/catalog/pg_constraint.h:#defineAnum_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 doesnot 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, Pennsylvania19073
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
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;
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.
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
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.
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
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, Pennsylvania19073
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;
}