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

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

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, Pennsylvania 19073

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