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