From 7ec49abfe80dc7f33935f63ef4c55e059076bbc5 Mon Sep 17 00:00:00 2001 From: Marco Nenciarini Date: Wed, 1 Aug 2012 19:28:54 +0200 Subject: [PATCH] Array ELEMENT foreign key v1 --- doc/src/sgml/catalogs.sgml | 20 + doc/src/sgml/ddl.sgml | 105 ++++ doc/src/sgml/ref/create_table.sgml | 95 +++- src/backend/catalog/heap.c | 2 + src/backend/catalog/index.c | 2 + src/backend/catalog/information_schema.sql | 4 +- src/backend/catalog/pg_constraint.c | 15 + src/backend/commands/tablecmds.c | 151 +++++- src/backend/commands/trigger.c | 2 + src/backend/commands/typecmds.c | 2 + src/backend/nodes/copyfuncs.c | 16 + src/backend/nodes/equalfuncs.c | 15 + src/backend/nodes/outfuncs.c | 14 + src/backend/parser/gram.y | 56 +- src/backend/parser/parse_utilcmd.c | 29 + src/backend/utils/adt/ri_triggers.c | 178 ++++++- src/backend/utils/adt/ruleutils.c | 64 ++- src/include/catalog/pg_constraint.h | 31 +- src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 17 + src/include/parser/kwlist.h | 1 + src/test/regress/expected/element_foreign_key.out | 591 +++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/element_foreign_key.sql | 452 ++++++++++++++++ 25 files changed, 1799 insertions(+), 67 deletions(-) create mode 100644 src/test/regress/expected/element_foreign_key.out create mode 100644 src/test/regress/sql/element_foreign_key.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index b100a42..a6c4c84 100644 *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *************** *** 2123,2128 **** --- 2123,2136 ---- + confiselement + bool + + If a foreign key, is it an array ELEMENT + foreign key? + + + conisonly bool *************** *** 2148,2153 **** --- 2156,2173 ---- + confelement + bool[] + + + If a foreign key, list of booleans expressing which columns + are array ELEMENT columns; see + + for details + + + + conpfeqop oid[] pg_operator.oid diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 013dc7c..6e9b6ab 100644 *** a/doc/src/sgml/ddl.sgml --- b/doc/src/sgml/ddl.sgml *************** CREATE TABLE order_items ( *** 877,882 **** --- 877,987 ---- + + Array ELEMENT Foreign Keys + + + ELEMENT foreign key + + + + constraint + Array ELEMENT foreign key + + + + constraint + ELEMENT foreign key + + + + referential integrity + + + + Another option you have with foreign keys is to use a + referencing column which is an array of elements with + the same type (or a compatible one) as the referenced + column in the related table. This feature is called + array element foreign key and is implemented + in PostgreSQL with ELEMENT foreign key constraints, + as described in the following example: + + + CREATE TABLE drivers ( + driver_id integer PRIMARY KEY, + first_name text, + last_name text, + ... + ); + + CREATE TABLE races ( + race_id integer PRIMARY KEY, + title text, + race_day DATE, + ... + final_positions integer[] ELEMENT REFERENCES drivers + ); + + + The above example uses an array (final_positions) + to store the results of a race: for each of its elements + a referential integrity check is enforced on the + drivers table. + Note that ELEMENT REFERENCES is an extension + of PostgreSQL and it is not included in the SQL standard. + + + + Even though the most common use case for array ELEMENT + foreign keys is on a single column key, you can define an array + ELEMENT foreign key constraint on a group + of columns. As the following example shows, it must be written in table + constraint form: + + + CREATE TABLE available_moves ( + kind text, + move text, + description text, + PRIMARY KEY (kind, move) + ); + + CREATE TABLE paths ( + description text, + kind text, + moves text[], + FOREIGN KEY (kind, ELEMENT moves) REFERENCES available_moves (kind, move) + ); + + INSERT INTO available_moves VALUES ('relative', 'LN', 'look north'); + INSERT INTO available_moves VALUES ('relative', 'RL', 'rotate left'); + INSERT INTO available_moves VALUES ('relative', 'RR', 'rotate right'); + INSERT INTO available_moves VALUES ('relative', 'MF', 'move forward'); + INSERT INTO available_moves VALUES ('absolute', 'N', 'move north'); + INSERT INTO available_moves VALUES ('absolute', 'S', 'move south'); + INSERT INTO available_moves VALUES ('absolute', 'E', 'move east'); + INSERT INTO available_moves VALUES ('absolute', 'W', 'move west'); + + INSERT INTO paths VALUES ('L-shaped path', 'relative', '{LN, RL, MF, RR, MF, MF}'); + INSERT INTO paths VALUES ('L-shaped path', 'absolute', '{W, N, N}'); + + + On top of standard foreign key requirements, + array ELEMENT foreign key constraints + require that the referencing column is an array of a compatible + type of the corresponding referenced column. + + + + For more detailed information on array ELEMENT + foreign key options and special cases, please refer to the documentation + for and + . + + + + Exclusion Constraints diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 19e6f8e..2b080ab 100644 *** a/doc/src/sgml/ref/create_table.sgml --- b/doc/src/sgml/ref/create_table.sgml *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 51,57 **** DEFAULT default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters | ! REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] --- 51,57 ---- DEFAULT default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters | ! [ELEMENT] REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 62,68 **** UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | ! FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] --- 62,68 ---- UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | ! FOREIGN KEY ( [ELEMENT] column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 570,579 **** ! REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint) ! FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] --- 570,580 ---- ! REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint) ! ! FOREIGN KEY ( [ELEMENT] column [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 595,600 **** --- 596,614 ---- + In case the column name column + is prepended with the ELEMENT keyword and column is an array of elements compatible + with the corresponding refcolumn + in reftable, an + array ELEMENT foreign key constraint is put in place + (see + for more information). + Multi-column keys with more than one ELEMENT column + are currently not allowed. + + + A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 657,663 **** Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the ! referenced columns, respectively. --- 671,678 ---- Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the ! referenced columns, respectively. Currently not supported ! with array ELEMENT foreign keys. *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 666,672 **** SET NULL ! Set the referencing column(s) to null. --- 681,688 ---- SET NULL ! Set the referencing column(s) to null. Currently not supported ! with array ELEMENT foreign keys. *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 678,683 **** --- 694,701 ---- Set the referencing column(s) to their default values. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) + Currently not supported with array ELEMENT + foreign keys. *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 693,698 **** --- 711,771 ---- + + ELEMENT REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint) + + + + The ELEMENT REFERENCES definition specifies + an array ELEMENT foreign key, + a special kind of foreign key + constraint requiring the referencing column to be an array of elements + of the same type (or a compatible one) as the referenced column + in the referenced table. The value of each element of the + refcolumn array + will be matched against some row of reftable. + + + + Array ELEMENT foreign keys are an extension + of PostgreSQL and are not included in the SQL standard. + + + + Even with ELEMENT foreign keys, modifications + in the referenced column can trigger actions to be performed on + the referencing array. + Similarly to standard foreign keys, you can specify these + actions using the ON DELETE and + ON UPDATE clauses. + However, only the two following actions for each clause are + currently allowed: + + + + NO ACTION + + + Same as standard foreign key constraints. This is the default action. + + + + + + RESTRICT + + + Same as standard foreign key constraints. + + + + + + + + + DEFERRABLE NOT DEFERRABLE *************** CREATE TABLE employees OF employee_type *** 1447,1452 **** --- 1520,1535 ---- effect can be had using the OID feature. + + + Array <literal>ELEMENT</literal> Foreign Keys + + + Array ELEMENT foreign keys and the + ELEMENT REFERENCES clause + are a PostgreSQL extension. + + diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index c91df90..151119c 100644 *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *************** StoreRelCheck(Relation rel, char *ccname *** 1934,1939 **** --- 1934,1941 ---- NULL, NULL, 0, + false, + NULL, ' ', ' ', ' ', diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 1546d48..d014c58 100644 *** a/src/backend/catalog/index.c --- b/src/backend/catalog/index.c *************** index_constraint_create(Relation heapRel *** 1145,1150 **** --- 1145,1152 ---- NULL, NULL, 0, + false, + NULL, ' ', ' ', ' ', diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 356797d..4764289 100644 *** a/src/backend/catalog/information_schema.sql --- b/src/backend/catalog/information_schema.sql *************** CREATE VIEW referential_constraints AS *** 1201,1207 **** WHEN 'd' THEN 'SET DEFAULT' WHEN 'r' THEN 'RESTRICT' WHEN 'a' THEN 'NO ACTION' END ! AS character_data) AS delete_rule FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace --- 1201,1209 ---- WHEN 'd' THEN 'SET DEFAULT' WHEN 'r' THEN 'RESTRICT' WHEN 'a' THEN 'NO ACTION' END ! AS character_data) AS delete_rule, ! ! con.confiselement AS is_element FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 224859d..5107eb5 100644 *** a/src/backend/catalog/pg_constraint.c --- b/src/backend/catalog/pg_constraint.c *************** CreateConstraintEntry(const char *constr *** 58,63 **** --- 58,65 ---- const Oid *ppEqOp, const Oid *ffEqOp, int foreignNKeys, + bool confisElement, + const bool *foreignElement, char foreignUpdateType, char foreignDeleteType, char foreignMatchType, *************** CreateConstraintEntry(const char *constr *** 76,81 **** --- 78,84 ---- Datum values[Natts_pg_constraint]; ArrayType *conkeyArray; ArrayType *confkeyArray; + ArrayType *confelementArray; ArrayType *conpfeqopArray; ArrayType *conppeqopArray; ArrayType *conffeqopArray; *************** CreateConstraintEntry(const char *constr *** 126,135 **** --- 129,144 ---- fkdatums[i] = ObjectIdGetDatum(ffEqOp[i]); conffeqopArray = construct_array(fkdatums, foreignNKeys, OIDOID, sizeof(Oid), true, 'i'); + for (i = 0; i < foreignNKeys; i++) { + fkdatums[i] = BoolGetDatum(foreignElement[i]); + } + confelementArray = construct_array(fkdatums, foreignNKeys, + BOOLOID, 1, true, 'c'); } else { confkeyArray = NULL; + confelementArray = NULL; conpfeqopArray = NULL; conppeqopArray = NULL; conffeqopArray = NULL; *************** CreateConstraintEntry(const char *constr *** 171,176 **** --- 180,186 ---- values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal); values[Anum_pg_constraint_coninhcount - 1] = Int32GetDatum(conInhCount); values[Anum_pg_constraint_connoinherit - 1] = BoolGetDatum(conNoInherit); + values[Anum_pg_constraint_coniselement - 1] = BoolGetDatum(confisElement); if (conkeyArray) values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray); *************** CreateConstraintEntry(const char *constr *** 182,187 **** --- 192,202 ---- else nulls[Anum_pg_constraint_confkey - 1] = true; + if (confelementArray) + values[Anum_pg_constraint_confelement - 1] = PointerGetDatum(confelementArray); + else + nulls[Anum_pg_constraint_confelement - 1] = true; + if (conpfeqopArray) values[Anum_pg_constraint_conpfeqop - 1] = PointerGetDatum(conpfeqopArray); else diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d044295..9a3aabb 100644 *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 35,40 **** --- 35,41 ---- #include "catalog/pg_inherits_fn.h" #include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" + #include "catalog/pg_operator.h" #include "catalog/pg_tablespace.h" #include "catalog/pg_trigger.h" #include "catalog/pg_type.h" *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5689,5694 **** --- 5690,5696 ---- Relation pkrel; int16 pkattnum[INDEX_MAX_KEYS]; int16 fkattnum[INDEX_MAX_KEYS]; + bool fkattelement[INDEX_MAX_KEYS]; Oid pktypoid[INDEX_MAX_KEYS]; Oid fktypoid[INDEX_MAX_KEYS]; Oid opclasses[INDEX_MAX_KEYS]; *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5765,5770 **** --- 5767,5773 ---- */ MemSet(pkattnum, 0, sizeof(pkattnum)); MemSet(fkattnum, 0, sizeof(fkattnum)); + MemSet(fkattelement, 0, sizeof(fkattelement)); MemSet(pktypoid, 0, sizeof(pktypoid)); MemSet(fktypoid, 0, sizeof(fktypoid)); MemSet(opclasses, 0, sizeof(opclasses)); *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5777,5782 **** --- 5780,5819 ---- fkattnum, fktypoid); /* + * If an array ELEMENT FK, decode the content of + * the fk_element_attrs array. + */ + if (fkconstraint->fk_is_element) + { + ListCell *l; + int attnum; + bool element_found = false; + + attnum = 0; + foreach(l, fkconstraint->fk_element_attrs) + { + if (lfirst_int(l)) { + + /* + * Currently, the ELEMENT flag cannot be set on more than + * one column. + */ + if (element_found) { + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("array ELEMENT foreign keys support only " + "one ELEMENT column"))); + } + + fkattelement[attnum] = true; + element_found = true; + } + attnum++; + } + + } + + /* * If the attribute list for the referenced table was omitted, lookup the * definition of the primary key and use it. Otherwise, validate the * supplied attribute list. In either case, discover the index OID and *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5825,5830 **** --- 5862,5884 ---- old_check_ok = (fkconstraint->old_conpfeqop != NIL); Assert(!old_check_ok || numfks == list_length(fkconstraint->old_conpfeqop)); + /* Enforce array ELEMENT foreign key restrictions */ + if (fkconstraint->fk_is_element) + { + /* + * Array ELEMENT foreign keys support only NO ACTION and + * RESTRICT actions + */ + if ((fkconstraint->fk_upd_action != FKCONSTR_ACTION_NOACTION + && fkconstraint->fk_upd_action != FKCONSTR_ACTION_RESTRICT) + || (fkconstraint->fk_del_action != FKCONSTR_ACTION_NOACTION + && fkconstraint->fk_del_action != FKCONSTR_ACTION_RESTRICT)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("array ELEMENT foreign keys only support NO ACTION " + "and RESTRICT actions"))); + } + for (i = 0; i < numpks; i++) { Oid pktype = pktypoid[i]; *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5840,5845 **** --- 5894,5900 ---- Oid ffeqop; int16 eqstrategy; Oid pfeqop_right; + Oid fk_element_type; /* We need several fields out of the pg_opclass entry */ cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i])); *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5873,5897 **** elog(ERROR, "missing operator %d(%u,%u) in opfamily %u", eqstrategy, opcintype, opcintype, opfamily); ! /* ! * Are there equality operators that take exactly the FK type? Assume ! * we should look through any domain here. ! */ ! fktyped = getBaseType(fktype); ! ! pfeqop = get_opfamily_member(opfamily, opcintype, fktyped, ! eqstrategy); ! if (OidIsValid(pfeqop)) { ! pfeqop_right = fktyped; ! ffeqop = get_opfamily_member(opfamily, fktyped, fktyped, eqstrategy); } else { ! /* keep compiler quiet */ ! pfeqop_right = InvalidOid; ! ffeqop = InvalidOid; } if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) --- 5928,5979 ---- elog(ERROR, "missing operator %d(%u,%u) in opfamily %u", eqstrategy, opcintype, opcintype, opfamily); ! if (fkattelement[i]) { ! /* ! * For every array ELEMENT FK, look if an equality operator that ! * takes exactly the FK element type exists. Assume we should ! * look through any domain here. ! */ ! fk_element_type = get_base_element_type(fktype); ! if (!OidIsValid(fk_element_type)) ! ereport(ERROR, ! (errcode(ERRCODE_DATATYPE_MISMATCH), ! errmsg("foreign key constraint \"%s\" " ! "cannot be implemented", ! fkconstraint->conname), ! errdetail("Key column \"%s\" has type %s " ! "which is not an array type.", ! strVal(list_nth(fkconstraint->fk_attrs, i)), ! format_type_be(fktype)))); ! ! pfeqop = get_opfamily_member(opfamily, opcintype, fk_element_type, eqstrategy); + pfeqop_right = fk_element_type; + ffeqop = ARRAY_EQ_OP; } else { ! /* ! * Are there equality operators that take exactly the FK type? ! * Assume we should look through any domain here. ! */ ! fktyped = getBaseType(fktype); ! ! pfeqop = get_opfamily_member(opfamily, opcintype, fktyped, ! eqstrategy); ! if (OidIsValid(pfeqop)) ! { ! pfeqop_right = fktyped; ! ffeqop = get_opfamily_member(opfamily, fktyped, fktyped, ! eqstrategy); ! } ! else ! { ! /* keep compiler quiet */ ! pfeqop_right = InvalidOid; ! ffeqop = InvalidOid; ! } } if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5909,5925 **** Oid target_typeids[2]; input_typeids[0] = pktype; ! input_typeids[1] = fktype; target_typeids[0] = opcintype; target_typeids[1] = opcintype; if (can_coerce_type(2, input_typeids, target_typeids, COERCION_IMPLICIT)) { ! pfeqop = ffeqop = ppeqop; pfeqop_right = opcintype; } } if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), --- 5991,6030 ---- Oid target_typeids[2]; input_typeids[0] = pktype; ! if (fkattelement[i]) ! input_typeids[1] = fk_element_type; ! else ! input_typeids[1] = fktype; target_typeids[0] = opcintype; target_typeids[1] = opcintype; if (can_coerce_type(2, input_typeids, target_typeids, COERCION_IMPLICIT)) { ! pfeqop = ppeqop; pfeqop_right = opcintype; + /* + * In case of an array ELEMENT FK, the ffeqop must be left + * untouched; otherwise we use the primary equality operator. + */ + if (!fkattelement[i]) + ffeqop = ppeqop; } } + /* + * In case of an array ELEMENT FK, make sure TYPECACHE_EQ_OPR exists + * for the FK element_type and it is compatible with pfeqop + */ + if (fkattelement[i] && OidIsValid(pfeqop)) + { + TypeCacheEntry *typentry = lookup_type_cache(fk_element_type, + TYPECACHE_EQ_OPR); + if (!OidIsValid(typentry->eq_opr) + || !equality_ops_are_compatible(typentry->eq_opr, pfeqop)) + /* Error: incompatible operators */ + pfeqop = InvalidOid; + } + if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5958,5965 **** * column types to the right (foreign) operand type of the pfeqop. * We may assume that pg_constraint.conkey is not changing. */ ! old_fktype = tab->oldDesc->attrs[fkattnum[i] - 1]->atttypid; ! new_fktype = fktype; old_pathtype = findFkeyCast(pfeqop_right, old_fktype, &old_castfunc); new_pathtype = findFkeyCast(pfeqop_right, new_fktype, --- 6063,6078 ---- * column types to the right (foreign) operand type of the pfeqop. * We may assume that pg_constraint.conkey is not changing. */ ! if (fkattelement[i]) ! { ! old_fktype = get_base_element_type(tab->oldDesc->attrs[fkattnum[i] - 1]->atttypid); ! new_fktype = fk_element_type; ! } ! else ! { ! old_fktype = tab->oldDesc->attrs[fkattnum[i] - 1]->atttypid; ! new_fktype = fktype; ! } old_pathtype = findFkeyCast(pfeqop_right, old_fktype, &old_castfunc); new_pathtype = findFkeyCast(pfeqop_right, new_fktype, *************** ATAddForeignKeyConstraint(AlteredTableIn *** 6030,6035 **** --- 6143,6150 ---- ppeqoperators, ffeqoperators, numpks, + fkconstraint->fk_is_element, + fkattelement, fkconstraint->fk_upd_action, fkconstraint->fk_del_action, fkconstraint->fk_matchtype, diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 5bea202..3fcaf0a 100644 *** a/src/backend/commands/trigger.c --- b/src/backend/commands/trigger.c *************** CreateTrigger(CreateTrigStmt *stmt, cons *** 450,455 **** --- 450,457 ---- NULL, NULL, 0, + false, + NULL, ' ', ' ', ' ', diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 353043d..d557908 100644 *** a/src/backend/commands/typecmds.c --- b/src/backend/commands/typecmds.c *************** domainAddConstraint(Oid domainOid, Oid d *** 2952,2957 **** --- 2952,2959 ---- NULL, NULL, 0, + false, + NULL, ' ', ' ', ' ', diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 799930a..6ea32f9 100644 *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** _copyConstraint(const Constraint *from) *** 2357,2362 **** --- 2357,2364 ---- COPY_SCALAR_FIELD(fk_upd_action); COPY_SCALAR_FIELD(fk_del_action); COPY_NODE_FIELD(old_conpfeqop); + COPY_SCALAR_FIELD(fk_is_element); + COPY_NODE_FIELD(fk_element_attrs); COPY_SCALAR_FIELD(skip_validation); COPY_SCALAR_FIELD(initially_valid); *************** _copyDefElem(const DefElem *from) *** 2376,2381 **** --- 2378,2394 ---- return newnode; } + static ForeignKeyColumnElem * + _copyForeignKeyColumnElem(const ForeignKeyColumnElem *from) + { + ForeignKeyColumnElem *newnode = makeNode(ForeignKeyColumnElem); + + COPY_NODE_FIELD(name); + COPY_SCALAR_FIELD(element); + + return newnode; + } + static LockingClause * _copyLockingClause(const LockingClause *from) { *************** copyObject(const void *from) *** 4468,4473 **** --- 4481,4489 ---- case T_DefElem: retval = _copyDefElem(from); break; + case T_ForeignKeyColumnElem: + retval = _copyForeignKeyColumnElem(from); + break; case T_LockingClause: retval = _copyLockingClause(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 802b063..ff62963 100644 *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** _equalConstraint(const Constraint *a, co *** 2236,2241 **** --- 2236,2243 ---- COMPARE_SCALAR_FIELD(fk_upd_action); COMPARE_SCALAR_FIELD(fk_del_action); COMPARE_NODE_FIELD(old_conpfeqop); + COMPARE_SCALAR_FIELD(fk_is_element); + COMPARE_NODE_FIELD(fk_element_attrs); COMPARE_SCALAR_FIELD(skip_validation); COMPARE_SCALAR_FIELD(initially_valid); *************** _equalDefElem(const DefElem *a, const De *** 2254,2259 **** --- 2256,2271 ---- } static bool + _equalForeignKeyColumnElem(const ForeignKeyColumnElem *a, + const ForeignKeyColumnElem *b) + { + COMPARE_NODE_FIELD(name); + COMPARE_SCALAR_FIELD(element); + + return true; + } + + static bool _equalLockingClause(const LockingClause *a, const LockingClause *b) { COMPARE_NODE_FIELD(lockedRels); *************** equal(const void *a, const void *b) *** 3019,3024 **** --- 3031,3039 ---- case T_DefElem: retval = _equalDefElem(a, b); break; + case T_ForeignKeyColumnElem: + retval = _equalForeignKeyColumnElem(a, b); + break; case T_LockingClause: retval = _equalLockingClause(a, b); break; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index b83bd1c..9aea276 100644 *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** _outTableLikeClause(StringInfo str, cons *** 2085,2090 **** --- 2085,2099 ---- } static void + _outForeignKeyColumnElem(StringInfo str, const ForeignKeyColumnElem *node) + { + WRITE_NODE_TYPE("FOREIGNKEYCOLUMNELEM"); + + WRITE_NODE_FIELD(name); + WRITE_BOOL_FIELD(element); + } + + static void _outLockingClause(StringInfo str, const LockingClause *node) { WRITE_NODE_TYPE("LOCKINGCLAUSE"); *************** _outConstraint(StringInfo str, const Con *** 2650,2655 **** --- 2659,2666 ---- WRITE_CHAR_FIELD(fk_upd_action); WRITE_CHAR_FIELD(fk_del_action); WRITE_NODE_FIELD(old_conpfeqop); + WRITE_BOOL_FIELD(fk_is_element); + WRITE_NODE_FIELD(fk_element_attrs); WRITE_BOOL_FIELD(skip_validation); WRITE_BOOL_FIELD(initially_valid); break; *************** _outNode(StringInfo str, const void *obj *** 3154,3159 **** --- 3165,3173 ---- case T_TableLikeClause: _outTableLikeClause(str, obj); break; + case T_ForeignKeyColumnElem: + _outForeignKeyColumnElem(str, obj); + break; case T_LockingClause: _outLockingClause(str, obj); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 6b69011..08a4bf7 100644 *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** static void processCASbits(int cas_bits, *** 326,332 **** execute_param_clause using_clause returning_clause opt_enum_val_list enum_val_list table_func_column_list create_generic_options alter_generic_options ! relation_expr_list dostmt_opt_list %type opt_fdw_options fdw_options %type fdw_option --- 326,332 ---- execute_param_clause using_clause returning_clause opt_enum_val_list enum_val_list table_func_column_list create_generic_options alter_generic_options ! relation_expr_list dostmt_opt_list foreignKeyColumnList %type opt_fdw_options fdw_options %type fdw_option *************** static void processCASbits(int cas_bits, *** 385,391 **** %type def_arg columnElem where_clause where_or_current_clause a_expr b_expr c_expr func_expr AexprConst indirection_el columnref in_expr having_clause func_table array_expr ! ExclusionWhereClause %type ExclusionConstraintList ExclusionConstraintElem %type func_arg_list %type func_arg_expr --- 385,391 ---- %type def_arg columnElem where_clause where_or_current_clause a_expr b_expr c_expr func_expr AexprConst indirection_el columnref in_expr having_clause func_table array_expr ! ExclusionWhereClause foreignKeyColumnElem %type ExclusionConstraintList ExclusionConstraintElem %type func_arg_list %type func_arg_expr *************** static void processCASbits(int cas_bits, *** 512,518 **** DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP ! EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTENSION EXTERNAL EXTRACT --- 512,518 ---- DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP ! EACH ELEMENT ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTENSION EXTERNAL EXTRACT *************** static void processCASbits(int cas_bits, *** 651,656 **** --- 651,657 ---- %left JOIN CROSS LEFT FULL RIGHT INNER_P NATURAL /* kluge to keep xml_whitespace_option from causing shift/reduce conflicts */ %right PRESERVE STRIP_P + %nonassoc ELEMENT %% *************** ColConstraintElem: *** 2740,2745 **** --- 2741,2764 ---- n->fk_matchtype = $4; n->fk_upd_action = (char) ($5 >> 8); n->fk_del_action = (char) ($5 & 0xFF); + n->fk_is_element = false; + n->skip_validation = false; + n->initially_valid = true; + $$ = (Node *)n; + } + | ELEMENT REFERENCES qualified_name opt_column_list + key_match key_actions + { + Constraint *n = makeNode(Constraint); + n->contype = CONSTR_FOREIGN; + n->location = @1; + n->pktable = $3; + n->fk_attrs = NIL; + n->pk_attrs = $4; + n->fk_matchtype = $5; + n->fk_upd_action = (char) ($6 >> 8); + n->fk_del_action = (char) ($6 & 0xFF); + n->fk_is_element = true; n->skip_validation = false; n->initially_valid = true; $$ = (Node *)n; *************** ConstraintElem: *** 2925,2932 **** NULL, yyscanner); $$ = (Node *)n; } ! | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name ! opt_column_list key_match key_actions ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_FOREIGN; --- 2944,2952 ---- NULL, yyscanner); $$ = (Node *)n; } ! | FOREIGN KEY '(' foreignKeyColumnList ')' REFERENCES ! qualified_name opt_column_list key_match key_actions ! ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_FOREIGN; *************** opt_no_inherit: NO INHERIT { $$ = *** 2950,2955 **** --- 2970,3000 ---- | /* EMPTY */ { $$ = FALSE; } ; + + foreignKeyColumnList: + foreignKeyColumnElem + { $$ = list_make1($1); } + | foreignKeyColumnList ',' foreignKeyColumnElem + { $$ = lappend($1, $3); } + ; + + foreignKeyColumnElem: + ELEMENT ColId + { + ForeignKeyColumnElem *n = makeNode(ForeignKeyColumnElem); + n->name = (Node *) makeString($2); + n->element = true; + $$ = (Node *) n; + } + | ColId + { + ForeignKeyColumnElem *n = makeNode(ForeignKeyColumnElem); + n->name = (Node *) makeString($1); + n->element = false; + $$ = (Node *) n; + } + ; + opt_column_list: '(' columnList ')' { $$ = $2; } | /*EMPTY*/ { $$ = NIL; } *************** unreserved_keyword: *** 12403,12408 **** --- 12448,12454 ---- | DOUBLE_P | DROP | EACH + | ELEMENT | ENABLE_P | ENCODING | ENCRYPTED diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index c22c6ed..27bde7e 100644 *** a/src/backend/parser/parse_utilcmd.c --- b/src/backend/parser/parse_utilcmd.c *************** transformColumnDefinition(CreateStmtCont *** 536,541 **** --- 536,543 ---- * list of FK constraints to be processed later. */ constraint->fk_attrs = list_make1(makeString(column->colname)); + constraint->fk_element_attrs = + list_make1_int(constraint->fk_is_element); cxt->fkconstraints = lappend(cxt->fkconstraints, constraint); break; *************** transformTableConstraint(CreateStmtConte *** 599,604 **** --- 601,633 ---- break; case CONSTR_FOREIGN: + /* + * Split the content of foreignKeyColumnList + * in two separate list. One list of fields + * and one list of boolean values. + */ + { + ListCell *i; + List *old_fk_attrs = constraint->fk_attrs; + + constraint->fk_attrs = NIL; + constraint->fk_is_element = false; + constraint->fk_element_attrs = NIL; + foreach (i, old_fk_attrs) + { + ForeignKeyColumnElem *elem = + (ForeignKeyColumnElem *)lfirst(i); + + Assert(IsA(elem, ForeignKeyColumnElem)); + constraint->fk_attrs = + lappend(constraint->fk_attrs, elem->name); + constraint->fk_is_element |= elem->element; + constraint->fk_element_attrs = + lappend_int(constraint->fk_element_attrs, + elem->element); + } + } + cxt->fkconstraints = lappend(cxt->fkconstraints, constraint); break; diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 983f631..226fdff 100644 *** a/src/backend/utils/adt/ri_triggers.c --- b/src/backend/utils/adt/ri_triggers.c *************** typedef struct RI_ConstraintInfo *** 110,121 **** --- 110,124 ---- NameData conname; /* name of the FK constraint */ Oid pk_relid; /* referenced relation */ Oid fk_relid; /* referencing relation */ + bool confiselement; /* is an array ELEMENT FK */ char confupdtype; /* foreign key's ON UPDATE action */ char confdeltype; /* foreign key's ON DELETE action */ char confmatchtype; /* foreign key's match type */ int nkeys; /* number of key columns */ int16 pk_attnums[RI_MAX_NUMKEYS]; /* attnums of referenced cols */ int16 fk_attnums[RI_MAX_NUMKEYS]; /* attnums of referencing cols */ + bool fk_element_atts[RI_MAX_NUMKEYS]; /* referencing cols is + * an array ELEMENT FK */ Oid pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = * FK) */ Oid pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = *************** static void ri_GenerateQual(StringInfo b *** 199,205 **** const char *sep, const char *leftop, Oid leftoptype, Oid opoid, ! const char *rightop, Oid rightoptype); static void ri_add_cast_to(StringInfo buf, Oid typid); static void ri_GenerateQualCollation(StringInfo buf, Oid collation); static int ri_NullCheck(HeapTuple tup, --- 202,209 ---- const char *sep, const char *leftop, Oid leftoptype, Oid opoid, ! const char *rightop, Oid rightoptype, ! bool is_array); static void ri_add_cast_to(StringInfo buf, Oid typid); static void ri_GenerateQualCollation(StringInfo buf, Oid collation); static int ri_NullCheck(HeapTuple tup, *************** RI_FKey_check(TriggerData *trigdata) *** 391,396 **** --- 395,401 ---- if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) { StringInfoData querybuf; + StringInfoData countbuf; char pkrelname[MAX_QUOTED_REL_NAME_LEN]; char attname[MAX_QUOTED_NAME_LEN]; char paramname[16]; *************** RI_FKey_check(TriggerData *trigdata) *** 402,413 **** --- 407,430 ---- * SELECT 1 FROM ONLY WHERE pkatt1 = $1 [AND ...] FOR SHARE * The type id's for the $ parameters are those of the * corresponding FK attributes. + * + * In case of an array ELEMENT foreign key, the previous query is used + * to count the number of matching rows and see if every combination + * is actually referenced. + * The wrapping query is + * SELECT 1 WHERE 1 * + * (SELECT count(DISTINCT y) FROM UNNEST($1) y WHERE y IS NOT NULL) + * [ * ...] = (SELECT count(*) FROM () z) * ---------- */ initStringInfo(&querybuf); quoteRelationName(pkrelname, pk_rel); appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname); querysep = "WHERE"; + if (riinfo->confiselement) { + initStringInfo(&countbuf); + appendStringInfo(&countbuf, "SELECT 1 WHERE 1"); + } for (i = 0; i < riinfo->nkeys; i++) { Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); *************** RI_FKey_check(TriggerData *trigdata) *** 416,433 **** quoteOneName(attname, RIAttName(pk_rel, riinfo->pk_attnums[i])); sprintf(paramname, "$%d", i + 1); ri_GenerateQual(&querybuf, querysep, attname, pk_type, riinfo->pf_eq_oprs[i], ! paramname, fk_type); querysep = "AND"; queryoids[i] = fk_type; } appendStringInfo(&querybuf, " FOR SHARE OF x"); ! /* Prepare and save the plan */ ! qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, ! &qkey, fk_rel, pk_rel, true); } /* --- 433,472 ---- quoteOneName(attname, RIAttName(pk_rel, riinfo->pk_attnums[i])); sprintf(paramname, "$%d", i + 1); + /* + * In case of an array ELEMENT foreign key, we check that every + * DISTINCT NOT NULL value in the array is present in the PK + * table. + */ + if (riinfo->fk_element_atts[i]) + { + appendStringInfo(&countbuf, + " * (SELECT count(DISTINCT y) " + "FROM UNNEST(%s) y WHERE y IS NOT NULL)" + , paramname); + } ri_GenerateQual(&querybuf, querysep, attname, pk_type, riinfo->pf_eq_oprs[i], ! paramname, fk_type, ! riinfo->fk_element_atts[i]); querysep = "AND"; queryoids[i] = fk_type; } appendStringInfo(&querybuf, " FOR SHARE OF x"); ! if (riinfo->confiselement) { ! appendStringInfo(&countbuf, " = " ! "(SELECT count(*) FROM (%s) z)", querybuf.data); ! ! /* Prepare and save the plan for array ELEMENT foreign keys */ ! qplan = ri_PlanCheck(countbuf.data, riinfo->nkeys, queryoids, ! &qkey, fk_rel, pk_rel, true); ! } ! else ! /* Prepare and save the plan */ ! qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, ! &qkey, fk_rel, pk_rel, true); } /* *************** ri_Check_Pk_Match(Relation pk_rel, Relat *** 553,559 **** ri_GenerateQual(&querybuf, querysep, attname, pk_type, riinfo->pp_eq_oprs[i], ! paramname, pk_type); querysep = "AND"; queryoids[i] = pk_type; } --- 592,599 ---- ri_GenerateQual(&querybuf, querysep, attname, pk_type, riinfo->pp_eq_oprs[i], ! paramname, pk_type, ! false); querysep = "AND"; queryoids[i] = pk_type; } *************** ri_restrict_del(TriggerData *trigdata, b *** 744,750 **** ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = "AND"; queryoids[i] = pk_type; } --- 784,791 ---- ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_element_atts[i]); querysep = "AND"; queryoids[i] = pk_type; } *************** ri_restrict_upd(TriggerData *trigdata, b *** 967,973 **** ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = "AND"; queryoids[i] = pk_type; } --- 1008,1015 ---- ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_element_atts[i]); querysep = "AND"; queryoids[i] = pk_type; } *************** RI_FKey_cascade_del(PG_FUNCTION_ARGS) *** 1123,1129 **** ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = "AND"; queryoids[i] = pk_type; } --- 1165,1172 ---- ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_element_atts[i]); querysep = "AND"; queryoids[i] = pk_type; } *************** RI_FKey_cascade_upd(PG_FUNCTION_ARGS) *** 1302,1308 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 1345,1352 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! false); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_FKey_setnull_del(PG_FUNCTION_ARGS) *** 1468,1474 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 1512,1519 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_element_atts[i]); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_FKey_setnull_upd(PG_FUNCTION_ARGS) *** 1644,1650 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 1689,1696 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_element_atts[i]); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_FKey_setdefault_del(PG_FUNCTION_ARGS) *** 1810,1816 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 1856,1863 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_element_atts[i]); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_FKey_setdefault_upd(PG_FUNCTION_ARGS) *** 2001,2007 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 2048,2055 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_element_atts[i]); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_Initial_Check(Trigger *trigger, Relat *** 2306,2311 **** --- 2354,2367 ---- * For MATCH FULL: * (fk.keycol1 IS NOT NULL [OR ...]) * + * In case of an array ELEMENT column, relname is replaced with the + * following subquery: + * + * SELECT unnest("keycol1") k1, "keycol1" ak1 [, ...] + * FROM ONLY "public"."fk" + * + * where all the columns are renamed in order to prevent name collisions. + * * We attach COLLATE clauses to the operators when comparing columns * that have different collations. *---------- *************** RI_Initial_Check(Trigger *trigger, Relat *** 2317,2331 **** { quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i])); ! appendStringInfo(&querybuf, "%sfk.%s", sep, fkattname); sep = ", "; } quoteRelationName(pkrelname, pk_rel); quoteRelationName(fkrelname, fk_rel); ! appendStringInfo(&querybuf, ! " FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON", ! fkrelname, pkrelname); strcpy(pkattname, "pk."); strcpy(fkattname, "fk."); --- 2373,2419 ---- { quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i])); ! if (riinfo->confiselement) ! if (riinfo->fk_element_atts[i]) ! appendStringInfo(&querybuf, "%sfk.ak%d %s", sep, i + 1, ! fkattname); ! else ! appendStringInfo(&querybuf, "%sfk.k%d %s", sep, i + 1, ! fkattname); ! else ! appendStringInfo(&querybuf, "%sfk.%s", sep, fkattname); sep = ", "; } quoteRelationName(pkrelname, pk_rel); quoteRelationName(fkrelname, fk_rel); ! ! ! if (riinfo->confiselement) ! { ! sep = ""; ! appendStringInfo(&querybuf, ! " FROM (SELECT "); ! for (i = 0; i < riinfo->nkeys; i++) ! { ! quoteOneName(fkattname, ! RIAttName(fk_rel, riinfo->fk_attnums[i])); ! if (riinfo->fk_element_atts[i]) ! appendStringInfo(&querybuf, "%sunnest(%s) k%d, %s ak%d", ! sep, fkattname, i + 1, fkattname, i + 1); ! else ! appendStringInfo(&querybuf, "%s%s k%d", sep, fkattname, ! i + 1); ! sep = ", "; ! } ! appendStringInfo(&querybuf, ! " FROM ONLY %s) fk LEFT OUTER JOIN ONLY %s pk ON", ! fkrelname, pkrelname); ! } ! else ! appendStringInfo(&querybuf, ! " FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON", ! fkrelname, pkrelname); strcpy(pkattname, "pk."); strcpy(fkattname, "fk."); *************** RI_Initial_Check(Trigger *trigger, Relat *** 2339,2350 **** quoteOneName(pkattname + 3, RIAttName(pk_rel, riinfo->pk_attnums[i])); ! quoteOneName(fkattname + 3, RIAttName(fk_rel, riinfo->fk_attnums[i])); ri_GenerateQual(&querybuf, sep, pkattname, pk_type, riinfo->pf_eq_oprs[i], ! fkattname, fk_type); if (pk_coll != fk_coll) ri_GenerateQualCollation(&querybuf, pk_coll); sep = "AND"; --- 2427,2442 ---- quoteOneName(pkattname + 3, RIAttName(pk_rel, riinfo->pk_attnums[i])); ! if (riinfo->confiselement) ! sprintf(fkattname + 3, "k%d", i + 1); ! else ! quoteOneName(fkattname + 3, RIAttName(fk_rel, riinfo->fk_attnums[i])); ri_GenerateQual(&querybuf, sep, pkattname, pk_type, riinfo->pf_eq_oprs[i], ! fkattname, fk_type, ! false); if (pk_coll != fk_coll) ri_GenerateQualCollation(&querybuf, pk_coll); sep = "AND"; *************** RI_Initial_Check(Trigger *trigger, Relat *** 2360,2366 **** sep = ""; for (i = 0; i < riinfo->nkeys; i++) { ! quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i])); appendStringInfo(&querybuf, "%sfk.%s IS NOT NULL", sep, fkattname); --- 2452,2461 ---- sep = ""; for (i = 0; i < riinfo->nkeys; i++) { ! if (riinfo->confiselement) ! sprintf(fkattname, "k%d", i + 1); ! else ! quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i])); appendStringInfo(&querybuf, "%sfk.%s IS NOT NULL", sep, fkattname); *************** ri_GenerateQual(StringInfo buf, *** 2547,2553 **** const char *sep, const char *leftop, Oid leftoptype, Oid opoid, ! const char *rightop, Oid rightoptype) { HeapTuple opertup; Form_pg_operator operform; --- 2642,2649 ---- const char *sep, const char *leftop, Oid leftoptype, Oid opoid, ! const char *rightop, Oid rightoptype, ! bool is_array) { HeapTuple opertup; Form_pg_operator operform; *************** ri_GenerateQual(StringInfo buf, *** 2568,2576 **** ri_add_cast_to(buf, operform->oprleft); appendStringInfo(buf, " OPERATOR(%s.", quote_identifier(nspname)); appendStringInfoString(buf, oprname); ! appendStringInfo(buf, ") %s", rightop); ! if (rightoptype != operform->oprright) ! ri_add_cast_to(buf, operform->oprright); ReleaseSysCache(opertup); } --- 2664,2686 ---- ri_add_cast_to(buf, operform->oprleft); appendStringInfo(buf, " OPERATOR(%s.", quote_identifier(nspname)); appendStringInfoString(buf, oprname); ! /* ! * If rightoptype is an array of leftoptype check equality using ANY(). ! * Needed for array support in foreign keys. ! */ ! if (is_array) ! { ! appendStringInfo(buf, ") ANY (%s", rightop); ! if (rightoptype != get_array_type (operform->oprright)) ! ri_add_cast_to(buf, get_array_type (operform->oprright)); ! appendStringInfo(buf, ")"); ! } ! else ! { ! appendStringInfo(buf, ") %s", rightop); ! if (rightoptype != operform->oprright) ! ri_add_cast_to(buf, operform->oprright); ! } ReleaseSysCache(opertup); } *************** ri_LoadConstraintInfo(Oid constraintOid) *** 2818,2823 **** --- 2928,2934 ---- riinfo->confupdtype = conForm->confupdtype; riinfo->confdeltype = conForm->confdeltype; riinfo->confmatchtype = conForm->confmatchtype; + riinfo->confiselement = conForm->confiselement; /* * We expect the arrays to be 1-D arrays of the right types; verify that. *************** ri_LoadConstraintInfo(Oid constraintOid) *** 2855,2860 **** --- 2966,2988 ---- if ((Pointer) arr != DatumGetPointer(adatum)) pfree(arr); /* free de-toasted copy, if any */ + adatum = SysCacheGetAttr(CONSTROID, tup, + Anum_pg_constraint_confelement, &isNull); + if (isNull) + elog(ERROR, "null confelement for constraint %u", constraintOid); + arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */ + numkeys = ARR_DIMS(arr)[0]; + if (ARR_NDIM(arr) != 1 || + numkeys != riinfo->nkeys || + numkeys > RI_MAX_NUMKEYS || + ARR_HASNULL(arr) || + ARR_ELEMTYPE(arr) != BOOLOID) + elog(ERROR, "confelement is not a 1-D boolean array"); + memcpy(riinfo->fk_element_atts, ARR_DATA_PTR(arr), + numkeys * sizeof(int16)); + if ((Pointer) arr != DatumGetPointer(adatum)) + pfree(arr); /* free de-toasted copy, if any */ + adatum = SysCacheGetAttr(CONSTROID, tup, Anum_pg_constraint_conpfeqop, &isNull); if (isNull) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 412dfe6..09d5852 100644 *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** static char *pg_get_viewdef_worker(Oid v *** 159,164 **** --- 159,167 ---- static char *pg_get_triggerdef_worker(Oid trigid, bool pretty); static void decompile_column_index_array(Datum column_index_array, Oid relId, StringInfo buf); + static void decompile_fk_column_index_array(Datum column_index_array, + Datum element_array, + Oid relId, StringInfo buf); static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags); static char *pg_get_indexdef_worker(Oid indexrelid, int colno, const Oid *excludeOps, *************** pg_get_constraintdef_worker(Oid constrai *** 1144,1149 **** --- 1147,1153 ---- case CONSTRAINT_FOREIGN: { Datum val; + Datum element; bool isnull; const char *string; *************** pg_get_constraintdef_worker(Oid constrai *** 1156,1166 **** if (isnull) elog(ERROR, "null conkey for constraint %u", constraintId); ! decompile_column_index_array(val, conForm->conrelid, &buf); /* add foreign relation name */ ! appendStringInfo(&buf, ") REFERENCES %s(", generate_relation_name(conForm->confrelid, NIL)); --- 1160,1179 ---- if (isnull) elog(ERROR, "null conkey for constraint %u", constraintId); + element = SysCacheGetAttr(CONSTROID, tup, + Anum_pg_constraint_confelement, + &isnull); + if (isnull) + elog(ERROR, "null confelement for constraint %u", + constraintId); ! decompile_fk_column_index_array(val, element, ! conForm->conrelid, &buf); ! ! appendStringInfo(&buf, ") REFERENCES "); /* add foreign relation name */ ! appendStringInfo(&buf, "%s(", generate_relation_name(conForm->confrelid, NIL)); *************** decompile_column_index_array(Datum colum *** 1444,1449 **** --- 1457,1509 ---- } } + /* + * Convert an int16[] Datum and an bool[] Datum into a comma-separated + * list of column names for the indicated relation prefixed by + * an optional ELEMENT keyword; append the list to buf. + * + * The two arrays must have the same cardinality. + */ + static void + decompile_fk_column_index_array(Datum column_index_array, + Datum element_array, + Oid relId, StringInfo buf) + { + Datum *keys; + int nKeys; + Datum *bools; + int nBools; + int j; + + /* Extract data from array of int16 */ + deconstruct_array(DatumGetArrayTypeP(column_index_array), + INT2OID, 2, true, 's', + &keys, NULL, &nKeys); + + /* Extract data from array of bool */ + deconstruct_array(DatumGetArrayTypeP(element_array), + BOOLOID, 1, true, 'c', + &bools, NULL, &nBools); + + if (nKeys != nBools) + elog(ERROR, "wrong confelement cardinality"); + + for (j = 0; j < nKeys; j++) + { + char *colName; + char *element; + + colName = get_relid_attribute_name(relId, DatumGetInt16(keys[j])); + element = DatumGetBool(bools[j])?"ELEMENT ":""; + + if (j == 0) + appendStringInfo(buf, "%s%s", element, + quote_identifier(colName)); + else + appendStringInfo(buf, ", %s%s", element, + quote_identifier(colName)); + } + } /* ---------- * get_expr - Decompile an expression tree diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index fc048ea..be8907d 100644 *** a/src/include/catalog/pg_constraint.h --- b/src/include/catalog/pg_constraint.h *************** CATALOG(pg_constraint,2606) *** 91,96 **** --- 91,99 ---- /* Has a local definition and cannot be inherited */ bool connoinherit; + /* true if an array ELEMENT foreign key */ + bool confiselement; + #ifdef CATALOG_VARLEN /* variable-length fields start here */ /* *************** CATALOG(pg_constraint,2606) *** 105,110 **** --- 108,119 ---- int16 confkey[1]; /* + * If a foreign key, true if array ELEMENT foreign key for each column of + * the constraint + */ + bool confelement[1]; + + /* * If a foreign key, the OIDs of the PK = FK equality operators for each * column of the constraint */ *************** typedef FormData_pg_constraint *Form_pg_ *** 151,157 **** * compiler constants for pg_constraint * ---------------- */ ! #define Natts_pg_constraint 24 #define Anum_pg_constraint_conname 1 #define Anum_pg_constraint_connamespace 2 #define Anum_pg_constraint_contype 3 --- 160,166 ---- * compiler constants for pg_constraint * ---------------- */ ! #define Natts_pg_constraint 26 #define Anum_pg_constraint_conname 1 #define Anum_pg_constraint_connamespace 2 #define Anum_pg_constraint_contype 3 *************** typedef FormData_pg_constraint *Form_pg_ *** 168,181 **** #define Anum_pg_constraint_conislocal 14 #define Anum_pg_constraint_coninhcount 15 #define Anum_pg_constraint_connoinherit 16 ! #define Anum_pg_constraint_conkey 17 ! #define Anum_pg_constraint_confkey 18 ! #define Anum_pg_constraint_conpfeqop 19 ! #define Anum_pg_constraint_conppeqop 20 ! #define Anum_pg_constraint_conffeqop 21 ! #define Anum_pg_constraint_conexclop 22 ! #define Anum_pg_constraint_conbin 23 ! #define Anum_pg_constraint_consrc 24 /* Valid values for contype */ --- 177,192 ---- #define Anum_pg_constraint_conislocal 14 #define Anum_pg_constraint_coninhcount 15 #define Anum_pg_constraint_connoinherit 16 ! #define Anum_pg_constraint_coniselement 17 ! #define Anum_pg_constraint_conkey 18 ! #define Anum_pg_constraint_confkey 19 ! #define Anum_pg_constraint_confelement 20 ! #define Anum_pg_constraint_conpfeqop 21 ! #define Anum_pg_constraint_conppeqop 22 ! #define Anum_pg_constraint_conffeqop 23 ! #define Anum_pg_constraint_conexclop 24 ! #define Anum_pg_constraint_conbin 25 ! #define Anum_pg_constraint_consrc 26 /* Valid values for contype */ *************** extern Oid CreateConstraintEntry(const c *** 222,227 **** --- 233,240 ---- const Oid *ppEqOp, const Oid *ffEqOp, int foreignNKeys, + bool confisElement, + const bool *foreignElement, char foreignUpdateType, char foreignDeleteType, char foreignMatchType, diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index b96a30b..cabea8a 100644 *** a/src/include/nodes/nodes.h --- b/src/include/nodes/nodes.h *************** typedef enum NodeTag *** 399,404 **** --- 399,405 ---- T_XmlSerialize, T_WithClause, T_CommonTableExpr, + T_ForeignKeyColumnElem, /* * TAGS FOR REPLICATION GRAMMAR PARSE NODES (replnodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 119e1ed..673757d 100644 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** typedef struct DefElem *** 570,575 **** --- 570,590 ---- } DefElem; /* + * ForeignKeyColumnElem - foreign key column (used in foreign key + * constraint) + * + * For a foreign key attribute, 'name' is the name of the table column to + * index, and element is true if it is an array ELEMENT fk. + */ + typedef struct ForeignKeyColumnElem + { + NodeTag type; + Node *name; /* name of the column, or NULL */ + bool element; /* true if an array ELEMENT foreign key */ + + } ForeignKeyColumnElem; + + /* * LockingClause - raw representation of FOR UPDATE/SHARE options * * Note: lockedRels == NIL means "all relations in query". Otherwise it *************** typedef struct Constraint *** 1555,1560 **** --- 1570,1577 ---- char fk_upd_action; /* ON UPDATE action */ char fk_del_action; /* ON DELETE action */ List *old_conpfeqop; /* pg_constraint.conpfeqop of my former self */ + bool fk_is_element; /* is array ELEMENT foreign key? */ + List *fk_element_attrs; /* array ELEMENT FK attrs */ /* Fields used for constraints that allow a NOT VALID specification */ bool skip_validation; /* skip validation of existing rows? */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 7e55a92..fcfc3cd 100644 *** a/src/include/parser/kwlist.h --- b/src/include/parser/kwlist.h *************** PG_KEYWORD("domain", DOMAIN_P, UNRESERVE *** 134,139 **** --- 134,140 ---- PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD) PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD) PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD) + PG_KEYWORD("element", ELEMENT, UNRESERVED_KEYWORD) PG_KEYWORD("else", ELSE, RESERVED_KEYWORD) PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD) PG_KEYWORD("encoding", ENCODING, UNRESERVED_KEYWORD) diff --git a/src/test/regress/expected/element_foreign_key.out b/src/test/regress/expected/element_foreign_key.out index ...bfe9ebe 100644 *** a/src/test/regress/expected/element_foreign_key.out --- b/src/test/regress/expected/element_foreign_key.out *************** *** 0 **** --- 1,591 ---- + -- ELEMENT FK CONSTRAINTS + -- + CREATE TABLE PKTABLEFORARRAY ( ptest1 int PRIMARY KEY, ptest2 text ); + -- Insert test data into PKTABLEFORARRAY + INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test1'); + INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test2'); + INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test3'); + INSERT INTO PKTABLEFORARRAY VALUES (4, 'Test4'); + INSERT INTO PKTABLEFORARRAY VALUES (5, 'Test5'); + -- Check alter table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (ELEMENT ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + -- Check alter table with rows + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 1); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (ELEMENT ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + -- Check alter table with failing rows + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + INSERT INTO FKTABLEFORARRAY VALUES ('{10,1}', 2); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (ELEMENT ftest1) REFERENCES PKTABLEFORARRAY; + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fkarray" + DETAIL: Key (ftest1)=({10,1}) is not present in table "pktableforarray". + DROP TABLE FKTABLEFORARRAY; + -- Check create table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + CREATE TABLE FKTABLEFORARRAYMDIM ( ftest1 int[][] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + CREATE TABLE FKTABLEFORARRAYNOTNULL ( ftest1 int[] NOT NULL ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + -- Insert successful rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 3); + INSERT INTO FKTABLEFORARRAY VALUES ('{2}', 4); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 5); + INSERT INTO FKTABLEFORARRAY VALUES ('{3}', 6); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 7); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,5}', 8); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,4}', 9); + INSERT INTO FKTABLEFORARRAY VALUES (NULL, 10); + INSERT INTO FKTABLEFORARRAY VALUES ('{}', 11); + INSERT INTO FKTABLEFORARRAY VALUES ('{1,NULL}', 12); + INSERT INTO FKTABLEFORARRAY VALUES ('{NULL}', 13); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{1,2},{1,3}}', 14); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{NULL,2},{NULL,3}}', 15); + -- Insert failed rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{6}', 16); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({6}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('{4,6}', 17); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({4,6}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL}', 18); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({6,NULL}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL,4,NULL}', 19); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({6,NULL,4,NULL}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{1,2},{6,NULL}}', 20); + ERROR: insert or update on table "fktableforarraymdim" violates foreign key constraint "fktableforarraymdim_ftest1_fkey" + DETAIL: Key (ftest1)=({{1,2},{6,NULL}}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAYNOTNULL VALUES (NULL, 21); + ERROR: null value in column "ftest1" violates not-null constraint + DETAIL: Failing row contains (null, 21). + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ----------+-------- + {1} | 3 + {2} | 4 + {1} | 5 + {3} | 6 + {1} | 7 + {4,5} | 8 + {4,4} | 9 + | 10 + {} | 11 + {1,NULL} | 12 + {NULL} | 13 + (11 rows) + + -- Delete a row from PK TABLE (must fail due to ON DELETE NO ACTION) + DELETE FROM PKTABLEFORARRAY WHERE ptest1=1; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray" + DETAIL: Key (ptest1)=(1) is still referenced from table "fktableforarray". + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ----------+-------- + {1} | 3 + {2} | 4 + {1} | 5 + {3} | 6 + {1} | 7 + {4,5} | 8 + {4,4} | 9 + | 10 + {} | 11 + {1,NULL} | 12 + {NULL} | 13 + (11 rows) + + -- Update a row from PK TABLE (must fail due to ON UPDATE NO ACTION) + UPDATE PKTABLEFORARRAY SET ptest1=7 WHERE ptest1=1; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray" + DETAIL: Key (ptest1)=(1) is still referenced from table "fktableforarray". + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ----------+-------- + {1} | 3 + {2} | 4 + {1} | 5 + {3} | 6 + {1} | 7 + {4,5} | 8 + {4,4} | 9 + | 10 + {} | 11 + {1,NULL} | 12 + {NULL} | 13 + (11 rows) + + -- Check UPDATE on FKTABLE + UPDATE FKTABLEFORARRAY SET ftest1=ARRAY[1] WHERE ftest2=4; + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ----------+-------- + {1} | 3 + {1} | 5 + {3} | 6 + {1} | 7 + {4,5} | 8 + {4,4} | 9 + | 10 + {} | 11 + {1,NULL} | 12 + {NULL} | 13 + {1} | 4 + (11 rows) + + DROP TABLE FKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAYNOTNULL; + DROP TABLE FKTABLEFORARRAYMDIM; + -- Allowed references with actions (NO ACTION, RESTRICT) + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + -- Not allowed references (SET NULL, SET DEFAULT, CASCADE) + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET NULL, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE SET NULL, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE NO ACTION, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE RESTRICT, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE SET NULL, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE NO ACTION, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE RESTRICT, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET NULL, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE NO ACTION, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE RESTRICT, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET NULL, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE CASCADE, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE CASCADE, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE CASCADE, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE CASCADE, ftest2 int ); + ERROR: array ELEMENT foreign keys only support NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + -- Cleanup + DROP TABLE PKTABLEFORARRAY; + -- Check reference on empty table + CREATE TABLE PKTABLEFORARRAY (ptest1 int PRIMARY KEY); + CREATE TABLE FKTABLEFORARRAY (ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY); + INSERT INTO FKTABLEFORARRAY VALUES ('{}'); + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Repeat a similar test using CHAR(1) keys rather than INTEGER + CREATE TABLE PKTABLEFORARRAY ( ptest1 CHAR(1) PRIMARY KEY, ptest2 text ); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'Test A'); + INSERT INTO PKTABLEFORARRAY VALUES ('B', 'Test B'); + INSERT INTO PKTABLEFORARRAY VALUES ('C', 'Test C'); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( ftest1 char(1)[] ELEMENT REFERENCES PKTABLEFORARRAY ON UPDATE RESTRICT ON DELETE RESTRICT, ftest2 int ); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{"A"}', 1); + INSERT INTO FKTABLEFORARRAY VALUES ('{"B"}', 2); + INSERT INTO FKTABLEFORARRAY VALUES ('{"C"}', 3); + INSERT INTO FKTABLEFORARRAY VALUES ('{"A","B","C"}', 4); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{"D"}', 5); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({D}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('{"A","B","D"}', 6); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({A,B,D}) is not present in table "pktableforarray". + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ---------+-------- + {A} | 1 + {B} | 2 + {C} | 3 + {A,B,C} | 4 + (4 rows) + + -- Delete a row from PK TABLE (must fail due to ON DELETE RESTRICT) + DELETE FROM PKTABLEFORARRAY WHERE ptest1='A'; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray" + DETAIL: Key (ptest1)=(A) is still referenced from table "fktableforarray". + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ---------+-------- + {A} | 1 + {B} | 2 + {C} | 3 + {A,B,C} | 4 + (4 rows) + + -- Update a row from PK TABLE (must fail due to ON UPDATE RESTRICT) + UPDATE PKTABLEFORARRAY SET ptest1='D' WHERE ptest1='B'; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray" + DETAIL: Key (ptest1)=(B) is still referenced from table "fktableforarray". + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ---------+-------- + {A} | 1 + {B} | 2 + {C} | 3 + {A,B,C} | 4 + (4 rows) + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Repeat a similar test using INT4 keys coerced from INT2 + CREATE TABLE PKTABLEFORARRAY ( ptest1 int4 PRIMARY KEY, ptest2 text ); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test 1'); + INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test 2'); + INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test 3'); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int2[] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1], 1); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[2], 2); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[3], 3); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1,2,3], 4); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[4], 5); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({4}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1,2,5], 6); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({1,2,5}) is not present in table "pktableforarray". + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Repeat a similar test using FLOAT keys coerced from INTEGER + CREATE TABLE PKTABLEFORARRAY ( ptest1 float PRIMARY KEY, ptest2 text ); + -- FAILS because equality operator are incompatible + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + ERROR: foreign key constraint "fktableforarray_ftest1_fkey" cannot be implemented + DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: integer[] and double precision. + -- Cleanup + DROP TABLE PKTABLEFORARRAY; + -- Composite primary keys + CREATE TABLE PKTABLEFORARRAY ( id1 CHAR(1), id2 CHAR(1), ptest2 text, PRIMARY KEY (id1, id2) ); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'A', 'Test A'); + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'B', 'Test B'); + INSERT INTO PKTABLEFORARRAY VALUES ('B', 'C', 'Test B'); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( fid1 CHAR(1), fid2 CHAR(1)[], ftest2 text, FOREIGN KEY (fid1, ELEMENT fid2) REFERENCES PKTABLEFORARRAY); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('A', ARRAY['A','B'], '1'); + INSERT INTO FKTABLEFORARRAY VALUES ('B', ARRAY['C'], '2'); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('A', ARRAY['A','B', 'C'], '3'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_fid1_fkey" + DETAIL: Key (fid1, fid2)=(A, {A,B,C}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('B', ARRAY['A'], '4'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_fid1_fkey" + DETAIL: Key (fid1, fid2)=(B, {A}) is not present in table "pktableforarray". + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Test ELEMENT foreign keys with composite type + CREATE TYPE INVOICEID AS (year_part INTEGER, progressive_part INTEGER); + CREATE TABLE PKTABLEFORARRAY ( id INVOICEID PRIMARY KEY, ptest2 text); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2010, 99), 'Last invoice for 2010'); + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2011, 1), 'First invoice for 2011'); + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2011, 2), 'Second invoice for 2011'); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, invoice_ids INVOICEID[] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 TEXT ); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2010,99)']::INVOICEID[], 'Product A'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,1)','(2011,2)']::INVOICEID[], 'Product B'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,2)']::INVOICEID[], 'Product C'); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,99)']::INVOICEID[], 'Product A'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_invoice_ids_fkey" + DETAIL: Key (invoice_ids)=({"(2011,99)"}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,1)','(2010,1)']::INVOICEID[], 'Product B'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_invoice_ids_fkey" + DETAIL: Key (invoice_ids)=({"(2011,1)","(2010,1)"}) is not present in table "pktableforarray". + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + id | invoice_ids | ftest2 + ----+-------------------------+----------- + 1 | {"(2010,99)"} | Product A + 2 | {"(2011,1)","(2011,2)"} | Product B + 3 | {"(2011,2)"} | Product C + (3 rows) + + -- Delete a row from PK TABLE + DELETE FROM PKTABLEFORARRAY WHERE id=ROW(2010,99); + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_invoice_ids_fkey" on table "fktableforarray" + DETAIL: Key (id)=((2010,99)) is still referenced from table "fktableforarray". + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + id | invoice_ids | ftest2 + ----+-------------------------+----------- + 1 | {"(2010,99)"} | Product A + 2 | {"(2011,1)","(2011,2)"} | Product B + 3 | {"(2011,2)"} | Product C + (3 rows) + + -- Update a row from PK TABLE + UPDATE PKTABLEFORARRAY SET id=ROW(2011,99) WHERE id=ROW(2011,1); + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_invoice_ids_fkey" on table "fktableforarray" + DETAIL: Key (id)=((2011,1)) is still referenced from table "fktableforarray". + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + id | invoice_ids | ftest2 + ----+-------------------------+----------- + 1 | {"(2010,99)"} | Product A + 2 | {"(2011,1)","(2011,2)"} | Product B + 3 | {"(2011,2)"} | Product C + (3 rows) + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + DROP TYPE INVOICEID; + -- Check FK with cast + CREATE TABLE PKTABLEFORARRAY (c smallint PRIMARY KEY); + CREATE TABLE FKTABLEFORARRAY (c int[] ELEMENT REFERENCES PKTABLEFORARRAY); + INSERT INTO PKTABLEFORARRAY VALUES (1), (2); + INSERT INTO FKTABLEFORARRAY VALUES ('{1,2}'); + UPDATE PKTABLEFORARRAY SET c = 3 WHERE c = 2; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_c_fkey" on table "fktableforarray" + DETAIL: Key (c)=(2) is still referenced from table "fktableforarray". + DELETE FROM PKTABLEFORARRAY WHERE c = 1; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_c_fkey" on table "fktableforarray" + DETAIL: Key (c)=(1) is still referenced from table "fktableforarray". + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Check for an array column referencing another array column (NOT ELEMENT FOREIGN KEY) + -- Create primary table with an array primary key + CREATE TABLE PKTABLEFORARRAY ( id INT[] PRIMARY KEY, ptest2 text); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, fids INT[] REFERENCES PKTABLEFORARRAY, ftest2 TEXT ); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('{1,1}', 'A'); + INSERT INTO PKTABLEFORARRAY VALUES ('{1,2}', 'B'); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{1,1}', 'Product A'); + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{1,2}', 'Product B'); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{0,1}', 'Product C'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_fids_fkey" + DETAIL: Key (fids)=({0,1}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{2,1}', 'Product D'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_fids_fkey" + DETAIL: Key (fids)=({2,1}) is not present in table "pktableforarray". + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- --------------------------------------- + -- Multi-column "ELEMENT" foreign key tests + -- --------------------------------------- + -- Create DIM1 table with two-column primary key + CREATE TABLE DIM1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, PRIMARY KEY (X, Y)); + -- Populate DIM1 table pairs + INSERT INTO DIM1 SELECT x.t, x.t * y.t + FROM (SELECT generate_series(1, 10) AS t) x, + (SELECT generate_series(0, 10) AS t) y; + -- Test with TABLE declaration of an element foreign key constraint (NO ACTION) + CREATE TABLE F1 ( + x INTEGER PRIMARY KEY, y INTEGER[], + FOREIGN KEY (x, ELEMENT y) REFERENCES DIM1(x, y) + ); + -- Insert facts + INSERT INTO F1 VALUES (1, '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES (2, '{0,2,4,6}'); -- OK + INSERT INTO F1 VALUES (3, '{0,3,6,9,0,3,6,9,0,0,0,0,9,9}'); -- OK (multiple occurrences) + INSERT INTO F1 VALUES (4, '{0,2,4}'); -- FAILS (2 is not present) + ERROR: insert or update on table "f1" violates foreign key constraint "f1_x_fkey" + DETAIL: Key (x, y)=(4, {0,2,4}) is not present in table "dim1". + INSERT INTO F1 VALUES (4, '{0,NULL,4}'); -- OK + INSERT INTO F1 VALUES (5, '{0,NULL,5}'); -- OK + -- Try updates + UPDATE F1 SET y = '{0,2,4,6}' WHERE x = 2; -- OK + UPDATE F1 SET y = '{0,2,3,4,6}' WHERE x = 2; -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_x_fkey" + DETAIL: Key (x, y)=(2, {0,2,3,4,6}) is not present in table "dim1". + UPDATE F1 SET x = 20, y = '{0,2,3,4,6}' WHERE x = 2; -- FAILS (20 does not exist) + ERROR: insert or update on table "f1" violates foreign key constraint "f1_x_fkey" + DETAIL: Key (x, y)=(20, {0,2,3,4,6}) is not present in table "dim1". + UPDATE F1 SET y = '{0,4,8}' WHERE x = 4; -- OK + UPDATE F1 SET y = '{0,5,NULL,10}' WHERE x = 5; -- OK + DROP TABLE F1; + -- Test with FOREIGN KEY after TABLE population + CREATE TABLE F1 ( + x INTEGER PRIMARY KEY, y INTEGER[] + ); + -- Insert facts + INSERT INTO F1 VALUES (1, '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES (2, '{0,2,4,6}'); -- OK + INSERT INTO F1 VALUES (3, '{0,3,6,9,0,3,6,9,0,0,0,0,9,9}'); -- OK (multiple occurrences) + INSERT INTO F1 VALUES (4, '{0,2,4}'); -- OK (2 is not present) + INSERT INTO F1 VALUES (5, '{0,NULL,5}'); -- OK + -- Add foreign key (FAILS) + ALTER TABLE F1 ADD FOREIGN KEY (x, ELEMENT y) REFERENCES DIM1(x, y); + ERROR: insert or update on table "f1" violates foreign key constraint "f1_x_fkey" + DETAIL: Key (x, y)=(4, {0,2,4}) is not present in table "dim1". + DROP TABLE F1; + -- Test with TABLE declaration of a two-dim ELEMENT foreign key constraint (FAILS) + CREATE TABLE F1 ( + x INTEGER[] PRIMARY KEY, y INTEGER[], + FOREIGN KEY (ELEMENT x, ELEMENT y) REFERENCES DIM1(x, y) + ); + ERROR: array ELEMENT foreign keys support only one ELEMENT column + -- Test with two-dim ELEMENT foreign key after TABLE population + CREATE TABLE F1 ( + x INTEGER[] PRIMARY KEY, y INTEGER[] + ); + INSERT INTO F1 VALUES ('{1}', '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES ('{1,2}', '{0,2,4,6}'); -- OK + -- Add foreign key (FAILS) + ALTER TABLE F1 ADD FOREIGN KEY (ELEMENT x, ELEMENT y) REFERENCES DIM1(x, y); + ERROR: array ELEMENT foreign keys support only one ELEMENT column + DROP TABLE F1; + -- Cleanup + DROP TABLE DIM1; + -- Check for potential name conflicts (with internal integrity checks) + CREATE TABLE x1(x1 int, x2 int, PRIMARY KEY(x1,x2)); + INSERT INTO x1 VALUES + (1,4), + (1,5), + (2,4), + (2,5), + (3,6), + (3,7) + ; + CREATE TABLE x2(x1 int[], x2 int, FOREIGN KEY(ELEMENT x1, x2) REFERENCES x1); + INSERT INTO x2 VALUES ('{1,2}',4); + INSERT INTO x2 VALUES ('{1,3}',6); -- FAILS + ERROR: insert or update on table "x2" violates foreign key constraint "x2_x1_fkey" + DETAIL: Key (x1, x2)=({1,3}, 6) is not present in table "x1". + DROP TABLE x2; + CREATE TABLE x2(x1 int[], x2 int); + INSERT INTO x2 VALUES ('{1,2}',4); + INSERT INTO x2 VALUES ('{1,3}',6); + ALTER TABLE x2 ADD CONSTRAINT fk_const FOREIGN KEY(ELEMENT x1, x2) REFERENCES x1; -- FAILS + ERROR: insert or update on table "x2" violates foreign key constraint "fk_const" + DETAIL: Key (x1, x2)=({1,3}, 6) is not present in table "x1". + DROP TABLE x2; + DROP TABLE x1; + -- --------------------------------------- + -- Multi-dimensional "ELEMENT" foreign key tests + -- --------------------------------------- + -- Create DIM1 table with two-column primary key + CREATE TABLE DIM1 (X INTEGER NOT NULL PRIMARY KEY, + CODE TEXT NOT NULL UNIQUE); + -- Populate DIM1 table pairs + INSERT INTO DIM1 SELECT t, 'DIM1-' || lpad(t::TEXT, 2, '0') + FROM (SELECT generate_series(1, 10)) x(t); + -- Test with TABLE declaration of an element foreign key constraint (NO ACTION) + CREATE TABLE F1 ( + ID SERIAL PRIMARY KEY, + SLOTS INTEGER[3][3] ELEMENT REFERENCES DIM1 + ); + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 3}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_slots_fkey" + DETAIL: Key (slots)=({{NULL,1,NULL},{NULL,NULL,11},{NULL,NULL,6}}) is not present in table "dim1". + INSERT INTO F1(SLOTS) VALUES ('{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{1, 2, 3, 4, 5, 6, 7, 8, 9}'); -- OK + UPDATE F1 SET SLOTS = '{{NULL, 1, NULL}, {NULL, NULL, 3}, {7, 8, 10}}' WHERE ID = 1; -- OK + UPDATE F1 SET SLOTS = '{{100, 100, 100}, {NULL, NULL, 20}, {7, 8, 10}}' WHERE ID = 1; -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_slots_fkey" + DETAIL: Key (slots)=({{100,100,100},{NULL,NULL,20},{7,8,10}}) is not present in table "dim1". + DROP TABLE F1; + -- Test with postponed foreign key + CREATE TABLE F1 ( + ID SERIAL PRIMARY KEY, + SLOTS INTEGER[3][3] + ); + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 3}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{1, 2, 3, 4, 5, 6, 7, 8, 9}'); -- OK + ALTER TABLE F1 ADD FOREIGN KEY (ELEMENT SLOTS) REFERENCES DIM1; -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_slots_fkey" + DETAIL: Key (slots)=({{NULL,1,NULL},{NULL,NULL,11},{NULL,NULL,6}}) is not present in table "dim1". + DELETE FROM F1 WHERE ID = 2; -- REMOVE ISSUE + ALTER TABLE F1 ADD FOREIGN KEY (ELEMENT SLOTS) REFERENCES DIM1; -- NOW OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_slots_fkey" + DETAIL: Key (slots)=({{NULL,1,NULL},{NULL,NULL,11},{NULL,NULL,6}}) is not present in table "dim1". + DROP TABLE F1; + -- Leave tables in the database + CREATE TABLE PKTABLEFORELEMENTFK ( ptest1 int PRIMARY KEY, ptest2 text ); + CREATE TABLE FKTABLEFORELEMENTFK ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORELEMENTFK, ftest2 int ); + -- Check ALTER TABLE ALTER TYPE + ALTER TABLE FKTABLEFORELEMENTFK ALTER FTEST1 TYPE INT[]; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index ac29194..d97c755 100644 *** a/src/test/regress/parallel_schedule --- b/src/test/regress/parallel_schedule *************** test: event_trigger *** 94,100 **** # ---------- # Another group of parallel tests # ---------- ! test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock json # ---------- # Another group of parallel tests --- 94,100 ---- # ---------- # Another group of parallel tests # ---------- ! test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock element_foreign_key # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 8576a7f..4e175c0 100644 *** a/src/test/regress/serial_schedule --- b/src/test/regress/serial_schedule *************** test: event_trigger *** 100,105 **** --- 100,106 ---- test: select_views test: portals_p2 test: foreign_key + test: element_foreign_key test: cluster test: dependency test: guc diff --git a/src/test/regress/sql/element_foreign_key.sql b/src/test/regress/sql/element_foreign_key.sql index ...ca50569 100644 *** a/src/test/regress/sql/element_foreign_key.sql --- b/src/test/regress/sql/element_foreign_key.sql *************** *** 0 **** --- 1,452 ---- + -- ELEMENT FK CONSTRAINTS + -- + CREATE TABLE PKTABLEFORARRAY ( ptest1 int PRIMARY KEY, ptest2 text ); + + -- Insert test data into PKTABLEFORARRAY + INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test1'); + INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test2'); + INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test3'); + INSERT INTO PKTABLEFORARRAY VALUES (4, 'Test4'); + INSERT INTO PKTABLEFORARRAY VALUES (5, 'Test5'); + + -- Check alter table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (ELEMENT ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + + -- Check alter table with rows + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 1); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (ELEMENT ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + + -- Check alter table with failing rows + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + INSERT INTO FKTABLEFORARRAY VALUES ('{10,1}', 2); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (ELEMENT ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + + -- Check create table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + CREATE TABLE FKTABLEFORARRAYMDIM ( ftest1 int[][] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + CREATE TABLE FKTABLEFORARRAYNOTNULL ( ftest1 int[] NOT NULL ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + + -- Insert successful rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 3); + INSERT INTO FKTABLEFORARRAY VALUES ('{2}', 4); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 5); + INSERT INTO FKTABLEFORARRAY VALUES ('{3}', 6); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 7); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,5}', 8); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,4}', 9); + INSERT INTO FKTABLEFORARRAY VALUES (NULL, 10); + INSERT INTO FKTABLEFORARRAY VALUES ('{}', 11); + INSERT INTO FKTABLEFORARRAY VALUES ('{1,NULL}', 12); + INSERT INTO FKTABLEFORARRAY VALUES ('{NULL}', 13); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{1,2},{1,3}}', 14); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{NULL,2},{NULL,3}}', 15); + + -- Insert failed rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{6}', 16); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,6}', 17); + INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL}', 18); + INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL,4,NULL}', 19); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{1,2},{6,NULL}}', 20); + INSERT INTO FKTABLEFORARRAYNOTNULL VALUES (NULL, 21); + + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + + -- Delete a row from PK TABLE (must fail due to ON DELETE NO ACTION) + DELETE FROM PKTABLEFORARRAY WHERE ptest1=1; + + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Update a row from PK TABLE (must fail due to ON UPDATE NO ACTION) + UPDATE PKTABLEFORARRAY SET ptest1=7 WHERE ptest1=1; + + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Check UPDATE on FKTABLE + UPDATE FKTABLEFORARRAY SET ftest1=ARRAY[1] WHERE ftest2=4; + + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + + DROP TABLE FKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAYNOTNULL; + DROP TABLE FKTABLEFORARRAYMDIM; + + -- Allowed references with actions (NO ACTION, RESTRICT) + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + -- Not allowed references (SET NULL, SET DEFAULT, CASCADE) + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + + -- Cleanup + DROP TABLE PKTABLEFORARRAY; + + -- Check reference on empty table + CREATE TABLE PKTABLEFORARRAY (ptest1 int PRIMARY KEY); + CREATE TABLE FKTABLEFORARRAY (ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY); + INSERT INTO FKTABLEFORARRAY VALUES ('{}'); + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Repeat a similar test using CHAR(1) keys rather than INTEGER + CREATE TABLE PKTABLEFORARRAY ( ptest1 CHAR(1) PRIMARY KEY, ptest2 text ); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'Test A'); + INSERT INTO PKTABLEFORARRAY VALUES ('B', 'Test B'); + INSERT INTO PKTABLEFORARRAY VALUES ('C', 'Test C'); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( ftest1 char(1)[] ELEMENT REFERENCES PKTABLEFORARRAY ON UPDATE RESTRICT ON DELETE RESTRICT, ftest2 int ); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{"A"}', 1); + INSERT INTO FKTABLEFORARRAY VALUES ('{"B"}', 2); + INSERT INTO FKTABLEFORARRAY VALUES ('{"C"}', 3); + INSERT INTO FKTABLEFORARRAY VALUES ('{"A","B","C"}', 4); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{"D"}', 5); + INSERT INTO FKTABLEFORARRAY VALUES ('{"A","B","D"}', 6); + + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + + -- Delete a row from PK TABLE (must fail due to ON DELETE RESTRICT) + DELETE FROM PKTABLEFORARRAY WHERE ptest1='A'; + + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Update a row from PK TABLE (must fail due to ON UPDATE RESTRICT) + UPDATE PKTABLEFORARRAY SET ptest1='D' WHERE ptest1='B'; + + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Repeat a similar test using INT4 keys coerced from INT2 + CREATE TABLE PKTABLEFORARRAY ( ptest1 int4 PRIMARY KEY, ptest2 text ); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test 1'); + INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test 2'); + INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test 3'); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int2[] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1], 1); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[2], 2); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[3], 3); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1,2,3], 4); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[4], 5); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1,2,5], 6); + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Repeat a similar test using FLOAT keys coerced from INTEGER + CREATE TABLE PKTABLEFORARRAY ( ptest1 float PRIMARY KEY, ptest2 text ); + -- FAILS because equality operator are incompatible + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 int ); + + -- Cleanup + DROP TABLE PKTABLEFORARRAY; + + -- Composite primary keys + CREATE TABLE PKTABLEFORARRAY ( id1 CHAR(1), id2 CHAR(1), ptest2 text, PRIMARY KEY (id1, id2) ); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'A', 'Test A'); + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'B', 'Test B'); + INSERT INTO PKTABLEFORARRAY VALUES ('B', 'C', 'Test B'); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( fid1 CHAR(1), fid2 CHAR(1)[], ftest2 text, FOREIGN KEY (fid1, ELEMENT fid2) REFERENCES PKTABLEFORARRAY); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('A', ARRAY['A','B'], '1'); + INSERT INTO FKTABLEFORARRAY VALUES ('B', ARRAY['C'], '2'); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('A', ARRAY['A','B', 'C'], '3'); + INSERT INTO FKTABLEFORARRAY VALUES ('B', ARRAY['A'], '4'); + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Test ELEMENT foreign keys with composite type + CREATE TYPE INVOICEID AS (year_part INTEGER, progressive_part INTEGER); + CREATE TABLE PKTABLEFORARRAY ( id INVOICEID PRIMARY KEY, ptest2 text); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2010, 99), 'Last invoice for 2010'); + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2011, 1), 'First invoice for 2011'); + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2011, 2), 'Second invoice for 2011'); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, invoice_ids INVOICEID[] ELEMENT REFERENCES PKTABLEFORARRAY, ftest2 TEXT ); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2010,99)']::INVOICEID[], 'Product A'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,1)','(2011,2)']::INVOICEID[], 'Product B'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,2)']::INVOICEID[], 'Product C'); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,99)']::INVOICEID[], 'Product A'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,1)','(2010,1)']::INVOICEID[], 'Product B'); + + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + + -- Delete a row from PK TABLE + DELETE FROM PKTABLEFORARRAY WHERE id=ROW(2010,99); + + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Update a row from PK TABLE + UPDATE PKTABLEFORARRAY SET id=ROW(2011,99) WHERE id=ROW(2011,1); + + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + DROP TYPE INVOICEID; + + -- Check FK with cast + CREATE TABLE PKTABLEFORARRAY (c smallint PRIMARY KEY); + CREATE TABLE FKTABLEFORARRAY (c int[] ELEMENT REFERENCES PKTABLEFORARRAY); + INSERT INTO PKTABLEFORARRAY VALUES (1), (2); + INSERT INTO FKTABLEFORARRAY VALUES ('{1,2}'); + UPDATE PKTABLEFORARRAY SET c = 3 WHERE c = 2; + DELETE FROM PKTABLEFORARRAY WHERE c = 1; + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Check for an array column referencing another array column (NOT ELEMENT FOREIGN KEY) + -- Create primary table with an array primary key + CREATE TABLE PKTABLEFORARRAY ( id INT[] PRIMARY KEY, ptest2 text); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, fids INT[] REFERENCES PKTABLEFORARRAY, ftest2 TEXT ); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('{1,1}', 'A'); + INSERT INTO PKTABLEFORARRAY VALUES ('{1,2}', 'B'); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{1,1}', 'Product A'); + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{1,2}', 'Product B'); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{0,1}', 'Product C'); + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{2,1}', 'Product D'); + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- --------------------------------------- + -- Multi-column "ELEMENT" foreign key tests + -- --------------------------------------- + + -- Create DIM1 table with two-column primary key + CREATE TABLE DIM1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, PRIMARY KEY (X, Y)); + -- Populate DIM1 table pairs + INSERT INTO DIM1 SELECT x.t, x.t * y.t + FROM (SELECT generate_series(1, 10) AS t) x, + (SELECT generate_series(0, 10) AS t) y; + + + -- Test with TABLE declaration of an element foreign key constraint (NO ACTION) + CREATE TABLE F1 ( + x INTEGER PRIMARY KEY, y INTEGER[], + FOREIGN KEY (x, ELEMENT y) REFERENCES DIM1(x, y) + ); + -- Insert facts + INSERT INTO F1 VALUES (1, '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES (2, '{0,2,4,6}'); -- OK + INSERT INTO F1 VALUES (3, '{0,3,6,9,0,3,6,9,0,0,0,0,9,9}'); -- OK (multiple occurrences) + INSERT INTO F1 VALUES (4, '{0,2,4}'); -- FAILS (2 is not present) + INSERT INTO F1 VALUES (4, '{0,NULL,4}'); -- OK + INSERT INTO F1 VALUES (5, '{0,NULL,5}'); -- OK + -- Try updates + UPDATE F1 SET y = '{0,2,4,6}' WHERE x = 2; -- OK + UPDATE F1 SET y = '{0,2,3,4,6}' WHERE x = 2; -- FAILS + UPDATE F1 SET x = 20, y = '{0,2,3,4,6}' WHERE x = 2; -- FAILS (20 does not exist) + UPDATE F1 SET y = '{0,4,8}' WHERE x = 4; -- OK + UPDATE F1 SET y = '{0,5,NULL,10}' WHERE x = 5; -- OK + DROP TABLE F1; + + + -- Test with FOREIGN KEY after TABLE population + CREATE TABLE F1 ( + x INTEGER PRIMARY KEY, y INTEGER[] + ); + -- Insert facts + INSERT INTO F1 VALUES (1, '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES (2, '{0,2,4,6}'); -- OK + INSERT INTO F1 VALUES (3, '{0,3,6,9,0,3,6,9,0,0,0,0,9,9}'); -- OK (multiple occurrences) + INSERT INTO F1 VALUES (4, '{0,2,4}'); -- OK (2 is not present) + INSERT INTO F1 VALUES (5, '{0,NULL,5}'); -- OK + -- Add foreign key (FAILS) + ALTER TABLE F1 ADD FOREIGN KEY (x, ELEMENT y) REFERENCES DIM1(x, y); + DROP TABLE F1; + + + -- Test with TABLE declaration of a two-dim ELEMENT foreign key constraint (FAILS) + CREATE TABLE F1 ( + x INTEGER[] PRIMARY KEY, y INTEGER[], + FOREIGN KEY (ELEMENT x, ELEMENT y) REFERENCES DIM1(x, y) + ); + + + -- Test with two-dim ELEMENT foreign key after TABLE population + CREATE TABLE F1 ( + x INTEGER[] PRIMARY KEY, y INTEGER[] + ); + INSERT INTO F1 VALUES ('{1}', '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES ('{1,2}', '{0,2,4,6}'); -- OK + -- Add foreign key (FAILS) + ALTER TABLE F1 ADD FOREIGN KEY (ELEMENT x, ELEMENT y) REFERENCES DIM1(x, y); + DROP TABLE F1; + + -- Cleanup + DROP TABLE DIM1; + + + -- Check for potential name conflicts (with internal integrity checks) + CREATE TABLE x1(x1 int, x2 int, PRIMARY KEY(x1,x2)); + INSERT INTO x1 VALUES + (1,4), + (1,5), + (2,4), + (2,5), + (3,6), + (3,7) + ; + CREATE TABLE x2(x1 int[], x2 int, FOREIGN KEY(ELEMENT x1, x2) REFERENCES x1); + INSERT INTO x2 VALUES ('{1,2}',4); + INSERT INTO x2 VALUES ('{1,3}',6); -- FAILS + DROP TABLE x2; + CREATE TABLE x2(x1 int[], x2 int); + INSERT INTO x2 VALUES ('{1,2}',4); + INSERT INTO x2 VALUES ('{1,3}',6); + ALTER TABLE x2 ADD CONSTRAINT fk_const FOREIGN KEY(ELEMENT x1, x2) REFERENCES x1; -- FAILS + DROP TABLE x2; + DROP TABLE x1; + + + -- --------------------------------------- + -- Multi-dimensional "ELEMENT" foreign key tests + -- --------------------------------------- + + -- Create DIM1 table with two-column primary key + CREATE TABLE DIM1 (X INTEGER NOT NULL PRIMARY KEY, + CODE TEXT NOT NULL UNIQUE); + -- Populate DIM1 table pairs + INSERT INTO DIM1 SELECT t, 'DIM1-' || lpad(t::TEXT, 2, '0') + FROM (SELECT generate_series(1, 10)) x(t); + + -- Test with TABLE declaration of an element foreign key constraint (NO ACTION) + CREATE TABLE F1 ( + ID SERIAL PRIMARY KEY, + SLOTS INTEGER[3][3] ELEMENT REFERENCES DIM1 + ); + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 3}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- FAILS + INSERT INTO F1(SLOTS) VALUES ('{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{1, 2, 3, 4, 5, 6, 7, 8, 9}'); -- OK + UPDATE F1 SET SLOTS = '{{NULL, 1, NULL}, {NULL, NULL, 3}, {7, 8, 10}}' WHERE ID = 1; -- OK + UPDATE F1 SET SLOTS = '{{100, 100, 100}, {NULL, NULL, 20}, {7, 8, 10}}' WHERE ID = 1; -- FAILS + DROP TABLE F1; + + -- Test with postponed foreign key + CREATE TABLE F1 ( + ID SERIAL PRIMARY KEY, + SLOTS INTEGER[3][3] + ); + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 3}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{1, 2, 3, 4, 5, 6, 7, 8, 9}'); -- OK + ALTER TABLE F1 ADD FOREIGN KEY (ELEMENT SLOTS) REFERENCES DIM1; -- FAILS + DELETE FROM F1 WHERE ID = 2; -- REMOVE ISSUE + ALTER TABLE F1 ADD FOREIGN KEY (ELEMENT SLOTS) REFERENCES DIM1; -- NOW OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- FAILS + DROP TABLE F1; + + -- Leave tables in the database + CREATE TABLE PKTABLEFORELEMENTFK ( ptest1 int PRIMARY KEY, ptest2 text ); + CREATE TABLE FKTABLEFORELEMENTFK ( ftest1 int[] ELEMENT REFERENCES PKTABLEFORELEMENTFK, ftest2 int ); + + -- Check ALTER TABLE ALTER TYPE + ALTER TABLE FKTABLEFORELEMENTFK ALTER FTEST1 TYPE INT[]; -- 1.7.10.4