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 <[EMAIL PROTECTED]> 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
  [EMAIL PROTECTED]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.129
diff -c -c -r1.129 ruleutils.c
*** src/backend/utils/adt/ruleutils.c   14 Dec 2002 00:17:59 -0000      1.129
--- src/backend/utils/adt/ruleutils.c   8 Jan 2003 22:51:03 -0000
***************
*** 688,693 ****
--- 688,698 ----
                                }
                                appendStringInfo(&buf, " ON DELETE %s", string);
  
+                               if (conForm->condeferrable)
+                                       appendStringInfo(&buf, " DEFERRABLE");
+                               if (conForm->condeferred)
+                                       appendStringInfo(&buf, " INITIALLY DEFERRED");
+ 
                                break;
                        }
  
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Reply via email to