Thread: Dependency / Constraint patch

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

All that, and I forgot the actual patch.

Attached.

--
Rod
----- Original Message -----
From: "Rod Taylor" <rbt@zort.ca>
To: <pgsql-patches@postgresql.org>
Sent: Saturday, June 15, 2002 3:10 PM
Subject: [PATCHES] Dependency / Constraint patch


> 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 doing work 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
>


----------------------------------------------------------------------
----------


>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Hi Rod,

If you break out the following two patch items:

> > - psql displays foreign keys (\d output)
> > - Foreign key triggers are autonamed based on the constraint name

I'm sure that part of the patch will get committed (so long as it's good),
as we'd agreed already to come up with such a patch (except I never got
around to it).  Have you also modified psql to NOT dump all those hundreds
of constraint triggers and show proper FK's instead?  Make it show normal
triggers and foreign keys as separate things...

> > - pg_dump uses ALTER TABLE / ADD FOREIGN KEY

The item above is trouble because it makes adding foreign keys from dumps
very slow on large tables.  The advantage of the CREATE CONSRAINT TRIGGER
approach is that it doesn't actually _check_ the constraint.

My earlier suggestion was to create a 'SET CONSTRAINTS UNCHECKED;' sort of
transaction-only function that would make ADD FOREIGN KEY _not_ check
constraints.  I can't remember what the repsonse to that was, but we need
something...

Chris

> If you break out the following two patch items:
>
> > > - psql displays foreign keys (\d output)
> > > - Foreign key triggers are autonamed based on the constraint
name
>
> I'm sure that part of the patch will get committed (so long as it's
good),
> as we'd agreed already to come up with such a patch (except I never
got

Yes, isconstraint triggers are ignored.  Triggers were easy to toss
the names around as they inherit the name of the constraint.  The
constraint was autonamed.

> > > - pg_dump uses ALTER TABLE / ADD FOREIGN KEY
>
> The item above is trouble because it makes adding foreign keys from
dumps
> very slow on large tables.  The advantage of the CREATE CONSRAINT
TRIGGER
> approach is that it doesn't actually _check_ the constraint.
>
> My earlier suggestion was to create a 'SET CONSTRAINTS UNCHECKED;'
sort of
> transaction-only function that would make ADD FOREIGN KEY _not_
check
> constraints.  I can't remember what the repsonse to that was, but we
need
> something...

No choice.  Using CREATE TRIGGER drops all dependency information with
a pgdump / restore -- not to mention the actual foreign key.  If you
want to wrap it with the above suggestion feel free.  I've got a few
other things to clean up before I could go into that.

On Wed, 19 Jun 2002, Christopher Kings-Lynne wrote:

> Hi Rod,
>
> If you break out the following two patch items:
>
> > > - psql displays foreign keys (\d output)
> > > - Foreign key triggers are autonamed based on the constraint name
>
> I'm sure that part of the patch will get committed (so long as it's good),
> as we'd agreed already to come up with such a patch (except I never got
> around to it).  Have you also modified psql to NOT dump all those hundreds
> of constraint triggers and show proper FK's instead?  Make it show normal
> triggers and foreign keys as separate things...
>
> > > - pg_dump uses ALTER TABLE / ADD FOREIGN KEY
>
> The item above is trouble because it makes adding foreign keys from dumps
> very slow on large tables.  The advantage of the CREATE CONSRAINT TRIGGER
> approach is that it doesn't actually _check_ the constraint.
>
> My earlier suggestion was to create a 'SET CONSTRAINTS UNCHECKED;' sort of
> transaction-only function that would make ADD FOREIGN KEY _not_ check
> constraints.  I can't remember what the repsonse to that was, but we need
> something...

If it only affects ALTER TABLE / ADD FOREIGN KEY, I think it's a good
idea.  I don't think we should do a general unchecked however.

Rod Taylor wrote:
> 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)

Rod, is this ready to be applied?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Rod Taylor wrote:
>> 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)

> Rod, is this ready to be applied?

I will take responsibility for reviewing and applying this.  I've been
meaning to do so for awhile, but having been on the road for half of
June hasn't left me with much time for it...

            regards, tom lane

> Rod Taylor wrote:
> > 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)
>
> Rod, is this ready to be applied?

It's as far along as I think I'll be able to get it without some
assistance.

As stated, I have a gut feeling that the locking isn't as strong as it
should be.  I can't tell that anything is missing by using it -- but
we all remember how my first patch turned out ;)

On Tue, 2002-06-25 at 19:30, Tom Lane wrote:
> I've been looking at this, and the naming seems seriously confusing.
> Am I right in thinking that what the code consistently refers to as
> the "dependee" is actually the *independent* object --- that is,
> cascaded deletes flow from dependee to depender?

Yeah, cascaded deletes go backwards through the dependencies.  It's a
bit like conventional versus electron flow in electronics.

> I suppose depender is an okay name, but I've got a problem with
> dependee; every time I look at one of these calls I think it's
> backwards.  Can you think of a less confusing naming convention?

