[PATCH] Support for foreign keys with arrays
This patch adds basic support of arrays in foreign keys, by allowing to
define a referencing column as an array of elements having the same type
as the referenced column in the referenced table.
Every NOT NULL element in the referencing array is matched against the
referenced table.
Example:
CREATE TABLE pt (
id INTEGER PRIMARY KEY,
...
);
CREATE TABLE ft (
id SERIAL PRIMARY KEY,
pids INTEGER[] REFERENCES pt,
...
);
This patch is for discussion and has been built against HEAD.
It compiles and passes all regressions tests (including specific ones -
see the src/test/regress/sql/foreign_key.sql file).
Empty arrays, multi-dimensional arrays, duplicate elements and NULL
values are allowed.
We had to enforce some limitations, due to the lack (yet) of a clear and
universally accepted behaviour and strategy.
For example, consider the ON DELETE action on the above tables: in case
of delete of a record in the 'pt' table, should we remove the whole row
or just the values from the array?
We hope we can start a discussion from here.
Current limitations:
* Only arrays of the same type as the primary key in the referenced
table are supported
* multi-column foreign keys are not supported (only single column)
* Only RESTRICT and NO ACTION methods for referential integrity
enforcement are currently supported
TODO:
* Improve check for empty arrays, which might interfere with SSI (see below)
* Verify interaction with serializable transactions
AUTHORS:
* Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
* Marco Nenciarini <marco.nenciarini@2ndQuadrant.it>
Cheers,
Gabriele (and Marco)
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
Attachments:
foreign-key-arrays.patch.v1text/plain; name=foreign-key-arrays.patch.v1; x-mac-creator=0; x-mac-type=0Download
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
***************
*** 764,769 **** CREATE TABLE order_items (
--- 764,796 ----
the last table.
</para>
+ <para>
+ Another option you have with foreign keys is to use a referencing column
+ which is an array of elements with the same type as the referenced column
+ in the related table. This feature, also known as <firstterm>foreign key arrays</firstterm>,
+ is described in the following example:
+
+ <programlisting>
+ CREATE TABLE countries (
+ country_id integer PRIMARY KEY,
+ name text,
+ ...
+ );
+
+ CREATE TABLE people (
+ person_id integer PRIMARY KEY,
+ first_name text,
+ last_name text,
+ ...
+ citizenship_ids integer[] REFERENCES countries
+ );
+ </programlisting>
+
+ The above example lists in an array the citizenships held by
+ a person and enforces referential integrity checks.
+
+ </para>
+
<indexterm>
<primary>CASCADE</primary>
<secondary>foreign key action</secondary>
***************
*** 852,857 **** CREATE TABLE order_items (
--- 879,891 ----
</para>
<para>
+ When working with foreign key arrays, you are currently limited
+ to <literal>RESTRICT</literal> and <literal>NO ACTION</literal>
+ options, as the default behaviour for the other cases is not
+ clearly and universally determined yet.
+ </para>
+
+ <para>
More information about updating and deleting data is in <xref
linkend="dml">.
</para>
*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
***************
*** 576,581 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
--- 576,587 ----
</para>
<para>
+ If the referencing column is an array of elements of the same type as
+ the referenced column in the referenced table, the value of each element
+ of the array will be matched against some row of the referenced table.
+ </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
***************
*** 634,640 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<para>
Delete any rows referencing the deleted row, or update the
value of the referencing column to the new value of the
! referenced column, respectively.
</para>
</listitem>
</varlistentry>
--- 640,647 ----
<para>
Delete any rows referencing the deleted row, or update the
value of the referencing column to the new value of the
! referenced column, respectively. Foreign key arrays are not
! supported by this action (as the behaviour is not easily determined).
</para>
</listitem>
</varlistentry>
***************
*** 643,649 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<term><literal>SET NULL</literal></term>
<listitem>
<para>
! Set the referencing column(s) to null.
</para>
</listitem>
</varlistentry>
--- 650,657 ----
<term><literal>SET NULL</literal></term>
<listitem>
<para>
! Set the referencing column(s) to null. Foreign key arrays are not
! supported by this action (as the behaviour is not easily determined).
</para>
</listitem>
</varlistentry>
***************
*** 652,658 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<term><literal>SET DEFAULT</literal></term>
<listitem>
<para>
! Set the referencing column(s) to their default values.
</para>
</listitem>
</varlistentry>
--- 660,667 ----
<term><literal>SET DEFAULT</literal></term>
<listitem>
<para>
! Set the referencing column(s) to their default values. Foreign key arrays are not
! supported by this action (as the behaviour is not easily determined).
</para>
</listitem>
</varlistentry>
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 5705,5710 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
--- 5705,5735 ----
Oid ffeqop;
int16 eqstrategy;
+ /* Check if foreign key is an array of primary key types */
+ const bool is_foreign_key_array = (fktype == get_array_type (pktype));
+
+ /* Enforce foreign key array restrictions */
+ if (is_foreign_key_array)
+ {
+ /*
+ * Foreign key array must not be part of a multi-column foreign key
+ */
+ if (is_foreign_key_array && numpks > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FOREIGN_KEY),
+ errmsg("foreign key arrays must not be part of a multi-column foreign key")));
+
+ /*
+ * We have to restrict foreign key array to NO ACTION and RESTRICT mode
+ * until the behaviour triggered by the other actions is clearer and well defined
+ */
+ 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("NO ACTION and RESTRICT are the only supported actions for foreign key arrays")));
+ }
+
/* We need several fields out of the pg_opclass entry */
cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i]));
if (!HeapTupleIsValid(cla_ht))
***************
*** 5766,5772 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
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,
--- 5791,5801 ----
Oid target_typeids[2];
input_typeids[0] = pktype;
! /* When is FKA we must use for FK the same type of PK */
! if (is_foreign_key_array)
! input_typeids[1] = pktype;
! else
! input_typeids[1] = fktype;
target_typeids[0] = opcintype;
target_typeids[1] = opcintype;
if (can_coerce_type(2, input_typeids, target_typeids,
*** a/src/backend/utils/adt/ri_triggers.c
--- b/src/backend/utils/adt/ri_triggers.c
***************
*** 460,465 **** RI_FKey_check(PG_FUNCTION_ARGS)
--- 460,466 ----
char paramname[16];
const char *querysep;
Oid queryoids[RI_MAX_NUMKEYS];
+ bool is_foreign_key_array = false;
/* ----------
* The query string built is
***************
*** 476,493 **** RI_FKey_check(PG_FUNCTION_ARGS)
{
Oid pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]);
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,
--- 477,524 ----
{
Oid pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]);
+ is_foreign_key_array = (fk_type == get_array_type (pk_type));
quoteOneName(attname,
RIAttName(pk_rel, riinfo.pk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
! /*
! * In case of an array foreign key, we check that every
! * DISTINCT NOT NULL value in the array is present in the PK table.
! * XXX: This works because the query is executed with LIMIT 1,
! * but may not work properly with SSI (a better approach would be
! * to inspect the array and skip the check in case of empty arrays).
! */
! if (is_foreign_key_array)
! {
! appendStringInfo(&querybuf, " %s (SELECT count(*) FROM (SELECT DISTINCT UNNEST(%s)) y WHERE y IS NOT NULL)", querysep, paramname);
! appendStringInfo(&querybuf, " = (SELECT count(*) FROM (SELECT 1 FROM ONLY %s y", pkrelname);
! ri_GenerateQual(&querybuf, "WHERE",
! attname, pk_type,
! riinfo.pf_eq_oprs[i],
! paramname, fk_type);
! /*
! * We lock for share every row in the pkreltable that is
! * referenced by the array elements
! */
! appendStringInfo(&querybuf, " FOR SHARE OF y) z)");
! }
! else
! {
! ri_GenerateQual(&querybuf, querysep,
! attname, pk_type,
! riinfo.pf_eq_oprs[i],
! paramname, fk_type);
! }
querysep = "AND";
queryoids[i] = fk_type;
}
! /*
! * We skip locking for share in case of foreign key arrays
! * as it has been done in the inner subquery
! */
! if (! is_foreign_key_array)
! appendStringInfo(&querybuf, " FOR SHARE OF x");
/* Prepare and save the plan */
qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,
***************
*** 2949,2957 **** ri_GenerateQual(StringInfo buf,
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);
}
--- 2980,3002 ----
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 (rightoptype == get_array_type (leftoptype))
! {
! 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);
}
*** a/src/test/regress/expected/foreign_key.out
--- b/src/test/regress/expected/foreign_key.out
***************
*** 968,978 **** drop table pktable;
drop table pktable_base;
-- 2 columns (1 table), mismatched types
create table pktable_base(base1 int not null, base2 int);
- create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
- pktable(base1, ptest1)) inherits (pktable_base);
- NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
- ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented
- DETAIL: Key columns "ptest2" and "ptest1" are of incompatible types: inet[] and inet.
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
pktable(ptest1, base1)) inherits (pktable_base);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
--- 968,973 ----
***************
*** 1319,1321 **** begin;
--- 1314,1537 ----
(2 rows)
commit;
+ -- ARRAY FK
+ --
+ CREATE TABLE PKTABLEFORARRAY ( ptest1 int PRIMARY KEY, ptest2 text );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- 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 (ftest1) REFERENCES PKTABLEFORARRAY;
+ DROP TABLE FKTABLEFORARRAY;
+ -- Check create table
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAYMDIM ( ftest1 int[][] REFERENCES PKTABLEFORARRAY, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAYNOTNULL ( ftest1 int[] NOT NULL REFERENCES PKTABLEFORARRAY, ftest2 int );
+ -- Insert successful rows into FK TABLE
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{2}', 3);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{3}', 4);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,5}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,4}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES (NULL, 1);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{}', 5);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1,NULL}', 6);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{NULL}', 7);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{1,2},{1,3}}', 8);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{NULL,2},{NULL,3}}', 9);
+ -- Insert failed rows into FK TABLE
+ INSERT INTO FKTABLEFORARRAY VALUES ('{6}', 3);
+ 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}', 4);
+ 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}', 5);
+ 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}', 6);
+ 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}}', 7);
+ 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, 1);
+ ERROR: null value in column "ftest1" violates not-null constraint
+ -- Check FKTABLE
+ SELECT * FROM FKTABLEFORARRAY;
+ ftest1 | ftest2
+ ----------+--------
+ {1} | 2
+ {2} | 3
+ {1} | 2
+ {3} | 4
+ {1} | 2
+ {4,5} | 2
+ {4,4} | 2
+ | 1
+ {} | 5
+ {1,NULL} | 6
+ {NULL} | 7
+ (11 rows)
+
+ -- Delete a row from PK TABLE (must fail)
+ 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} | 2
+ {2} | 3
+ {1} | 2
+ {3} | 4
+ {1} | 2
+ {4,5} | 2
+ {4,4} | 2
+ | 1
+ {} | 5
+ {1,NULL} | 6
+ {NULL} | 7
+ (11 rows)
+
+ -- Update a row from PK TABLE (must fail)
+ 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} | 2
+ {2} | 3
+ {1} | 2
+ {3} | 4
+ {1} | 2
+ {4,5} | 2
+ {4,4} | 2
+ | 1
+ {} | 5
+ {1,NULL} | 6
+ {NULL} | 7
+ (11 rows)
+
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE FKTABLEFORARRAYNOTNULL;
+ DROP TABLE FKTABLEFORARRAYMDIM;
+ -- Allowed references with actions 'RESTRICT' and 'NO ACTION'
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int );
+ DROP TABLE FKTABLEFORARRAY;
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int );
+ DROP TABLE FKTABLEFORARRAY;
+ -- Failed attempts of creating references with actions other than 'RESTRICT' and 'NO ACTION'
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE RESTRICT, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE CASCADE, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE NO ACTION, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE CASCADE, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET NULL, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET DEFAULT, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ -- Cleanup
+ DROP TABLE PKTABLEFORARRAY;
+ -- Repeat a similar test using CHAR(1) keys rather than INTEGER
+ CREATE TABLE PKTABLEFORARRAY ( ptest1 CHAR(1) PRIMARY KEY, ptest2 text );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- 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)[] REFERENCES PKTABLEFORARRAY, 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".
+ -- Cleanup
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE PKTABLEFORARRAY;
+ -- Composite primary keys (unsupported)
+ CREATE TABLE PKTABLEFORARRAY ( id1 CHAR(1), id2 CHAR(1), ptest2 text, PRIMARY KEY (id1, id2) );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- 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');
+ -- Must fail (cannot use arrays in composite foreign keys - use an array of composite types)
+ CREATE TABLE FKTABLEFORARRAY ( fid1 CHAR(1), fid2 CHAR(1)[], ftest2 text, FOREIGN KEY (fid1, fid2) REFERENCES PKTABLEFORARRAY (id1, id2));
+ ERROR: foreign key arrays must not be part of a multi-column foreign key
+ -- Cleanup
+ DROP TABLE PKTABLEFORARRAY;
+ -- Test foreign key arrays with composite type
+ CREATE TYPE INVOICEID AS (year_part INTEGER, progressive_part INTEGER);
+ CREATE TABLE PKTABLEFORARRAY ( id INVOICEID PRIMARY KEY, ptest2 text);
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- 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[] REFERENCES PKTABLEFORARRAY, ftest2 TEXT );
+ NOTICE: CREATE TABLE will create implicit sequence "fktableforarray_id_seq" for serial column "fktableforarray.id"
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktableforarray_pkey" for table "fktableforarray"
+ -- 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".
+ -- Cleanup
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE PKTABLEFORARRAY;
+ -- Create primary table with a primary key array
+ CREATE TABLE PKTABLEFORARRAY ( id INT[] PRIMARY KEY, ptest2 text);
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- Create the foreign table
+ CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, fids INT[] REFERENCES PKTABLEFORARRAY, ftest2 TEXT );
+ NOTICE: CREATE TABLE will create implicit sequence "fktableforarray_id_seq" for serial column "fktableforarray.id"
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktableforarray_pkey" for table "fktableforarray"
+ -- 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;
*** a/src/test/regress/sql/foreign_key.sql
--- b/src/test/regress/sql/foreign_key.sql
***************
*** 602,609 **** drop table pktable_base;
-- 2 columns (1 table), mismatched types
create table pktable_base(base1 int not null, base2 int);
- create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
- pktable(base1, ptest1)) inherits (pktable_base);
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
pktable(ptest1, base1)) inherits (pktable_base);
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
--- 602,607 ----
***************
*** 943,945 **** begin;
--- 941,1111 ----
update selfref set a = 456 where a = 123;
select a, b from selfref;
commit;
+
+ -- ARRAY FK
+ --
+ 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 (ftest1) REFERENCES PKTABLEFORARRAY;
+ DROP TABLE FKTABLEFORARRAY;
+
+ -- Check create table
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAYMDIM ( ftest1 int[][] REFERENCES PKTABLEFORARRAY, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAYNOTNULL ( ftest1 int[] NOT NULL REFERENCES PKTABLEFORARRAY, ftest2 int );
+
+ -- Insert successful rows into FK TABLE
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{2}', 3);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{3}', 4);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,5}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,4}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES (NULL, 1);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{}', 5);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1,NULL}', 6);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{NULL}', 7);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{1,2},{1,3}}', 8);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{NULL,2},{NULL,3}}', 9);
+
+ -- Insert failed rows into FK TABLE
+ INSERT INTO FKTABLEFORARRAY VALUES ('{6}', 3);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,6}', 4);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL}', 5);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL,4,NULL}', 6);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{1,2},{6,NULL}}', 7);
+ INSERT INTO FKTABLEFORARRAYNOTNULL VALUES (NULL, 1);
+
+ -- Check FKTABLE
+ SELECT * FROM FKTABLEFORARRAY;
+
+ -- Delete a row from PK TABLE (must fail)
+ DELETE FROM PKTABLEFORARRAY WHERE ptest1=1;
+
+ -- Check FKTABLE for removal of matched row
+ SELECT * FROM FKTABLEFORARRAY;
+
+ -- Update a row from PK TABLE (must fail)
+ UPDATE PKTABLEFORARRAY SET ptest1=7 WHERE ptest1=1;
+
+ -- Check FKTABLE for update of matched row
+ SELECT * FROM FKTABLEFORARRAY;
+
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE FKTABLEFORARRAYNOTNULL;
+ DROP TABLE FKTABLEFORARRAYMDIM;
+
+ -- Allowed references with actions 'RESTRICT' and 'NO ACTION'
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int );
+ DROP TABLE FKTABLEFORARRAY;
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int );
+ DROP TABLE FKTABLEFORARRAY;
+
+ -- Failed attempts of creating references with actions other than 'RESTRICT' and 'NO ACTION'
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE RESTRICT, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE CASCADE, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE NO ACTION, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE CASCADE, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET NULL, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET DEFAULT, ftest2 int );
+
+ -- Cleanup
+ 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)[] REFERENCES PKTABLEFORARRAY, 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);
+
+ -- Cleanup
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE PKTABLEFORARRAY;
+
+ -- Composite primary keys (unsupported)
+ 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');
+
+ -- Must fail (cannot use arrays in composite foreign keys - use an array of composite types)
+ CREATE TABLE FKTABLEFORARRAY ( fid1 CHAR(1), fid2 CHAR(1)[], ftest2 text, FOREIGN KEY (fid1, fid2) REFERENCES PKTABLEFORARRAY (id1, id2));
+
+ -- Cleanup
+ DROP TABLE PKTABLEFORARRAY;
+
+ -- Test foreign key arrays 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[] 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');
+
+ -- Cleanup
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE PKTABLEFORARRAY;
+
+ -- Create primary table with a primary key array
+ 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;
Hi Gabriele,
On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote:
CREATE TABLE pt (
id INTEGER PRIMARY KEY,
...
);CREATE TABLE ft (
id SERIAL PRIMARY KEY,
pids INTEGER[] REFERENCES pt,
...
);
This seems useful.
I'm assuming the SQL spec says nothing about a feature like this?
This patch is for discussion and has been built against HEAD.
It compiles and passes all regressions tests (including specific ones -
see the src/test/regress/sql/foreign_key.sql file).
Empty arrays, multi-dimensional arrays, duplicate elements and NULL
values are allowed.
With this patch, RI_Initial_Check does not detect a violation in an array that
contains at least one conforming element:
BEGIN;
CREATE TABLE parent (c int PRIMARY KEY);
CREATE TABLE child (c int[]);
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES ('{3,1,2}');
ALTER TABLE child ADD FOREIGN KEY (c) REFERENCES parent; -- should error
INSERT INTO child VALUES ('{3,1,2}'); -- does error, as expected
ROLLBACK;
The error message DETAIL on constraint violation would benefit from
array-FK-specific language. Example of current message:
ERROR: insert or update on table "child" violates foreign key constraint "child_c_fkey"
DETAIL: Key (c)=({3,1,2}) is not present in table "parent".
The patch is missing a change to the code that does FK=FK checks when a user
updates the FK side:
\set VERBOSITY verbose
BEGIN;
CREATE TABLE parent (c int PRIMARY KEY);
CREATE TABLE child (c int[] REFERENCES parent);
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES ('{1,1}');
COMMIT;
-- ERROR: XX000: no conversion function from integer[] to integer
-- LOCATION: ri_HashCompareOp, ri_triggers.c:4097
UPDATE child SET c = '{1,1}';
DROP TABLE parent, child;
COMMIT;
Please audit each ri_triggers.c entry point for further problems like this.
We had to enforce some limitations, due to the lack (yet) of a clear and
universally accepted behaviour and strategy.
For example, consider the ON DELETE action on the above tables: in case
of delete of a record in the 'pt' table, should we remove the whole row
or just the values from the array?
We hope we can start a discussion from here.
Removing values from the array seems best to me. There's no doubt about what
ON UPDATE CASCADE should do, and having ON DELETE CASCADE excise individual
array elements is consistent with that. It's less clear for SET NULL, but I'd
continue with a per-element treatment. I'd continue to forbid SET DEFAULT.
However, Jeff Davis did expect ON DELETE CASCADE to remove entire rows:
http://archives.postgresql.org/message-id/1288119207.15279.24.camel@jdavis-ux.asterdata.local
So, perhaps the behavior needs to be user-selectable.
Current limitations:
* Only arrays of the same type as the primary key in the referenced
table are supported
This is understandable for a WIP, but the final patch will need to use our
existing, looser foreign key type match requirement.
* multi-column foreign keys are not supported (only single column)
Any particular reason for this?
*** a/doc/src/sgml/ddl.sgml --- b/doc/src/sgml/ddl.sgml *************** *** 764,769 **** CREATE TABLE order_items ( --- 764,796 ---- the last table. </para>+ <para> + Another option you have with foreign keys is to use a referencing column + which is an array of elements with the same type as the referenced column + in the related table. This feature, also known as <firstterm>foreign key arrays</firstterm>, + is described in the following example:
Please wrap your documentation paragraphs.
*** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 5705,5710 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, --- 5705,5735 ---- Oid ffeqop; int16 eqstrategy;+ /* Check if foreign key is an array of primary key types */ + const bool is_foreign_key_array = (fktype == get_array_type (pktype));
We don't declare non-pointer, local variables "const". Also, [not positive on
this one] when an initial assignment requires a comment, declare the variable
with no assignment and no comment. Then, assign it later with the comment.
This keeps the per-block declarations packed together.
This test wrongly rejects FK types that are domains over the array type:
BEGIN;
CREATE TABLE parent (c int PRIMARY KEY);
CREATE DOMAIN intarrdom AS int[];
CREATE TABLE child (c intarrdom REFERENCES parent);
ROLLBACK;
+ + /* Enforce foreign key array restrictions */ + if (is_foreign_key_array) + { + /* + * Foreign key array must not be part of a multi-column foreign key + */ + if (is_foreign_key_array && numpks > 1) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key arrays must not be part of a multi-column foreign key"))); + + /* + * We have to restrict foreign key array to NO ACTION and RESTRICT mode + * until the behaviour triggered by the other actions is clearer and well defined + */ + 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))
Break these lines to keep things within 78 columns. Audit the remainder of
your changes for long lines, and break when in doubt.
+ ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("NO ACTION and RESTRICT are the only supported actions for foreign key arrays")));
Error message constants can remain unbroken, though.
+ }
+
/* We need several fields out of the pg_opclass entry */
cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i]));
if (!HeapTupleIsValid(cla_ht))
***************
*** 5766,5772 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
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, --- 5791,5801 ---- Oid target_typeids[2];input_typeids[0] = pktype;
! /* When is FKA we must use for FK the same type of PK */
! if (is_foreign_key_array)
! input_typeids[1] = pktype;
! else
! input_typeids[1] = fktype;
target_typeids[0] = opcintype;
target_typeids[1] = opcintype;
if (can_coerce_type(2, input_typeids, target_typeids,
This is bogus; the can_coerce_type test will always pass (excluding bad cases
of catalog inconsistency).
ATAddForeignKeyConstraint should choose to make an array foreign key whenever
the PK side is a scalar and the FK side is an array. Then, grab the element
type of the FK side and feed that through the operator-identification logic.
*** a/src/backend/utils/adt/ri_triggers.c --- b/src/backend/utils/adt/ri_triggers.c *************** *** 460,465 **** RI_FKey_check(PG_FUNCTION_ARGS) --- 460,466 ---- char paramname[16]; const char *querysep; Oid queryoids[RI_MAX_NUMKEYS]; + bool is_foreign_key_array = false;/* ----------
* The query string built is
***************
*** 476,493 **** RI_FKey_check(PG_FUNCTION_ARGS)
{
Oid pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]);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, --- 477,524 ---- { Oid pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]); Oid fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]); + is_foreign_key_array = (fk_type == get_array_type (pk_type));
Drop the extra whitespace before the function argument list.
quoteOneName(attname,
RIAttName(pk_rel, riinfo.pk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
! /*
! * In case of an array foreign key, we check that every
! * DISTINCT NOT NULL value in the array is present in the PK table.
! * XXX: This works because the query is executed with LIMIT 1,
I found this comment confusing, since the SQL syntax "LIMIT 1" is never used
here. I suppose you're referring to the fact that we call into SPI with
tcount = 1?
! * but may not work properly with SSI (a better approach would be
! * to inspect the array and skip the check in case of empty arrays).
Why might serializable transactions be specially affected?
! */
! if (is_foreign_key_array)
! {
! appendStringInfo(&querybuf, " %s (SELECT count(*) FROM (SELECT DISTINCT UNNEST(%s)) y WHERE y IS NOT NULL)", querysep, paramname);
! appendStringInfo(&querybuf, " = (SELECT count(*) FROM (SELECT 1 FROM ONLY %s y", pkrelname);
! ri_GenerateQual(&querybuf, "WHERE",
! attname, pk_type,
! riinfo.pf_eq_oprs[i],
! paramname, fk_type);
! /*
! * We lock for share every row in the pkreltable that is
! * referenced by the array elements
! */
! appendStringInfo(&querybuf, " FOR SHARE OF y) z)");
The resulting query performs an irrelevant sequential scan on the PK table:
SELECT 1 FROM ONLY "public"."parent" x WHERE (SELECT count(*) FROM (SELECT DISTINCT UNNEST($1)) y WHERE y IS NOT NULL) = (SELECT count(*) FROM (SELECT 1 FROM ONLY "public"."parent" y WHERE "c" OPERATOR(pg_catalog.=) ANY ($1) FOR SHARE OF y) z)
As you suggested with that comment above, this scan always ends after one row.
That places a bound on the actual performance hit. However, we still read the
one row, which may mean loading a page for nothing. At a minimum, simplify
this query to:
SELECT 1 WHERE (SELECT count(*) FROM (SELECT DISTINCT UNNEST($1)) y WHERE y IS NOT NULL) = (SELECT count(*) FROM (SELECT 1 FROM ONLY "public"."parent" y WHERE "c" OPERATOR(pg_catalog.=) ANY ($1) FOR SHARE OF y) z)
That also naturally handles empty arrays against empty PK tables, which
currently fail for me even at READ COMMITTED:
BEGIN;
CREATE TABLE parent (c int PRIMARY KEY);
CREATE TABLE child (c int[] REFERENCES parent);
INSERT INTO child VALUES ('{}'); -- fails wrongly
ROLLBACK;
! }
! else
! {
! ri_GenerateQual(&querybuf, querysep,
! attname, pk_type,
! riinfo.pf_eq_oprs[i],
! paramname, fk_type);
! }
querysep = "AND";
queryoids[i] = fk_type;
}
! /*
! * We skip locking for share in case of foreign key arrays
! * as it has been done in the inner subquery
! */
! if (! is_foreign_key_array)
Drop the whitespace after the "!".
! appendStringInfo(&querybuf, " FOR SHARE OF x");
/* Prepare and save the plan */
qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,
*** a/src/test/regress/expected/foreign_key.out --- b/src/test/regress/expected/foreign_key.out *************** *** 968,978 **** drop table pktable; drop table pktable_base; -- 2 columns (1 table), mismatched types create table pktable_base(base1 int not null, base2 int); - create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references - pktable(base1, ptest1)) inherits (pktable_base); - NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" - ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented - DETAIL: Key columns "ptest2" and "ptest1" are of incompatible types: inet[] and inet.
Instead of deleting this test, change the type from inet[] to something
unrelated, like float8.
Thanks,
nm
Hello
2011/11/17 Noah Misch <noah@leadboat.com>:
Hi Gabriele,
On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote:
CREATE TABLE pt (
id INTEGER PRIMARY KEY,
...
);CREATE TABLE ft (
id SERIAL PRIMARY KEY,
pids INTEGER[] REFERENCES pt,
...
);This seems useful.
will be supported situation
CREATE TABLE main(
id int[] PRIMARY KEY,
...
);
CREATE TABLE child(
main_id int[] REFERENCES main(id),
??
Regards
Pavel Stehule
Show quoted text
I'm assuming the SQL spec says nothing about a feature like this?
This patch is for discussion and has been built against HEAD.
It compiles and passes all regressions tests (including specific ones -
see the src/test/regress/sql/foreign_key.sql file).
Empty arrays, multi-dimensional arrays, duplicate elements and NULL
values are allowed.With this patch, RI_Initial_Check does not detect a violation in an array that
contains at least one conforming element:BEGIN;
CREATE TABLE parent (c int PRIMARY KEY);
CREATE TABLE child (c int[]);
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES ('{3,1,2}');
ALTER TABLE child ADD FOREIGN KEY (c) REFERENCES parent; -- should error
INSERT INTO child VALUES ('{3,1,2}'); -- does error, as expected
ROLLBACK;The error message DETAIL on constraint violation would benefit from
array-FK-specific language. Example of current message:ERROR: insert or update on table "child" violates foreign key constraint "child_c_fkey"
DETAIL: Key (c)=({3,1,2}) is not present in table "parent".The patch is missing a change to the code that does FK=FK checks when a user
updates the FK side:\set VERBOSITY verbose
BEGIN;
CREATE TABLE parent (c int PRIMARY KEY);
CREATE TABLE child (c int[] REFERENCES parent);
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES ('{1,1}');
COMMIT;
-- ERROR: XX000: no conversion function from integer[] to integer
-- LOCATION: ri_HashCompareOp, ri_triggers.c:4097
UPDATE child SET c = '{1,1}';
DROP TABLE parent, child;
COMMIT;Please audit each ri_triggers.c entry point for further problems like this.
We had to enforce some limitations, due to the lack (yet) of a clear and
universally accepted behaviour and strategy.
For example, consider the ON DELETE action on the above tables: in case
of delete of a record in the 'pt' table, should we remove the whole row
or just the values from the array?
We hope we can start a discussion from here.Removing values from the array seems best to me. There's no doubt about what
ON UPDATE CASCADE should do, and having ON DELETE CASCADE excise individual
array elements is consistent with that. It's less clear for SET NULL, but I'd
continue with a per-element treatment. I'd continue to forbid SET DEFAULT.However, Jeff Davis did expect ON DELETE CASCADE to remove entire rows:
http://archives.postgresql.org/message-id/1288119207.15279.24.camel@jdavis-ux.asterdata.local
So, perhaps the behavior needs to be user-selectable.Current limitations:
* Only arrays of the same type as the primary key in the referenced
table are supportedThis is understandable for a WIP, but the final patch will need to use our
existing, looser foreign key type match requirement.* multi-column foreign keys are not supported (only single column)
Any particular reason for this?
*** a/doc/src/sgml/ddl.sgml --- b/doc/src/sgml/ddl.sgml *************** *** 764,769 **** CREATE TABLE order_items ( --- 764,796 ---- the last table. </para>+ <para> + Another option you have with foreign keys is to use a referencing column + which is an array of elements with the same type as the referenced column + in the related table. This feature, also known as <firstterm>foreign key arrays</firstterm>, + is described in the following example:Please wrap your documentation paragraphs.
*** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 5705,5710 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, --- 5705,5735 ---- Oid ffeqop; int16 eqstrategy;+ /* Check if foreign key is an array of primary key types */ + const bool is_foreign_key_array = (fktype == get_array_type (pktype));We don't declare non-pointer, local variables "const". Also, [not positive on
this one] when an initial assignment requires a comment, declare the variable
with no assignment and no comment. Then, assign it later with the comment.
This keeps the per-block declarations packed together.This test wrongly rejects FK types that are domains over the array type:
BEGIN;
CREATE TABLE parent (c int PRIMARY KEY);
CREATE DOMAIN intarrdom AS int[];
CREATE TABLE child (c intarrdom REFERENCES parent);
ROLLBACK;+ + /* Enforce foreign key array restrictions */ + if (is_foreign_key_array) + { + /* + * Foreign key array must not be part of a multi-column foreign key + */ + if (is_foreign_key_array && numpks > 1) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key arrays must not be part of a multi-column foreign key"))); + + /* + * We have to restrict foreign key array to NO ACTION and RESTRICT mode + * until the behaviour triggered by the other actions is clearer and well defined + */ + 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))Break these lines to keep things within 78 columns. Audit the remainder of
your changes for long lines, and break when in doubt.+ ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("NO ACTION and RESTRICT are the only supported actions for foreign key arrays")));Error message constants can remain unbroken, though.
+ }
+
/* We need several fields out of the pg_opclass entry */
cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i]));
if (!HeapTupleIsValid(cla_ht))
***************
*** 5766,5772 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
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, --- 5791,5801 ---- Oid target_typeids[2];input_typeids[0] = pktype;
! /* When is FKA we must use for FK the same type of PK */
! if (is_foreign_key_array)
! input_typeids[1] = pktype;
! else
! input_typeids[1] = fktype;
target_typeids[0] = opcintype;
target_typeids[1] = opcintype;
if (can_coerce_type(2, input_typeids, target_typeids,This is bogus; the can_coerce_type test will always pass (excluding bad cases
of catalog inconsistency).ATAddForeignKeyConstraint should choose to make an array foreign key whenever
the PK side is a scalar and the FK side is an array. Then, grab the element
type of the FK side and feed that through the operator-identification logic.*** a/src/backend/utils/adt/ri_triggers.c --- b/src/backend/utils/adt/ri_triggers.c *************** *** 460,465 **** RI_FKey_check(PG_FUNCTION_ARGS) --- 460,466 ---- char paramname[16]; const char *querysep; Oid queryoids[RI_MAX_NUMKEYS]; + bool is_foreign_key_array = false;/* ----------
* The query string built is
***************
*** 476,493 **** RI_FKey_check(PG_FUNCTION_ARGS)
{
Oid pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]);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, --- 477,524 ---- { Oid pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]); Oid fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]); + is_foreign_key_array = (fk_type == get_array_type (pk_type));Drop the extra whitespace before the function argument list.
quoteOneName(attname,
RIAttName(pk_rel, riinfo.pk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
! /*
! * In case of an array foreign key, we check that every
! * DISTINCT NOT NULL value in the array is present in the PK table.
! * XXX: This works because the query is executed with LIMIT 1,I found this comment confusing, since the SQL syntax "LIMIT 1" is never used
here. I suppose you're referring to the fact that we call into SPI with
tcount = 1?! * but may not work properly with SSI (a better approach would be
! * to inspect the array and skip the check in case of empty arrays).Why might serializable transactions be specially affected?
! */
! if (is_foreign_key_array)
! {
! appendStringInfo(&querybuf, " %s (SELECT count(*) FROM (SELECT DISTINCT UNNEST(%s)) y WHERE y IS NOT NULL)", querysep, paramname);
! appendStringInfo(&querybuf, " = (SELECT count(*) FROM (SELECT 1 FROM ONLY %s y", pkrelname);
! ri_GenerateQual(&querybuf, "WHERE",
! attname, pk_type,
! riinfo.pf_eq_oprs[i],
! paramname, fk_type);
! /*
! * We lock for share every row in the pkreltable that is
! * referenced by the array elements
! */
! appendStringInfo(&querybuf, " FOR SHARE OF y) z)");The resulting query performs an irrelevant sequential scan on the PK table:
SELECT 1 FROM ONLY "public"."parent" x WHERE (SELECT count(*) FROM (SELECT DISTINCT UNNEST($1)) y WHERE y IS NOT NULL) = (SELECT count(*) FROM (SELECT 1 FROM ONLY "public"."parent" y WHERE "c" OPERATOR(pg_catalog.=) ANY ($1) FOR SHARE OF y) z)
As you suggested with that comment above, this scan always ends after one row.
That places a bound on the actual performance hit. However, we still read the
one row, which may mean loading a page for nothing. At a minimum, simplify
this query to:SELECT 1 WHERE (SELECT count(*) FROM (SELECT DISTINCT UNNEST($1)) y WHERE y IS NOT NULL) = (SELECT count(*) FROM (SELECT 1 FROM ONLY "public"."parent" y WHERE "c" OPERATOR(pg_catalog.=) ANY ($1) FOR SHARE OF y) z)
That also naturally handles empty arrays against empty PK tables, which
currently fail for me even at READ COMMITTED:BEGIN;
CREATE TABLE parent (c int PRIMARY KEY);
CREATE TABLE child (c int[] REFERENCES parent);
INSERT INTO child VALUES ('{}'); -- fails wrongly
ROLLBACK;! }
! else
! {
! ri_GenerateQual(&querybuf, querysep,
! attname, pk_type,
! riinfo.pf_eq_oprs[i],
! paramname, fk_type);
! }
querysep = "AND";
queryoids[i] = fk_type;
}
! /*
! * We skip locking for share in case of foreign key arrays
! * as it has been done in the inner subquery
! */
! if (! is_foreign_key_array)Drop the whitespace after the "!".
! appendStringInfo(&querybuf, " FOR SHARE OF x");
/* Prepare and save the plan */
qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,*** a/src/test/regress/expected/foreign_key.out --- b/src/test/regress/expected/foreign_key.out *************** *** 968,978 **** drop table pktable; drop table pktable_base; -- 2 columns (1 table), mismatched types create table pktable_base(base1 int not null, base2 int); - create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references - pktable(base1, ptest1)) inherits (pktable_base); - NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" - ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented - DETAIL: Key columns "ptest2" and "ptest1" are of incompatible types: inet[] and inet.Instead of deleting this test, change the type from inet[] to something
unrelated, like float8.Thanks,
nm--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Noah Misch <noah@leadboat.com> writes:
On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote:
CREATE TABLE pt (
id INTEGER PRIMARY KEY,CREATE TABLE ft (
id SERIAL PRIMARY KEY,
pids INTEGER[] REFERENCES pt,
I'm assuming the SQL spec says nothing about a feature like this?
I'm pretty certain that the SQL spec flat out forbids this.
The least we could do is invent some non-spec syntax that makes the
intention clear, rather than having the system assume that an error case
was intended to mean something else. Maybe
pids INTEGER[] ARRAY REFERENCES pt,
or something like that. (ARRAY is a fully reserved word already,
so I think this syntax should work, but I've not tried it.)
BTW, has anyone thought through whether this is a sane idea at all?
It seems to me to be full of cases that will require rather arbitrary
decisions, like whether ON DELETE CASCADE should involve deleting the
whole row or just one array element.
regards, tom lane
BTW, has anyone thought through whether this is a sane idea at all?
It seems to me to be full of cases that will require rather arbitrary
decisions, like whether ON DELETE CASCADE should involve deleting the
whole row or just one array element.
One array element, presumably.
Does the patch implement CASCADE?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
2011/11/17 Tom Lane <tgl@sss.pgh.pa.us>:
Noah Misch <noah@leadboat.com> writes:
On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote:
CREATE TABLE pt (
id INTEGER PRIMARY KEY,CREATE TABLE ft (
id SERIAL PRIMARY KEY,
pids INTEGER[] REFERENCES pt,I'm assuming the SQL spec says nothing about a feature like this?
I'm pretty certain that the SQL spec flat out forbids this.
The least we could do is invent some non-spec syntax that makes the
intention clear, rather than having the system assume that an error case
was intended to mean something else. Maybepids INTEGER[] ARRAY REFERENCES pt,
or something like that. (ARRAY is a fully reserved word already,
so I think this syntax should work, but I've not tried it.)
+1
Regards
Pavel Stehule
Show quoted text
BTW, has anyone thought through whether this is a sane idea at all?
It seems to me to be full of cases that will require rather arbitrary
decisions, like whether ON DELETE CASCADE should involve deleting the
whole row or just one array element.regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Folks,
BTW, I don't want to block this patch. However, it occurs to me that a
more generalized FK based on non-equality conditions (i.e. expressions)
would be nice if it were possible. Then we could have FKs from all
kinds of complex structures.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
On Wed, Nov 16, 2011 at 11:28 PM, Noah Misch <noah@leadboat.com> wrote:
Removing values from the array seems best to me. There's no doubt about what
ON UPDATE CASCADE should do, and having ON DELETE CASCADE excise individual
array elements is consistent with that. It's less clear for SET NULL, but I'd
continue with a per-element treatment. I'd continue to forbid SET DEFAULT.However, Jeff Davis did expect ON DELETE CASCADE to remove entire rows:
http://archives.postgresql.org/message-id/1288119207.15279.24.camel@jdavis-ux.asterdata.local
So, perhaps the behavior needs to be user-selectable.
i will agree with Jeff on this...
i mean, on the normal case it will delete the row. no?
the docs says about the CASCADE action
"""
CASCADE
Delete any rows referencing the deleted row, or update the value of
the referencing column to the new value of the referenced column,
respectively.
"""
so, that is what i will expect
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Josh Berkus <josh@agliodbs.com> writes:
BTW, has anyone thought through whether this is a sane idea at all?
It seems to me to be full of cases that will require rather arbitrary
decisions, like whether ON DELETE CASCADE should involve deleting the
whole row or just one array element.
One array element, presumably.
Um, why? One reasonable interpretation of an array reference is that
the row depends on *all* of the referenced pkeys. Also, if you do
delete one array element at a time, what do you do when the array
becomes empty --- delete the row, or not, and in each case what's your
semantic justification for that choice?
In short, "presumably" doesn't cut it here.
regards, tom lane
Hi Noah,
thanks for your unvaluable review, rich of useful and thorough comments
and notes. Marco and myself will add your proposed tests as soon as
possible (most likely after the Italian PGDay which is this week).
However, given the feedback received from other developers too
(including Tom), I would first concentrate on defining the syntax and
how referential integrity actions should work.
Il 17/11/11 05:28, Noah Misch ha scritto:
Removing values from the array seems best to me. There's no doubt
about what ON UPDATE CASCADE should do, and having ON DELETE CASCADE
excise individual array elements is consistent with that. It's less
clear for SET NULL, but I'd continue with a per-element treatment. I'd
continue to forbid SET DEFAULT. However, Jeff Davis did expect ON
DELETE CASCADE to remove entire rows:
http://archives.postgresql.org/message-id/1288119207.15279.24.camel@jdavis-ux.asterdata.local
So, perhaps the behavior needs to be user-selectable.
I would agree with what Tom is saying here, given that SQL specs do not
say anything about this feature. We could leave standard REFERENCES
keyword handling the array value as it is now. If a user wants to take
advantage of in-array referential integrity, we could implement the
special keyword "ARRAY REFERENCES" as Tom proposes (or a similar keyword).
Consequently, we need to agree on what the actions on delete and update
operations are. In case of ARRAY REFERENCES, I would be inclined to
leave the same meaning of ROW scope actions to CASCADE and SET NULL
actions, while disallowing the SET DEFAULT action (as Noah suggests
too). At the same time, I would add two actions for ARRAY REFERENCES
which will be processing array elements:
* ARRAY CASCADE
* ARRAY SET NULL
(Of course if you are welcome to propose a better naming convention).
This table summarises the scope of the actions.
--------------- --------- ---------
| ON | ON |
Action | DELETE | UPDATE |
--------------- --------- ---------
CASCADE | Row | Element |
SET NULL | Row | Row |
ARRAY CASCADE | Element | Element |
ARRAY SET NULL | Element | Element |
SET DEFAULT | Error | Error |
NO ACTION | - | - |
RESTRICT | - | - |
--------------- --------- ---------
For instance, with an "ARRAY REFERENCES ... ON DELETE CASCADE", I would
expect that the whole row is deleted (as Jeff et al. say). However, if I
specify "ARRAY REFERENCES ... ON DELETE ARRAY CASCADE", I would expect
that elements in the referencing array are removed.
Similary the "ARRAY REFERENCES ... ON DELETE SET NULL" will set the row
to NULL, whereas "ARRAY REFERENCES ... ON DELETE ARRAY SET NULL" will
set individual elements in the referencing array to NULL.
In case of updates, SET NULL and ARRAY SET NULL works the same (updating
the whole row or the single elements). CASCADE and ARRAY CASCADE are
synonyms, as they would work in individual elements (which is the action
that makes more sense anyway).
I believe that, before we proceed with one implementation or another, it
is important we discuss this sort of things and agree on a possible
long-term path (so that we can organise intermediate deliverables).
Thanks,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
Hi Gabriele and Marco,
On Sun, Nov 20, 2011 at 10:36:15AM +0100, Gabriele Bartolini wrote:
--------------- --------- ---------
| ON | ON |
Action | DELETE | UPDATE |
--------------- --------- ---------
CASCADE | Row | Element |
SET NULL | Row | Row |
ARRAY CASCADE | Element | Element |
ARRAY SET NULL | Element | Element |
SET DEFAULT | Error | Error |
NO ACTION | - | - |
RESTRICT | - | - |
--------------- --------- ---------
thank you for this very clear and concise summary!
I agree with your appeal for a broad discussion on the proposed
syntax, and I will use the same language to express my proposal (for
clarity and to simplify the discussion):
------------------ --------- ---------------
| ON | ON |
Action | DELETE | UPDATE |
------------------ --------- ---------------
CASCADE | Element | Element |
SET NULL | Element | Element |
SET DEFAULT | Error | Error |
ARRAY CASCADE | Row | Element = Row |
ARRAY SET NULL | Row | Row |
ARRAY SET DEFAULT | Row | Row |
NO ACTION | - | - |
RESTRICT | - | - |
------------------ --------- ---------------
I have swapped your syntax in the following way which looks cleaner to
me: the ARRAY (CASCADE | SET NULL | SET DEFAULT) syntax denote
operations that happen on the whole array, and CASCADE | SET NULL |
SET DEFAULT denote instead operations that happen on the elements of
the array.
Associating the "Element" behaviour with the ON DELETE CASCADE syntax
is also consistent with the case where the referencing table A is
constructed as GROUP BY of another table B, and the array reference on
A is built by aggregating a non-array reference on B with ON DELETE
CASCADE syntax. In other words, the same syntax (ON DELETE CASCADE)
would denote the same behaviour in both the aggregated case ( = one
row per object, using array references) and the non-aggregated case
(multiple rows for one object, using equality references), which
represent two distinct implementations of the same abstraction.
The "Row" behaviour would instead be associated to a new syntax (ON
DELETE ARRAY CASCADE), which cannot be obtained via the existing
syntax in the non-aggregated implementation, on the grounds that it
might be useful for some semantics (for instance: if you remove a
vertex from a polygon, you can either destroy the polygon or transform
it into a polygon with less vertices).
The same principle of considering the two implementations as the same
abstraction would also confirm your choice to raise an exception on ON
(DELETE | UPDATE) SET DEFAULT.
It would also suggest to enable ON (DELETE | UPDATE) ARRAY SET
DEFAULT. The reasoning is that we can actually specify a default array
in the referencing column, but we can't specify a default element.
Before I briefly thought to use the referenced column default as a
default for the single element, but it seems a bad idea: a default is
an expression (possibly non-constant) which is evaluated only when a
new row is created in the referenced table, and using it outside of
that context looks inappropriate.
Regarding ON UPDATE ARRAY CASCADE, I agree to make it a synonym, since
updating the whole array to take into account the update on the
referenced field is equivalent to updating the single element to take
into account the same fact.
Finally, ON UPDATE ARRAY SET NULL would still have an use case as a
different behaviour than ON UPDATE SET NULL, which we make available
to the database designer: instead of replacing the updated element in
the array with a NULL, we replace the whole array with a NULL. This is
essentially the same difference that we have between ON DELETE ARRAY
CASCADE and ON DELETE CASCADE.
Thanks,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it
On Sun, Nov 20, 2011 at 10:36:15AM +0100, Gabriele Bartolini wrote:
I would agree with what Tom is saying here, given that SQL specs do not
say anything about this feature. We could leave standard REFERENCES
keyword handling the array value as it is now. If a user wants to take
advantage of in-array referential integrity, we could implement the
special keyword "ARRAY REFERENCES" as Tom proposes (or a similar
keyword).
No objection to that.
--------------- --------- ---------
| ON | ON |
Action | DELETE | UPDATE |
--------------- --------- ---------
CASCADE | Row | Element |
SET NULL | Row | Row |
ARRAY CASCADE | Element | Element |
ARRAY SET NULL | Element | Element |
SET DEFAULT | Error | Error |
NO ACTION | - | - |
RESTRICT | - | - |
--------------- --------- ---------
I like this.
CASCADE and ARRAY CASCADE are
synonyms, as they would work in individual elements (which is the action
that makes more sense anyway).
What about making ON UPDATE CASCADE an error? That way, we can say that ARRAY
<action> always applies to array elements, and plain <action> always applies to
entire rows.
SET DEFAULT should now be fine to allow. It's ARRAY SET DEFAULT, in your new
terminology, that wouldn't make sense.
Thanks,
nm
On Fri, Nov 4, 2011 at 7:48 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:
This patch adds basic support of arrays in foreign keys, by allowing to
define a referencing column as an array of elements having the same type as
the referenced column in the referenced table.
Every NOT NULL element in the referencing array is matched against the
referenced table.
I like the idea of being able to define more flexible foreign keys,
but are we gilding the lily here? The proposed solution is really
quite specific to the nuances of arrays. Perhaps there is a more
general expression based syntax that leaves the door open for other
types conditions such as dealing fields dependent on other fields?
merlin
On mån, 2011-11-21 at 10:30 -0600, Merlin Moncure wrote:
I like the idea of being able to define more flexible foreign keys,
but are we gilding the lily here? The proposed solution is really
quite specific to the nuances of arrays. Perhaps there is a more
general expression based syntax that leaves the door open for other
types conditions such as dealing fields dependent on other fields?
Yeah, basically you'd just need a contains and/or is-contained-by
operator between the two types.
Hi Noah,
thanks for your feedback.
Il 20/11/11 14:05, Noah Misch ha scritto:
What about making ON UPDATE CASCADE an error? That way, we can say that ARRAY
<action> always applies to array elements, and plain<action> always applies to
entire rows.SET DEFAULT should now be fine to allow. It's ARRAY SET DEFAULT, in your new
terminology, that wouldn't make sense.
I have tried to gather your ideas with Gianni's and come to a
compromise, which I hope you can both agree on.
The reason why I would be inclined to leave CASCADE act on rows (rather
than array elements as Gianni suggests) is for backward compatibility
(people that are already using referential integrity based on array
values). For the same reason, I am not sure whether we should raise an
error on update, but will leave this for later.
So, here is a summary:
--------------- --------- ---------
| ON | ON |
Action | DELETE | UPDATE |
--------------- --------- ---------
CASCADE | Row | Error |
SET NULL | Row | Row |
SET DEFAULT | Row | Row |
ARRAY CASCADE | Element | Element |
ARRAY SET NULL | Element | Element |
NO ACTION | - | - |
RESTRICT | - | - |
--------------- --------- ---------
If that's fine with you guys, Marco and I will refactor the development
based on these assumptions.
Thanks,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
On Sat, Dec 10, 2011 at 09:47:53AM +0100, Gabriele Bartolini wrote:
Il 20/11/11 14:05, Noah Misch ha scritto:
What about making ON UPDATE CASCADE an error? That way, we can say that ARRAY
<action> always applies to array elements, and plain<action> always applies to
entire rows.SET DEFAULT should now be fine to allow. It's ARRAY SET DEFAULT, in your new
terminology, that wouldn't make sense.I have tried to gather your ideas with Gianni's and come to a
compromise, which I hope you can both agree on.The reason why I would be inclined to leave CASCADE act on rows (rather
than array elements as Gianni suggests) is for backward compatibility
(people that are already using referential integrity based on array
values). For the same reason, I am not sure whether we should raise an
error on update, but will leave this for later.
Your conclusion is reasonable, but I don't understand this argument for it. The
patch does not change the meaning of any SQL that works today.
So, here is a summary:
--------------- --------- ---------
| ON | ON |
Action | DELETE | UPDATE |
--------------- --------- ---------
CASCADE | Row | Error |
SET NULL | Row | Row |
SET DEFAULT | Row | Row |
ARRAY CASCADE | Element | Element |
ARRAY SET NULL | Element | Element |
NO ACTION | - | - |
RESTRICT | - | - |
--------------- --------- ---------If that's fine with you guys, Marco and I will refactor the development
based on these assumptions.
Looks fine.
On Thu, Nov 17, 2011 at 12:08:32AM -0500, Tom Lane wrote:
The least we could do is invent some non-spec syntax that makes the
intention clear, rather than having the system assume that an error case
was intended to mean something else. Maybepids INTEGER[] ARRAY REFERENCES pt,
+1. Perhaps this for the table_constraint syntax:
FOREIGN KEY (ARRAY foo, bar, ARRAY pids) REFERENCES pt
Hello,
Il giorno dom, 11/12/2011 alle 19.45 -0500, Noah Misch ha scritto:
On Sat, Dec 10, 2011 at 09:47:53AM +0100, Gabriele Bartolini wrote:
So, here is a summary:
--------------- --------- ---------
| ON | ON |
Action | DELETE | UPDATE |
--------------- --------- ---------
CASCADE | Row | Error |
SET NULL | Row | Row |
SET DEFAULT | Row | Row |
ARRAY CASCADE | Element | Element |
ARRAY SET NULL | Element | Element |
NO ACTION | - | - |
RESTRICT | - | - |
--------------- --------- ---------If that's fine with you guys, Marco and I will refactor the development
based on these assumptions.Looks fine.
This is our latest version of the patch. Gabriele, Gianni and I have
discussed a lot and decided to send an initial patch which uses EACH
REFERENCES instead of ARRAY REFERENCES. The reason behind this is that
ARRAY REFERENCES generates an ambiguous grammar, and we all agreed that
EACH REFERENCES makes sense (and the same time does not introduce any
new keyword). This is however open for discussion.
The patch now includes the following clauses on the delete/update
actions - as per previous emails:
--------------- --------- ---------
| ON | ON |
Action | DELETE | UPDATE |
--------------- --------- ---------
CASCADE | Row | Error |
SET NULL | Row | Row |
SET DEFAULT | Row | Row |
ARRAY CASCADE | Element | Element |
ARRAY SET NULL | Element | Element |
NO ACTION | - | - |
RESTRICT | - | - |
--------------- --------- ---------
We will resubmit the patch for the 2012-01 commit fest.
Thank you,
Marco
--
Marco Nenciarini - System manager @ Devise.IT
marco.nenciarini@devise.it | http://www.devise.it
Attachments:
foreign-key-array-v2.patch.gzapplication/x-gzip; name=foreign-key-array-v2.patch.gzDownload
���O foreign-key-array-v2.patch �=is�8���_���n���uXW�xK#�3z������T6��H��3E2$�;��n$xIr�L2S��:&�h }7����|N�������k>��jiz���UP�'2[������#�����h��To�Z�T+������O����h=������i7�j�S#� q��CX���#����VH}�>����q\���:Z��}��e��p�3���M�=z���`����1pN�4$�����T�1tV3�j�����d��:�{�(z�����w:Z�Ri����*6u~P�kS�V��4��R�_�4���NR��q�q������XP�����-'��m�Z���OuS����#���<#���J�Gl
��`����\W<'g��pnQ�<���V�H�=P_D����[��\���?�����O�+�\�[����X�<��!���`<�*T�X�|g�)� OW����#<!-���&�4�\k+����z��2����Y���4���g�����D�����d@&�����M�OA��)�!Z�C������: �G��� $l�eIO�u�'^�?rB�:�������+��?Prc�����dP���>�S�:��\����Cn��@P�� BKm�����3
�%%(C������IG�w�G����a�)���,G�����b�d����TW>���\;��#���������XY �n�=��JM�5�a��s���\9��/=�>�j��|�������������@�-�\��������Cz�x5���o���9���~eC�\�������1<����<���?[�43
S����&o���,,�o���/��X�qR�/c�NK���$�p���M�����@%� �����!w�id���n� z@�P$� f-��k������@�u��@����%����|!T h� �(@^��'z���X# %�a
{e��A0�o�)HMzp�f�������o-u����e�w2@z� P0�&�$��E���w5O�FMk7��n��N���'���!�}A�|��5�T����b���d ���|�=P~F�T������(�r,��<��7�����Q�R>��`BF���b�29�uL\f$�oP�+Pl�� �.XD�����`�g��O6�~u�SA� {�|r)��~������1�M:W������B?!���
l���3������\�T�ShFx�[6m`�RD3N�S�D
<j�ZB�HA@��>Qf�v����Q��I�#��}p���/v�����UHv��:�,)�8}�A4��I�3�`�1d+��4���d=��9�� �9qZ�t N�'�+���&
q�Z�=$o������B����]�0 �����a��_�����+��
�0��B���FJ��A�P��Lh0����P_%�����bk�byBF9c����l����M�j!���B�l�G`���h��s��L��H����*��;��|��)@�B� 5Zi���r�lv�m�&���$�����A�s�1<-}���'%#�'{L��N@��hi�����D��|4
��B��(tR��\<���
���Y����x�n� O��Bw�{����������V�\oWT���Uh<��6DN���� Q����G�U!E�����q�b���G�D�B���={F�yc*iO�������T!g�tP���@]g�/>p�h�����@F�53�6����~[�j�_��Fj{����GU�^�#�[I05�0A�-�*��K��}��-"H2\��m��u�8����gC+��A ��Y@v���'�d�@�_[J�;��ty�����Do�V� gf����8B*��[�q<lW��SI$����u �*@g��r�Y����7�%��u[�}!uP��xD��,�14�m{��d )���*h���o2��
9�`_��t���c�0������� ��bf�{p\]����r`�<���,/�������+�j����f�ygM���&'�,�|x��|v�S��|"g}����L��.��-��4��=�������l��
�
'S�K�V��O��������D'b@���}���!�3�L��K��� �����c���)�mMSh���=y�����~K�{H�NQm>��;������+H�� �~K�*o�h���r+*1�;�Ad[_���#�� Ro�r�Odt1Qp��h�9{#^N .n��N�8 \h.o�U������!v�����<6k���y���5�/l�%�W
}?��������j����'H�������d�����kf$"UJo��L�p>������M\���M��
D�,��(o����5�����e�w��hn^N/���������T�4b#t���5Y�)���<�������L�w+��@_#hjW�t5�
lH�t>�>�D���=�,����s�a�&��Qh�=7�P,��X��R>����(�N%q�W��u�y���Ira�9P��'�`���O1��r5BsO��x��c����a3n�!�����N#�+�Se�J��b��g��>@�/\�[�#VH)����
:��_Y>-�* Y �_f��w.��#<V~!I���������?:.��2)fK�)#vm���$�+�3H���|�U�:n�p�H:R;�y9���^����Z�P+Q23u��e! ��*�t,1�p��m�zV��cP�j�T-
�CA@"Q1ZN�nY�����i��;��\K��1t^����y�����
)Q��Q����X2�UH5L������lp���7�g��G��� �X����`"Q����0� �e1�m}P��j��>M��zn0��S�#�|�sElY�a�p�TO����"�G���/F���z�/�2�9������M�UU�7Lb�T���� =�33�th�hkJ�����?�s ����p��Z��j��
�:�mb�����J%0�AR��[��DI"��Vd+(�����=TW����������h����7�&z�T��f����}���H��B��E`XC�M3zc�-Xe�Q�0%d�s��uCf�u��������o%^�����l\)�iq���{U�V�����)�B����q��$�Oc��l�J?����l(� �3H���Mo��P1�/�1�Cr���S<��M%^�L����RH�.N��)�b�R��������R'��w�j��PK)$Ft�a��J�y1<���2��X��������0X!�>2}x ��'nB�7�����t��p�D��m.Ul6�Q��Z|�w����q������]������><].�s�$��X�}��,q�r"���b����^� �5/E��i�+U����J�^����6k���5 ���x9�vo;��x���S[#�B��S�@�q���@@;�C��7o�dv��2t��X��ej;OyJ��B[%�%O���09p
�]3��Y��%�O�Y>���]@�
(M��7����������?���a�\��`m��f������i������S^jse8o� i�K��M�$o�29��U� �$��C�FpN4_i�,^5�S���SemI&�o������i`,�R/��B>�����F��.����9�T�a|.�ur����K�6��^}���P�K!�H@KuR��.�"y�,�������x��)��s�@�~���F����L��<I.E?�o_�M8�'���`p$�[�2�(9|�b��Mu|����/��
��t�e�h�� �N�'/�:G�.K��*�����W����;�|y��]2�M�F�6�|��l��t|qNJ�;�<�������p4���^G
��
v(��������� ���b������M��a���]��=���H~��`'�����
����O���A@ca�����|�S��*�w:�m"�4�b���n��#�&�%�E����L��5������D�Gx�N 4#O=o����r����w���^��1�
9/���SE�S�^�m� �� �W����{� �!��R�A�-�[q��6O��=g��&8�Z���M��;���`�?��R�%8�����fD��]����NX�%P9�>l�+����9]�[v�y
��yHT������9i8��v�����@���=n_�f��h�B�{��*��4��6��U6��z��,�iM7��U�C���y`)�d��
�P�l����5�G���]�,�tP�
���)/v��"����:B<+��%k���mI��� ���i��Mz�)��/�"�z6H 5'8���v��nk�fG�u�vGk�j�<�D}�pMZ������t8��w6<��B�����=ik /���.�?�4O�T���~v�f�&(��O���W�8���;5����Cm�$����V��Xq������by����<e�����<�\��'-���j=Y�LD��i9�
��rL�{����=���ci��$.������q���lGS�A\�$?� >�"���<\�18}���9CoHq�����1�0l�������s�ud��R�3��6+}��L�3(��@7�M�}Y�Y��e����������� f�����;X.�D����Fb.���Zya��v�Y��\9"�n
��g��w��KaV�5��^��o�wG�4��z�x�!��a~�]Bzu��e�C���s}i�qd���X�:/�����:~L�;Y��_��X
�'�/Y���H/VK*1�P,��m�u��wWWv)6Nq�2� ���0g���Oz���o��E(oN�����L�QI>'V,�j���� �%f
��pC���������
*G���7a�c��fr���G��)�X�e��}���8C���8g����$J�\���\.%��G@��X��q%[;��l���0.P>h!8.�G%�(g�z��������O|�$+Z�������E_}��@�@�����@�x�}�t}oa��V�Fo���������� \r��|C��`)[����e��d_+�a���O9�d"���`���/*$�B7�����WL���$s�88�O��z�����*A\@�Eg_rz����s��e��H}���o���2���wG��P�Q���B�R�*����Gy�YZ2W��P3��a�y0,Q���(D��T�O�)�k$�IU���`�G��W<���D�pl9"e�<��e�;��9��R���yr����1�=�19��� �0k��s] ��i��������#���������?N�-�����x��;�h�}A$)�U�$qD|OBT2Q�����W�����]#��`I^L����{���w�n,��q��Q6+j�*��d���K�?%X�}Io�$J%b��C������:!�mJX�1u�E�����d��%;��5F��,"�L��>���������#���en:��u�3�Y�*Y/�>���qv����=��{0��@o������X�������n/��%$UBn��r����k�`����������E����%w�b~o�#<P���%�O�<OPG���$5�v���
��0��aAX��gA���3�>z�,�~�A�*���z��ii�N���f���O\�m-���(�����3E���#��xsrd��� 2���=����19'��%H���5�
<n�/�+�����l6��OQ����D�<|��X��Q�m�����\�!]�B�J����&6��1���=�b��f��;�e�5;��9o�1����f4�P��3`[���Z�3e �h b�y�fy����h�w!�J�h���SK������aYp?El�Yk6kJ=f�U�L^~]EK�WWD����"�����"���_E�� �5" �D*��h�\nU���L��B$B��>$J[^�$lF)~�����[c�rl
���vx���P�D��k*��`3�5�A�wr.m��tE����\��CA��{�2�x�n�fUk�%_�fMk�������@��P \�TN;�DO�
������W'������������� B���� �G!T�dt!�3�a&`�d� �$2�Z��6�����E�\��1 �iQ�"Ph�����;mc[�g�W����D�H���~�q�U���N{z{�X2�E�����~��3#��
����s�cK3��=�5{����������-9�T�Y�[��D�qH���N�(��{�pIB���|����e@2������[O�W��#�g:�hs��_J�Y��� ������q���4T����\I5�=��!{�l�].��3�a���Y�W��Z���������)T��Y����+�a�VP��Z��?i�8��k�'A��������9*Y��?�I�����I��G
��7 *i�V��d:� ���tp���7��U4K%��Z��
���`�������, aO��S(�����:��*��G
��Fn��(j�}���;G��F����m<y��#Ev�md�1 !����Y�P����=���k���r%�6���m��@�j[�jkpQ*5���e;i��!�6\i�qz��������n���\��=2�-{��v�w=�Yp����>����!M"0 ��:H0].�P�{-B�/���R�e[f���� � Q�/5 l>������N���n�k.*���W��Y�JQ���O��=<�����l}x#�����o�h����(t�C�nyH��
O!�g�����X}�t������v�\i�kM�e�������������J��'�I���}�q@��-��-ZN�`�3���$Bg\2<���\����'gg�Q���e|��~s��#{��q��]�}}��|����[�)�r5�}�0Z�@�� N��x}�q,���p�;��>]�����a�����s�S�U;�_;j���S������4���3s������2O%���W�9�����2������������^��K�����������}E��cM��g�Y���\m��V�rp �^�&D��Ge+)��a�����a8�Y��^��'�c�#���D<��c
q�% ����G����,������������O��5�+���(x/ Np�~�����
@r������G�# �X����������Kz���4j����P��4�����PK� Z2&h��$�B�%��N�&(6��YO��V*2�+4NIE|0���0t@ ���Rw�}i^~��w:�-_�4�mP��8�- e��@�q�����������F���R���|����k���w�; L�>k���z],�L��2��,Ce*�h�r���e�
<�cO#�D�7��t5�4 �����An{^��Y�� ���5����CINy-�k-��U�eC��n��}�����T����W�ID��Nc� s������� ������Y2=�;+�����6������E�?��M��3�,*�`�X�� Hl(R_.Fcg�,v ���a���f^��*c�}����%��&��G4������w�����w�������f4�zM����2!��Y�����~��?9��}a�� L���,��bm�'Z��Q�D�}���T��D��,���{8dik1�aHr\�A�g��2�zRyz|��q��+�������21��Ja� ��j$*(W�*C�O� �?����R
������9��X�wo�=�S�8�
9�X�4���������3tGp�����)dq 3�O��<�M0�h!}��<��+&����s�9��bR��p��o������
��
<�����#;���r�=��r`�Xi�����5����G ����B������q�lp�����hOp��7zy�����u�ni ��}L_'�Y�9Cc��?�����K���������^,�M�nK�`��=�g��P.��jx����A���#VJ�O��3l^�
��@�Y�� ��'x"��n�T�6dU �����D���B��'w���,�w��Vz#�aM�X�Q����a��
rl���<H ���xOq��0����"E�
8�!E���
g
���;G��E���6��o������f�k�C�F�SY1���B������P���X<�#�+b������Q�3�0?e��n���[O����35���CM��S63{�>w��H}�WG�K���HE,�Aj���Lv(y~n �{>�-���S��|$h�GF��B��L���+@RI\dl[-�3V������dQ5�(�<�jd���P?�*j��c���g�3���^��,i�?��^���~� AP�x�)��m�4����
j�����4��=8�uX`!Za�N�2�����c������!.��A�����0�w���w�]I.rK� �>E-Q���^���S!� ���L��`h����sQ[�IFT�>E�|��
��'� a)����';����pE�Ba�l�c��%�Qu��5��@���1�XD�l,��q���k��+�+�u.�(�����c:P 2�cL���;��t��� 4�H�V'��bm���t���#%;Q���>��(��h:�i��L���� >G���y�wz����-B��r_��m�*�����:�b��*����K � ��X>����5r�a���I���`,e �@��Ur���� J������HYq�B�I+<�k|yE�JL�s��jH�9��gv|�~D_P���
��%���A� S ���� �^.>`>����tX�Bn]`y���RSo2ab�y'z&z��p'����D�J{�dB��{�KZ%�1X��P��/Yx�8��5!���-��&Ft������H�����Tn�i��.���Uta�����~a����8���AavDT��V�I���*O8a+5� �/�$D}�>c�f�O�H�.��9�9�</�bj9���:�piN����Ld+�?� (f������9?����������v��3|��y3q�~a�^zd�����Az��P�
g�w�Vd��1��'&��#:��@�� T
6|��_�V�W�vO^w�o{G�o�{���u:����,�����&���������%_FW ��Tln�����Asx�Z��Z_�i�
��e��O_�X���,��9��1<��\������B���_B�HMQ��b��>�d#����K&.I��D:�3�,��U}!��IAI��38pQz�k������a�g�Eg�����a����K�g��#�|������5�h3q�J��s({_P�u|�M�;��\~Y�lB�R(K��:��_������X7X���=��);G�� �\Y�o��
J ~���t��]��)��.�XA� G3p�&DlE�����UO���p���grJ2J)z��ta?u(�#@?P9��7�f��-��#J���hP�$�vo:c�Y;cG�Xh��������V�k���������� �g�l����U��ZB��������D5���D���V���%h��-��d#t�\�C�3�wmk
1���Z�&�� �L�[����(a�����(n-�[�b����7hC��l�[�wn �+��������mcR3[����|8�v�P������L�����
G��0��p����a\�6��4a\�0xc�`�Ypk�Kg�#9�5�e�����{4�>d�n��u>���<P�V"8�b�U�T�5��i�rAU���AU[��P����4=S�������
���u{�G�c%oFy{�t;�fHK)����J��Z1
qD��Q��[�I��F|cud3�q�|�J|cu�*�u���������s8���vzG�cM+��x,����O+��]���)d�'f0�%2 Kc�����4�n�\�
C��[�^����]�gK�R*Q��P$�
D��
,��=�T��G�N5y�������Z��O��T��H����*U���R��G*��:��{]y��������nPGHgfg$
��%�i���'xx����I
��`���n@6���:�[Q~�������~_H����+����rW��}�nh�o��6b
?>Se��!��xXm����% t�N ����NVd��m��� .����j�� �JT���Q�%��(L�pa��m7�#u
l�6�zU�5 (E*T2�y~���f����Z�."fCy gaN��t�������@�
�k�3 ��s�`E`O��~�;��<���s?� 5@���������s�f>��4���jS�vJ �u���2fl~�^5���F�n�-"��%��3[�rA���k�
|1(v�EmO�(��_zuP��%���8����nF��#����f6p-�nz�*���<����p`�=���t������*�,�����83 �bX�Ys���C�����H{�"��_�H@`��r�Y!f�l�b���9'#���1d}�De�������)��B���i�8B,`x�v����h'��E���v:�gXl�����$�c��0���9�)��8����-]��)�0'ry\��a����R��D��ls�]Z��`=n�����C@a�����c7�#F�l4�0��H�/�},gu�������/pz���w��EF�����LRx��k�(�=<sWS����^s�+��0���Sz�gRgL��qVK��Q��c�3�:C�3���Wb�r5��l�`�;�a�6�~Ak$�`h&m�N�^��?�r +0 ���-��J�I����nx/�zC����]�A�{&np���<^��)�cM������� ���0Z[s�4��R��zZ�=�t���Ah�����w��_���I�z�����R]7=D��Wl��~����l�;S�� 5[z�%��q5r7n�[uQZ�d����T!B�k�
�)R�������I����X�~ �xx��nVz�VUR����������+O���l�- �V��-��Q�`.�je�
�1�`5C��$k0�\f��e����G�h�`T*
��T+I�`�9�P��2�28h��D�Qi���9�.Ty��[zf��+�-�8E
�XjF����:.���r
bXE�U��G�������F������Y:4���z":<x��2j��2��J���qbD*b�&f�Q��������k�Af�a�t��$*������{h-�q�i��~$�=:�0+��nV�-�����Q� �u#��r��Z5�HT<�����1�Y��p���{���j�aVt�!)1z����h�n<��#��`,tA�0�!�������y�H���p
4 ?
�*
9�}��{�� ?�����POZ&&��T3�s���\��F�,�3Z��t~��]�����8
����Z-3�^_k�n�m�A�d���eF�$>��g�n\=C�����;9�t��'��c��G.;]`d)X��ya����;|������"��\��2`YS�r�BIHY�c����em��#�N�r��k�p|s��W���W�fKa2�)�fA�2�F��72J��F�YV��}�{�X�C�� �;y�kM�f����������r��V�{��_�6.�/�ln���
lS����|������B���-,
����,t2^�����T�����N��)9�W�jS���E#P�I�
sOs]� ~;����xl��J�8��'srxr�!�+�[�[��O���|��*{��������pK�7�>��������3�w��#�\$|�"~��!�F�r��W�r��Q+�U���I>%��\�$q�J��U�K�&�5N*����q�j�)*#�(�X<��X!ZU@���
C��LW�&�d�@`L ����.�u����G���z����G^�v�zUco�Gn�=h��^�A%7��u)sXOk!�����t"�(��}��S)��,:�?eQ�:iQ�/&��_x�:����E��&�Mb/����Q���g��8����b��� ��� c�����u��3G)�#%�o�s]| � U�U���C�}]��C��-FW�;� /g��T�����8B�S���o����@$�q|��T\�H���&6������{VV�� $���C#?�8��x'S^)���|��^����@#�Tr�%�4)c'.��/;�
~�}�����fpZp�p-�A
�Eq ��p0�ibL�z���L�����~�|��A�d�#��[u���=w�3"�t9��.��(��(�;(X�:#I��^j ���3�2[|��s����FM8y����tQ��#�d�e���i����9L�����F�����%4��Qr�7�i�,z�^C�9�/qW�E�*j��s�}�m�|����%��v����������Z�i�T*I!k\t��yWV��
�{w|v��;<8�(BOm�_�3�{����,�hZ!�� ��?|l<���9iS�h$���w��>��<mv{�����S����2X��^�����������VL]���1���,��3i;�+��P��9�@O0b��2�I]]���Ie����e���)�r�V�D ����C��JdnJ�G�G1��7��kiC�w������G��
kO?��_����D ^B��*��_�x��'�� ��;{�n������z��Rmh��� /-L��B2 g&<���x���)����Z3���ui8���qL�s�
Z����H���B�HJ�0���zM�k���E4�!�����$�GH���k���{�$����G6m����zm�1������j�%���m��),H�!�h�e(�@4�q�B����m?S�*2���D������FQ�C��g����X����
��5�l��min�v2�q\���� ���ED[|�:"l����b^��t2�U�\�D�;����l��b������C/�p(BY�X�}y;�<�E���y7ay��{"B����y���)/���)�
St��\�P�C����@1Dx�"�DX����b /���� ����;�����R�{Q��"��x��@4!i����)���I���W���`o����!�:�}������\�=�Wq��������P�*���l�Dr��y���$pO��{���"Y������d���u������� �x�e�,X�q2/�d'.�Y&�:��h��l}s�^�*%���"t���U���!Z;���q���-i$L�;I�v���z��A�R���D*)�G���V���K��I[��jls����=?l�j�9��i��#W�Sf�<�`Mp�%O�{g�NOO��r�-7��:7�����8��1+����I���=� E�G4r��{�M�}C9��E���O�j������n��c��$��W�#I��^��OX�6h����%�l��.�;'o�b�������P�������
<v�d�����%������)�������&�~ VL���0���d�
9r9b���g���/�:|��6<�d��-��:Q����P(q�
9Em��xkM��5@m��E��&�I�YqBY;h��E��,���'Q�bQ�1B���1���(�[W67��V���n����?�Q���a���jk���&�f�6)(+��3XlC$R��7p��x�����K>xo< �[o�;�:~v��cC��xaFD�������R��Z�� l����1#f5,CU{�y��C��|f��[��`E���m�\���o�������}#V��{��&0�������
�����
��F���s�e��u[Ny�3���tx
�A�8i�lf[O����������z��f
m�ny�������-j[����!��y`��k�|M.��01��l: u������Iq���p)w�
�K�t���h��.B���������$���,}��cW��2n�� �R�6�(hf�����������@d�3��Z��2Kc*����x-�b�I����|���h����|d,a.��r}�xJ�v��)��WWS��;&���B��B ��@�t� C����.w�>��F���r���e����X��o( x�8�H,�A��F��V��0���
�
`��~��������;Q���������?��F��x����-"�~e����M��������h6u��Z:���0.�V* ������{0�O������^M���]X�3Yq��\�9��$�1�7D+�EM���~ 6 q��z�xq����~�~���X�c���(�����7�u���Fq�!�4J�v�Z�h��P�����~��e��wpV���<����|1�4�+�����d�4����h8�����u�������r1;��`���;����.��<�������j�V*
.f��iF�\�V)�h�/Q6�4_�g>�]�0��g����9%@/h"�Qi��QuK��������=&�{�&�,� ���I�u�'�/�� =[���-�H�q��E��n� t������B^.U�]r��Z�{8j!�Y)p�X�����+?��]�� W����eTv����w�@W��<�<���]7�)��7D�f6P��e{�2��<U����8������t��+V�X;#��B�������������M���&*�V��;f�L��W�*O�5[�:8��a�iT`�M);�U�G��&��hj�����.
�P�C��.+Zq��o��f�Z*��iZfP6'��I���~��k���N^�v�Y/�YR�FSoV<�?�Jd
r���E����5����o��0&k���f]rf��S��f��j��p��tcpq���0E]XoQ+ ��>�'*8o�b�?|���8��n���AY��@WQJ �+�I���v�w�h�����i(�� �SvT{"��0�T�Tk
5m�\����&fTF���]�[�4�����+g[;����~�h��BS��=Y^�mz��-�T
}`�����K�[��%c�j���^�������/?Id�Ll ��Z�[2~ �)K��h�I��9�Bc3��K�9���z���}�QMn;���.W�Z���HM��M�s J5���[6[���_�4m%7���f�M����[f���i��]�&H{�{�S0(�����9���?���O����'
�K\���G=)���d����"\�IA�$��K=�D��&�� �aj�p�'���Q������
����W�zji��/��/{>��}{�o/�=w��d1�.��ZE&��4��Nj�T�V9SiL���Of��_'^�I��p�{����]��&�5�����HQ�����w��z.�T��wyL��H)�}���l>���9�{~���j��a�Jf�V���T�
>B�9���o�~�l�[�[��s����H�q9!�A��Y����tXf\� (���h��s���}8���JM+h�b�'�����g����1�(��l*�����f����*y�G�������
�WW������/��b�����j���|��[�//ls��U�j�[�)����8SZ�L�B���{*��Z�km��/���pJ�+��?�O��eF��%���5S.�\�
+�/�Z*k������P\m���V��rNL5i���CT�L����-�FN����'��&i�IN���& ��Y��I��������3^��R��a���QZ��n�����<j����Q��);�S4J��?�������G�����+r��q|�1�V<7��KT�~�na��siH-~��i2���"V`���nd�
�L�1+��V(%������n����6z���%��&��g7�Du���N���_��_ W���Q��F�
�G�i�X�6��7��}[���H��y5�y]���.c���O����Y�F�k@ 6�Z��o���Y*���a��Zx�A5<�Q����#��X��6��^R�W�
���n���$K�����qu����wb��Kh6Ih�oer;������
Wng/�������{`=�.<AtCq��>0��3@��D�@��/4�T�a\��V��0&��g�v��{�� ~T�.�&�T)W
�[�i�1�����5\X�-���nY���n����q���g��Z�����|X����f�6s���>��.�}K�$���q4�����N�i�3Dd��l���8B$sO\J���w���>([�z�4h
�"��N�$[�}�Y���<�gy�B4�%�y���EC7
����Y�����qH^���{�G�=�*{���.''u��db;���O���2Z��3�����ODy��8�d���:Y&g.���'��_������1��������~'�qq�|r��b^NbL9
(H d_z�9V���rQ*�k�f��1���D�I,23^�H�|���[M�FGZf(2���R���[2:�W�����J�P���j������_��<��j_�M���keX&|.����il-��BS��� u/`v�pF�g��mav/s�f�{%��\�o���QTv�T���z5D���c���9���\���|e� �'��=D{f�5�M�0[������K=�O������r�}��f�j<�hk��������c��m:�s��0��?�G������}}���<M~.S��\�*����~�
5RE�L"I�#�`�EC�Y^la����fD�JE�����(k�����{g�[C����b�_�0<<I���r1R�
�v-�e�+����$1�o�#��h��E�h+.���,�]��`�����#J�m�o��/����v��{�~u��N�N��YA��|
��O��o����_:�����a�&��8��u�k���r/N���k�7�Gx7����Xx�����1P�Y�->4���23�F��Rt{�[4=<>�t������*�0��s�]�9������t1y3}�
�RI����T�w��.5� ��NY���A�������������2 h���y������1P� �9��t;���
l�`��oIdY����)jq���n�L����;_wt�� ��qq��sG�)#����=G�� �&"��D�����h�F�14q�g��z�h�s�><�`�81��\n��1�n��_�G��R����s
NWpg������M$
���-���|��y��Y�� ��1W%�q���j4_Y�L�P]���V�P��U��X�CU�a��J]����������7���,\��P7s�nn��}'
�:�t��I?+_i�Dd� d�_�X5����� x��e�tK�TU����S4���We�i��-�����ne�u�
��[5��-.\�z�oet�b�����0���MjZyMX\
FW��{���o����6Td
$(�K��i��lv��aB���%-�����l��@,���%<:�u��1�Z��m��� =I�G�>4����l���D��*��TV�Z��aE}Xk���(���M��*?l�����Q��_����~�������WT0ny�`�1�r�"�����KE�@�LPjj�%%��'�a�6���>���,����4d��3w��D���8J�
�L�A��,�B#E��"�S2����5Fr�`J��7��,r"=
��`q$�������q��EeI�m1�60����"l�'��3��F�B��?��M�_V_dq'���T
�d��*�-W����*�.�7�+jA�Lv���!3�|&���i�����b�sv�=�;���l"��$;du�Jb��O3[�4�]��a�;d�����9f3H����09��K�� f�����E�O1�8�Az��d�@)�^vI:|��"��9f�����u�� ��M4{ ��L��o��#p�3I�!s�4f��Cg�-`*��.eGM3[�5���x�D�������f�������"�?�;���������g���6K(������wzG }�zh*rnp?0g9�Z9���R���#pA#���7�2�.�:�.-�[�D����;�o�yb��&^���'A?<