diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/catalog/pg_type.c | 13 | ||||
| -rw-r--r-- | src/backend/commands/command.c | 365 | ||||
| -rw-r--r-- | src/backend/parser/analyze.c | 1844 | ||||
| -rw-r--r-- | src/backend/tcop/utility.c | 4 | ||||
| -rw-r--r-- | src/include/commands/command.h | 4 | ||||
| -rw-r--r-- | src/include/nodes/parsenodes.h | 11 | ||||
| -rw-r--r-- | src/test/regress/expected/alter_table.out | 28 | ||||
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 2 |
8 files changed, 1112 insertions, 1159 deletions
diff --git a/src/backend/catalog/pg_type.c b/src/backend/catalog/pg_type.c index b31892860a2..b79bd7068fc 100644 --- a/src/backend/catalog/pg_type.c +++ b/src/backend/catalog/pg_type.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/catalog/pg_type.c,v 1.63 2001/09/06 02:07:42 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/catalog/pg_type.c,v 1.64 2001/10/12 00:07:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -555,9 +555,8 @@ TypeRename(const char *oldTypeName, const char *newTypeName) * makeArrayTypeName(typeName); * - given a base type name, make an array of type name out of it * - * the CALLER is responsible for pfreeing the + * the caller is responsible for pfreeing the result */ - char * makeArrayTypeName(char *typeName) { @@ -565,10 +564,8 @@ makeArrayTypeName(char *typeName) if (!typeName) return NULL; - arr = palloc(strlen(typeName) + 2); - arr[0] = '_'; - strcpy(arr + 1, typeName); - + arr = palloc(NAMEDATALEN); + snprintf(arr, NAMEDATALEN, + "_%.*s", NAMEDATALEN - 2, typeName); return arr; - } diff --git a/src/backend/commands/command.c b/src/backend/commands/command.c index 2b3cc08e2d4..a81d097d8b0 100644 --- a/src/backend/commands/command.c +++ b/src/backend/commands/command.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.143 2001/10/05 17:28:11 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.144 2001/10/12 00:07:14 tgl Exp $ * * NOTES * The PerformAddAttribute() code, like most of the relation @@ -309,9 +309,10 @@ AlterTableAddColumn(const char *relationName, int i; int minattnum, maxatts; - Relation idescs[Num_pg_attr_indices]; - Relation ridescs[Num_pg_class_indices]; - bool hasindex; + HeapTuple typeTuple; + Form_pg_type tform; + char *typename; + int attndims; /* * permissions checking. this would normally be done in utility.c, @@ -339,57 +340,64 @@ AlterTableAddColumn(const char *relationName, heap_close(rel, NoLock); /* close rel but keep lock! */ /* - * we can't add a not null attribute - */ - if (colDef->is_not_null) - elog(ERROR, "Can't add a NOT NULL attribute to an existing relation"); - - if (colDef->raw_default || colDef->cooked_default) - elog(ERROR, "Adding columns with defaults is not implemented."); - - - /* - * if the first element in the 'schema' list is a "*" then we are - * supposed to add this attribute to all classes that inherit from - * 'relationName' (as well as to 'relationName'). + * Recurse to add the column to child classes, if requested. * * any permissions or problems with duplicate attributes will cause the * whole transaction to abort, which is what we want -- all or * nothing. */ - if (colDef != NULL) + if (inherits) { - if (inherits) - { - List *child, - *children; + List *child, + *children; - /* this routine is actually in the planner */ - children = find_all_inheritors(myrelid); + /* this routine is actually in the planner */ + children = find_all_inheritors(myrelid); - /* - * find_all_inheritors does the recursive search of the - * inheritance hierarchy, so all we have to do is process all - * of the relids in the list that it returns. - */ - foreach(child, children) - { - Oid childrelid = lfirsti(child); - char *childrelname; + /* + * find_all_inheritors does the recursive search of the + * inheritance hierarchy, so all we have to do is process all + * of the relids in the list that it returns. + */ + foreach(child, children) + { + Oid childrelid = lfirsti(child); + char *childrelname; - if (childrelid == myrelid) - continue; - rel = heap_open(childrelid, AccessExclusiveLock); - childrelname = pstrdup(RelationGetRelationName(rel)); - heap_close(rel, AccessExclusiveLock); + if (childrelid == myrelid) + continue; + rel = heap_open(childrelid, AccessExclusiveLock); + childrelname = pstrdup(RelationGetRelationName(rel)); + heap_close(rel, AccessExclusiveLock); - AlterTableAddColumn(childrelname, false, colDef); + AlterTableAddColumn(childrelname, false, colDef); - pfree(childrelname); - } + pfree(childrelname); } } + /* + * OK, get on with it... + * + * Implementation restrictions: because we don't touch the table rows, + * the new column values will initially appear to be NULLs. (This + * happens because the heap tuple access routines always check for + * attnum > # of attributes in tuple, and return NULL if so.) Therefore + * we can't support a DEFAULT value in SQL92-compliant fashion, and + * we also can't allow a NOT NULL constraint. + * + * We do allow CHECK constraints, even though these theoretically + * could fail for NULL rows (eg, CHECK (newcol IS NOT NULL)). + */ + if (colDef->raw_default || colDef->cooked_default) + elog(ERROR, "Adding columns with defaults is not implemented." + "\n\tAdd the column, then use ALTER TABLE SET DEFAULT."); + + if (colDef->is_not_null) + elog(ERROR, "Adding NOT NULL columns is not implemented." + "\n\tAdd the column, then use ALTER TABLE ADD CONSTRAINT."); + + rel = heap_openr(RelationRelationName, RowExclusiveLock); reltup = SearchSysCache(RELNAME, @@ -400,22 +408,39 @@ AlterTableAddColumn(const char *relationName, elog(ERROR, "ALTER TABLE: relation \"%s\" not found", relationName); + if (SearchSysCacheExists(ATTNAME, + ObjectIdGetDatum(reltup->t_data->t_oid), + PointerGetDatum(colDef->colname), + 0, 0)) + elog(ERROR, "ALTER TABLE: column name \"%s\" already exists in table \"%s\"", + colDef->colname, relationName); + minattnum = ((Form_pg_class) GETSTRUCT(reltup))->relnatts; maxatts = minattnum + 1; if (maxatts > MaxHeapAttributeNumber) elog(ERROR, "ALTER TABLE: relations limited to %d columns", MaxHeapAttributeNumber); + i = minattnum + 1; attrdesc = heap_openr(AttributeRelationName, RowExclusiveLock); - /* - * Open all (if any) pg_attribute indices - */ - hasindex = RelationGetForm(attrdesc)->relhasindex; - if (hasindex) - CatalogOpenIndices(Num_pg_attr_indices, Name_pg_attr_indices, idescs); + if (colDef->typename->arrayBounds) + { + attndims = length(colDef->typename->arrayBounds); + typename = makeArrayTypeName(colDef->typename->name); + } + else + { + attndims = 0; + typename = colDef->typename->name; + } - attributeD.attrelid = reltup->t_data->t_oid; + typeTuple = SearchSysCache(TYPENAME, + PointerGetDatum(typename), + 0, 0, 0); + if (!HeapTupleIsValid(typeTuple)) + elog(ERROR, "ALTER TABLE: type \"%s\" does not exist", typename); + tform = (Form_pg_type) GETSTRUCT(typeTuple); attributeTuple = heap_addheader(Natts_pg_attribute, ATTRIBUTE_TUPLE_SIZE, @@ -423,70 +448,36 @@ AlterTableAddColumn(const char *relationName, attribute = (Form_pg_attribute) GETSTRUCT(attributeTuple); - i = 1 + minattnum; - + attribute->attrelid = reltup->t_data->t_oid; + namestrcpy(&(attribute->attname), colDef->colname); + attribute->atttypid = typeTuple->t_data->t_oid; + attribute->attstattarget = DEFAULT_ATTSTATTARGET; + attribute->attlen = tform->typlen; + attribute->attcacheoff = -1; + attribute->atttypmod = colDef->typename->typmod; + attribute->attnum = i; + attribute->attbyval = tform->typbyval; + attribute->attndims = attndims; + attribute->attisset = (bool) (tform->typtype == 'c'); + attribute->attstorage = tform->typstorage; + attribute->attalign = tform->typalign; + attribute->attnotnull = colDef->is_not_null; + attribute->atthasdef = (colDef->raw_default != NULL || + colDef->cooked_default != NULL); + + ReleaseSysCache(typeTuple); + + heap_insert(attrdesc, attributeTuple); + + /* Update indexes on pg_attribute */ + if (RelationGetForm(attrdesc)->relhasindex) { - HeapTuple typeTuple; - Form_pg_type tform; - char *typename; - int attndims; - - if (SearchSysCacheExists(ATTNAME, - ObjectIdGetDatum(reltup->t_data->t_oid), - PointerGetDatum(colDef->colname), - 0, 0)) - elog(ERROR, "ALTER TABLE: column name \"%s\" already exists in table \"%s\"", - colDef->colname, relationName); + Relation idescs[Num_pg_attr_indices]; - /* - * check to see if it is an array attribute. - */ - - typename = colDef->typename->name; - - if (colDef->typename->arrayBounds) - { - attndims = length(colDef->typename->arrayBounds); - typename = makeArrayTypeName(colDef->typename->name); - } - else - attndims = 0; - - typeTuple = SearchSysCache(TYPENAME, - PointerGetDatum(typename), - 0, 0, 0); - if (!HeapTupleIsValid(typeTuple)) - elog(ERROR, "ALTER TABLE: type \"%s\" does not exist", typename); - tform = (Form_pg_type) GETSTRUCT(typeTuple); - - namestrcpy(&(attribute->attname), colDef->colname); - attribute->atttypid = typeTuple->t_data->t_oid; - attribute->attlen = tform->typlen; - attribute->attstattarget = DEFAULT_ATTSTATTARGET; - attribute->attcacheoff = -1; - attribute->atttypmod = colDef->typename->typmod; - attribute->attnum = i; - attribute->attbyval = tform->typbyval; - attribute->attndims = attndims; - attribute->attisset = (bool) (tform->typtype == 'c'); - attribute->attstorage = tform->typstorage; - attribute->attalign = tform->typalign; - attribute->attnotnull = false; - attribute->atthasdef = (colDef->raw_default != NULL || - colDef->cooked_default != NULL); - - ReleaseSysCache(typeTuple); - - heap_insert(attrdesc, attributeTuple); - if (hasindex) - CatalogIndexInsert(idescs, - Num_pg_attr_indices, - attrdesc, - attributeTuple); - } - - if (hasindex) + CatalogOpenIndices(Num_pg_attr_indices, Name_pg_attr_indices, idescs); + CatalogIndexInsert(idescs, Num_pg_attr_indices, attrdesc, attributeTuple); CatalogCloseIndices(Num_pg_attr_indices, idescs); + } heap_close(attrdesc, NoLock); @@ -499,9 +490,14 @@ AlterTableAddColumn(const char *relationName, simple_heap_update(rel, &newreltup->t_self, newreltup); /* keep catalog indices current */ - CatalogOpenIndices(Num_pg_class_indices, Name_pg_class_indices, ridescs); - CatalogIndexInsert(ridescs, Num_pg_class_indices, rel, newreltup); - CatalogCloseIndices(Num_pg_class_indices, ridescs); + if (RelationGetForm(rel)->relhasindex) + { + Relation ridescs[Num_pg_class_indices]; + + CatalogOpenIndices(Num_pg_class_indices, Name_pg_class_indices, ridescs); + CatalogIndexInsert(ridescs, Num_pg_class_indices, rel, newreltup); + CatalogCloseIndices(Num_pg_class_indices, ridescs); + } heap_freetuple(newreltup); ReleaseSysCache(reltup); @@ -509,10 +505,27 @@ AlterTableAddColumn(const char *relationName, heap_close(rel, NoLock); /* + * Make our catalog updates visible for subsequent steps. + */ + CommandCounterIncrement(); + + /* + * Add any CHECK constraints attached to the new column. + * + * To do this we must re-open the rel so that its new attr list + * gets loaded into the relcache. + */ + if (colDef->constraints != NIL) + { + rel = heap_openr(relationName, AccessExclusiveLock); + AddRelationRawConstraints(rel, NIL, colDef->constraints); + heap_close(rel, NoLock); + } + + /* * Automatically create the secondary relation for TOAST if it * formerly had no such but now has toastable attributes. */ - CommandCounterIncrement(); AlterTableCreateToastTable(relationName, true); } @@ -596,7 +609,6 @@ AlterTableAlterColumnDefault(const char *relationName, if (newDefault) { /* SET DEFAULT */ - List *rawDefaults = NIL; RawColumnDefault *rawEnt; /* Get rid of the old one first */ @@ -605,13 +617,12 @@ AlterTableAlterColumnDefault(const char *relationName, rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault)); rawEnt->attnum = attnum; rawEnt->raw_default = newDefault; - rawDefaults = lappend(rawDefaults, rawEnt); /* * This function is intended for CREATE TABLE, so it processes a * _list_ of defaults, but we just do one. */ - AddRelationRawConstraints(rel, rawDefaults, NIL); + AddRelationRawConstraints(rel, makeList1(rawEnt), NIL); } else { @@ -1169,10 +1180,11 @@ AlterTableDropColumn(const char *relationName, */ void AlterTableAddConstraint(char *relationName, - bool inh, Node *newConstraint) + bool inh, List *newConstraints) { - if (newConstraint == NULL) - elog(ERROR, "ALTER TABLE / ADD CONSTRAINT passed invalid constraint."); + Relation rel; + Oid myrelid; + List *listptr; #ifndef NO_SECURITY if (!pg_ownercheck(GetUserId(), relationName, RELNAME)) @@ -1184,6 +1196,41 @@ AlterTableAddConstraint(char *relationName, elog(ERROR, "ALTER TABLE ADD CONSTRAINT: %s is not a table", relationName); + rel = heap_openr(relationName, AccessExclusiveLock); + myrelid = RelationGetRelid(rel); + + if (inh) { + List *child, + *children; + + /* this routine is actually in the planner */ + children = find_all_inheritors(myrelid); + + /* + * find_all_inheritors does the recursive search of the + * inheritance hierarchy, so all we have to do is process all + * of the relids in the list that it returns. + */ + foreach(child, children) + { + Oid childrelid = lfirsti(child); + char *childrelname; + Relation childrel; + + if (childrelid == myrelid) + continue; + childrel = heap_open(childrelid, AccessExclusiveLock); + childrelname = pstrdup(RelationGetRelationName(childrel)); + heap_close(childrel, AccessExclusiveLock); + AlterTableAddConstraint(childrelname, false, newConstraints); + pfree(childrelname); + } + } + + foreach(listptr, newConstraints) + { + Node *newConstraint = lfirst(listptr); + switch (nodeTag(newConstraint)) { case T_Constraint: @@ -1202,33 +1249,14 @@ AlterTableAddConstraint(char *relationName, HeapTuple tuple; RangeTblEntry *rte; List *qual; - List *constlist; - Relation rel; Node *expr; char *name; - Oid myrelid; if (constr->name) name = constr->name; else name = "<unnamed>"; - constlist = makeList1(constr); - - rel = heap_openr(relationName, AccessExclusiveLock); - myrelid = RelationGetRelid(rel); - - /* make sure it is not a view */ - if (rel->rd_rel->relkind == RELKIND_VIEW) - elog(ERROR, "ALTER TABLE: cannot add constraint to a view"); - - /* - * Scan all of the rows, looking for a false - * match - */ - scan = heap_beginscan(rel, false, SnapshotNow, 0, NULL); - AssertState(scan != NULL); - /* * We need to make a parse state and range * table to allow us to transformExpr and @@ -1280,6 +1308,8 @@ AlterTableAddConstraint(char *relationName, * Scan through the rows now, checking the * expression at each row. */ + scan = heap_beginscan(rel, false, SnapshotNow, 0, NULL); + while (HeapTupleIsValid(tuple = heap_getnext(scan, 0))) { ExecStoreTuple(tuple, slot, InvalidBuffer, false); @@ -1291,11 +1321,11 @@ AlterTableAddConstraint(char *relationName, ResetExprContext(econtext); } + heap_endscan(scan); + FreeExprContext(econtext); pfree(slot); - heap_endscan(scan); - if (!successful) elog(ERROR, "AlterTableAddConstraint: rejected due to CHECK constraint %s", name); @@ -1306,38 +1336,8 @@ AlterTableAddConstraint(char *relationName, * the constraint against tuples already in * the table. */ - AddRelationRawConstraints(rel, NIL, constlist); - heap_close(rel, NoLock); - - if (inh) { - List *child, - *children; - - /* this routine is actually in the planner */ - children = find_all_inheritors(myrelid); - - /* - * find_all_inheritors does the recursive search of the - * inheritance hierarchy, so all we have to do is process all - * of the relids in the list that it returns. - */ - foreach(child, children) - { - Oid childrelid = lfirsti(child); - char *childrelname; - - if (childrelid == myrelid) - continue; - rel = heap_open(childrelid, AccessExclusiveLock); - childrelname = pstrdup(RelationGetRelationName(rel)); - heap_close(rel, AccessExclusiveLock); - - AlterTableAddConstraint(childrelname, false, newConstraint); - - pfree(childrelname); - } - } - pfree(constlist); + AddRelationRawConstraints(rel, NIL, + makeList1(constr)); break; } @@ -1345,7 +1345,6 @@ AlterTableAddConstraint(char *relationName, { char *iname = constr->name; bool istemp = is_temp_rel_name(relationName); - Relation rel; List *indexoidlist; List *indexoidscan; Form_pg_attribute *rel_attrs; @@ -1389,7 +1388,6 @@ AlterTableAddConstraint(char *relationName, } /* Need to check for unique key already on field(s) */ - rel = heap_openr(relationName, AccessExclusiveLock); /* * First we check for limited correctness of the @@ -1490,9 +1488,6 @@ AlterTableAddConstraint(char *relationName, elog(NOTICE, "Unique constraint supercedes existing index on relation \"%s\". Drop the existing index to remove redundancy.", relationName); pfree(iname); - /* Finally, close relation */ - heap_close(rel, NoLock); - break; } default: @@ -1503,8 +1498,7 @@ AlterTableAddConstraint(char *relationName, case T_FkConstraint: { FkConstraint *fkconstraint = (FkConstraint *) newConstraint; - Relation rel, - pkrel; + Relation pkrel; HeapScanDesc scan; HeapTuple tuple; Trigger trig; @@ -1538,16 +1532,11 @@ AlterTableAddConstraint(char *relationName, fkconstraint->pktable_name); /* - * Grab an exclusive lock on the fk table, and then scan - * through each tuple, calling the RI_FKey_Match_Ins + * Scan through each tuple, calling the RI_FKey_Match_Ins * (insert trigger) as if that tuple had just been * inserted. If any of those fail, it should elog(ERROR) * and that's that. */ - rel = heap_openr(relationName, AccessExclusiveLock); - if (rel->rd_rel->relkind != RELKIND_RELATION) - elog(ERROR, "referencing table \"%s\" not a relation", - relationName); /* * First we check for limited correctness of the @@ -1741,8 +1730,6 @@ AlterTableAddConstraint(char *relationName, RI_FKey_check_ins(&fcinfo); } heap_endscan(scan); - heap_close(rel, NoLock); /* close rel but keep - * lock! */ pfree(trig.tgargs); break; @@ -1750,6 +1737,10 @@ AlterTableAddConstraint(char *relationName, default: elog(ERROR, "ALTER TABLE / ADD CONSTRAINT unable to determine type of constraint passed"); } + } + + /* Close rel, but keep lock till commit */ + heap_close(rel, NoLock); } diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index d4af6f68619..94e5e1602eb 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.200 2001/10/03 20:54:20 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.201 2001/10/12 00:07:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -36,11 +36,33 @@ #include "utils/numeric.h" #include "utils/relcache.h" #include "utils/syscache.h" +#include "utils/temprel.h" #ifdef MULTIBYTE #include "mb/pg_wchar.h" #endif + +/* State shared by transformCreateStmt and its subroutines */ +typedef struct +{ + const char *stmtType; /* "CREATE TABLE" or "ALTER TABLE" */ + char *relname; /* name of relation */ + List *inhRelnames; /* names of relations to inherit from */ + bool istemp; /* is it to be a temp relation? */ + Oid relOid; /* OID of table, if ALTER TABLE case */ + List *columns; /* ColumnDef items */ + List *ckconstraints; /* CHECK constraints */ + List *fkconstraints; /* FOREIGN KEY constraints */ + List *ixconstraints; /* index-creating constraints */ + List *blist; /* "before list" of things to do before + * creating the table */ + List *alist; /* "after list" of things to do after + * creating the table */ + IndexStmt *pkey; /* PRIMARY KEY index, if any */ +} CreateStmtContext; + + static Query *transformStmt(ParseState *pstate, Node *stmt); static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt); static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt); @@ -52,17 +74,28 @@ static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt); static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt); static Query *transformCreateStmt(ParseState *pstate, CreateStmt *stmt); static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt); +static void transformColumnDefinition(ParseState *pstate, + CreateStmtContext *cxt, + ColumnDef *column); +static void transformTableConstraint(ParseState *pstate, + CreateStmtContext *cxt, + Constraint *constraint); +static void transformIndexConstraints(ParseState *pstate, + CreateStmtContext *cxt); +static void transformFKConstraints(ParseState *pstate, + CreateStmtContext *cxt); static Node *transformTypeRefs(ParseState *pstate, Node *stmt); static void transformTypeRefsList(ParseState *pstate, List *l); static void transformTypeRef(ParseState *pstate, TypeName *tn); static List *getSetColTypes(ParseState *pstate, Node *node); static void transformForUpdate(Query *qry, List *forUpdate); -static void transformFkeyGetPrimaryKey(FkConstraint *fkconstraint, Oid *pktypoid); static void transformConstraintAttrs(List *constraintList); static void transformColumnType(ParseState *pstate, ColumnDef *column); static void transformFkeyCheckAttrs(FkConstraint *fkconstraint, Oid *pktypoid); - +static void transformFkeyGetPrimaryKey(FkConstraint *fkconstraint, Oid *pktypoid); +static bool relationHasPrimaryKey(char *relname); +static Oid transformFkeyGetColType(CreateStmtContext *cxt, char *colname); static void release_pstate_resources(ParseState *pstate); static FromExpr *makeFromExpr(List *fromlist, Node *quals); @@ -679,296 +712,341 @@ CreateIndexName(char *table_name, char *column_name, static Query * transformCreateStmt(ParseState *pstate, CreateStmt *stmt) { + CreateStmtContext cxt; Query *q; List *elements; - Node *element; - List *columns; - List *dlist; - ColumnDef *column; - List *constraints, - *clist; - Constraint *constraint; - List *fkconstraints, /* List of FOREIGN KEY constraints to */ - *fkclist; /* add finally */ - FkConstraint *fkconstraint; - List *keys; - Ident *key; - List *blist = NIL; /* "before list" of things to do before - * creating the table */ - List *ilist = NIL; /* "index list" of things to do after - * creating the table */ - IndexStmt *index, - *pkey = NULL; - IndexElem *iparam; - bool saw_nullable; - bool is_serial; - q = makeNode(Query); - q->commandType = CMD_UTILITY; - - fkconstraints = NIL; - constraints = stmt->constraints; - columns = NIL; - dlist = NIL; + cxt.stmtType = "CREATE TABLE"; + cxt.relname = stmt->relname; + cxt.inhRelnames = stmt->inhRelnames; + cxt.istemp = stmt->istemp; + cxt.relOid = InvalidOid; + cxt.columns = NIL; + cxt.ckconstraints = NIL; + cxt.fkconstraints = NIL; + cxt.ixconstraints = NIL; + cxt.blist = NIL; + cxt.alist = NIL; + cxt.pkey = NULL; /* - * Run through each primary element in the table creation clause + * Run through each primary element in the table creation clause. + * Separate column defs from constraints, and do preliminary analysis. */ foreach(elements, stmt->tableElts) { - element = lfirst(elements); + Node *element = lfirst(elements); + switch (nodeTag(element)) { case T_ColumnDef: - column = (ColumnDef *) element; - columns = lappend(columns, column); + transformColumnDefinition(pstate, &cxt, + (ColumnDef *) element); + break; - /* Check for SERIAL pseudo-types */ - is_serial = false; - if (strcmp(column->typename->name, "serial") == 0 || - strcmp(column->typename->name, "serial4") == 0) - { - is_serial = true; - column->typename->name = pstrdup("int4"); - } - else if (strcmp(column->typename->name, "bigserial") == 0 || - strcmp(column->typename->name, "serial8") == 0) - { - is_serial = true; - column->typename->name = pstrdup("int8"); - } + case T_Constraint: + transformTableConstraint(pstate, &cxt, + (Constraint *) element); + break; - /* Do necessary work on the column type declaration */ - transformColumnType(pstate, column); + case T_FkConstraint: + /* No pre-transformation needed */ + cxt.fkconstraints = lappend(cxt.fkconstraints, element); + break; - /* Special actions for SERIAL pseudo-types */ - if (is_serial) - { - char *sname; - char *qstring; - A_Const *snamenode; - FuncCall *funccallnode; - CreateSeqStmt *sequence; + default: + elog(ERROR, "parser: unrecognized node (internal error)"); + } + } - /* - * Create appropriate constraints for SERIAL. We do - * this in full, rather than shortcutting, so that we - * will detect any conflicting constraints the user - * wrote (like a different DEFAULT). - */ - sname = makeObjectName(stmt->relname, column->colname, - "seq"); + Assert(stmt->constraints == NIL); - /* - * Create an expression tree representing the function - * call nextval('"sequencename"') - */ - qstring = palloc(strlen(sname) + 2 + 1); - sprintf(qstring, "\"%s\"", sname); - snamenode = makeNode(A_Const); - snamenode->val.type = T_String; - snamenode->val.val.str = qstring; - funccallnode = makeNode(FuncCall); - funccallnode->funcname = "nextval"; - funccallnode->args = makeList1(snamenode); - funccallnode->agg_star = false; - funccallnode->agg_distinct = false; - - constraint = makeNode(Constraint); - constraint->contype = CONSTR_DEFAULT; - constraint->name = sname; - constraint->raw_expr = (Node *) funccallnode; - constraint->cooked_expr = NULL; - constraint->keys = NIL; - column->constraints = lappend(column->constraints, - constraint); - - constraint = makeNode(Constraint); - constraint->contype = CONSTR_UNIQUE; - constraint->name = NULL; /* assign later */ - column->constraints = lappend(column->constraints, - constraint); - - constraint = makeNode(Constraint); - constraint->contype = CONSTR_NOTNULL; - column->constraints = lappend(column->constraints, - constraint); + /* + * Postprocess constraints that give rise to index definitions. + */ + transformIndexConstraints(pstate, &cxt); - /* - * Build a CREATE SEQUENCE command to create the - * sequence object, and add it to the list of things - * to be done before this CREATE TABLE. - */ - sequence = makeNode(CreateSeqStmt); - sequence->seqname = pstrdup(sname); - sequence->istemp = stmt->istemp; - sequence->options = NIL; + /* + * Postprocess foreign-key constraints. + */ + transformFKConstraints(pstate, &cxt); + + /* + * Output results. + */ + q = makeNode(Query); + q->commandType = CMD_UTILITY; + q->utilityStmt = (Node *) stmt; + stmt->tableElts = cxt.columns; + stmt->constraints = cxt.ckconstraints; + extras_before = cxt.blist; + extras_after = cxt.alist; - elog(NOTICE, "CREATE TABLE will create implicit sequence '%s' for SERIAL column '%s.%s'", - sequence->seqname, stmt->relname, column->colname); + return q; +} - blist = lappend(blist, sequence); - } +static void +transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt, + ColumnDef *column) +{ + bool is_serial; + bool saw_nullable; + Constraint *constraint; + List *clist; + Ident *key; - /* Process column constraints, if any... */ - transformConstraintAttrs(column->constraints); + cxt->columns = lappend(cxt->columns, column); - saw_nullable = false; + /* Check for SERIAL pseudo-types */ + is_serial = false; + if (strcmp(column->typename->name, "serial") == 0 || + strcmp(column->typename->name, "serial4") == 0) + { + is_serial = true; + column->typename->name = pstrdup("int4"); + } + else if (strcmp(column->typename->name, "bigserial") == 0 || + strcmp(column->typename->name, "serial8") == 0) + { + is_serial = true; + column->typename->name = pstrdup("int8"); + } - foreach(clist, column->constraints) - { - constraint = lfirst(clist); + /* Do necessary work on the column type declaration */ + transformColumnType(pstate, column); - /* - * If this column constraint is a FOREIGN KEY - * constraint, then we fill in the current attributes - * name and throw it into the list of FK constraints - * to be processed later. - */ - if (IsA(constraint, FkConstraint)) - { - Ident *id = makeNode(Ident); + /* Special actions for SERIAL pseudo-types */ + if (is_serial) + { + char *sname; + char *qstring; + A_Const *snamenode; + FuncCall *funccallnode; + CreateSeqStmt *sequence; - id->name = column->colname; - id->indirection = NIL; - id->isRel = false; + /* + * Create appropriate constraints for SERIAL. We do + * this in full, rather than shortcutting, so that we + * will detect any conflicting constraints the user + * wrote (like a different DEFAULT). + */ + sname = makeObjectName(cxt->relname, column->colname, "seq"); - fkconstraint = (FkConstraint *) constraint; - fkconstraint->fk_attrs = makeList1(id); + /* + * Create an expression tree representing the function + * call nextval('"sequencename"') + */ + qstring = palloc(strlen(sname) + 2 + 1); + sprintf(qstring, "\"%s\"", sname); + snamenode = makeNode(A_Const); + snamenode->val.type = T_String; + snamenode->val.val.str = qstring; + funccallnode = makeNode(FuncCall); + funccallnode->funcname = "nextval"; + funccallnode->args = makeList1(snamenode); + funccallnode->agg_star = false; + funccallnode->agg_distinct = false; + + constraint = makeNode(Constraint); + constraint->contype = CONSTR_DEFAULT; + constraint->name = sname; + constraint->raw_expr = (Node *) funccallnode; + constraint->cooked_expr = NULL; + constraint->keys = NIL; + column->constraints = lappend(column->constraints, constraint); + + constraint = makeNode(Constraint); + constraint->contype = CONSTR_UNIQUE; + constraint->name = NULL; /* assign later */ + column->constraints = lappend(column->constraints, constraint); + + constraint = makeNode(Constraint); + constraint->contype = CONSTR_NOTNULL; + column->constraints = lappend(column->constraints, constraint); - fkconstraints = lappend(fkconstraints, constraint); - continue; - } + /* + * Build a CREATE SEQUENCE command to create the + * sequence object, and add it to the list of things + * to be done before this CREATE/ALTER TABLE. + */ + sequence = makeNode(CreateSeqStmt); + sequence->seqname = pstrdup(sname); + sequence->istemp = cxt->istemp; + sequence->options = NIL; - switch (constraint->contype) - { - case CONSTR_NULL: - if (saw_nullable && column->is_not_null) - elog(ERROR, "CREATE TABLE/(NOT) NULL conflicting declaration" - " for '%s.%s'", stmt->relname, column->colname); - column->is_not_null = FALSE; - saw_nullable = true; - break; + elog(NOTICE, "%s will create implicit sequence '%s' for SERIAL column '%s.%s'", + cxt->stmtType, sequence->seqname, cxt->relname, column->colname); - case CONSTR_NOTNULL: - if (saw_nullable && !column->is_not_null) - elog(ERROR, "CREATE TABLE/(NOT) NULL conflicting declaration" - " for '%s.%s'", stmt->relname, column->colname); - column->is_not_null = TRUE; - saw_nullable = true; - break; + cxt->blist = lappend(cxt->blist, sequence); + } - case CONSTR_DEFAULT: - if (column->raw_default != NULL) - elog(ERROR, "CREATE TABLE/DEFAULT multiple values specified" - " for '%s.%s'", stmt->relname, column->colname); - column->raw_default = constraint->raw_expr; - Assert(constraint->cooked_expr == NULL); - break; + /* Process column constraints, if any... */ + transformConstraintAttrs(column->constraints); - case CONSTR_PRIMARY: - if (constraint->name == NULL) - constraint->name = makeObjectName(stmt->relname, NULL, "pkey"); - if (constraint->keys == NIL) - { - key = makeNode(Ident); - key->name = pstrdup(column->colname); - constraint->keys = makeList1(key); - } - dlist = lappend(dlist, constraint); - break; + saw_nullable = false; - case CONSTR_UNIQUE: - if (constraint->name == NULL) - constraint->name = makeObjectName(stmt->relname, column->colname, "key"); - if (constraint->keys == NIL) - { - key = makeNode(Ident); - key->name = pstrdup(column->colname); - constraint->keys = makeList1(key); - } - dlist = lappend(dlist, constraint); - break; + foreach(clist, column->constraints) + { + constraint = lfirst(clist); - case CONSTR_CHECK: - if (constraint->name == NULL) - constraint->name = makeObjectName(stmt->relname, column->colname, NULL); - constraints = lappend(constraints, constraint); - break; + /* + * If this column constraint is a FOREIGN KEY + * constraint, then we fill in the current attributes + * name and throw it into the list of FK constraints + * to be processed later. + */ + if (IsA(constraint, FkConstraint)) + { + FkConstraint *fkconstraint = (FkConstraint *) constraint; + Ident *id = makeNode(Ident); - case CONSTR_ATTR_DEFERRABLE: - case CONSTR_ATTR_NOT_DEFERRABLE: - case CONSTR_ATTR_DEFERRED: - case CONSTR_ATTR_IMMEDIATE: - /* transformConstraintAttrs took care of these */ - break; + id->name = column->colname; + id->indirection = NIL; + id->isRel = false; - default: - elog(ERROR, "parser: unrecognized constraint (internal error)"); - break; - } - } - break; + fkconstraint->fk_attrs = makeList1(id); - case T_Constraint: - constraint = (Constraint *) element; - switch (constraint->contype) - { - case CONSTR_PRIMARY: - if (constraint->name == NULL) - constraint->name = makeObjectName(stmt->relname, NULL, "pkey"); - dlist = lappend(dlist, constraint); - break; + cxt->fkconstraints = lappend(cxt->fkconstraints, fkconstraint); + continue; + } - case CONSTR_UNIQUE: - dlist = lappend(dlist, constraint); - break; + Assert(IsA(constraint, Constraint)); - case CONSTR_CHECK: - constraints = lappend(constraints, constraint); - break; + switch (constraint->contype) + { + case CONSTR_NULL: + if (saw_nullable && column->is_not_null) + elog(ERROR, "%s/(NOT) NULL conflicting declaration for '%s.%s'", + cxt->stmtType, cxt->relname, column->colname); + column->is_not_null = FALSE; + saw_nullable = true; + break; - case CONSTR_NULL: - case CONSTR_NOTNULL: - case CONSTR_DEFAULT: - case CONSTR_ATTR_DEFERRABLE: - case CONSTR_ATTR_NOT_DEFERRABLE: - case CONSTR_ATTR_DEFERRED: - case CONSTR_ATTR_IMMEDIATE: - elog(ERROR, "parser: illegal context for constraint (internal error)"); - break; + case CONSTR_NOTNULL: + if (saw_nullable && !column->is_not_null) + elog(ERROR, "%s/(NOT) NULL conflicting declaration for '%s.%s'", + cxt->stmtType, cxt->relname, column->colname); + column->is_not_null = TRUE; + saw_nullable = true; + break; - default: - elog(ERROR, "parser: unrecognized constraint (internal error)"); - break; + case CONSTR_DEFAULT: + if (column->raw_default != NULL) + elog(ERROR, "%s/DEFAULT multiple values specified for '%s.%s'", + cxt->stmtType, cxt->relname, column->colname); + column->raw_default = constraint->raw_expr; + Assert(constraint->cooked_expr == NULL); + break; + + case CONSTR_PRIMARY: + if (constraint->name == NULL) + constraint->name = makeObjectName(cxt->relname, + NULL, + "pkey"); + if (constraint->keys == NIL) + { + key = makeNode(Ident); + key->name = pstrdup(column->colname); + constraint->keys = makeList1(key); } + cxt->ixconstraints = lappend(cxt->ixconstraints, constraint); break; - case T_FkConstraint: + case CONSTR_UNIQUE: + if (constraint->name == NULL) + constraint->name = makeObjectName(cxt->relname, + column->colname, + "key"); + if (constraint->keys == NIL) + { + key = makeNode(Ident); + key->name = pstrdup(column->colname); + constraint->keys = makeList1(key); + } + cxt->ixconstraints = lappend(cxt->ixconstraints, constraint); + break; - /* - * Table level FOREIGN KEY constraints are already - * complete. Just remember for later. - */ - fkconstraints = lappend(fkconstraints, element); + case CONSTR_CHECK: + if (constraint->name == NULL) + constraint->name = makeObjectName(cxt->relname, + column->colname, + NULL); + cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); + break; + + case CONSTR_ATTR_DEFERRABLE: + case CONSTR_ATTR_NOT_DEFERRABLE: + case CONSTR_ATTR_DEFERRED: + case CONSTR_ATTR_IMMEDIATE: + /* transformConstraintAttrs took care of these */ break; default: - elog(ERROR, "parser: unrecognized node (internal error)"); + elog(ERROR, "parser: unrecognized constraint (internal error)"); + break; } } +} - stmt->tableElts = columns; - stmt->constraints = constraints; +static void +transformTableConstraint(ParseState *pstate, CreateStmtContext *cxt, + Constraint *constraint) +{ + switch (constraint->contype) + { + case CONSTR_PRIMARY: + if (constraint->name == NULL) + constraint->name = makeObjectName(cxt->relname, + NULL, + "pkey"); + cxt->ixconstraints = lappend(cxt->ixconstraints, constraint); + break; -/* Now run through the "deferred list" to complete the query transformation. - * For PRIMARY KEY, mark each column as NOT NULL and create an index. - * For UNIQUE, create an index as for PRIMARY KEY, but do not insist on - * NOT NULL. - */ - while (dlist != NIL) + case CONSTR_UNIQUE: + cxt->ixconstraints = lappend(cxt->ixconstraints, constraint); + break; + + case CONSTR_CHECK: + cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); + break; + + case CONSTR_NULL: + case CONSTR_NOTNULL: + case CONSTR_DEFAULT: + case CONSTR_ATTR_DEFERRABLE: + case CONSTR_ATTR_NOT_DEFERRABLE: + case CONSTR_ATTR_DEFERRED: + case CONSTR_ATTR_IMMEDIATE: + elog(ERROR, "parser: illegal context for constraint (internal error)"); + break; + + default: + elog(ERROR, "parser: unrecognized constraint (internal error)"); + break; + } +} + +static void +transformIndexConstraints(ParseState *pstate, CreateStmtContext *cxt) +{ + List *listptr; + List *keys; + IndexStmt *index; + IndexElem *iparam; + ColumnDef *column; + List *columns; + List *indexlist = NIL; + + /* + * Run through the constraints that need to generate an index. + * For PRIMARY KEY, mark each column as NOT NULL and create an index. + * For UNIQUE, create an index as for PRIMARY KEY, but do not insist on + * NOT NULL. + */ + foreach(listptr, cxt->ixconstraints) { - constraint = lfirst(dlist); + Constraint *constraint = lfirst(listptr); + Assert(IsA(constraint, Constraint)); Assert((constraint->contype == CONSTR_PRIMARY) || (constraint->contype == CONSTR_UNIQUE)); @@ -979,32 +1057,40 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt) index->primary = (constraint->contype == CONSTR_PRIMARY); if (index->primary) { - if (pkey != NULL) - elog(ERROR, "CREATE TABLE/PRIMARY KEY multiple primary keys" - " for table '%s' are not allowed", stmt->relname); - pkey = index; + /* In ALTER TABLE case, a primary index might already exist */ + if (cxt->pkey != NULL || + (OidIsValid(cxt->relOid) && + relationHasPrimaryKey(cxt->relname))) + elog(ERROR, "%s/PRIMARY KEY multiple primary keys" + " for table '%s' are not allowed", + cxt->stmtType, cxt->relname); + cxt->pkey = index; } if (constraint->name != NULL) index->idxname = pstrdup(constraint->name); else if (constraint->contype == CONSTR_PRIMARY) - index->idxname = makeObjectName(stmt->relname, NULL, "pkey"); + index->idxname = makeObjectName(cxt->relname, NULL, "pkey"); else index->idxname = NULL; /* will set it later */ - index->relname = stmt->relname; + index->relname = cxt->relname; index->accessMethod = "btree"; index->indexParams = NIL; index->whereClause = NULL; + /* + * Make sure referenced keys exist. If we are making a + * PRIMARY KEY index, also make sure they are NOT NULL. + */ foreach(keys, constraint->keys) { + Ident *key = (Ident *) lfirst(keys); bool found = false; - key = (Ident *) lfirst(keys); Assert(IsA(key, Ident)); column = NULL; - foreach(columns, stmt->tableElts) + foreach(columns, cxt->columns) { column = lfirst(columns); Assert(IsA(column, ColumnDef)); @@ -1020,13 +1106,12 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt) if (constraint->contype == CONSTR_PRIMARY) column->is_not_null = TRUE; } - else + else if (cxt->inhRelnames) { /* try inherited tables */ - List *inhRelnames = stmt->inhRelnames; List *inher; - foreach(inher, inhRelnames) + foreach(inher, cxt->inhRelnames) { Value *inh = lfirst(inher); Relation rel; @@ -1072,18 +1157,41 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt) break; } } + else if (OidIsValid(cxt->relOid)) + { + /* ALTER TABLE case: does column already exist? */ + HeapTuple atttuple; + + atttuple = SearchSysCache(ATTNAME, + ObjectIdGetDatum(cxt->relOid), + PointerGetDatum(key->name), + 0, 0); + if (HeapTupleIsValid(atttuple)) + { + found = true; + /* + * We require pre-existing column to be already marked + * NOT NULL. + */ + if (constraint->contype == CONSTR_PRIMARY && + !((Form_pg_attribute) GETSTRUCT(atttuple))->attnotnull) + elog(ERROR, "Existing attribute \"%s\" cannot be a PRIMARY KEY because it is not marked NOT NULL", + key->name); + ReleaseSysCache(atttuple); + } + } if (!found) - elog(ERROR, "CREATE TABLE: column \"%s\" named in key does not exist", - key->name); + elog(ERROR, "%s: column \"%s\" named in key does not exist", + cxt->stmtType, key->name); /* Check for PRIMARY KEY(foo, foo) */ foreach(columns, index->indexParams) { iparam = (IndexElem *) lfirst(columns); if (strcmp(key->name, iparam->name) == 0) - elog(ERROR, "CREATE TABLE: column \"%s\" appears twice in %s constraint", - key->name, + elog(ERROR, "%s: column \"%s\" appears twice in %s constraint", + cxt->stmtType, key->name, index->primary ? "PRIMARY KEY" : "UNIQUE"); } @@ -1095,8 +1203,7 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt) index->indexParams = lappend(index->indexParams, iparam); } - ilist = lappend(ilist, index); - dlist = lnext(dlist); + indexlist = lappend(indexlist, index); } /* @@ -1105,31 +1212,32 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt) * KEY or SERIAL UNIQUE. A strict reading of SQL92 would suggest * raising an error instead, but that strikes me as too * anal-retentive. - tgl 2001-02-14 + * + * XXX in ALTER TABLE case, it'd be nice to look for duplicate + * pre-existing indexes, too. */ - dlist = ilist; - ilist = NIL; - if (pkey != NULL) + cxt->alist = NIL; + if (cxt->pkey != NULL) { /* Make sure we keep the PKEY index in preference to others... */ - ilist = makeList1(pkey); + cxt->alist = makeList1(cxt->pkey); } - while (dlist != NIL) + while (indexlist != NIL) { - index = lfirst(dlist); + index = lfirst(indexlist); - /* if it's pkey, it's already in ilist */ - if (index != pkey) + /* if it's pkey, it's already in cxt->alist */ + if (index != cxt->pkey) { bool keep = true; List *priorlist; - foreach(priorlist, ilist) + foreach(priorlist, cxt->alist) { IndexStmt *priorindex = lfirst(priorlist); if (equal(index->indexParams, priorindex->indexParams)) { - /* * If the prior index is as yet unnamed, and this one * is named, then transfer the name to the prior @@ -1145,502 +1253,411 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt) } if (keep) - ilist = lappend(ilist, index); + cxt->alist = lappend(cxt->alist, index); } - dlist = lnext(dlist); + indexlist = lnext(indexlist); } /* * Finally, select unique names for all not-previously-named indices, * and display notice messages. + * + * XXX in ALTER TABLE case, we fail to consider name collisions + * against pre-existing indexes. */ - dlist = ilist; - while (dlist != NIL) + foreach(indexlist, cxt->alist) { - index = lfirst(dlist); + index = lfirst(indexlist); if (index->idxname == NULL && index->indexParams != NIL) { iparam = lfirst(index->indexParams); - index->idxname = CreateIndexName(stmt->relname, iparam->name, - "key", ilist); + index->idxname = CreateIndexName(cxt->relname, iparam->name, + "key", cxt->alist); } if (index->idxname == NULL) /* should not happen */ - elog(ERROR, "CREATE TABLE: failed to make implicit index name"); + elog(ERROR, "%s: failed to make implicit index name", + cxt->stmtType); - elog(NOTICE, "CREATE TABLE/%s will create implicit index '%s' for table '%s'", - (index->primary ? "PRIMARY KEY" : "UNIQUE"), - index->idxname, stmt->relname); - - dlist = lnext(dlist); + elog(NOTICE, "%s/%s will create implicit index '%s' for table '%s'", + cxt->stmtType, (index->primary ? "PRIMARY KEY" : "UNIQUE"), + index->idxname, cxt->relname); } +} - q->utilityStmt = (Node *) stmt; - extras_before = blist; - extras_after = ilist; +static void +transformFKConstraints(ParseState *pstate, CreateStmtContext *cxt) +{ + CreateTrigStmt *fk_trigger; + List *fkactions = NIL; + List *fkclist; + List *fk_attr; + List *pk_attr; + Ident *id; + Oid pktypoid[INDEX_MAX_KEYS]; + Oid fktypoid[INDEX_MAX_KEYS]; + int i; + + if (cxt->fkconstraints == NIL) + return; - /* - * Now process the FOREIGN KEY constraints and add appropriate queries - * to the extras_after statements list. - */ - if (fkconstraints != NIL) + elog(NOTICE, "%s will create implicit trigger(s) for FOREIGN KEY check(s)", + cxt->stmtType); + + foreach(fkclist, cxt->fkconstraints) { - CreateTrigStmt *fk_trigger; - List *fk_attr; - List *pk_attr; - Ident *id; - Oid pktypoid[INDEX_MAX_KEYS]; - Oid fktypoid[INDEX_MAX_KEYS]; - int i; + FkConstraint *fkconstraint = (FkConstraint *) lfirst(fkclist); + int attnum; + List *fkattrs; - for (i=0; i<INDEX_MAX_KEYS; i++) { - pktypoid[i]=fktypoid[i]=0; - } - elog(NOTICE, "CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)"); + /* + * If the constraint has no name, set it to <unnamed> + */ + if (fkconstraint->constr_name == NULL) + fkconstraint->constr_name = "<unnamed>"; + + for (attnum=0; attnum<INDEX_MAX_KEYS; attnum++) + pktypoid[attnum] = fktypoid[attnum] = InvalidOid; - foreach(fkclist, fkconstraints) + /* + * Look up the referencing attributes to make sure they exist + * (or will exist) in this table, and remember their type OIDs. + */ + attnum = 0; + foreach(fkattrs, fkconstraint->fk_attrs) { - fkconstraint = (FkConstraint *) lfirst(fkclist); + Ident *fkattr = lfirst(fkattrs); - /* - * If the constraint has no name, set it to <unnamed> - * - */ - if (fkconstraint->constr_name == NULL) - fkconstraint->constr_name = "<unnamed>"; + if (attnum >= INDEX_MAX_KEYS) + elog(ERROR, "Can only have %d keys in a foreign key", + INDEX_MAX_KEYS); + fktypoid[attnum++] = transformFkeyGetColType(cxt, + fkattr->name); + } - /* - * Check to see if the attributes mentioned by the constraint - * actually exist on this table. - */ - if (fkconstraint->fk_attrs != NIL) + /* + * If the attribute list for the referenced table was omitted, + * lookup the definition of the primary key. + */ + if (fkconstraint->pk_attrs == NIL) + { + if (strcmp(fkconstraint->pktable_name, cxt->relname) != 0) + transformFkeyGetPrimaryKey(fkconstraint, pktypoid); + else if (cxt->pkey != NULL) { - int found = 0; - int attnum=0; - List *cols; - List *fkattrs; - Ident *fkattr = NULL; - ColumnDef *col; - - foreach(fkattrs, fkconstraint->fk_attrs) - { - found = 0; - fkattr = lfirst(fkattrs); - foreach(cols, stmt->tableElts) - { - col = lfirst(cols); - if (strcmp(col->colname, fkattr->name) == 0) - { - char *buff=TypeNameToInternalName(col->typename); - Oid type=typenameTypeId(buff); - if (!OidIsValid(type)) { - elog(ERROR, "Unable to lookup type %s", col->typename->name); - } - fktypoid[attnum++]=type; - found = 1; - break; - } - } - if (!found) { - List *inher; - List *inhRelnames = stmt->inhRelnames; - Relation rel; + /* Use the to-be-created primary key */ + List *attr; - foreach(inher, inhRelnames) - { - Value *inh = lfirst(inher); - int count; - - Assert(IsA(inh, String)); - rel = heap_openr(strVal(inh), AccessShareLock); - if (rel->rd_rel->relkind != RELKIND_RELATION) - elog(ERROR, "inherited table \"%s\" is not a relation", - strVal(inh)); - for (count = 0; count < rel->rd_att->natts; count++) - { - char *name = NameStr(rel->rd_att->attrs[count]->attname); - - if (strcmp(fkattr->name, name) == 0) - { - fktypoid[attnum++]=rel->rd_att->attrs[count]->atttypid; - found = 1; - break; - } - } - heap_close(rel, NoLock); - if (found) - break; - } - } - if (!found) - break; + attnum=0; + foreach(attr, cxt->pkey->indexParams) + { + IndexElem *ielem = lfirst(attr); + Ident *pkattr = (Ident *) makeNode(Ident); + + pkattr->name = pstrdup(ielem->name); + pkattr->indirection = NIL; + pkattr->isRel = false; + fkconstraint->pk_attrs = lappend(fkconstraint->pk_attrs, + pkattr); + if (attnum >= INDEX_MAX_KEYS) + elog(ERROR, "Can only have %d keys in a foreign key", + INDEX_MAX_KEYS); + pktypoid[attnum++] = transformFkeyGetColType(cxt, + ielem->name); } - if (!found) - elog(ERROR, "columns referenced in foreign key constraint not found."); } - - /* - * If the attribute list for the referenced table was omitted, - * lookup for the definition of the primary key. If the - * referenced table is this table, use the definition we found - * above, rather than looking to the system tables. - * - */ - if (fkconstraint->fk_attrs != NIL && fkconstraint->pk_attrs == NIL) + else { - if (strcmp(fkconstraint->pktable_name, stmt->relname) != 0) + /* In ALTER TABLE case, primary key may already exist */ + if (OidIsValid(cxt->relOid)) transformFkeyGetPrimaryKey(fkconstraint, pktypoid); - else if (pkey != NULL) - { - List *pkey_attr = pkey->indexParams; - List *attr; - List *findattr; - IndexElem *ielem; - Ident *pkattr; - int attnum=0; - ColumnDef *col; - - foreach(attr, pkey_attr) - { - ielem = lfirst(attr); - pkattr = (Ident *) makeNode(Ident); - pkattr->name = pstrdup(ielem->name); - pkattr->indirection = NIL; - pkattr->isRel = false; - fkconstraint->pk_attrs = lappend(fkconstraint->pk_attrs, pkattr); - foreach (findattr, stmt->tableElts) { - col=lfirst(findattr); - if (strcmp(col->colname, ielem->name)==0) { - char *buff=TypeNameToInternalName(col->typename); - Oid type=typenameTypeId(buff); - if (!OidIsValid(type)) { - elog(ERROR, "Unable to lookup type %s", col->typename->name); - } - pktypoid[attnum++]=type; /* need to convert typename */ - break; - } - } - } - } else - { elog(ERROR, "PRIMARY KEY for referenced table \"%s\" not found", fkconstraint->pktable_name); - } } + } + else + { + /* Validate the specified referenced key list */ + if (strcmp(fkconstraint->pktable_name, cxt->relname) != 0) + transformFkeyCheckAttrs(fkconstraint, pktypoid); else { - if (strcmp(fkconstraint->pktable_name, stmt->relname) != 0) - transformFkeyCheckAttrs(fkconstraint, pktypoid); - else + /* Look for a matching new unique/primary constraint */ + List *index; + bool found = false; + + foreach(index, cxt->alist) { - /* Get a unique/pk constraint from above */ - List *index; - int found = 0; + IndexStmt *ind = lfirst(index); + List *pkattrs; - foreach(index, ilist) + if (!ind->unique) + continue; + if (length(ind->indexParams) != + length(fkconstraint->pk_attrs)) + continue; + attnum=0; + foreach(pkattrs, fkconstraint->pk_attrs) { - IndexStmt *ind = lfirst(index); - IndexElem *indparm; + Ident *pkattr = lfirst(pkattrs); List *indparms; - List *pkattrs; - List *findattr; - ColumnDef *col; - Ident *pkattr; - if (ind->unique) + found = false; + foreach(indparms, ind->indexParams) { - int count = 0; - int attnum=0; - - foreach(indparms, ind->indexParams) - count++; - if (count != length(fkconstraint->pk_attrs)) - found = 0; - else + IndexElem *indparm = lfirst(indparms); + + if (strcmp(indparm->name, pkattr->name) == 0) { - foreach(pkattrs, fkconstraint->pk_attrs) - { - found = 0; - pkattr = lfirst(pkattrs); - foreach(indparms, ind->indexParams) - { - indparm = lfirst(indparms); - if (strcmp(indparm->name, pkattr->name) == 0) - { - foreach (findattr, stmt->tableElts) { - col=lfirst(findattr); - if (strcmp(col->colname, indparm->name)==0) { - char *buff=TypeNameToInternalName(col->typename); - Oid type=typenameTypeId(buff); - if (!OidIsValid(type)) { - elog(ERROR, "Unable to lookup type %s", col->typename->name); - } - pktypoid[attnum++]=type; - found=1; - break; - } - } - if (!found) { - List *inher; - List *inhRelnames=stmt->inhRelnames; - Relation rel; - foreach (inher, inhRelnames) { - Value *inh=lfirst(inher); - int count; - Assert(IsA(inh, String)); - rel=heap_openr(strVal(inh), AccessShareLock); - if (rel->rd_rel->relkind!=RELKIND_RELATION) - elog(ERROR, "inherited table \"%s\" is not a relation", strVal(inh)); - for (count=0; count<rel->rd_att->natts; count++) { - char *name=NameStr(rel->rd_att->attrs[count]->attname); - if (strcmp(pkattr->name, name)==0) { - pktypoid[attnum++]=rel->rd_att->attrs[count]->atttypid; - found=1; - break; - } - } - heap_close(rel, NoLock); - if (found) - break; - } - } - break; - } - } - if (!found) - break; - } + found = true; + break; } } - if (found) + if (!found) break; + if (attnum >= INDEX_MAX_KEYS) + elog(ERROR, "Can only have %d keys in a foreign key", + INDEX_MAX_KEYS); + pktypoid[attnum++] = transformFkeyGetColType(cxt, + pkattr->name); } - if (!found) + if (found) + break; + } + if (!found) + { + /* In ALTER TABLE case, such an index may already exist */ + if (OidIsValid(cxt->relOid)) + transformFkeyCheckAttrs(fkconstraint, pktypoid); + else elog(ERROR, "UNIQUE constraint matching given keys for referenced table \"%s\" not found", fkconstraint->pktable_name); } } + } - for (i = 0; i < INDEX_MAX_KEYS && fktypoid[i] != 0; i++) { - /* - * fktypoid[i] is the foreign key table's i'th element's type oid - * pktypoid[i] is the primary key table's i'th element's type oid - * We let oper() do our work for us, including elog(ERROR) if the - * types don't compare with = - */ - Operator o=oper("=", fktypoid[i], pktypoid[i], false); - ReleaseSysCache(o); - } + /* Be sure referencing and referenced column types are comparable */ + for (i = 0; i < INDEX_MAX_KEYS && fktypoid[i] != 0; i++) + { /* - * Build a CREATE CONSTRAINT TRIGGER statement for the CHECK - * action. - * + * fktypoid[i] is the foreign key table's i'th element's type oid + * pktypoid[i] is the primary key table's i'th element's type oid + * We let oper() do our work for us, including elog(ERROR) if the + * types don't compare with = */ - fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt); - fk_trigger->trigname = fkconstraint->constr_name; - fk_trigger->relname = stmt->relname; - fk_trigger->funcname = "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 = fkconstraint->deferrable; - fk_trigger->initdeferred = fkconstraint->initdeferred; - fk_trigger->constrrelname = fkconstraint->pktable_name; - - fk_trigger->args = NIL; - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->constr_name)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(stmt->relname)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->pktable_name)); - fk_trigger->args = lappend(fk_trigger->args, + Operator o=oper("=", fktypoid[i], pktypoid[i], false); + ReleaseSysCache(o); + } + + /* + * Build a CREATE CONSTRAINT TRIGGER statement for the CHECK + * action. + */ + fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt); + fk_trigger->trigname = fkconstraint->constr_name; + fk_trigger->relname = cxt->relname; + fk_trigger->funcname = "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 = fkconstraint->deferrable; + fk_trigger->initdeferred = fkconstraint->initdeferred; + fk_trigger->constrrelname = fkconstraint->pktable_name; + + fk_trigger->args = NIL; + fk_trigger->args = lappend(fk_trigger->args, + makeString(fkconstraint->constr_name)); + fk_trigger->args = lappend(fk_trigger->args, + makeString(cxt->relname)); + fk_trigger->args = lappend(fk_trigger->args, + makeString(fkconstraint->pktable_name)); + fk_trigger->args = lappend(fk_trigger->args, makeString(fkconstraint->match_type)); - fk_attr = fkconstraint->fk_attrs; - pk_attr = fkconstraint->pk_attrs; - if (length(fk_attr) != length(pk_attr)) - { - elog(NOTICE, "Illegal FOREIGN KEY definition REFERENCES \"%s\"", - fkconstraint->pktable_name); - elog(ERROR, "number of key attributes in referenced table must be equal to foreign key"); - } - while (fk_attr != NIL) - { - id = (Ident *) lfirst(fk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); + fk_attr = fkconstraint->fk_attrs; + pk_attr = fkconstraint->pk_attrs; + if (length(fk_attr) != length(pk_attr)) + elog(ERROR, "number of key attributes in referenced table must be equal to foreign key" + "\n\tIllegal FOREIGN KEY definition references \"%s\"", + fkconstraint->pktable_name); + + while (fk_attr != NIL) + { + id = (Ident *) lfirst(fk_attr); + fk_trigger->args = lappend(fk_trigger->args, + makeString(id->name)); - id = (Ident *) lfirst(pk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); + id = (Ident *) lfirst(pk_attr); + fk_trigger->args = lappend(fk_trigger->args, + makeString(id->name)); - fk_attr = lnext(fk_attr); - pk_attr = lnext(pk_attr); - } + fk_attr = lnext(fk_attr); + pk_attr = lnext(pk_attr); + } - extras_after = lappend(extras_after, (Node *) fk_trigger); + fkactions = lappend(fkactions, (Node *) fk_trigger); - /* - * Build a CREATE CONSTRAINT TRIGGER statement for the ON - * DELETE action fired on the PK table !!! - * - */ - fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt); - fk_trigger->trigname = fkconstraint->constr_name; - fk_trigger->relname = fkconstraint->pktable_name; - 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 = fkconstraint->deferrable; - fk_trigger->initdeferred = fkconstraint->initdeferred; - fk_trigger->constrrelname = stmt->relname; - switch ((fkconstraint->actions & FKCONSTR_ON_DELETE_MASK) - >> FKCONSTR_ON_DELETE_SHIFT) - { - case FKCONSTR_ON_KEY_NOACTION: - fk_trigger->funcname = "RI_FKey_noaction_del"; - break; - case FKCONSTR_ON_KEY_RESTRICT: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = "RI_FKey_restrict_del"; - break; - case FKCONSTR_ON_KEY_CASCADE: - fk_trigger->funcname = "RI_FKey_cascade_del"; - break; - case FKCONSTR_ON_KEY_SETNULL: - fk_trigger->funcname = "RI_FKey_setnull_del"; - break; - case FKCONSTR_ON_KEY_SETDEFAULT: - fk_trigger->funcname = "RI_FKey_setdefault_del"; - break; - default: - elog(ERROR, "Only one ON DELETE action can be specified for FOREIGN KEY constraint"); - break; - } + /* + * Build a CREATE CONSTRAINT TRIGGER statement for the ON + * DELETE action fired on the PK table !!! + */ + fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt); + fk_trigger->trigname = fkconstraint->constr_name; + fk_trigger->relname = fkconstraint->pktable_name; + 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 = fkconstraint->deferrable; + fk_trigger->initdeferred = fkconstraint->initdeferred; + fk_trigger->constrrelname = cxt->relname; + switch ((fkconstraint->actions & FKCONSTR_ON_DELETE_MASK) + >> FKCONSTR_ON_DELETE_SHIFT) + { + case FKCONSTR_ON_KEY_NOACTION: + fk_trigger->funcname = "RI_FKey_noaction_del"; + break; + case FKCONSTR_ON_KEY_RESTRICT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = "RI_FKey_restrict_del"; + break; + case FKCONSTR_ON_KEY_CASCADE: + fk_trigger->funcname = "RI_FKey_cascade_del"; + break; + case FKCONSTR_ON_KEY_SETNULL: + fk_trigger->funcname = "RI_FKey_setnull_del"; + break; + case FKCONSTR_ON_KEY_SETDEFAULT: + fk_trigger->funcname = "RI_FKey_setdefault_del"; + break; + default: + elog(ERROR, "Only one ON DELETE action can be specified for FOREIGN KEY constraint"); + break; + } - fk_trigger->args = NIL; - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->constr_name)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(stmt->relname)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->pktable_name)); - fk_trigger->args = lappend(fk_trigger->args, + fk_trigger->args = NIL; + fk_trigger->args = lappend(fk_trigger->args, + makeString(fkconstraint->constr_name)); + fk_trigger->args = lappend(fk_trigger->args, + makeString(cxt->relname)); + fk_trigger->args = lappend(fk_trigger->args, + makeString(fkconstraint->pktable_name)); + fk_trigger->args = lappend(fk_trigger->args, makeString(fkconstraint->match_type)); - fk_attr = fkconstraint->fk_attrs; - pk_attr = fkconstraint->pk_attrs; - while (fk_attr != NIL) - { - id = (Ident *) lfirst(fk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); + fk_attr = fkconstraint->fk_attrs; + pk_attr = fkconstraint->pk_attrs; + while (fk_attr != NIL) + { + id = (Ident *) lfirst(fk_attr); + fk_trigger->args = lappend(fk_trigger->args, + makeString(id->name)); - id = (Ident *) lfirst(pk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); + id = (Ident *) lfirst(pk_attr); + fk_trigger->args = lappend(fk_trigger->args, + makeString(id->name)); - fk_attr = lnext(fk_attr); - pk_attr = lnext(pk_attr); - } + fk_attr = lnext(fk_attr); + pk_attr = lnext(pk_attr); + } - extras_after = lappend(extras_after, (Node *) fk_trigger); + fkactions = lappend(fkactions, (Node *) fk_trigger); - /* - * Build a CREATE CONSTRAINT TRIGGER statement for the ON - * UPDATE action fired on the PK table !!! - * - */ - fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt); - fk_trigger->trigname = fkconstraint->constr_name; - fk_trigger->relname = fkconstraint->pktable_name; - 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 = fkconstraint->deferrable; - fk_trigger->initdeferred = fkconstraint->initdeferred; - fk_trigger->constrrelname = stmt->relname; - switch ((fkconstraint->actions & FKCONSTR_ON_UPDATE_MASK) - >> FKCONSTR_ON_UPDATE_SHIFT) - { - case FKCONSTR_ON_KEY_NOACTION: - fk_trigger->funcname = "RI_FKey_noaction_upd"; - break; - case FKCONSTR_ON_KEY_RESTRICT: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = "RI_FKey_restrict_upd"; - break; - case FKCONSTR_ON_KEY_CASCADE: - fk_trigger->funcname = "RI_FKey_cascade_upd"; - break; - case FKCONSTR_ON_KEY_SETNULL: - fk_trigger->funcname = "RI_FKey_setnull_upd"; - break; - case FKCONSTR_ON_KEY_SETDEFAULT: - fk_trigger->funcname = "RI_FKey_setdefault_upd"; - break; - default: - elog(ERROR, "Only one ON UPDATE action can be specified for FOREIGN KEY constraint"); - break; - } + /* + * Build a CREATE CONSTRAINT TRIGGER statement for the ON + * UPDATE action fired on the PK table !!! + */ + fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt); + fk_trigger->trigname = fkconstraint->constr_name; + fk_trigger->relname = fkconstraint->pktable_name; + 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 = fkconstraint->deferrable; + fk_trigger->initdeferred = fkconstraint->initdeferred; + fk_trigger->constrrelname = cxt->relname; + switch ((fkconstraint->actions & FKCONSTR_ON_UPDATE_MASK) + >> FKCONSTR_ON_UPDATE_SHIFT) + { + case FKCONSTR_ON_KEY_NOACTION: + fk_trigger->funcname = "RI_FKey_noaction_upd"; + break; + case FKCONSTR_ON_KEY_RESTRICT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = "RI_FKey_restrict_upd"; + break; + case FKCONSTR_ON_KEY_CASCADE: + fk_trigger->funcname = "RI_FKey_cascade_upd"; + break; + case FKCONSTR_ON_KEY_SETNULL: + fk_trigger->funcname = "RI_FKey_setnull_upd"; + break; + case FKCONSTR_ON_KEY_SETDEFAULT: + fk_trigger->funcname = "RI_FKey_setdefault_upd"; + break; + default: + elog(ERROR, "Only one ON UPDATE action can be specified for FOREIGN KEY constraint"); + break; + } - fk_trigger->args = NIL; - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->constr_name)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(stmt->relname)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->pktable_name)); - fk_trigger->args = lappend(fk_trigger->args, + fk_trigger->args = NIL; + fk_trigger->args = lappend(fk_trigger->args, + makeString(fkconstraint->constr_name)); + fk_trigger->args = lappend(fk_trigger->args, + makeString(cxt->relname)); + fk_trigger->args = lappend(fk_trigger->args, + makeString(fkconstraint->pktable_name)); + fk_trigger->args = lappend(fk_trigger->args, makeString(fkconstraint->match_type)); - fk_attr = fkconstraint->fk_attrs; - pk_attr = fkconstraint->pk_attrs; - while (fk_attr != NIL) - { - id = (Ident *) lfirst(fk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); - - id = (Ident *) lfirst(pk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); + fk_attr = fkconstraint->fk_attrs; + pk_attr = fkconstraint->pk_attrs; + while (fk_attr != NIL) + { + id = (Ident *) lfirst(fk_attr); + fk_trigger->args = lappend(fk_trigger->args, + makeString(id->name)); - fk_attr = lnext(fk_attr); - pk_attr = lnext(pk_attr); - } + id = (Ident *) lfirst(pk_attr); + fk_trigger->args = lappend(fk_trigger->args, + makeString(id->name)); - extras_after = lappend(extras_after, (Node *) fk_trigger); + fk_attr = lnext(fk_attr); + pk_attr = lnext(pk_attr); } - } - return q; -} /* transformCreateStmt() */ + fkactions = lappend(fkactions, (Node *) fk_trigger); + } + /* + * Attach completed list of extra actions to cxt->alist. We cannot + * do this earlier, because we assume above that cxt->alist still + * holds only IndexStmts. + */ + cxt->alist = nconc(cxt->alist, fkactions); +} /* * transformIndexStmt - @@ -2481,262 +2498,89 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) /* * tranformAlterTableStmt - * transform an Alter Table Statement - * */ static Query * transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt) { + CreateStmtContext cxt; Query *qry; - qry = makeNode(Query); - qry->commandType = CMD_UTILITY; - /* - * The only subtypes that currently have special handling are 'A'dd - * column and Add 'C'onstraint. In addition, right now only Foreign - * Key 'C'onstraints have a special transformation. - * + * The only subtypes that currently require parse transformation + * handling are 'A'dd column and Add 'C'onstraint. These largely + * re-use code from CREATE TABLE. */ switch (stmt->subtype) { case 'A': - transformColumnType(pstate, (ColumnDef *) stmt->def); + cxt.stmtType = "ALTER TABLE"; + cxt.relname = stmt->relname; + cxt.inhRelnames = NIL; + cxt.istemp = is_temp_rel_name(stmt->relname); + cxt.relOid = GetSysCacheOid(RELNAME, + PointerGetDatum(stmt->relname), + 0, 0, 0); + cxt.columns = NIL; + cxt.ckconstraints = NIL; + cxt.fkconstraints = NIL; + cxt.ixconstraints = NIL; + cxt.blist = NIL; + cxt.alist = NIL; + cxt.pkey = NULL; + + Assert(IsA(stmt->def, ColumnDef)); + transformColumnDefinition(pstate, &cxt, + (ColumnDef *) stmt->def); + + transformIndexConstraints(pstate, &cxt); + transformFKConstraints(pstate, &cxt); + + ((ColumnDef *) stmt->def)->constraints = cxt.ckconstraints; + extras_before = cxt.blist; + extras_after = cxt.alist; break; - case 'C': - if (stmt->def && IsA(stmt->def, FkConstraint)) - { - CreateTrigStmt *fk_trigger; - List *fk_attr; - List *pk_attr; - Ident *id; - FkConstraint *fkconstraint; - - extras_after = NIL; - elog(NOTICE, "ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)"); - - fkconstraint = (FkConstraint *) stmt->def; - - /* - * If the constraint has no name, set it to <unnamed> - * - */ - if (fkconstraint->constr_name == NULL) - fkconstraint->constr_name = "<unnamed>"; - - /* - * If the attribute list for the referenced table was - * omitted, lookup for the definition of the primary key - * - */ - if (fkconstraint->fk_attrs != NIL && fkconstraint->pk_attrs == NIL) { - Oid pktypoid[INDEX_MAX_KEYS]; - transformFkeyGetPrimaryKey(fkconstraint, pktypoid); - } - - /* - * Build a CREATE CONSTRAINT TRIGGER statement for the - * CHECK action. - * - */ - fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt); - fk_trigger->trigname = fkconstraint->constr_name; - fk_trigger->relname = stmt->relname; - fk_trigger->funcname = "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 = fkconstraint->deferrable; - fk_trigger->initdeferred = fkconstraint->initdeferred; - fk_trigger->constrrelname = fkconstraint->pktable_name; - - fk_trigger->args = NIL; - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->constr_name)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(stmt->relname)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->pktable_name)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->match_type)); - fk_attr = fkconstraint->fk_attrs; - pk_attr = fkconstraint->pk_attrs; - if (length(fk_attr) != length(pk_attr)) - { - elog(NOTICE, "Illegal FOREIGN KEY definition REFERENCES \"%s\"", - fkconstraint->pktable_name); - elog(ERROR, "number of key attributes in referenced table must be equal to foreign key"); - } - while (fk_attr != NIL) - { - id = (Ident *) lfirst(fk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); - - id = (Ident *) lfirst(pk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); - - fk_attr = lnext(fk_attr); - pk_attr = lnext(pk_attr); - } - - extras_after = lappend(extras_after, (Node *) fk_trigger); - - /* - * Build a CREATE CONSTRAINT TRIGGER statement for the ON - * DELETE action fired on the PK table !!! - * - */ - fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt); - fk_trigger->trigname = fkconstraint->constr_name; - fk_trigger->relname = fkconstraint->pktable_name; - switch ((fkconstraint->actions & FKCONSTR_ON_DELETE_MASK) - >> FKCONSTR_ON_DELETE_SHIFT) - { - case FKCONSTR_ON_KEY_NOACTION: - fk_trigger->funcname = "RI_FKey_noaction_del"; - break; - case FKCONSTR_ON_KEY_RESTRICT: - fk_trigger->funcname = "RI_FKey_restrict_del"; - break; - case FKCONSTR_ON_KEY_CASCADE: - fk_trigger->funcname = "RI_FKey_cascade_del"; - break; - case FKCONSTR_ON_KEY_SETNULL: - fk_trigger->funcname = "RI_FKey_setnull_del"; - break; - case FKCONSTR_ON_KEY_SETDEFAULT: - fk_trigger->funcname = "RI_FKey_setdefault_del"; - break; - default: - elog(ERROR, "Only one ON DELETE action can be specified for FOREIGN KEY constraint"); - break; - } - 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 = fkconstraint->deferrable; - fk_trigger->initdeferred = fkconstraint->initdeferred; - fk_trigger->constrrelname = stmt->relname; - - fk_trigger->args = NIL; - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->constr_name)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(stmt->relname)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->pktable_name)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->match_type)); - fk_attr = fkconstraint->fk_attrs; - pk_attr = fkconstraint->pk_attrs; - while (fk_attr != NIL) - { - id = (Ident *) lfirst(fk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); - - id = (Ident *) lfirst(pk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); - - fk_attr = lnext(fk_attr); - pk_attr = lnext(pk_attr); - } - extras_after = lappend(extras_after, (Node *) fk_trigger); - - /* - * Build a CREATE CONSTRAINT TRIGGER statement for the ON - * UPDATE action fired on the PK table !!! - * - */ - fk_trigger = (CreateTrigStmt *) makeNode(CreateTrigStmt); - fk_trigger->trigname = fkconstraint->constr_name; - fk_trigger->relname = fkconstraint->pktable_name; - switch ((fkconstraint->actions & FKCONSTR_ON_UPDATE_MASK) - >> FKCONSTR_ON_UPDATE_SHIFT) - { - case FKCONSTR_ON_KEY_NOACTION: - fk_trigger->funcname = "RI_FKey_noaction_upd"; - break; - case FKCONSTR_ON_KEY_RESTRICT: - fk_trigger->funcname = "RI_FKey_restrict_upd"; - break; - case FKCONSTR_ON_KEY_CASCADE: - fk_trigger->funcname = "RI_FKey_cascade_upd"; - break; - case FKCONSTR_ON_KEY_SETNULL: - fk_trigger->funcname = "RI_FKey_setnull_upd"; - break; - case FKCONSTR_ON_KEY_SETDEFAULT: - fk_trigger->funcname = "RI_FKey_setdefault_upd"; - break; - default: - elog(ERROR, "Only one ON UPDATE action can be specified for FOREIGN KEY constraint"); - break; - } - 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 = fkconstraint->deferrable; - fk_trigger->initdeferred = fkconstraint->initdeferred; - fk_trigger->constrrelname = stmt->relname; - - fk_trigger->args = NIL; - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->constr_name)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(stmt->relname)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->pktable_name)); - fk_trigger->args = lappend(fk_trigger->args, - makeString(fkconstraint->match_type)); - fk_attr = fkconstraint->fk_attrs; - pk_attr = fkconstraint->pk_attrs; - while (fk_attr != NIL) - { - id = (Ident *) lfirst(fk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); - - id = (Ident *) lfirst(pk_attr); - fk_trigger->args = lappend(fk_trigger->args, - makeString(id->name)); + case 'C': + cxt.stmtType = "ALTER TABLE"; + cxt.relname = stmt->relname; + cxt.inhRelnames = NIL; + cxt.istemp = is_temp_rel_name(stmt->relname); + cxt.relOid = GetSysCacheOid(RELNAME, + PointerGetDatum(stmt->relname), + 0, 0, 0); + cxt.columns = NIL; + cxt.ckconstraints = NIL; + cxt.fkconstraints = NIL; + cxt.ixconstraints = NIL; + cxt.blist = NIL; + cxt.alist = NIL; + cxt.pkey = NULL; + + if (IsA(stmt->def, Constraint)) + transformTableConstraint(pstate, &cxt, + (Constraint *) stmt->def); + else if (IsA(stmt->def, FkConstraint)) + cxt.fkconstraints = lappend(cxt.fkconstraints, stmt->def); + else + elog(ERROR, "Unexpected node type in ALTER TABLE ADD CONSTRAINT"); - fk_attr = lnext(fk_attr); - pk_attr = lnext(pk_attr); - } + transformIndexConstraints(pstate, &cxt); + transformFKConstraints(pstate, &cxt); - extras_after = lappend(extras_after, (Node *) fk_trigger); - } + Assert(cxt.columns == NIL); + stmt->def = (Node *) nconc(cxt.ckconstraints, cxt.fkconstraints); + extras_before = cxt.blist; + extras_after = cxt.alist; break; + default: break; } + + qry = makeNode(Query); + qry->commandType = CMD_UTILITY; qry->utilityStmt = (Node *) stmt; + return qry; } @@ -2931,9 +2775,8 @@ transformForUpdate(Query *qry, List *forUpdate) /* * transformFkeyCheckAttrs - * - * Try to make sure that the attributes of a referenced table + * Make sure that the attributes of a referenced table * belong to a unique (or primary key) constraint. - * */ static void transformFkeyCheckAttrs(FkConstraint *fkconstraint, Oid *pktypoid) @@ -2949,9 +2792,6 @@ transformFkeyCheckAttrs(FkConstraint *fkconstraint, Oid *pktypoid) * Open the referenced table and get the attributes list */ pkrel = heap_openr(fkconstraint->pktable_name, AccessShareLock); - if (pkrel == NULL) - elog(ERROR, "referenced table \"%s\" not found", - fkconstraint->pktable_name); pkrel_attrs = pkrel->rd_att->attrs; /* @@ -2971,7 +2811,7 @@ transformFkeyCheckAttrs(FkConstraint *fkconstraint, Oid *pktypoid) ObjectIdGetDatum(indexoid), 0, 0, 0); if (!HeapTupleIsValid(indexTuple)) - elog(ERROR, "transformFkeyGetPrimaryKey: index %u not found", + elog(ERROR, "transformFkeyCheckAttrs: index %u not found", indexoid); indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); @@ -2980,7 +2820,8 @@ transformFkeyCheckAttrs(FkConstraint *fkconstraint, Oid *pktypoid) List *attrl; int attnum=0; - for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0; i++); + for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0; i++) + ; if (i != length(fkconstraint->pk_attrs)) found = false; else @@ -3030,7 +2871,6 @@ transformFkeyCheckAttrs(FkConstraint *fkconstraint, Oid *pktypoid) * * Try to find the primary key attributes of a referenced table if * the column list in the REFERENCES specification was omitted. - * */ static void transformFkeyGetPrimaryKey(FkConstraint *fkconstraint, Oid *pktypoid) @@ -3048,9 +2888,6 @@ transformFkeyGetPrimaryKey(FkConstraint *fkconstraint, Oid *pktypoid) * Open the referenced table and get the attributes list */ pkrel = heap_openr(fkconstraint->pktable_name, AccessShareLock); - if (pkrel == NULL) - elog(ERROR, "referenced table \"%s\" not found", - fkconstraint->pktable_name); pkrel_attrs = pkrel->rd_att->attrs; /* @@ -3095,11 +2932,10 @@ transformFkeyGetPrimaryKey(FkConstraint *fkconstraint, Oid *pktypoid) int pkattno = indexStruct->indkey[i]; Ident *pkattr = makeNode(Ident); - pkattr->name = DatumGetCString(DirectFunctionCall1(nameout, - NameGetDatum(&(pkrel_attrs[pkattno - 1]->attname)))); + pkattr->name = pstrdup(NameStr(pkrel_attrs[pkattno-1]->attname)); pkattr->indirection = NIL; pkattr->isRel = false; - pktypoid[attnum++]=pkrel_attrs[pkattno-1]->atttypid; + pktypoid[attnum++] = pkrel_attrs[pkattno-1]->atttypid; fkconstraint->pk_attrs = lappend(fkconstraint->pk_attrs, pkattr); } @@ -3110,6 +2946,130 @@ transformFkeyGetPrimaryKey(FkConstraint *fkconstraint, Oid *pktypoid) } /* + * relationHasPrimaryKey - + * + * See whether an existing relation has a primary key. + */ +static bool +relationHasPrimaryKey(char *relname) +{ + bool result = false; + Relation rel; + List *indexoidlist, + *indexoidscan; + + rel = heap_openr(relname, AccessShareLock); + + /* + * Get the list of index OIDs for the table from the relcache, and + * look up each one in the pg_index syscache until we find one marked + * primary key (hopefully there isn't more than one such). + */ + indexoidlist = RelationGetIndexList(rel); + + foreach(indexoidscan, indexoidlist) + { + Oid indexoid = lfirsti(indexoidscan); + HeapTuple indexTuple; + + indexTuple = SearchSysCache(INDEXRELID, + ObjectIdGetDatum(indexoid), + 0, 0, 0); + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "relationHasPrimaryKey: index %u not found", + indexoid); + result = ((Form_pg_index) GETSTRUCT(indexTuple))->indisprimary; + ReleaseSysCache(indexTuple); + if (result) + break; + } + + freeList(indexoidlist); + + heap_close(rel, AccessShareLock); + + return result; +} + +/* + * transformFkeyGetColType - + * + * Find a referencing column by name, and return its type OID. + * Error if it can't be found. + */ +static Oid +transformFkeyGetColType(CreateStmtContext *cxt, char *colname) +{ + List *cols; + List *inher; + Oid result; + + /* First look for column among the newly-created columns */ + foreach(cols, cxt->columns) + { + ColumnDef *col = lfirst(cols); + + if (strcmp(col->colname, colname) == 0) + { + char *buff = TypeNameToInternalName(col->typename); + + result = typenameTypeId(buff); + if (!OidIsValid(result)) + elog(ERROR, "Unable to lookup type %s", + col->typename->name); + return result; + } + } + /* Look for column among inherited columns (if CREATE TABLE case) */ + foreach(inher, cxt->inhRelnames) + { + Value *inh = lfirst(inher); + Relation rel; + int count; + + Assert(IsA(inh, String)); + rel = heap_openr(strVal(inh), AccessShareLock); + if (rel->rd_rel->relkind != RELKIND_RELATION) + elog(ERROR, "inherited table \"%s\" is not a relation", + strVal(inh)); + for (count = 0; count < rel->rd_att->natts; count++) + { + char *name = NameStr(rel->rd_att->attrs[count]->attname); + + if (strcmp(name, colname) == 0) + { + result = rel->rd_att->attrs[count]->atttypid; + + heap_close(rel, NoLock); + return result; + } + } + heap_close(rel, NoLock); + } + /* Look for column among existing columns (if ALTER TABLE case) */ + if (OidIsValid(cxt->relOid)) + { + HeapTuple atttuple; + + atttuple = SearchSysCache(ATTNAME, + ObjectIdGetDatum(cxt->relOid), + PointerGetDatum(colname), + 0, 0); + if (HeapTupleIsValid(atttuple)) + { + result = ((Form_pg_attribute) GETSTRUCT(atttuple))->atttypid; + + ReleaseSysCache(atttuple); + return result; + } + } + + elog(ERROR, "%s: column \"%s\" referenced in foreign key constraint does not exist", + cxt->stmtType, colname); + return InvalidOid; /* keep compiler quiet */ +} + +/* * Preprocess a list of column constraint clauses * to attach constraint attributes to their primary constraint nodes * and detect inconsistent/misplaced constraint attributes. diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 84a12a33e04..6822fe803bd 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.119 2001/10/09 14:00:22 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.120 2001/10/12 00:07:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -450,7 +450,7 @@ ProcessUtility(Node *parsetree, case 'C': /* ADD CONSTRAINT */ AlterTableAddConstraint(stmt->relname, interpretInhOption(stmt->inhOpt), - stmt->def); + (List *) stmt->def); break; case 'X': /* DROP CONSTRAINT */ AlterTableDropConstraint(stmt->relname, diff --git a/src/include/commands/command.h b/src/include/commands/command.h index 7eb1a4fab84..78aa06a2dfb 100644 --- a/src/include/commands/command.h +++ b/src/include/commands/command.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: command.h,v 1.27 2001/05/07 00:43:25 tgl Exp $ + * $Id: command.h,v 1.28 2001/10/12 00:07:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -56,7 +56,7 @@ extern void AlterTableDropColumn(const char *relationName, int behavior); extern void AlterTableAddConstraint(char *relationName, - bool inh, Node *newConstraint); + bool inh, List *newConstraints); extern void AlterTableDropConstraint(const char *relationName, bool inh, const char *constrName, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e190fb20bbb..44961bcca94 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parsenodes.h,v 1.145 2001/10/02 21:39:36 tgl Exp $ + * $Id: parsenodes.h,v 1.146 2001/10/12 00:07:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -187,6 +187,12 @@ typedef struct CopyStmt /* ---------------------- * Create Table Statement + * + * NOTE: in the raw gram.y output, ColumnDef, Constraint, and FkConstraint + * nodes are intermixed in tableElts, and constraints is NIL. After parse + * analysis, tableElts contains just ColumnDefs, and constraints contains + * just Constraint nodes (in fact, only CONSTR_CHECK nodes, in the present + * implementation). * ---------------------- */ typedef struct CreateStmt @@ -196,8 +202,7 @@ typedef struct CreateStmt List *tableElts; /* column definitions (list of ColumnDef) */ List *inhRelnames; /* relations to inherit from (list of * T_String Values) */ - List *constraints; /* constraints (list of Constraint and - * FkConstraint nodes) */ + List *constraints; /* constraints (list of Constraint nodes) */ bool istemp; /* is this a temp table? */ bool hasoids; /* should it have OIDs? */ } CreateStmt; diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index de702ff54c5..eb9d17ae0df 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -287,25 +287,25 @@ INSERT INTO tmp3 values (1,20); INSERT INTO tmp3 values (5,50); -- Try (and fail) to add constraint due to invalid source columns ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full; -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: columns referenced in foreign key constraint not found. +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) +ERROR: ALTER TABLE: column "c" referenced in foreign key constraint does not exist -- Try (and fail) to add constraint due to invalide destination columns explicitly given ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full; -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table "tmp2" not found -- Try (and fail) to add constraint due to invalid data ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full; -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: tmpconstr referential integrity violation - key referenced from tmp3 not found in tmp2 -- Delete failing row DELETE FROM tmp3 where a=5; -- Try (and succeed) ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full; -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on -- tmp4 is a,b ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full; -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table "tmp4" not found DROP TABLE tmp5; DROP TABLE tmp4; @@ -321,13 +321,13 @@ NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for CREATE TEMP TABLE FKTABLE (ftest1 text); -- This next should fail, because text=int does not exist ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: Unable to identify an operator '=' for types 'text' and 'integer' You will have to retype this query using an explicit cast -- This should also fail for the same reason, but here we -- give the column name ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: Unable to identify an operator '=' for types 'text' and 'integer' You will have to retype this query using an explicit cast -- This should succeed, even though they are different types @@ -335,10 +335,10 @@ ERROR: Unable to identify an operator '=' for types 'text' and 'integer' DROP TABLE FKTABLE; CREATE TEMP TABLE FKTABLE (ftest1 varchar); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- As should this ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) DROP TABLE pktable; NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" @@ -349,7 +349,7 @@ NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for -- This should fail, because we just chose really odd types CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 datetime); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer' You will have to retype this query using an explicit cast -- Again, so should this... @@ -357,7 +357,7 @@ DROP TABLE FKTABLE; CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 datetime); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest1, ptest2); -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer' You will have to retype this query using an explicit cast -- This fails because we mixed up the column ordering @@ -365,13 +365,13 @@ DROP TABLE FKTABLE; CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 text); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest2, ptest1); -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: Unable to identify an operator '=' for types 'integer' and 'text' You will have to retype this query using an explicit cast -- As does this... ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1, ptest2); -NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) +NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: Unable to identify an operator '=' for types 'text' and 'integer' You will have to retype this query using an explicit cast -- temp tables should go away by themselves, need not drop them. diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 523b5b96f7a..95e66841567 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -694,7 +694,7 @@ CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: columns referenced in foreign key constraint not found. +ERROR: CREATE TABLE: column "ftest2" referenced in foreign key constraint does not exist CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table "pktable" not found |