> All I've come up with in a short amount of time is master/slave,
> which might be considered too politically incorrect these days...

It's not accurate either.  Slaves to the bidding of a master.  Likewise,
children come from the parents.  This really isn't either of those.

Think of a upside down pyramid scheme.  A whole bunch of people at the
bottom depend upon the existence of a few below them, who depend on a
few below that, etc.  Take away one of the many top elements, and the
pyramid doesn't fall. But take away the one on the bottom and the whole
thing collapses.

The dependee (those receiving the dependency from the depender) are
closer to the bottom supporting them.


The confusing part is that we deal primarily with the supporting objects
while dealing with the dependency.  Perhaps better naming would be
'supporter' (dependee) and 'supportee' (depender) which reverses the
logic -- more conventional?  That is, pass around the opposite of a
dependency.

I'm used to dealing with 'negative logic' -- so the current naming made
perfect sense.

Ceiling depends on the walls which depends on the floor -- or floor
supports the walls which support the ceiling.  Either way, take away the
floor and you're left with a crumbled building.

Perhaps simply supporter and depender?

> I'd also like to rename dependCreate in some way that helps make
> the order of the arguments unmistakable.  Perhaps dependsOn() ?
> Any thoughts?

I'm not partial to either.  The change would be a simple 'rpl'.

I was considering createDependency(this, that) myself.  dependsOn seems
like it should be:

this->dependsOn(that);

> The confusing part is that we deal primarily with the supporting objects
> while dealing with the dependency.  Perhaps better naming would be
> 'supporter' (dependee) and 'supportee' (depender) which reverses the
> logic -- more conventional?  That is, pass around the opposite of a
> dependency.

Hmm, that might help.  I was thinking of "depender" and "master".

> Perhaps simply supporter and depender?

That could work too; two positive identifications instead of a positive
and negative.

Another line of thought is 'referencer' and 'referencee', since in
general the dependent object is going to have an actual link to the
independent object somewhere in its definition.  Basically what
pg_depend is doing for us is providing an index that lets us trace those
links in the reverse direction efficiently.


> I was considering createDependency(this, that) myself.  dependsOn seems
> like it should be:

> this->dependsOn(that);

Yeah, it would work better in a C++-ish syntax, but we don't have that
luxury.  My problem with either dependCreate or createDependency is that
they don't give you a cue about the argument ordering, and with such an
easily-reversed relationship I think a cue is important.


I'm also somewhat uncomfortable with the notion of "implicit
dependencies" (alwayscascade = true) that you've invented; this seems
like a bad idea to me, but I haven't quite worked out why I don't like
it.

            regards, tom lane

On Tue, 2002-06-25 at 20:34, Tom Lane wrote:
> > The confusing part is that we deal primarily with the supporting objects
> > while dealing with the dependency.  Perhaps better naming would be
> > 'supporter' (dependee) and 'supportee' (depender) which reverses the
> > logic -- more conventional?  That is, pass around the opposite of a
> > dependency.
>
> Hmm, that might help.  I was thinking of "depender" and "master".

Thats not so bad.  Either would do.

> > Perhaps simply supporter and depender?

> Another line of thought is 'referencer' and 'referencee', since in

I'd expect this to have similar issues as depender and dependee in
implementation.

> > I was considering createDependency(this, that) myself.  dependsOn seems
> > like it should be:
>
> > this->dependsOn(that);
>
> Yeah, it would work better in a C++-ish syntax, but we don't have that
> luxury.  My problem with either dependCreate or createDependency is that
> they don't give you a cue about the argument ordering, and with such an
> easily-reversed relationship I think a cue is important.

Thats fair.

> I'm also somewhat uncomfortable with the notion of "implicit
> dependencies" (alwayscascade = true) that you've invented; this seems
> like a bad idea to me, but I haven't quite worked out why I don't like
> it.

Certainly allows for removal of a large portion of the 'drop this too'
code that was lying all over the place.  The theory is those were
completely hidden items with special support code.  I saw an oppertunity
to remove some of the 'special' from it.


Did you want a new patch with the above changes applied?  I would expect
a direct string replacement would do the job.

Rod Taylor <rbt@zort.ca> writes:
> On Tue, 2002-06-25 at 20:34, Tom Lane wrote:
>> Another line of thought is 'referencer' and 'referencee', since in

> I'd expect this to have similar issues as depender and dependee in
> implementation.

Could be.  I thought the direction of "references" might be more obvious
than that of "depends", but maybe not.  Anyone else have any naming ideas?

>> I'm also somewhat uncomfortable with the notion of "implicit
>> dependencies" (alwayscascade = true) that you've invented; this seems
>> like a bad idea to me, but I haven't quite worked out why I don't like
>> it.

> Certainly allows for removal of a large portion of the 'drop this too'
> code that was lying all over the place.

Yeah, but having to instead write "make this dependency too" code seems
like it largely cancels out in terms of code bulk and reliability.

