The attached patch allows SET CONSTRAINTS to take a schema qualified constraint name (myschema.t1_fk_t2) and when given a bare constraint name it uses the search_path to determine the matching constraint instead of the previous behavior of disabling all identically named constraints.


Kris Jurka
? src/backend/parser/.deps
? src/backend/commands/.deps
? src/backend/commands/.trigger.c.swp
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.537
diff -c -r2.537 gram.y
*** src/backend/parser/gram.y   23 Mar 2006 00:19:29 -0000      2.537
--- src/backend/parser/gram.y   11 Apr 2006 00:01:54 -0000
***************
*** 1282,1288 ****
  
  constraints_set_list:
                        ALL                                                     
                        { $$ = NIL; }
!                       | name_list                                             
                { $$ = $1; }
                ;
  
  constraints_set_mode:
--- 1282,1288 ----
  
  constraints_set_list:
                        ALL                                                     
                        { $$ = NIL; }
!                       | qualified_name_list                                   
{ $$ = $1; }
                ;
  
  constraints_set_mode:
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.306
diff -c -r1.306 parsenodes.h
*** src/include/nodes/parsenodes.h      23 Mar 2006 00:19:30 -0000      1.306
--- src/include/nodes/parsenodes.h      11 Apr 2006 00:01:54 -0000
***************
*** 1803,1809 ****
  typedef struct ConstraintsSetStmt
  {
        NodeTag         type;
!       List       *constraints;        /* List of names as Value strings */
        bool            deferred;
  } ConstraintsSetStmt;
  
--- 1803,1809 ----
  typedef struct ConstraintsSetStmt
  {
        NodeTag         type;
!       List       *constraints;        /* List of names as RangeVars */
        bool            deferred;
  } ConstraintsSetStmt;
  
Index: doc/src/sgml/ref/set_constraints.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/set_constraints.sgml,v
retrieving revision 1.12
diff -c -r1.12 set_constraints.sgml
*** doc/src/sgml/ref/set_constraints.sgml       10 Sep 2004 18:39:53 -0000      
1.12
--- doc/src/sgml/ref/set_constraints.sgml       11 Apr 2006 00:01:54 -0000
***************
*** 93,105 ****
     foreign-key constraints.
    </para>
  
-   <para>
-    The SQL standard says that constraint names appearing in <command>SET
-    CONSTRAINTS</command> can be schema-qualified.  This is not yet
-    supported by <productname>PostgreSQL</productname>: the names must
-    be unqualified, and all constraints matching the command will be
-    affected no matter which schema they are in.
-   </para>
   </refsect1>
  </refentry>
  
--- 93,98 ----
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.200
diff -c -r1.200 trigger.c
*** src/backend/commands/trigger.c      5 Mar 2006 15:58:25 -0000       1.200
--- src/backend/commands/trigger.c      11 Apr 2006 00:01:54 -0000
***************
*** 24,29 ****
--- 24,30 ----
  #include "catalog/pg_proc.h"
  #include "catalog/pg_trigger.h"
  #include "catalog/pg_type.h"
+ #include "commands/dbcommands.h"
  #include "commands/defrem.h"
  #include "commands/trigger.h"
  #include "executor/executor.h"
***************
*** 37,42 ****
--- 38,44 ----
  #include "utils/inval.h"
  #include "utils/lsyscache.h"
  #include "utils/memutils.h"
+ #include "utils/relcache.h"
  #include "utils/syscache.h"
  
  
