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.
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:
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...
pgsql-hackers by date
|Next:||From: Bruce Momjian||Date: 2003-01-01 20:10:02|
|Subject: Re: Bug in pg_get_constraintdef (for deferrable constraints)|
|Previous:||From: Bruce Momjian||Date: 2002-12-31 19:26:09|
|Subject: Re: PostgreSQL Password Cracker|