Re: 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073