Horton

Re: [PATCH] Support for foreign keys with arrays

Noah Misch <noah@leadboat.com>

View thread
From: Noah Misch <noah@leadboat.com>
To: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>, Marco Nenciarini <marco.nenciarini@2ndquadrant.it>
Date: 2011-11-20T13:05:26Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow LEAKPROOF functions for better performance of security views.

  2. Improve labeling of pg_test_fsync open_sync test output.

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