Re: 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 <pgman(at)candle(dot)pha(dot)pa(dot)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(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