The real problem is that I don't think a one-size-fits-all solution
exists for all those special-cased relationships.  For example, the
relationship between a table and its rowtype (pg_type entry) doesn't
really fit the model.  Unless I'm missing something, your patch doesn't
prevent someone from dropping the type without dropping the table.
Special-case code makes it easier to do the right thing (whatever we
decide that to be) in special cases...

BTW, there were a number of places where it seemed that you were trying
to avoid creating circular dependencies --- is there a problem with
doing that?  I think it's a situation that we will have to be able to
cope with.

> Did you want a new patch with the above changes applied?  I would expect
> a direct string replacement would do the job.

Nah, I can hack it up myself easily enough; I've already identified a
bunch of smaller changes to make (coding style issues mainly).

            regards, tom lane

> >> I'm also somewhat uncomfortable with the notion of "implicit
> >> dependencies" (alwayscascade = true) that you've invented; this seems
> >> like a bad idea to me, but I haven't quite worked out why I don't like
> >> it.
>
> > Certainly allows for removal of a large portion of the 'drop this too'
> > code that was lying all over the place.
>
> Yeah, but having to instead write "make this dependency too" code seems
> like it largely cancels out in terms of code bulk and reliability.

Thats true -- but the code removed was certainly larger.

> Special-case code makes it easier to do the right thing (whatever we
> decide that to be) in special cases...

Most of this was in dropping a heap (toast tables, indices, constraints,
...) and the array type entry to a type entry.  They all appeared to
have to go out of their way to seek down and destroy anything using
them.  In the array type example, it even depended on naming
convention.  I think it's somewhat more generic -- but could be much
better if circular dependencies were allowed (see below) as it could
RESTRICT drops of a toast table or complex type on a table or CASCADE
them when appropriate.

> BTW, there were a number of places where it seemed that you were trying
> to avoid creating circular dependencies --- is there a problem with
> doing that?  I think it's a situation that we will have to be able to
> cope with.

Yeah.  Circular dependencies right now cause a never ending loop when
dropping them.  I tried reversing the order in dependDelete (move calls
to self to the end) but that caused lots of problems with items
expecting.  It really needs to keep a list of already found dependencies
and not attempt to drop them again.

If it kept a list and passed it into each dependDelete call (from
itself) that would help.  Perhaps it should be broken up into 2
functions.  One facing externally, and one static that does the
recursive dirty work.

> > Did you want a new patch with the above changes applied?  I would expect
> > a direct string replacement would do the job.
>
> Nah, I can hack it up myself easily enough; I've already identified a
> bunch of smaller changes to make (coding style issues mainly).

If you don't mind sending me back a patch with all of the changes when
complete, I'd appreciate it.  I'm still learning most of the basics, but
being able to see the changes made without digging through a bunch of
files on the cvsweb interface would be useful.

Rod Taylor <rbt@zort.ca> writes:
>> BTW, there were a number of places where it seemed that you were trying
>> to avoid creating circular dependencies --- is there a problem with
>> doing that?  I think it's a situation that we will have to be able to
>> cope with.

> Yeah.  Circular dependencies right now cause a never ending loop when
> dropping them.  I tried reversing the order in dependDelete (move calls
> to self to the end) but that caused lots of problems with items
> expecting.  It really needs to keep a list of already found dependencies
> and not attempt to drop them again.

I was thinking that the arrangement you have with triggering drops
partway through a drop of another object is inherently unsafe: first,
because it doesn't work for circular dependencies, and second because
CommandCounterIncrement partway through a drop of a complex object like
a table is not a good idea.  The CCI might trigger a relcache reload
attempt, and if you have inconsistent state in the system tables (say,
number of triggers or attributes different from what the pg_class row
says to expect) then the relcache routines will elog.

I am thinking that the correct approach is:

1. Drop an object.

2. CommandCounterIncrement.

3. Scan pg_depend for dependency records.  For each one found, if
   the referencing object still exists then delete it (via a recursive
   performance of this same procedure).  In any case delete the
   dependency record.

Because of the CCI, the object deletion will be visible during the
step-3 scan, and so any recursive invocation will see the original
object as no longer existing, should it happen to follow a dependency
loop back to the original object.  If we error out anyplace, then the
whole thing rolls back anyhow.

This will not work for "internal" structure of a table, like the
trigger -> tgrelid dependency that you wanted to use to replace
RelationRemoveTriggers, but I wasn't thrilled with that application
of pg_depend anyway.  I will be satisfied with using it to handle
cascaded drops of quasi-independent objects.


>> Nah, I can hack it up myself easily enough; I've already identified a
>> bunch of smaller changes to make (coding style issues mainly).

> If you don't mind sending me back a patch with all of the changes when
> complete, I'd appreciate it.

Will do.

            regards, tom lane

I am a student doing my graduation in India. I want to know what are the
other OODBMS features ( other than inheritance ) available
in PostGreSQL. It would be great if you can help me out with some
information regarding this.

Thanks,
Nishkala

--
Being yourself in the world which is constantly trying to change you to something else is the biggest challenge