diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index ea655a10a8..712f631e88 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2288,6 +2288,14 @@ SCRAM-SHA-256$<iteration count>:<salt>< + confiselement + bool + + If a foreign key, is it an array ELEMENT + foreign key? + + + coninhcount int4 @@ -2324,6 +2332,18 @@ SCRAM-SHA-256$<iteration count>:<salt>< + 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 b05a9c2150..c1c847bc7e 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -881,7 +881,112 @@ CREATE TABLE order_items ( . - + + + 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 b15c19d3d0..1d7749ce38 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -65,7 +65,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] | UNIQUE index_parameters | PRIMARY KEY index_parameters | - REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] + [ELEMENT] REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -76,7 +76,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI 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 [, ... ] ) ] + 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 ] @@ -779,10 +779,11 @@ FROM ( { numeric_literal | - + REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint) - FOREIGN KEY ( column_name [, ... ] ) + + FOREIGN KEY ( [ELEMENT] column [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] @@ -806,6 +807,19 @@ FROM ( { numeric_literal | + 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 @@ -868,7 +882,8 @@ FROM ( { numeric_literal | 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. + referenced columns, respectively. Currently not supported + with array ELEMENT foreign keys. @@ -877,7 +892,8 @@ FROM ( { numeric_literal | SET NULL - Set the referencing column(s) to null. + Set the referencing column(s) to null. Currently not supported + with array ELEMENT foreign keys. @@ -889,7 +905,9 @@ FROM ( { numeric_literal | + Currently not supported with array ELEMENT + foreign keys. + @@ -904,6 +922,60 @@ FROM ( { numeric_literal | + + 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 @@ -1843,6 +1915,16 @@ CREATE TABLE cities_ab_10000_to_100000 + Array <literal>ELEMENT</literal> Foreign Keys + + + Array ELEMENT foreign keys and the + ELEMENT REFERENCES clause + are a PostgreSQL extension. + + + + <literal>PARTITION BY</> Clause diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index a376b99f1e..a25ccd084c 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2099,7 +2099,9 @@ StoreRelCheck(Relation rel, char *ccname, Node *expr, NULL, NULL, 0, - ' ', + false, + NULL, + ' ', ' ', ' ', NULL, /* not an exclusion constraint */ diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 027abd56b0..a2e8baba55 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1212,6 +1212,8 @@ index_constraint_create(Relation heapRelation, NULL, NULL, 0, + false, + NULL, ' ', ' ', ' ', diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 98bcfa08c6..0c731b1085 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1226,7 +1226,9 @@ CREATE VIEW referential_constraints AS WHEN 'd' THEN 'SET DEFAULT' WHEN 'r' THEN 'RESTRICT' WHEN 'a' THEN 'NO ACTION' END - AS character_data) AS delete_rule + 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 1336c46d3f..c99b7c6cad 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -63,6 +63,8 @@ CreateConstraintEntry(const char *constraintName, const Oid *ppEqOp, const Oid *ffEqOp, int foreignNKeys, + bool confisElement, + const bool *foreignElement, char foreignUpdateType, char foreignDeleteType, char foreignMatchType, @@ -82,6 +84,7 @@ CreateConstraintEntry(const char *constraintName, Datum values[Natts_pg_constraint]; ArrayType *conkeyArray; ArrayType *confkeyArray; + ArrayType *confelementArray; ArrayType *conpfeqopArray; ArrayType *conppeqopArray; ArrayType *conffeqopArray; @@ -177,6 +180,7 @@ CreateConstraintEntry(const char *constraintName, 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); @@ -188,6 +192,11 @@ CreateConstraintEntry(const char *constraintName, 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 7d9c769b06..03dfa2ea5e 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -41,6 +41,7 @@ #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" @@ -6995,6 +6996,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, 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]; @@ -7082,6 +7084,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, */ 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)); @@ -7094,6 +7097,39 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, 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 @@ -7141,6 +7177,22 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, 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]; @@ -7156,6 +7208,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, Oid ffeqop; int16 eqstrategy; Oid pfeqop_right; + Oid fk_element_type = InvalidOid; /* We need several fields out of the pg_opclass entry */ cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i])); @@ -7189,6 +7242,31 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, 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. @@ -7208,6 +7286,26 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, /* keep compiler quiet */ pfeqop_right = InvalidOid; ffeqop = InvalidOid; + /* + * 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))) @@ -7225,25 +7323,46 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, Oid target_typeids[2]; input_typeids[0] = pktype; - input_typeids[1] = fktype; + 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 = ffeqop = ppeqop; + 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), - errmsg("foreign key constraint \"%s\" " - "cannot be implemented", + errmsg("foreign key constraint \"%s\" cannot be implemented", fkconstraint->conname), - errdetail("Key columns \"%s\" and \"%s\" " - "are of incompatible types: %s and %s.", + errdetail("Key columns \"%s\" and \"%s\" are of incompatible types: %s and %s.", strVal(list_nth(fkconstraint->fk_attrs, i)), strVal(list_nth(fkconstraint->pk_attrs, i)), format_type_be(fktype), @@ -7274,8 +7393,16 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, * 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; + 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, @@ -7345,6 +7472,8 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, ppeqoperators, ffeqoperators, numpks, + fkconstraint->fk_is_element, + fkattelement, fkconstraint->fk_upd_action, fkconstraint->fk_del_action, fkconstraint->fk_matchtype,