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-12-12T00:45:06Z
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 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.