Re: Bug #896: Column Constraint Not Working in ALTER TABLE ADD COLUMN?

pgsql-bugs@xxxxxxxxxxxxxx writes:
> Column Constraint Not Working in ALTER TABLE ADD COLUMN?

Yeah, the parser was losing foreign key constraints in this case :-(
Patch against 7.3 is attached.

                        regards, tom lane

*** src/backend/parser/analyze.c.orig   Mon Feb 10 23:13:39 2003
--- src/backend/parser/analyze.c        Thu Feb 13 17:41:13 2003
***************
*** 111,117 ****
  static void transformIndexConstraints(ParseState *pstate,
                                                  CreateStmtContext *cxt);
  static void transformFKConstraints(ParseState *pstate,
!                                          CreateStmtContext *cxt);
  static void applyColumnNames(List *dst, List *src);
  static List *getSetColTypes(ParseState *pstate, Node *node);
  static void transformForUpdate(Query *qry, List *forUpdate);
--- 111,118 ----
  static void transformIndexConstraints(ParseState *pstate,
                                                  CreateStmtContext *cxt);
  static void transformFKConstraints(ParseState *pstate,
!                                                                  
CreateStmtContext *cxt,
!                                                                  bool 
isAddConstraint);
  static void applyColumnNames(List *dst, List *src);
  static List *getSetColTypes(ParseState *pstate, Node *node);
  static void transformForUpdate(Query *qry, List *forUpdate);
***************
*** 770,776 ****
        /*
         * Postprocess foreign-key constraints.
         */
!       transformFKConstraints(pstate, &cxt);
  
        /*
         * Output results.
--- 771,777 ----
        /*
         * Postprocess foreign-key constraints.
         */
!       transformFKConstraints(pstate, &cxt, false);
  
        /*
         * Output results.
***************
*** 1296,1302 ****
  }
  
  static void
! transformFKConstraints(ParseState *pstate, CreateStmtContext *cxt)
  {
        if (cxt->fkconstraints == NIL)
                return;
--- 1297,1304 ----
  }
  
  static void
! transformFKConstraints(ParseState *pstate, CreateStmtContext *cxt,
!                                          bool isAddConstraint)
  {
        if (cxt->fkconstraints == NIL)
                return;
***************
*** 1305,1320 ****
                 cxt->stmtType);
  
        /*
!        * For ALTER TABLE ADD CONSTRAINT, nothing to do.  For CREATE TABLE,
!        * gin up an ALTER TABLE ADD CONSTRAINT command to execute after
!        * the basic CREATE TABLE is complete.
         *
         * Note: the ADD CONSTRAINT command must also execute after any index
         * creation commands.  Thus, this should run after
         * transformIndexConstraints, so that the CREATE INDEX commands are
         * already in cxt->alist.
         */
!       if (strcmp(cxt->stmtType, "CREATE TABLE") == 0)
        {
                AlterTableStmt *alterstmt = makeNode(AlterTableStmt);
                List       *fkclist;
--- 1307,1322 ----
                 cxt->stmtType);
  
        /*
!        * For ALTER TABLE ADD CONSTRAINT, nothing to do.  For CREATE TABLE or
!        * ALTER TABLE ADD COLUMN, gin up an ALTER TABLE ADD CONSTRAINT command
!        * to execute after the basic command is complete.
         *
         * Note: the ADD CONSTRAINT command must also execute after any index
         * creation commands.  Thus, this should run after
         * transformIndexConstraints, so that the CREATE INDEX commands are
         * already in cxt->alist.
         */
!       if (!isAddConstraint)
        {
                AlterTableStmt *alterstmt = makeNode(AlterTableStmt);
                List       *fkclist;
***************
*** 2253,2259 ****
                                                                          
(ColumnDef *) stmt->def);
  
                        transformIndexConstraints(pstate, &cxt);
!                       transformFKConstraints(pstate, &cxt);
  
                        ((ColumnDef *) stmt->def)->constraints = 
cxt.ckconstraints;
                        *extras_before = nconc(*extras_before, cxt.blist);
--- 2255,2261 ----
                                                                          
(ColumnDef *) stmt->def);
  
                        transformIndexConstraints(pstate, &cxt);
!                       transformFKConstraints(pstate, &cxt, false);
  
                        ((ColumnDef *) stmt->def)->constraints = 
cxt.ckconstraints;
                        *extras_before = nconc(*extras_before, cxt.blist);
***************
*** 2290,2298 ****
                                elog(ERROR, "Unexpected node type in ALTER 
TABLE ADD CONSTRAINT");
  
                        transformIndexConstraints(pstate, &cxt);
!                       transformFKConstraints(pstate, &cxt);
  
                        Assert(cxt.columns == NIL);
                        stmt->def = (Node *) nconc(cxt.ckconstraints, 
cxt.fkconstraints);
                        *extras_before = nconc(*extras_before, cxt.blist);
                        *extras_after = nconc(cxt.alist, *extras_after);
--- 2292,2301 ----
                                elog(ERROR, "Unexpected node type in ALTER 
TABLE ADD CONSTRAINT");
  
                        transformIndexConstraints(pstate, &cxt);
!                       transformFKConstraints(pstate, &cxt, true);
  
                        Assert(cxt.columns == NIL);
+                       /* fkconstraints should be put into my own stmt in this 
case */
                        stmt->def = (Node *) nconc(cxt.ckconstraints, 
cxt.fkconstraints);
                        *extras_before = nconc(*extras_before, cxt.blist);
                        *extras_after = nconc(cxt.alist, *extras_after);

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)