***************
*** 2922,2986 ****
  
                foreach(l, stmt->constraints)
                {
!                       char       *cname = strVal(lfirst(l));
                        ScanKeyData skey;
                        SysScanDesc tgscan;
                        HeapTuple       htup;
                        bool            found;
  
!                       /*
!                        * Check that only named constraints are set explicitly
!                        */
!                       if (strlen(cname) == 0)
!                               ereport(ERROR,
!                                               (errcode(ERRCODE_INVALID_NAME),
!                                       errmsg("unnamed constraints cannot be 
set explicitly")));
  
!                       /*
!                        * Setup to scan pg_trigger by tgconstrname ...
                         */
!                       ScanKeyInit(&skey,
!                                               Anum_pg_trigger_tgconstrname,
!                                               BTEqualStrategyNumber, F_NAMEEQ,
!                                               PointerGetDatum(cname));
! 
!                       tgscan = systable_beginscan(tgrel, 
TriggerConstrNameIndexId, true,
!                                                                               
SnapshotNow, 1, &skey);
  
-                       /*
-                        * ... and search for the constraint trigger row
-                        */
                        found = false;
! 
!                       while (HeapTupleIsValid(htup = 
systable_getnext(tgscan)))
                        {
!                               Form_pg_trigger pg_trigger = (Form_pg_trigger) 
GETSTRUCT(htup);
  
                                /*
!                                * If we found some, check that they fit the 
deferrability but
!                                * skip referential action ones, since they are 
silently never
!                                * deferrable.
                                 */
!                               if (pg_trigger->tgfoid != 
F_RI_FKEY_RESTRICT_UPD &&
!                                       pg_trigger->tgfoid != 
F_RI_FKEY_RESTRICT_DEL &&
!                                       pg_trigger->tgfoid != 
F_RI_FKEY_CASCADE_UPD &&
!                                       pg_trigger->tgfoid != 
F_RI_FKEY_CASCADE_DEL &&
!                                       pg_trigger->tgfoid != 
F_RI_FKEY_SETNULL_UPD &&
!                                       pg_trigger->tgfoid != 
F_RI_FKEY_SETNULL_DEL &&
!                                       pg_trigger->tgfoid != 
F_RI_FKEY_SETDEFAULT_UPD &&
!                                       pg_trigger->tgfoid != 
F_RI_FKEY_SETDEFAULT_DEL)
                                {
!                                       if (stmt->deferred && 
!pg_trigger->tgdeferrable)
!                                               ereport(ERROR,
!                                                               
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
!                                                                
errmsg("constraint \"%s\" is not deferrable",
!                                                                               
cname)));
!                                       oidlist = lappend_oid(oidlist, 
HeapTupleGetOid(htup));
                                }
!                               found = true;
                        }
  
!                       systable_endscan(tgscan);
  
                        /*
                         * Not found ?
--- 2924,3056 ----
  
                foreach(l, stmt->constraints)
                {
!                       RangeVar   *constraint = lfirst(l);
                        ScanKeyData skey;
                        SysScanDesc tgscan;
                        HeapTuple       htup;
                        bool            found;
+                       List       *namespaceSearchList;
+                       ListCell   *namespaceSearchCell;
  
!                       if (constraint->catalogname)
!                       {
!                               if (strcmp(constraint->catalogname, 
get_database_name(MyDatabaseId)) != 0)
!                                       ereport(ERROR,
!                                                       
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
!                                                        errmsg("cross-database 
references are not implemented: \"%s.%s.%s\"",
!                                                                       
constraint->catalogname, constraint->schemaname,
!                                                                       
constraint->relname)));
!                       }
  
!                       /* 
!                        * If we're given the schema name with the constraint, 
look only
!                        * in that schema.  If given a bare constraint name, 
use the
!                        * search path to find the first matching constraint.
                         */
!                       if (constraint->schemaname) {
!                               Oid namespaceId = 
LookupExplicitNamespace(constraint->schemaname);
!                               namespaceSearchList = 
list_make1_oid(namespaceId);
!                       } else {
!                               namespaceSearchList = fetch_search_path(true);
!                       }
  
                        found = false;
!                       foreach(namespaceSearchCell, namespaceSearchList)
                        {
!                               Oid searchNamespaceId = 
lfirst_oid(namespaceSearchCell);
! 
!                               /*
!                                * Setup to scan pg_trigger by tgconstrname ...
!                                */
!                               ScanKeyInit(&skey,
!                                                       
Anum_pg_trigger_tgconstrname,
!                                                       BTEqualStrategyNumber, 
F_NAMEEQ,
!                                                       
PointerGetDatum(constraint->relname));
! 
!                               tgscan = systable_beginscan(tgrel, 
TriggerConstrNameIndexId, true,
!                                                                               
        SnapshotNow, 1, &skey);
  
                                /*
!                                * ... and search for the constraint trigger row
                                 */
!                               while (HeapTupleIsValid(htup = 
systable_getnext(tgscan)))
                                {
!                                       Form_pg_trigger pg_trigger = 
(Form_pg_trigger) GETSTRUCT(htup);
!                                       Relation constraintRel;
!                                       Oid constraintNamespaceId;
! 
!                                       /*
!                                        * Foreign key constraints have 
triggers on both the
!                                        * parent and child tables.  Since 
these tables may be
!                                        * in different schemas we must pick 
the child table
!                                        * because that table "owns" the 
constraint.
!                                        *
!                                        * Referential triggers on the parent 
table other than
!                                        * NOACTION_DEL and NOACTION_UPD are 
ignored below, so
!                                        * it is possible to not check them 
here, but it seems
!                                        * safer to always check.
!                                        */
!                                       if (pg_trigger->tgfoid == 
F_RI_FKEY_NOACTION_DEL ||
!                                               pg_trigger->tgfoid == 
F_RI_FKEY_NOACTION_UPD ||
!                                               pg_trigger->tgfoid == 
F_RI_FKEY_RESTRICT_UPD ||
!                                               pg_trigger->tgfoid == 
F_RI_FKEY_RESTRICT_DEL ||
!                                               pg_trigger->tgfoid == 
F_RI_FKEY_CASCADE_UPD ||
!                                               pg_trigger->tgfoid == 
F_RI_FKEY_CASCADE_DEL ||
!                                               pg_trigger->tgfoid == 
F_RI_FKEY_SETNULL_UPD ||
!                                               pg_trigger->tgfoid == 
F_RI_FKEY_SETNULL_DEL ||
!                                               pg_trigger->tgfoid == 
F_RI_FKEY_SETDEFAULT_UPD ||
!                                               pg_trigger->tgfoid == 
F_RI_FKEY_SETDEFAULT_DEL)
!                                       {
!                                               constraintRel = 
RelationIdGetRelation(pg_trigger->tgconstrrelid);
!                                       } else {
!                                               constraintRel = 
RelationIdGetRelation(pg_trigger->tgrelid);
!                                       }
!                                       constraintNamespaceId = 
RelationGetNamespace(constraintRel);
!                                       RelationClose(constraintRel);
! 
!                                       /*
!                                        * If this constraint is not in the 
schema we're
!                                        * currently searching for, keep 
looking.
!                                        */
!                                       if (constraintNamespaceId != 
searchNamespaceId)
!                                               continue;
! 
!                                       /*
!                                        * If we found some, check that they 
fit the deferrability but
!                                        * skip referential action ones, since 
they are silently never
!                                        * deferrable.
!                                        */
!                                       if (pg_trigger->tgfoid != 
F_RI_FKEY_RESTRICT_UPD &&
!                                               pg_trigger->tgfoid != 
F_RI_FKEY_RESTRICT_DEL &&
!                                               pg_trigger->tgfoid != 
F_RI_FKEY_CASCADE_UPD &&
!                                               pg_trigger->tgfoid != 
F_RI_FKEY_CASCADE_DEL &&
!                                               pg_trigger->tgfoid != 
F_RI_FKEY_SETNULL_UPD &&
!                                               pg_trigger->tgfoid != 
F_RI_FKEY_SETNULL_DEL &&
!                                               pg_trigger->tgfoid != 
F_RI_FKEY_SETDEFAULT_UPD &&
!                                               pg_trigger->tgfoid != 
F_RI_FKEY_SETDEFAULT_DEL)
!                                       {
!                                               if (stmt->deferred && 
!pg_trigger->tgdeferrable)
!                                                       ereport(ERROR,
!                                                                       
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
!                                                                        
errmsg("constraint \"%s\" is not deferrable",
!                                                                               
        constraint->relname)));
!                                               oidlist = lappend_oid(oidlist, 
HeapTupleGetOid(htup));
!                                       }
!                                       found = true;
                                }
! 
!                               systable_endscan(tgscan);
! 
!                               /*
!                                * Once we've found a matching constraint we do 
not search
!                                * later parts of the search path.
!                                */
!                               if (found)
!                                       break;
! 
                        }
  
!                       list_free(namespaceSearchList);
  
                        /*
                         * Not found ?
***************
*** 2989,2995 ****
                                ereport(ERROR,
                                                
(errcode(ERRCODE_UNDEFINED_OBJECT),
                                                 errmsg("constraint \"%s\" does 
not exist",
!                                                               cname)));
                }
                heap_close(tgrel, AccessShareLock);
  
--- 3059,3065 ----
                                ereport(ERROR,
                                                
(errcode(ERRCODE_UNDEFINED_OBJECT),
                                                 errmsg("constraint \"%s\" does 
not exist",
!                                                               
constraint->relname)));
                }
                heap_close(tgrel, AccessShareLock);
  
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to