Dependency / Constraint patch - Mailing list pgsql-patches
| From | Rod Taylor |
|---|---|
| Subject | Dependency / Constraint patch |
| Date | |
| Msg-id | 3D0B9112.6010201@zort.ca Whole thread Raw |
| Responses |
Re: Dependency / Constraint patch
|
| List | pgsql-patches |
Differences from previous version:
- Fully functional ALTER TABLE / DROP CONSTRAINT
- pg_dump uses ALTER TABLE / ADD FOREIGN KEY
- psql displays foreign keys (\d output)
- Foreign key triggers are autonamed based on the constraint name
- Namespace dependencies were quickly added. Unable to test them very
well (DROP SCHEMA required)
Postgresql TODO items completed (or very close):
# Add ALTER TABLE DROP non-CHECK CONSTRAINT
# Allow psql \d to show foreign keys
* Add pg_depend table for dependency recording; use sysrelid, oid,
depend_sysrelid, depend_oid, name
# Auto-destroy sequence on DROP of table with SERIAL
# Prevent column dropping if column is used by foreign key
# Automatically drop constraints/functions when object is dropped
# Make constraints clearer in dump file
# Make foreign keys easier to identify
The locking of relations may not be as strong as it should be. I was
unable to cause failure -- and can't see what it would be missing but it
has been bothering me.
I've not touched pg_dump for SERIAL stuff. I may do it later.
Basic documentation updates included. I'll scour for examples or notes
which may no longer apply in a couple of weeks.
Attached files:
TODO.depend - A short list of items I completed, notes, any assumptions,
and possible outstanding items
src/backend/catalog/pg_constraint.c
src/backend/catalog/pg_depend.c
src/include/catalog/pg_constraint.h
src/include/catalog/pg_depend.h
src/test/regress/expected/drop.out
Remove:
src/backend/catalog/pg_relcheck.c
src/include/catalog/pg_relcheck.h
CHANGES
-------
initdb process has been changed in an attempt to automatically pin some basic types.
pg_type and pg_proc currently.
pg_relcheck replaced with more generic pg_constraint.
Nearly all objects have an enforced RESTRICT / CASCADE except for in 'compiled' expressions.
Ie. Views, function contents, default expressions,
[-] completed
[*] yet to do
- Create Type
- Create View
* Create View (on precompile set deps in source)
- Create Trigger
- Create Table (Columns on Types)
* Create Table / Column Defaults (on precompile set deps in source)
- Create Sequence (currval, nextval, setval are PINNED)
- Create Rule (always cascade)
- Create Operator
- Create Language
- Create Index
- Create Function
* Create Function (on precompile set additional deps in source)
- Create Aggregate
- Drop Type (regress tested)
- Drop View
- Drop Trigger
- Drop Table
- Drop Sequence
- Drop Rule
- Drop Operator
- Drop Language
- Drop Index
- Drop Function (regress tested)
- Drop Aggregate
- Alter Table / Primary Key
- Alter Table / unique index
* Alter Table / Default (Compiled default depends on functions / types within)
- Alter Table / Add Column
- Alter table / add column which creates toast table
- Alter Table / Drop Constraint (Fixed to function as expected)
- Drop pg_relcheck
- Create pg_constraint
- Insert check constraints into pg_constraint
- Insert unique constraints into pg_constraint
- Have unique key indicies depend on pg_constraint (implicit cascade)
- Insert primary key constraints into pg_constraint
- Have primary key indicies depend on pg_constraint (implicit cascade)
- Insert foreign key constraints into pg_constraint
- Have foreign key triggers depend on pg_constraint
- Have pg_constraint depend on the table columns (not the table itself)
- heap.c - Drop RemoveRelChecks()
- pg_constraint - ConstraintCreate dependencies
- Base type dependency on array type managed by pg_depend (always cascaded)
- Table drop foreign key triggers as managed by pg_depend (always cascaded)
- Toast tables depend on relation (always cascade)
- Enable opt_behaviour for most items in gram.y
- Disallow base functionality (types, procedures, and catalogs required for operation) to be dropped ever
- Implicit drop of a SERIALs sequence (regress tested)
- Alter Table / Drop Constraint (tablecmds->AlterTableDropConstraint)
- Enable psql to view check and foreign key constraints (\d) on table definition
- Have pg_dump use ALTER TABLE commands for Foreign Keys
- Move Foreign Key constraint trigger creation to constraintCreate() from analyze.c.
- Name triggers after the constraints -- but append a number and guarentee uniqueness
OTHER NOTES
-----------
CREATE TABLE tab (col1 int4 DEFAULT nextval('seq'));
- DROP FUNCTION nextval(text) CASCADE;
- Drop the column (col1) or set the default to NULL?
Do objects depend on users (ownership)? ie. DROP USER CASCADE to dump everything they own when they're removed?
Unique constraints for indicies have unique names across BOTH pg_constraint and pg_index. pg_constraint
is unique to relid and index name so it's not that bad.
One can drop a unique index without the constraint being dropped (DOH!). Attempts to fix cause circular dependency.
ALTER TABLE DROP COLUMN should allow foreign key relations to only drop the foreign column, not the whole relation.
CASCADEd drops should occur regardless of ownership of objects other than the one specifically specified (parent of
cascade)
Change foreign keys to work through a set of constraint functions using definitions from pg_constraint rather than
doingwork in the parser.
/*-------------------------------------------------------------------------
*
* pg_constraint.c
* routines to support manipulation of the pg_namespace relation
*
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
*
* IDENTIFICATION
* $Header$
*
*-------------------------------------------------------------------------
*/
#include "postgres.h"
#include "access/heapam.h"
#include "access/genam.h"
#include "catalog/catname.h"
#include "catalog/indexing.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_depend.h"
#include "commands/trigger.h"
#include "nodes/makefuncs.h"
#include "nodes/parsenodes.h"
#include "parser/gramparse.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
/*
* ConstraintCreate
* Create the constraint table portion as well as any dependencies.
*/
Oid
constraintCreate(Oid relId,
const char *constraintName,
char constraintType,
bool isDeferrable,
bool isDeferred,
const AttrNumber *constraintKey,
int constraintNKeys,
Oid foreignRelId,
const AttrNumber *foreignKey,
int foreignNKeys,
char foreignUpdateType,
char foreignDeleteType,
char foreignMatchType,
char *conBin,
char *conSrc)
{
Relation conDesc;
HeapTuple tup;
char nulls[Natts_pg_constraint];
Datum values[Natts_pg_constraint];
int i = 0;
Oid conOid;
Datum conkey[constraintNKeys];
Datum confkey[foreignNKeys];
SysScanDesc rcscan;
ScanKeyData skey[2];
ObjectAddress myself,
dependee;
NameData cname;
conDesc = heap_openr(ConstraintRelationName, RowExclusiveLock);
/* sanity checks */
if (!constraintName)
{
namestrcpy(&cname, getConstraintName(relId));
}
else
{
namestrcpy(&cname, constraintName);
/* make sure there is no existing constraints of the same name */
ScanKeyEntryInitialize(&skey[i++],
0, Anum_pg_constraint_conrelid, F_OIDEQ,
ObjectIdGetDatum(relId));
ScanKeyEntryInitialize(&skey[i++],
0, Anum_pg_constraint_conname, F_NAMEEQ,
NameGetDatum(&cname));
rcscan = systable_beginscan(conDesc, ConstraintRelidNameIndex, true,
SnapshotNow,
i, skey);
tup = systable_getnext(rcscan);
if (HeapTupleIsValid(tup))
elog(ERROR, "constraint \"%s\" already exists", NameStr(cname));
systable_endscan(rcscan);
}
/* Build Datum array for ConstraintKey */
for (i = 0; i < constraintNKeys; i++)
conkey[i] = Int16GetDatum(constraintKey[i]);
/*
* Build Datum array for foreignKey. Use a
* placeholder entry if otherwise NULL.
*/
if (foreignNKeys && foreignNKeys > 0)
for (i = 0; i < foreignNKeys; i++)
confkey[i] = Int16GetDatum(foreignKey[i]);
else
confkey[0] = Int16GetDatum(0);
/* initialize nulls and values */
for (i = 0; i < Natts_pg_constraint; i++)
{
nulls[i] = ' ';
values[i] = (Datum) NULL;
}
values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId);
values[Anum_pg_constraint_conname - 1] = NameGetDatum(&cname);
values[Anum_pg_constraint_contype - 1] = CharGetDatum(constraintType);
values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable);
values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred);
values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(construct_array(conkey,
constraintNKeys,
true, 2, 'i'));
/* Record what we were given, or a placeholder if NULL */
if (foreignRelId)
values[Anum_pg_constraint_confrelid - 1] = ObjectIdGetDatum(foreignRelId);
else
values[Anum_pg_constraint_confrelid - 1] = ObjectIdGetDatum(InvalidOid);
/* Record what we were given, or a placeholder if NULL */
values[Anum_pg_constraint_confkey - 1] = PointerGetDatum(construct_array(confkey,
foreignNKeys > 0 ? foreignNKeys : 1,
true, 2, 'i'));
/* Record what we were given, or a placeholder if NULL */
if (foreignUpdateType)
values[Anum_pg_constraint_confupdtype - 1] = CharGetDatum(foreignUpdateType);
else
values[Anum_pg_constraint_confupdtype - 1] = CharGetDatum(CONSTRAINT_FKEY_RESTRICT);
/* Record what we were given, or a placeholder if NULL */
if (foreignDeleteType)
values[Anum_pg_constraint_confdeltype - 1] = CharGetDatum(foreignDeleteType);
else
values[Anum_pg_constraint_confdeltype - 1] = CharGetDatum(CONSTRAINT_FKEY_RESTRICT);
/* Record what we were given, or a placeholder if NULL */
if (foreignMatchType)
values[Anum_pg_constraint_confmatchtype - 1] = CharGetDatum(foreignMatchType);
else
values[Anum_pg_constraint_confmatchtype - 1] = CharGetDatum(CONSTRAINT_FKEY_FULL);
/*
* initialize the binary form of the check constraint.
*/
if (conBin)
values[Anum_pg_constraint_conbin - 1] = DirectFunctionCall1(textin,
CStringGetDatum(conBin));
else
nulls[Anum_pg_constraint_conbin - 1] = 'n';
/*
* initialize the text form of the check constraint
*/
if(conSrc)
values[Anum_pg_constraint_consrc - 1] = DirectFunctionCall1(textin,
CStringGetDatum(conSrc));
else
nulls[Anum_pg_constraint_consrc - 1] = 'n';
tup = heap_formtuple(RelationGetDescr(conDesc), values, nulls);
if (!HeapTupleIsValid(tup))
elog(ERROR, "ConstraintCreate: heap_formtuple failed");
conOid = simple_heap_insert(conDesc, tup);
if (!OidIsValid(conOid))
elog(ERROR, "ConstraintCreate: heap_insert failed");
/* Handle Indicies */
if (RelationGetForm(conDesc)->relhasindex)
{
Relation idescs[Num_pg_constraint_indices];
CatalogOpenIndices(Num_pg_constraint_indices, Name_pg_constraint_indices, idescs);
CatalogIndexInsert(idescs, Num_pg_constraint_indices, conDesc, tup);
CatalogCloseIndices(Num_pg_constraint_indices, idescs);
}
/*
* Handle Dependencies
*/
myself.classId = RelationGetRelid(conDesc);
myself.objectId = conOid;
myself.objectSubId = 0;
/* The constraint depends on the relation */
dependee.classId = RelOid_pg_class;
dependee.objectId = relId;
dependee.objectSubId = 0;
dependCreate(&myself, &dependee, true);
/*
* The constraint depends on the foreign relation columns
*
* Relation dependencies are skipped if we depend
* directly on ourselves
*/
if (foreignNKeys && foreignNKeys > 0
&& relId != foreignRelId)
{
Assert(foreignNKeys = constraintNKeys);
for (i = 0; i < foreignNKeys; i++)
{
ObjectAddress depender;
depender.classId = RelOid_pg_class;
depender.objectId = relId;
depender.objectSubId = conkey[i];
dependee.classId = RelOid_pg_class;
dependee.objectId = foreignRelId;
dependee.objectSubId = foreignKey[i];
dependCreate(&depender, &dependee, false);
dependCreate(&myself, &dependee, true);
}
}
/*
* Create the required triggers to enforce the requested
* foreign key constraint. Record dependencies of the
* trigger to the FK Constraint.
*/
if (foreignNKeys && foreignNKeys > 0)
{
CreateTrigStmt *fk_trigger;
Oid trigId;
RangeVar *foreignRel;
RangeVar *localRel;
char *foreignNameSpace;
char *localNameSpace;
char *foreignRelation;
char *localRelation;
char *mType = "UNSPECIFIED";
/* Pull relation names */
foreignNameSpace = get_namespace_name(get_rel_namespace(foreignRelId));
localNameSpace = get_namespace_name(get_rel_namespace(relId));
foreignRelation = get_rel_name(foreignRelId);
localRelation = get_rel_name(relId);
localRel = makeRangeVar(localNameSpace, localRelation);
foreignRel = makeRangeVar(foreignNameSpace, foreignRelation);
/* Find the trigger name for match types */
switch (foreignMatchType)
{
case CONSTRAINT_FKEY_FULL:
mType = "FULL";
break;
case CONSTRAINT_FKEY_PARTIAL:
mType = "PARTIAL";
break;
case CONSTRAINT_FKEY_UNSPECIFIED:
mType = "UNSPECIFIED";
break;
default:
elog(ERROR, "constraintCreate: Unknown MATCH TYPE");
}
/* Double check keys align */
Assert(foreignNKeys = constraintNKeys);
/*
* Build a CREATE CONSTRAINT TRIGGER statement for the CHECK
* action.
*/
fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt);
fk_trigger->trigname = getTriggerName(relId, NameStr(cname));
fk_trigger->relation = localRel;
fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins");
fk_trigger->before = false;
fk_trigger->row = true;
fk_trigger->actions[0] = 'i';
fk_trigger->actions[1] = 'u';
fk_trigger->actions[2] = '\0';
fk_trigger->lang = NULL;
fk_trigger->text = NULL;
fk_trigger->attr = NIL;
fk_trigger->when = NULL;
fk_trigger->isconstraint = true;
fk_trigger->deferrable = isDeferrable;
fk_trigger->initdeferred = isDeferred;
fk_trigger->constrrel = foreignRel;
fk_trigger->args = NIL;
fk_trigger->args = lappend(fk_trigger->args,
makeString(NameStr(cname)));
fk_trigger->args = lappend(fk_trigger->args,
makeString(localRel->relname));
fk_trigger->args = lappend(fk_trigger->args,
makeString(foreignRel->relname));
fk_trigger->args = lappend(fk_trigger->args,
makeString(mType));
for (i = 0; i < foreignNKeys; i++)
{
fk_trigger->args = lappend(fk_trigger->args,
makeString(get_attname(relId, constraintKey[i])));
fk_trigger->args = lappend(fk_trigger->args,
makeString(get_attname(foreignRelId, foreignKey[i])));
}
trigId = CreateTrigger(fk_trigger);
/* The trigger depends on the constraint */
dependee.classId = get_relname_relid(TriggerRelationName, PG_CATALOG_NAMESPACE);;
dependee.objectId = trigId;
dependee.objectSubId = 0;
dependCreate(&dependee, &myself, true);
/*
* Bump the command counter to prevent the next trigger
* from attempting to use the same name as the previous
*/
CommandCounterIncrement();
/*
* Build a CREATE CONSTRAINT TRIGGER statement for the ON DELETE
* action fired on the PK table !!!
*/
fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt);
fk_trigger->trigname = getTriggerName(foreignRelId, NameStr(cname));
fk_trigger->relation = foreignRel;
fk_trigger->before = false;
fk_trigger->row = true;
fk_trigger->actions[0] = 'd';
fk_trigger->actions[1] = '\0';
fk_trigger->lang = NULL;
fk_trigger->text = NULL;
fk_trigger->attr = NIL;
fk_trigger->when = NULL;
fk_trigger->isconstraint = true;
fk_trigger->deferrable = isDeferrable;
fk_trigger->initdeferred = isDeferred;
fk_trigger->constrrel = localRel;
switch (foreignDeleteType)
{
case CONSTRAINT_FKEY_NOACTION:
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_del");
break;
case CONSTRAINT_FKEY_RESTRICT:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_del");
break;
case CONSTRAINT_FKEY_CASCADE:
fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del");
break;
case CONSTRAINT_FKEY_NULL:
fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del");
break;
case CONSTRAINT_FKEY_DEFAULT:
fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_del");
break;
}
fk_trigger->args = NIL;
fk_trigger->args = lappend(fk_trigger->args,
makeString(NameStr(cname)));
fk_trigger->args = lappend(fk_trigger->args,
makeString(localRel->relname));
fk_trigger->args = lappend(fk_trigger->args,
makeString(foreignRel->relname));
fk_trigger->args = lappend(fk_trigger->args,
makeString(mType));
for (i = 0; i < foreignNKeys; i++)
{
fk_trigger->args = lappend(fk_trigger->args,
makeString(get_attname(relId, constraintKey[i])));
fk_trigger->args = lappend(fk_trigger->args,
makeString(get_attname(foreignRelId, foreignKey[i])));
}
trigId = CreateTrigger(fk_trigger);
/* The trigger depends on the constraint */
dependee.classId = get_relname_relid(TriggerRelationName, PG_CATALOG_NAMESPACE);;
dependee.objectId = trigId;
dependee.objectSubId = 0;
dependCreate(&dependee, &myself, true);
/*
* Bump the command counter to prevent the next trigger
* from attempting to use the same name as the previous
*/
CommandCounterIncrement();
/*
* Build a CREATE CONSTRAINT TRIGGER statement for the ON UPDATE
* action fired on the PK table !!!
*/
fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt);
fk_trigger->trigname = getTriggerName(foreignRelId, NameStr(cname));
fk_trigger->relation = foreignRel;
fk_trigger->before = false;
fk_trigger->row = true;
fk_trigger->actions[0] = 'u';
fk_trigger->actions[1] = '\0';
fk_trigger->lang = NULL;
fk_trigger->text = NULL;
fk_trigger->attr = NIL;
fk_trigger->when = NULL;
fk_trigger->isconstraint = true;
fk_trigger->deferrable = isDeferrable;
fk_trigger->initdeferred = isDeferred;
fk_trigger->constrrel = localRel;
switch (foreignUpdateType)
{
case CONSTRAINT_FKEY_NOACTION:
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_upd");
break;
case CONSTRAINT_FKEY_RESTRICT:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_upd");
break;
case CONSTRAINT_FKEY_CASCADE:
fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_upd");
break;
case CONSTRAINT_FKEY_NULL:
fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_upd");
break;
case CONSTRAINT_FKEY_DEFAULT:
fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_upd");
break;
}
fk_trigger->args = NIL;
fk_trigger->args = lappend(fk_trigger->args,
makeString(NameStr(cname)));
fk_trigger->args = lappend(fk_trigger->args,
makeString(localRel->relname));
fk_trigger->args = lappend(fk_trigger->args,
makeString(foreignRel->relname));
fk_trigger->args = lappend(fk_trigger->args,
makeString(mType));
for (i = 0; i < foreignNKeys; i++)
{
fk_trigger->args = lappend(fk_trigger->args,
makeString(get_attname(relId, constraintKey[i])));
fk_trigger->args = lappend(fk_trigger->args,
makeString(get_attname(foreignRelId, foreignKey[i])));
}
trigId = CreateTrigger(fk_trigger);
/* The trigger depends on the constraint */
dependee.classId = get_relname_relid(TriggerRelationName, PG_CATALOG_NAMESPACE);;
dependee.objectId = trigId;
dependee.objectSubId = 0;
dependCreate(&dependee, &myself, true);
}
/* Cleanup, but keep lock */
heap_close(conDesc, NoLock);
return conOid;
}
char *
getConstraintName(Oid relId)
{
int j = 1;
bool success;
Relation conDesc;
HeapTuple tup;
char *cname;
cname = palloc(NAMEDATALEN * sizeof(char));
conDesc = heap_openr(ConstraintRelationName, RowExclusiveLock);
/* Loop until we find a non-conflicting constraint name */
/* What happens if this loops forever? */
do
{
int i = 0;
SysScanDesc rcscan;
ScanKeyData skey[2];
success = false;
snprintf(cname, NAMEDATALEN, "constraint_%d", j);
/* make sure there is no existing constraints of the same name */
ScanKeyEntryInitialize(&skey[i++],
0, Anum_pg_constraint_conrelid, F_OIDEQ,
ObjectIdGetDatum(relId));
ScanKeyEntryInitialize(&skey[i++],
0, Anum_pg_constraint_conname, F_NAMEEQ,
NameGetDatum(cname));
rcscan = systable_beginscan(conDesc, ConstraintRelidNameIndex, true,
SnapshotNow,
i, skey);
tup = systable_getnext(rcscan);
if (!HeapTupleIsValid(tup))
success = true;
systable_endscan(rcscan);
++j;
} while (!success);
/* Cleanup, but keep lock */
heap_close(conDesc, NoLock);
return cname;
}
void
DropConstraintById(Oid conId, int behavior)
{
ObjectAddress myself;
Relation conDesc;
HeapTuple tup;
ScanKeyData skey[1];
SysScanDesc rcscan;
int i = 0;
Relation ridescs[Num_pg_class_indices];
Form_pg_constraint con;
/* Better be a valid Id */
Assert(OidIsValid(conId));
/* CONSTRAINT */
ScanKeyEntryInitialize(&skey[i++],
0, ObjectIdAttributeNumber, F_OIDEQ,
ObjectIdGetDatum(conId));
conDesc = heap_openr(ConstraintRelationName, RowExclusiveLock);
rcscan = systable_beginscan(conDesc, ConstraintOidIndex, true,
SnapshotNow,
i, skey);
tup = systable_getnext(rcscan);
/*
* Due to circular constraint dependencies we simply
* skip the drop when we don't find the constraint rather
* than the below:
*
*
*/
if (!HeapTupleIsValid(tup))
elog(ERROR, "Constraint OID %d missing", conId);
con = (Form_pg_constraint) GETSTRUCT(tup);
/*
* Now we need to update the relcheck count
* if it was a check constraint being dropped
*/
if (con->contype == CONSTRAINT_CHECK)
{
Relation rel;
HeapTuple relTup;
rel = heap_openr(RelationRelationName, RowExclusiveLock);
relTup = SearchSysCache(RELOID,
ObjectIdGetDatum(con->conrelid),
0, 0, 0);
if (!HeapTupleIsValid(relTup))
elog(ERROR, "DropConstraintById: Relation Tuple non-existant");
((Form_pg_class) GETSTRUCT(relTup))->relchecks -= 1;
simple_heap_update(rel, &relTup->t_self, relTup);
CatalogOpenIndices(Num_pg_class_indices, Name_pg_class_indices, ridescs);
CatalogIndexInsert(ridescs, Num_pg_class_indices, rel, relTup);
CatalogCloseIndices(Num_pg_class_indices, ridescs);
ReleaseSysCache(relTup);
heap_close(rel, RowExclusiveLock);
}
/* Fry the constraint itself*/
simple_heap_delete(conDesc, &tup->t_self);
/* Clean up */
systable_endscan(rcscan);
heap_close(conDesc, RowExclusiveLock);
/* Deal with dependencies */
myself.classId = get_relname_relid(ConstraintRelationName, PG_CATALOG_NAMESPACE);
myself.objectId = conId;
myself.objectSubId = 0;
dependDelete(&myself, behavior);
};
/*-------------------------------------------------------------------------
*
* depend.c
* random postgres portal and utility support code
*
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
*
* IDENTIFICATION
* $Header$
*
* NOTES
* Manage dependencies between varying system objects.
*
*-------------------------------------------------------------------------
*/
#include "postgres.h"
#include "miscadmin.h"
#include "access/heapam.h"
#include "access/genam.h"
#include "catalog/catname.h"
#include "catalog/index.h"
#include "catalog/indexing.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_language.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/comment.h"
#include "commands/defrem.h"
#include "commands/tablecmds.h"
#include "commands/trigger.h"
#include "commands/view.h"
#include "nodes/parsenodes.h"
#include "nodes/pg_list.h"
#include "nodes/makefuncs.h"
#include "rewrite/rewriteRemove.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
static char *getObjectName(const ObjectAddress *object);
static char *getObjectType(const ObjectAddress *object);
static bool isObjectPinned(const ObjectAddress *object, Relation rel);
static bool isStructureOfPin(const ObjectAddress *object);
/*
* Records a requested dependency between 2 objects via their
* respective objectAddress.
*
* It makes the assumption that both objects currently (or will)
* exist before the end of the transaction.
*
* Behaviour, if true tells dependDelete to ignore RESTRICT as
* the issued behaviour at the time and cascade to the object
* anyway. The reason for this is sequences generated by SERIAL
* and array types.
*/
void
dependCreate(const ObjectAddress *depender,
const ObjectAddress *dependee,
bool behavior)
{
if (!IsBootstrapProcessingMode())
{
Relation dependDesc;
TupleDesc tupDesc;
HeapTuple tup;
int i;
char nulls[Natts_pg_depend];
Datum values[Natts_pg_depend];
for (i = 0; i < Natts_pg_depend; ++i)
{
nulls[i] = ' ';
values[i] = (Datum) 0;
}
dependDesc = heap_openr(DependRelationName, RowExclusiveLock);
/* Test to see if the object is pinned (permenant) */
if (!isObjectPinned(dependee, dependDesc))
{
Relation idescs[Num_pg_depend_indices];
/*
* Record the Dependency. Assume it can be added, and
* doesn't previously exist. Some items (type creation)
* may add duplicates.
*/
values[Anum_pg_depend_classid - 1] = ObjectIdGetDatum(depender->classId);
values[Anum_pg_depend_objid - 1] = ObjectIdGetDatum(depender->objectId);
values[Anum_pg_depend_objsubid - 1] = Int32GetDatum(depender->objectSubId);
values[Anum_pg_depend_depclassid - 1] = ObjectIdGetDatum(dependee->classId);
values[Anum_pg_depend_depobjid - 1] = ObjectIdGetDatum(dependee->objectId);
values[Anum_pg_depend_depobjsubid - 1] = Int32GetDatum(dependee->objectSubId);
values[Anum_pg_depend_alwayscascade -1] = BoolGetDatum(behavior);
tupDesc = dependDesc->rd_att;
if (!HeapTupleIsValid(tup = heap_formtuple(tupDesc,
values,
nulls)))
elog(ERROR, "DependCreate: heap_formtuple failed");
simple_heap_insert(dependDesc, tup);
/*
* Keep indices current
*/
CatalogOpenIndices(Num_pg_depend_indices, Name_pg_depend_indices, idescs);
CatalogIndexInsert(idescs, Num_pg_depend_indices, dependDesc, tup);
CatalogCloseIndices(Num_pg_depend_indices, idescs);
}
heap_close(dependDesc, RowExclusiveLock); /* Required? */
}
}
/*
* Drops the interdependencies between the object and it's
* children depending on the behavior specified. RESTRICT or
* CASCADE types supported.
*
* RESTRICT will abort the transaction if other objects depend
* on this one.
*
* CASCADE will drop all objects which depend on the supplied
* object address.
*/
void
dependDelete(ObjectAddress *object, int behavior)
{
Relation rel;
ScanKeyData dropkey[3];
HeapTuple tup;
int dropnkeys = 0;
SysScanDesc scan;
bool deprem = true;
ObjectAddress objectCopy;
Assert(behavior == DEPEND_RESTRICT || behavior == DEPEND_CASCADE || behavior == DEPEND_IMPLICITONLY);
/*
* A copy of the object passed in needs to be taken, else we risk
* it being wiped from memory mid way through the drops.
*/
objectCopy.classId = object->classId;
objectCopy.objectId = object->objectId;
objectCopy.objectSubId = object->objectSubId;
/* Delete any comments associated with this object */
DeleteComments(objectCopy.objectId, objectCopy.classId, objectCopy.objectSubId);
/*
* Test whether object being dropped is a dependee
* or not.
*/
rel = heap_openr(DependRelationName, RowExclusiveLock);
/* If object is pinned dissallow it's removal */
if (isObjectPinned(&objectCopy, rel))
elog(ERROR, "Drop Restricted as %s %s is an essential for the database to function",
getObjectType(&objectCopy),
getObjectName(&objectCopy));
while (deprem)
{
ScanKeyData key[3];
ObjectAddress foundObject;
Form_pg_depend foundTup;
int nkeys = 0;
/* Class Oid */
Assert(objectCopy.classId != InvalidOid);
ScanKeyEntryInitialize(&key[nkeys++],
0, Anum_pg_depend_depclassid, F_OIDEQ,
ObjectIdGetDatum(objectCopy.classId));
/* Object Oid */
Assert(objectCopy.objectId != InvalidOid);
ScanKeyEntryInitialize(&key[nkeys++],
0, Anum_pg_depend_depobjid, F_OIDEQ,
ObjectIdGetDatum(objectCopy.objectId));
/* SubObject Id */
ScanKeyEntryInitialize(&key[nkeys++],
0, Anum_pg_depend_depobjsubid, F_INT4EQ,
Int32GetDatum(objectCopy.objectSubId));
scan = systable_beginscan(rel, DependDependeeIndex, true,
SnapshotNow, nkeys, key);
/*
* If no type tuple exists for the given type name, then end the scan
* and return appropriate information.
*/
tup = systable_getnext(scan);
if (!HeapTupleIsValid(tup))
{
deprem = false;
continue;
}
foundTup = (Form_pg_depend) GETSTRUCT(tup);
/*
* Lets load up and test the object which depends
* on the one we want to drop.
*/
foundObject.classId = foundTup->classid;
foundObject.objectId = foundTup->objid;
foundObject.objectSubId = foundTup->objsubid;
systable_endscan(scan);
/*
* If there are dependencies and behaviour is RESTRICT
* then drop them all.
*/
if (behavior == DEPEND_RESTRICT && !foundTup->alwayscascade)
elog(ERROR, "Drop Restricted as %s %s Depends on %s %s",
getObjectType(&foundObject),
getObjectName(&foundObject),
getObjectType(&objectCopy),
getObjectName(&objectCopy));
/*
* When IMPLICITONLY we don't want to cascade or restrict.
* Simply drop all items implicitly associated with this object.
*/
if (behavior == DEPEND_IMPLICITONLY && !foundTup->alwayscascade)
{
continue;
}
/* Tell the user */
if (foundTup->alwayscascade)
elog(DEBUG1, "Implicit drop of %s %s",
getObjectType(&foundObject),
getObjectName(&foundObject));
else
elog(NOTICE, "Cascading drop to %s %s",
getObjectType(&foundObject),
getObjectName(&foundObject));
/*
* The below functions are expected to cascade back here by calling
* dependDelete(). If they don't, a partial cascade can occur leaving
* poor relations in place.
*/
switch (foundObject.classId)
{
case RelOid_pg_proc:
RemoveFunctionById(foundObject.objectId, behavior);
break;
case RelOid_pg_class:
{
HeapTuple relTup;
char relKind;
char *relName;
char *schemaName;
relTup = SearchSysCache(RELOID,
ObjectIdGetDatum(foundObject.objectId),
0, 0, 0);
if (!HeapTupleIsValid(relTup)) {
elog(ERROR, "dependDelete: Relation %d does not exist",
foundObject.objectId);
}
relKind = ((Form_pg_class) GETSTRUCT(relTup))->relkind;
relName = NameStr(((Form_pg_class) GETSTRUCT(relTup))->relname);
schemaName = get_namespace_name(((Form_pg_class) GETSTRUCT(relTup))->relnamespace);
ReleaseSysCache(relTup);
switch(relKind)
{
case RELKIND_INDEX:
/* Drop INDEX
*
* Future use will use below once indexes drops are
* corrected to be selfcontained. Messages of tuple
* updates occur if more than a single index is removed
* during a table drop.
*/
index_drop(foundObject.objectId, behavior);
break;
case RELKIND_VIEW:
RemoveView(makeRangeVar(schemaName, relName), behavior);
break;
case RELKIND_RELATION:
case RELKIND_SEQUENCE:
case RELKIND_TOASTVALUE:
case RELKIND_SPECIAL:
RemoveRelation(makeRangeVar(schemaName, relName), behavior);
break;
default:
elog(ERROR, "dependDelete: Unknown relkind %c", relKind);
}
break;
}
case RelOid_pg_type:
{
TypeName *typename;
/* Make a TypeName so we can use standard type lookup machinery */
typename = makeNode(TypeName);
typename->names = NIL;
typename->typeid = foundObject.objectId;
typename->typmod = -1;
typename->arrayBounds = NIL;
/* Drop the type */
RemoveTypeByTypeName(typename, behavior);
break;
}
case RelOid_pg_attribute:
elog(ERROR, "Removing Attribute");
break;
default:
/* Can't compare to a 'static' OID */
if (foundObject.classId == get_relname_relid(AggregateRelationName, PG_CATALOG_NAMESPACE))
elog(ERROR, "Removing Aggregate");
else if (foundObject.classId == get_relname_relid(ConstraintRelationName, PG_CATALOG_NAMESPACE))
DropConstraintById(foundObject.objectId, behavior);
else if (foundObject.classId == get_relname_relid(LanguageRelationName, PG_CATALOG_NAMESPACE))
elog(ERROR, "PL Handler");
else if (foundObject.classId == get_relname_relid(OperatorRelationName, PG_CATALOG_NAMESPACE))
RemoveOperatorById(foundObject.objectId, behavior);
else if (foundObject.classId == get_relname_relid(RewriteRelationName, PG_CATALOG_NAMESPACE))
RemoveRewriteRuleById(foundObject.objectId, behavior);
else if (foundObject.classId == get_relname_relid(TriggerRelationName, PG_CATALOG_NAMESPACE))
DropTriggerById(foundObject.objectId, behavior);
else
elog(ERROR, "getObjectType: Unknown object class %d", foundObject.classId);
}
/*
* We need to assume that cascaded items could potentially
* remove dependencies we want to as well. The simplest
* way to ovoid double deletions (and warnings about tuples
* being modified twice) is to rescan our list after
* bumping the command counter.
*/
CommandCounterIncrement();
}
/*
* Now go through the whole thing again looking for our object
* as the depender so we can drop those dependencies.
*/
/* Class Oid */
Assert(objectCopy.classId != InvalidOid);
ScanKeyEntryInitialize(&dropkey[dropnkeys++],
0, Anum_pg_depend_classid, F_OIDEQ,
ObjectIdGetDatum(objectCopy.classId));
/* Object Oid */
Assert(objectCopy.objectId != InvalidOid);
ScanKeyEntryInitialize(&dropkey[dropnkeys++],
0, Anum_pg_depend_objid, F_OIDEQ,
ObjectIdGetDatum(objectCopy.objectId));
/* SubObject Id */
ScanKeyEntryInitialize(&dropkey[dropnkeys++],
0, Anum_pg_depend_objsubid, F_INT4EQ,
Int32GetDatum(objectCopy.objectSubId));
scan = systable_beginscan(rel, DependDependerIndex, true,
SnapshotNow, dropnkeys, dropkey);
/* Drop dependencies found */
while (HeapTupleIsValid(tup = systable_getnext(scan))) {
simple_heap_delete(rel, &tup->t_self);
}
systable_endscan(scan);
/* Cleanup and get out */
heap_close(rel, RowExclusiveLock);
}
/* Delete function to save time */
void
dependDeleteTuple(const HeapTuple tup, const Relation relation, int behavior)
{
ObjectAddress myself;
/* Collect the information and call the real delete function */
myself.classId = RelationGetRelid(relation);
myself.objectId = tup->t_data->t_oid;
myself.objectSubId = 0;
dependDelete(&myself, behavior);
}
/* Fetch the Object Name for display */
static char *
getObjectName(const ObjectAddress *object)
{
char *name = "Unknown"; /* Unknown to Keep compiler quiet */
switch (object->classId)
{
case RelOid_pg_proc:
{
/* FUNCTION */
HeapTuple procTup;
procTup = SearchSysCache(PROCOID,
ObjectIdGetDatum(object->objectId),
0, 0, 0);
name = NameStr(((Form_pg_proc) GETSTRUCT(procTup))->proname);
ReleaseSysCache(procTup);
break;
}
case RelOid_pg_class:
{
/* RELATION */
HeapTuple relTup;
relTup = SearchSysCache(RELOID,
ObjectIdGetDatum(object->objectId),
0, 0, 0);
name = NameStr(((Form_pg_class) GETSTRUCT(relTup))->relname);
ReleaseSysCache(relTup);
break;
}
case RelOid_pg_type:
{
/* TYPE */
HeapTuple typeTup;
typeTup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(object->objectId),
0, 0, 0);
name = NameStr(((Form_pg_type) GETSTRUCT(typeTup))->typname);
ReleaseSysCache(typeTup);
break;
}
case RelOid_pg_attribute:
/* ATTRIBUTE */
name = "Unknown";
break;
default:
/* Can't compare to a 'static' OID */
if (object->classId == get_relname_relid(AggregateRelationName, PG_CATALOG_NAMESPACE))
/* AGGREGATE */
name = "Unknown";
else if (object->classId == get_relname_relid(ConstraintRelationName, PG_CATALOG_NAMESPACE))
{
HeapTuple tup;
Relation conDesc;
ScanKeyData skey[1];
SysScanDesc rcscan;
int i = 0;
/* CONSTRAINT */
ScanKeyEntryInitialize(&skey[i++],
0, ObjectIdAttributeNumber, F_OIDEQ,
ObjectIdGetDatum(object->objectId));
conDesc = heap_openr(ConstraintRelationName, RowExclusiveLock);
rcscan = systable_beginscan(conDesc, ConstraintOidIndex, true,
SnapshotNow,
i, skey);
tup = systable_getnext(rcscan);
if (!HeapTupleIsValid(tup))
/*
* elog(ERROR, "Constraint OID %d missing", object->objectId);
*
* Due to circular dependencies we simply say we don't know rather
* than the above line. This shouldn't happen in any other case
* than a the circular constraint dependency anyway.
*/
name = "<Unknown>";
else
name = NameStr(((Form_pg_constraint) GETSTRUCT(tup))->conname);
/* Clean up */
systable_endscan(rcscan);
}
else if (object->classId == get_relname_relid(LanguageRelationName, PG_CATALOG_NAMESPACE))
{
/* LANGUAGE */
HeapTuple langTup;
langTup = SearchSysCache(LANGOID,
ObjectIdGetDatum(object->objectId),
0, 0, 0);
name = NameStr(((Form_pg_language) GETSTRUCT(langTup))->lanname);
ReleaseSysCache(langTup);
}
else if (object->classId == get_relname_relid(OperatorRelationName, PG_CATALOG_NAMESPACE))
{
/* OPERATOR */
HeapTuple operTup;
operTup = SearchSysCache(OPEROID,
ObjectIdGetDatum(object->objectId),
0, 0, 0);
name = NameStr(((Form_pg_operator) GETSTRUCT(operTup))->oprname);
ReleaseSysCache(operTup);
}
else if (object->classId == get_relname_relid(RewriteRelationName, PG_CATALOG_NAMESPACE))
/* RULE */
name = "Unknown";
else if (object->classId == get_relname_relid(TriggerRelationName, PG_CATALOG_NAMESPACE))
/* TRIGGER */
name = "Unknown";
else
elog(ERROR, "getObjectType: Unknown object class %d", object->classId);
}
return name;
}
/* Fetch the Object Type for display */
static char *
getObjectType(const ObjectAddress *object)
{
char *name = "Unknown"; /* Unknown to keep compiler quiet */
switch (object->classId)
{
case RelOid_pg_proc:
name = "Function";
break;
case RelOid_pg_class:
{
HeapTuple relTup;
char relKind;
relTup = SearchSysCache(RELOID,
ObjectIdGetDatum(object->objectId),
0, 0, 0);
if (!HeapTupleIsValid(relTup)) {
elog(ERROR, "getObjectType: Relation %d does not exist",
object->objectId);
}
relKind = ((Form_pg_class) GETSTRUCT(relTup))->relkind;
ReleaseSysCache(relTup);
switch(relKind)
{
case RELKIND_INDEX:
name = "Index";
break;
case RELKIND_VIEW:
name = "View";
break;
case RELKIND_RELATION:
name = "Table";
break;
case RELKIND_TOASTVALUE:
name = "Toast Table";
break;
case RELKIND_SEQUENCE:
name = "Sequence";
break;
case RELKIND_SPECIAL:
name = "Special";
break;
default:
elog(ERROR, "dependDelete: Unknown relkind %c", relKind);
}
break;
}
case RelOid_pg_type:
name = "Type";
break;
case RelOid_pg_attribute:
name = "Table Attribute";
break;
default:
/* Can't compare to a 'static' OID */
if (object->classId == get_relname_relid(AggregateRelationName, PG_CATALOG_NAMESPACE))
name = "Aggregate";
else if (object->classId == get_relname_relid(ConstraintRelationName, PG_CATALOG_NAMESPACE))
name = "Constraint";
else if (object->classId == get_relname_relid(LanguageRelationName, PG_CATALOG_NAMESPACE))
name = "PL Hander";
else if (object->classId == get_relname_relid(OperatorRelationName, PG_CATALOG_NAMESPACE))
name = "Operator";
else if (object->classId == get_relname_relid(RewriteRelationName, PG_CATALOG_NAMESPACE))
name = "Rule";
else if (object->classId == get_relname_relid(TriggerRelationName, PG_CATALOG_NAMESPACE))
name = "Trigger";
else
elog(ERROR, "getObjectType: Unknown object class %d", object->classId);
}
return name;
}
/*
* isPinnedDependee()
*
* Test if the dependee of a found object is a permenant requirement
* for basic database functionality.
*/
static bool
isStructureOfPin(const ObjectAddress *object)
{
bool ret = false;
if (object->classId == InvalidOid
&& object->objectId == InvalidOid
&& object->objectSubId == 0)
ret = true;
return(ret);
}
/*
* isObjectPinned()
*
* Test if an object is permenantly required for basic database functionality
*/
static bool
isObjectPinned(const ObjectAddress *object, Relation rel)
{
SysScanDesc scan;
HeapTuple tup;
bool ret = false;
ScanKeyData key[6];
int nkeys = 0;
/* Pinned in Depender Slot*/
Assert(object->classId);
ScanKeyEntryInitialize(&key[nkeys++],
0, Anum_pg_depend_classid, F_OIDEQ,
ObjectIdGetDatum(object->classId));
Assert(object->objectId);
ScanKeyEntryInitialize(&key[nkeys++],
0, Anum_pg_depend_objid, F_OIDEQ,
ObjectIdGetDatum(object->objectId));
ScanKeyEntryInitialize(&key[nkeys++],
0, Anum_pg_depend_objsubid, F_INT4EQ,
Int32GetDatum(object->objectSubId));
scan = systable_beginscan(rel, DependDependerIndex, true,
SnapshotNow, nkeys, key);
/*
* If we find a match, skip the entire process.
*/
tup = systable_getnext(scan);
if (HeapTupleIsValid(tup))
{
ObjectAddress foundObject;
Form_pg_depend foundTup;
/*
* Pinned objects have a dependee ObjectAddress of 0, 0, 0
* and will only ever have one entry.
*/
foundTup = (Form_pg_depend) GETSTRUCT(tup);
foundObject.classId = foundTup->depclassid;
foundObject.objectId = foundTup->depobjid;
foundObject.objectSubId = foundTup->depobjsubid;
if (isStructureOfPin(&foundObject))
ret = true;
}
/* Cleanup and return */
systable_endscan(scan);
return(ret);
}
/*-------------------------------------------------------------------------
*
* pg_constraint.h
*
*
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
* NOTES
* the genbki.sh script reads this file and generates .bki
* information from the DATA() statements.
*
*-------------------------------------------------------------------------
*/
#ifndef PG_CONSTRAINT_H
#define PG_CONSTRAINT_H
/* ----------------
* postgres.h contains the system type definintions and the
* CATALOG(), BOOTSTRAP and DATA() sugar words so this file
* can be read by both genbki.sh and the C compiler.
* ----------------
*/
/* ----------------
* pg_constraint definition. cpp turns this into
* typedef struct FormData_pg_constraint
* ----------------
*/
CATALOG(pg_constraint)
{
/* Oid of the relation this constraint constrains */
Oid conrelid;
/* Name of this constraint */
NameData conname;
/*
* contype is the Constraint Type.
*
* Includes 'p'rimary keys, 'u'nique keys, 'f'oreign keys
* and 'c'heck constraints.
*/
char contype;
/*
* Can application of the constraint be deferred until
* transaction commit?
*/
bool condeferrable;
/*
* Is the constraint deferred until transaction commit
* by default?
*/
bool condeferred;
/*
* Foreign key'd relation
*/
Oid confrelid;
/*
* confupdtype is the type of update action expected
*/
char confupdtype;
/*
* confdeltype is the type of update action expected
*/
char confdeltype;
/*
* confmatchtype the match type of the foreign key
* 'f'ull or 'p'artial
*/
char confmatchtype;
/*
* Columns of conrelid that the constraint applies to
*/
int2 conkey[1];
/*
* Foreign key'd columns
*/
int2 confkey[1];
/*
* Source (text and binary) for check constraints
*/
text conbin;
text consrc;
} FormData_pg_constraint;
/* ----------------
* Form_pg_constraint corresponds to a pointer to a tuple with
* the format of pg_constraint relation.
* ----------------
*/
typedef FormData_pg_constraint *Form_pg_constraint;
/* ----------------
* compiler constants for pg_constraint
* ----------------
*/
#define Natts_pg_constraint 13
#define Anum_pg_constraint_conrelid 1
#define Anum_pg_constraint_conname 2
#define Anum_pg_constraint_contype 3
#define Anum_pg_constraint_condeferrable 4
#define Anum_pg_constraint_condeferred 5
#define Anum_pg_constraint_confrelid 6
#define Anum_pg_constraint_confupdtype 7
#define Anum_pg_constraint_confdeltype 8
#define Anum_pg_constraint_confmatchtype 9
#define Anum_pg_constraint_conkey 10
#define Anum_pg_constraint_confkey 11
#define Anum_pg_constraint_conbin 12
#define Anum_pg_constraint_consrc 13
#define CONSTRAINT_FKEY_RESTRICT 'r'
#define CONSTRAINT_FKEY_CASCADE 'c'
#define CONSTRAINT_FKEY_NULL 'n'
#define CONSTRAINT_FKEY_DEFAULT 'd'
#define CONSTRAINT_FKEY_NOACTION 'a'
#define CONSTRAINT_FKEY_PARTIAL 'p'
#define CONSTRAINT_FKEY_FULL 'f'
#define CONSTRAINT_FKEY_UNSPECIFIED 'u'
#define CONSTRAINT_CHECK 'c'
#define CONSTRAINT_FOREIGN 'f'
#define CONSTRAINT_PRIMARY 'p'
#define CONSTRAINT_UNIQUE 'u'
/*
* prototypes for functions in pg_constraint.c
*/
extern Oid constraintCreate(Oid relId,
const char *constraintName,
char constraintType,
bool isDeferrable,
bool isDeferred,
const AttrNumber *constraintKey,
int constraintNKeys,
Oid foreignRelId,
const AttrNumber *foreignKey,
int foreignNKeys,
char foreignUpdateType,
char foreignDeleteType,
char foreignMatchType,
char *conBin,
char *conSrc);
extern void DropConstraintById(Oid conId, int behavior);
extern char *getConstraintName(Oid relId);
#endif /* PG_CONSTRAINT_H */
/*-------------------------------------------------------------------------
*
* pg_depend.h
* definition of the system "depend" relation (pg_depend)
* along with the relation's initial contents.
*
*
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
* $Header$
*
* NOTES
* the genbki.sh script reads this file and generates .bki
* information from theDATA() statements.
*
*-------------------------------------------------------------------------
*/
#ifndef PG_DEPEND_H
#define PG_DEPEND_H
/* ----------------
* postgres.h contains the system type definitions and the
* CATALOG(), BOOTSTRAP andDATA() sugar words so this file
* can be read by both genbki.sh and the C compiler.
* ----------------
*/
/* ----------------
* pg_depend definition. cpp turns this into
* typedef struct FormData_pg_depend
* ----------------
*/
CATALOG(pg_depend) BKI_PINWRITE BKI_WITHOUT_OIDS
{
Oid classid; /* OID of table containing object */
Oid objid; /* OID of object itself */
int4 objsubid; /* column number, or 0 if not used */
Oid depclassid; /* OID of table containing dependee object */
Oid depobjid; /* OID of dependee object itself */
int4 depobjsubid; /* dependee column number, or 0 if not used */
/*
* Always cascade to the item, even when the RESTRICT behavior has been
* specified. Used primarily for SERIAL, and ARRAY types.
*/
bool alwayscascade;
} FormData_pg_depend;
/* ----------------
* Form_pg_depend corresponds to a pointer to a row with
* the format of pg_depend relation.
* ----------------
*/
typedef FormData_pg_depend *Form_pg_depend;
/* ----------------
* compiler constants for pg_depend
* ----------------
*/
#define Natts_pg_depend 7
#define Anum_pg_depend_classid 1
#define Anum_pg_depend_objid 2
#define Anum_pg_depend_objsubid 3
#define Anum_pg_depend_depclassid 4
#define Anum_pg_depend_depobjid 5
#define Anum_pg_depend_depobjsubid 6
#define Anum_pg_depend_alwayscascade 7
#define DEPEND_RESTRICT 1
#define DEPEND_CASCADE 2
#define DEPEND_IMPLICITONLY 3
/*
* Dependencies are automatically discovered in the genbki.sh
* script by using tha TABLEOID variable located at the top of
* the table description files.
*/
typedef struct ObjectAddress
{
Oid classId; /* Class Id from pg_class */
Oid objectId; /* OID of the object */
int32 objectSubId; /* Subitem within the object (column of table) */
} ObjectAddress;
extern void dependCreate(const ObjectAddress *depender,
const ObjectAddress *dependee, bool behavior);
extern void dependDelete(ObjectAddress *object, int behavior);
extern void dependDeleteTuple(const HeapTuple tup,
const Relation relation,
int behavior);
#endif /* PG_DEPEND_H */
--
-- Test RESTRICT and CASCADE keywords.
--
-- Ensure system types cannot be removed
DROP TYPE int4 CASCADE;
ERROR: Drop Restricted as Type int4 is an essential for the database to function
DROP FUNCTION nextval(text) CASCADE;
ERROR: Drop Restricted as Function nextval is an essential for the database to function
DROP TABLE pg_type CASCADE;
ERROR: table "pg_type" is a system table
-- Function RESTRICT / CASCADE
DROP FUNCTION widget_in(opaque) RESTRICT; -- fail
ERROR: Drop Restricted as Type widget Depends on Function widget_in
DROP TYPE widget RESTRICT; -- fail
ERROR: Drop Restricted as Operator <% Depends on Type widget
DROP FUNCTION widget_in(opaque) CASCADE;
NOTICE: Cascading drop to Type widget
NOTICE: Cascading drop to Operator <%
NOTICE: Cascading drop to Function pt_in_widget
DROP TYPE widget RESTRICT; -- doesn't exist
ERROR: Type "widget" does not exist
-- Type RESTRICT / CASCADE
DROP TYPE city_budget RESTRICT; -- fail
ERROR: Drop Restricted as Table city Depends on Type city_budget
DROP TYPE city_budget CASCADE;
NOTICE: Cascading drop to Table city
DROP TABLE city RESTRICT; -- doesn't exist
ERROR: table "city" does not exist
-- Domain RESTRICT / CASCADE
DROP DOMAIN ddef1 RESTRICT; -- fail
ERROR: Drop Restricted as Table defaulttest Depends on Type ddef1
DROP DOMAIN ddef1 CASCADE;
NOTICE: Cascading drop to Table defaulttest
DROP TABLE defaulttest RESTRICT; -- doesn't exist
ERROR: table "defaulttest" does not exist
-- Procedural languge RESTRICT / CASCADE
DROP LANGUAGE plpgsql RESTRICT; -- fail
ERROR: Drop Restricted as Function recursion_test Depends on PL Hander plpgsql
DROP LANGUAGE plpgsql CASCADE;
NOTICE: Cascading drop to Function recursion_test
NOTICE: Cascading drop to Function wslot_slotlink_view
NOTICE: Cascading drop to Function pslot_slotlink_view
NOTICE: Cascading drop to Function pslot_backlink_view
NOTICE: Cascading drop to Function tg_slotlink_unset
NOTICE: Cascading drop to Function tg_slotlink_set
NOTICE: Cascading drop to Function tg_slotlink_a
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_backlink_unset
NOTICE: Cascading drop to Function tg_backlink_set
NOTICE: Cascading drop to Function tg_backlink_a
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_phone_bu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_hslot_bu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_iface_bu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_pline_bu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_wslot_bu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_pslot_bu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_chkbacklink
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_chkslotlink
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_chkslotname
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_hslot_bd
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_hslot_biu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_hub_adjustslots
NOTICE: Cascading drop to Function tg_hub_a
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_iface_biu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_system_au
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_pslot_biu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_pfield_ad
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_pfield_au
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_wslot_biu
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_room_ad
NOTICE: Cascading drop to Trigger Unknown
NOTICE: Cascading drop to Function tg_room_au
NOTICE: Cascading drop to Trigger Unknown
SELECT recursion_test(2,3); -- doesn't exist
ERROR: Function 'recursion_test(integer, integer)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
-- Foreign Key RESTRICT / CASCADE
-- See alter table pktable and fktable tests
pgsql-patches by date: