Re: [PATCH] Support for foreign keys with arrays

From: Marco Nenciarini <marco(dot)nenciarini(at)2ndQuadrant(dot)it>
To: Gabriele Bartolini <gabriele(dot)bartolini(at)2ndQuadrant(dot)it>
Cc: Noah Misch <noah(at)leadboat(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCH] Support for foreign keys with arrays
Date: 2012-02-06 18:04:42
Message-ID: 1328551482.3354.64.camel@greygoo.devise-it.lan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi guys,

Please find attached version 3 of our patch. We thoroughly followed your
suggestions and were able to implement "EACH foreign key constraints"
with multi-column syntax as well.

"EACH foreign key constraints" represent PostgreSQL implementation of
what are also known as Foreign Key Arrays.

Some limitations occur in this release, but as previously agreed these
can be refined and defined in future release implementations.

This patch adds:

* support for EACH REFERENCES column constraint on array types
- e.g. c1 INT[] EACH REFERENCES t1
* support for EACH foreign key table constraints
- e.g. FOREIGN KEY (EACH c1) REFERENCES t1
* support for EACH foreign keys in multi-column foreign key table
constraints
- e.g. FOREIGN KEY (c1, EACH c2) REFERENCES t1 (u1, u2)
* support for two new referential actions on update/delete operations
for single-column only EACH foreign keys:
** EACH CASCADE (deletes or updates elements inside the referencing
array)
** EACH SET NULL (sets to NULL referencing element inside the foreign
array)
* support for array_replace and array_remove functions as required by
the above actions

As previously said, we preferred to keep this patch simple for 9.2 and
forbid EACH CASCADE and EACH SET NULL on multi-column foreign keys.
After all, majority of use cases is represented by EACH foreign key
column constraints (single-column foreign key arrays), and more
complicated use cases can be discussed for 9.3 - should this patch make
it. :)
We can use multi-dimensional arrays as well as referencing columns. In
that case though, ON DELETE EACH CASCADE will behave like ON DELETE EACH
SET NULL. This is a safe way of implementing the action.
We have some ideas on how to implement this, but we feel it is better to
limit the behaviour for now.

As far as documentation is concerned, we:
* added actions and constraint info in the catalog
* added an entire section on "EACH foreign key constraints" in the data
definition language chapter (we've simplified the second example,
greatly following Noah's advice - let us know if this is ok with you)
* added array_remove (currently limited to single-dimensional arrays)
and array_replace in the array functions chapter
* modified REFERENCES/FOREIGN KEY section in the CREATE TABLE command's
documentation and added a special section on the EACH REFERENCES clause
(using square braces as suggested)

Here follows a short list of notes for Noah:

* You proposed these changes: ARRAY CASCADE -> EACH CASCADE and ARRAY
SET NULL -> EACH SET NULL. We stack with EACH CASCADE and decided to
prepend the "EACH" keyword to standard's CASCADE and SET NULL. Grammar
is simpler and the emphasis is on the EACH keyword.
* Multi-dimensional arrays: ON DELETE EACH CASCADE -> ON DELETE EACH SET
NULL. We cannot determine the array's number of dimensions at definition
time as it depends on the actual values. As anticipated above, we have
some ideas on multi-dimensional element removal, but not for this patch
for the aforementioned reasons.
* Support of EACH CASCADE/SET NULL in ConvertTriggerToFK(): we decided
to leave it.

Regards,
Marco

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

Attachment Content-Type Size
EACH-foreign-key-constraints-aka-foreign-key-arrays.v3.patch.bz2 application/x-bzip 27.6 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2012-02-06 18:05:01 Re: 16-bit page checksums for 9.2
Previous Message Merlin Moncure 2012-02-06 18:03:36 Re: SKIP LOCKED DATA