*What I did:*
- read into the old patch but couldn't apply it since it's quite old. It
needs to be rebased and that's what I am working on. It's a lot of work.
- incomplete patch can be found attached here
*Bugs*
- problem with the @>(anyarray, anyelement) opertator: if for example,
you apply the operator as follows '{AAAAAAAAAA646'}' @> 'AAAAAAAAAA646' it
maps to @>(anyarray, anyarray) since 'AAAAAAAAAA646' is interpreted as
char[] instead of Text
*Suggestion:*
- since I needed to check if the Datum was null and its type, I had to
do it in the arraycontainselem and pass it as a parameter to the underlying
function array_contains_elem. I'm proposing to introduce a new struct like
ArrayType, but ElementType along all with brand new MACROs to make dealing
with anyelement easier in any polymorphic context.
Best Regards,
Mark Rofail
On Tue, Jun 20, 2017 at 12:19 AM, Alvaro Herrera <alvhe...@2ndquadrant.com>
wrote:
> Mark Rofail wrote:
> > Okay, so major breakthrough.
> >
> > *Updates:*
> >
> > - The operator @>(anyarray, anyelement) is now functional
> > - The segmentation fault was due to applying PG_FREE_IF_COPY on a
> > datum when it should only be applied on TOASTed inputs
> > - The only problem now is if for example you apply the operator as
> > follows '{AAAAAAAAAA646'}' @> 'AAAAAAAAAA646' it maps to
> @>(anyarray,
> > anyarray) since 'AAAAAAAAAA646' is interpreted as char[] instead
> of Text
> > - Added some regression tests (src/test/regress/sql/arrays.sql) and
> > their results(src/test/regress/expected/arrays.out)
> > - wokred on the new GIN strategy, I don't think it would vary much
> from
> > GinContainsStrategy.
>
> OK, that's great.
>
> > *What I plan to do:*
> >
> > - I need to start working on the Referential Integrity code but I
> don't
> > where to start
>
> You need to study the old patch posted by Marco Nenciarini.
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
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$<replaceable><iteration count></>:<replaceable><salt><
</row>
<row>
+ <entry><structfield>confiselement</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>If a foreign key, is it an array <literal>ELEMENT</literal>
+ foreign key?</entry>
+ </row>
+
+ <row>
<entry><structfield>coninhcount</structfield></entry>
<entry><type>int4</type></entry>
<entry></entry>
@@ -2324,6 +2332,18 @@ SCRAM-SHA-256$<replaceable><iteration count></>:<replaceable><salt><
</row>
<row>
+ <entry><structfield>confelement</structfield></entry>
+ <entry><type>bool[]</type></entry>
+ <entry></entry>
+ <entry>
+ If a foreign key, list of booleans expressing which columns
+ are array <literal>ELEMENT</literal> columns; see
+ <xref linkend="sql-createtable-element-foreign-key-constraints">
+ for details
+ </entry>
+ </row>
+
+ <row>
<entry><structfield>conpfeqop</structfield></entry>
<entry><type>oid[]</type></entry>
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</></entry>
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 (
<xref linkend="sql-createtable">.
</para>
</sect2>
-
+
+ <sect2 id="ddl-constraints-element-fk">
+ <title>Array ELEMENT Foreign Keys</title>
+
+ <indexterm>
+ <primary>ELEMENT foreign key</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>constraint</primary>
+ <secondary>Array ELEMENT foreign key</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>constraint</primary>
+ <secondary>ELEMENT foreign key</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>referential integrity</primary>
+ </indexterm>
+
+ <para>
+ 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
+ <firstterm>array element foreign key</firstterm> and is implemented
+ in PostgreSQL with <firstterm>ELEMENT foreign key constraints</firstterm>,
+ as described in the following example:
+
+ <programlisting>
+ 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[] <emphasis>ELEMENT REFERENCES drivers</emphasis>
+ );
+ </programlisting>
+
+ The above example uses an array (<literal>final_positions</literal>)
+ to store the results of a race: for each of its elements
+ a referential integrity check is enforced on the
+ <literal>drivers</literal> table.
+ Note that <literal>ELEMENT REFERENCES</literal> is an extension
+ of PostgreSQL and it is not included in the SQL standard.
+ </para>
+
+ <para>
+ Even though the most common use case for array <literal>ELEMENT</literal>
+ foreign keys is on a single column key, you can define an <quote>array
+ <literal>ELEMENT</literal> foreign key constraint</quote> on a group
+ of columns. As the following example shows, it must be written in table
+ constraint form:
+
+ <programlisting>
+ CREATE TABLE available_moves (
+ kind text,
+ move text,
+ description text,
+ PRIMARY KEY (kind, move)
+ );
+
+ CREATE TABLE paths (
+ description text,
+ kind text,
+ moves text[],
+ <emphasis>FOREIGN KEY (kind, ELEMENT moves) REFERENCES available_moves (kind, move)</emphasis>
+ );
+
+ 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}');
+ </programlisting>
+
+ On top of standard foreign key requirements,
+ array <literal>ELEMENT</literal> foreign key constraints
+ require that the referencing column is an array of a compatible
+ type of the corresponding referenced column.
+ </para>
+
+ <para>
+ For more detailed information on array <literal>ELEMENT</literal>
+ foreign key options and special cases, please refer to the documentation
+ for <xref linkend="sql-createtable-foreign-key"> and
+ <xref linkend="sql-createtable-element-foreign-key-constraints">.
+ </para>
+
+ </sect2>
+
<sect2 id="ddl-constraints-exclusion">
<title>Exclusion Constraints</title>
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 [ ( <replaceable>sequence_options</replaceable> ) ] |
UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
- REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
+ [ELEMENT] REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
@@ -76,7 +76,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
- FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
+ FOREIGN KEY ( [ELEMENT] <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
@@ -779,10 +779,11 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
</listitem>
</varlistentry>
- <varlistentry>
+ <varlistentry id="sql-createtable-foreign-key" xreflabel="FOREIGN KEY">
<term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal> (column constraint)</term>
- <term><literal>FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] )
+ <term>
+ <literal>FOREIGN KEY ( [ELEMENT] <replaceable class="parameter">column</replaceable> [, ... ] )
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
[ MATCH <replaceable class="parameter">matchtype</replaceable> ]
[ ON DELETE <replaceable class="parameter">action</replaceable> ]
@@ -806,6 +807,19 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
</para>
<para>
+ In case the column name <replaceable class="parameter">column</replaceable>
+ is prepended with the <literal>ELEMENT</literal> keyword and <replaceable
+ class="parameter">column</replaceable> is an array of elements compatible
+ with the corresponding <replaceable class="parameter">refcolumn</replaceable>
+ in <replaceable class="parameter">reftable</replaceable>, an
+ array <literal>ELEMENT</literal> foreign key constraint is put in place
+ (see <xref linkend="sql-createtable-element-foreign-key-constraints">
+ for more information).
+ Multi-column keys with more than one <literal>ELEMENT</literal> column
+ are currently not allowed.
+ </para>
+
+ <para>
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: <literal>MATCH
@@ -868,7 +882,8 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
<para>
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 <literal>ELEMENT</literal> foreign keys.
</para>
</listitem>
</varlistentry>
@@ -877,7 +892,8 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
<term><literal>SET NULL</literal></term>
<listitem>
<para>
- Set the referencing column(s) to null.
+ Set the referencing column(s) to null. Currently not supported
+ with array <literal>ELEMENT</literal> foreign keys.
</para>
</listitem>
</varlistentry>
@@ -889,7 +905,9 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
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.)
- </para>
+ Currently not supported with array <literal>ELEMENT</literal>
+ foreign keys.
+ </para>
</listitem>
</varlistentry>
</variablelist>
@@ -904,6 +922,60 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-element-foreign-key-constraints" xreflabel="ELEMENT REFERENCES">
+ <term><literal>ELEMENT REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal> (column constraint)</term>
+ <listitem>
+ <para>
+ The <literal>ELEMENT REFERENCES</literal> definition specifies
+ an <quote>array <literal>ELEMENT</literal> foreign key</quote>,
+ 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
+ <replaceable class="parameter">refcolumn</replaceable> array
+ will be matched against some row of <replaceable
+ class="parameter">reftable</replaceable>.
+ </para>
+
+ <para>
+ Array <literal>ELEMENT</literal> foreign keys are an extension
+ of PostgreSQL and are not included in the SQL standard.
+ </para>
+
+ <para>
+ Even with <literal>ELEMENT</literal> 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 <literal>ON DELETE</literal> and
+ <literal>ON UPDATE</literal> clauses.
+ However, only the two following actions for each clause are
+ currently allowed:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>NO ACTION</literal></term>
+ <listitem>
+ <para>
+ Same as standard foreign key constraints. This is the default action.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RESTRICT</literal></term>
+ <listitem>
+ <para>
+ Same as standard foreign key constraints.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>DEFERRABLE</literal></term>
<term><literal>NOT DEFERRABLE</literal></term>
@@ -1843,6 +1915,16 @@ CREATE TABLE cities_ab_10000_to_100000
</refsect2>
<refsect2>
+ <title id="sql-createtable-foreign-key-arrays">Array <literal>ELEMENT</literal> Foreign Keys</title>
+
+ <para>
+ Array <literal>ELEMENT</literal> foreign keys and the
+ <literal>ELEMENT REFERENCES</literal> clause
+ are a <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect2>
+
+ <refsect2>
<title><literal>PARTITION BY</> Clause</title>
<para>
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,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
