Re: [HACKERS] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (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 - 1.129 --- src/backend/utils/adt/ruleutils.c 8 Jan 2003 22:51:03 - *** *** 688,693 --- 688,698 } appendStringInfo(buf, ON DELETE %s, string); + if (conForm-condeferrable) + appendStringInfo(buf, DEFERRABLE); + if (conForm-condeferred) + appendStringInfo(buf, INITIALLY DEFERRED); + break; } ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 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 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
Re: [HACKERS] 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: boolcondeferrable; /* deferrable constraint? */ ./include/catalog/pg_constraint.h: boolcondeferred;/* 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (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; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)
On Wed, 1 Jan 2003, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] 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.
*** pgsql/src/backend/utils/adt/ruleutils.c 2003-01-01 15:03:35.0 -0800
--- pgsql/src/backend/utils/adt/ruleutils.c.new 2003-01-01 15:02:32.0 -0800
***
*** 688,693
--- 688,704
}
appendStringInfo(buf, ON DELETE %s, string);
+ if (!conForm-condeferrable) {
+ appendStringInfo(buf, NOT);
+ }
+ appendStringInfo(buf, DEFERRABLE);
+ if (conForm-condeferred) {
+ appendStringInfo(buf, INITIALLY DEFERRED);
+ }
+ else {
+ appendStringInfo(buf, INITIALLY
+IMMEDIATE);
+ }
+
break;
}
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
