schema-qualified SET CONSTRAINTS
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
Attachments:
set-constraints-schema.patchtext/plain; charset=US-ASCII; name=set-constraints-schema.patchDownload
? 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);
Kris Jurka <books@ejurka.com> writes:
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.
This patch seems egregiously non backwards compatible :-(. A behavior
that would be backwards compatible is to retain the previous behavior
given an un-qualified name, while if given a schema-qualified name,
modify all matching constraints within that schema. That doesn't seem
very self-consistent though. A compromise that might succeed in making
*everybody* unhappy would be for the unqualified-name case to only
affect constraints that are visible in the current search path (but
affect all of them, not only one as in this patch).
Given the fundamental point that we don't insist on uniqueness of
constraint names within schemas, I'm not sure that the spec gives us
any useful guidance on what SET CONSTRAINTS should affect.
Anyway, I'm not sure what to do, but I am sure it requires some
discussion not just a patch.
regards, tom lane
On Mon, 10 Apr 2006, Tom Lane wrote:
Kris Jurka <books@ejurka.com> writes:
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.This patch seems egregiously non backwards compatible :-(.
Yes, it does change the existing behavior, but "egregiously"? How many
applications intentionally defer constraints in multiple schemas at once?
Not many. I would guess the more likely situation is that these
applications don't even realize that they are deferring more than one
constraint when it happens. So there will be some very minor pain when
they must select the desired constraint (if it doesn't happen already by
search_path) or explicitly defer more than one constraint, but I'm OK
with that. The existing behavior of SET CONSTRAINTS affecting everything
is not what a user would expect when we have tools like search_path
available.
Kris Jurka
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.
This patch seems egregiously non backwards compatible :-(.
Yes, it does change the existing behavior, but "egregiously"? How many
applications intentionally defer constraints in multiple schemas at
once?
intentionally defer "specifically named" constraints in multiple schemas
(The default application would imho eighter defer all, or a specific
constraint)
Not many. I would guess the more likely situation is that these
applications don't even realize that they are deferring more than one
constraint when it happens.
I agree. I think the new behavior is more intuitive, and would even
argue
the old behavior gets it wrong.
Andreas
Import Notes
Resolved by subject fallback
Based on discussion, it seems the idea of using search path seems
accepted.
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Kris Jurka wrote:
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
Content-Description:
[ Attachment, skipping... ]
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Patch applied. Thanks. I also updated our SGML documentation:
The current schema search path is used to find the first matching name
if no schema name is specified.
---------------------------------------------------------------------------
Kris Jurka wrote:
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
Content-Description:
[ Attachment, skipping... ]
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +