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
