Re: [PATCH] Support for foreign keys with arrays - Mailing list pgsql-hackers

Hello Tom,<br /><br /><div class="ace-line" id="magicdomid393"><span class="">> I started to look at this patch a
bit. I'm quite confused by the fact</span></div><div class="ace-line" id="magicdomid394"><span class="">> that some,
butnot all, of the possible FK action types now come in an</span></div><div class="ace-line" id="magicdomid395"><span
class="">>EACH variant.  This makes no sense at all to me.  ISTM that EACH is a</span></div><div class="ace-line"
id="magicdomid396"><spanclass="">> property of the FK constraint as a whole, that is that it says
the</span></div><divclass="ace-line" id="magicdomid397"><span class="">> constraint is from array elements on the
referencingside to column</span></div><div class="ace-line" id="magicdomid398"><span class="">> values on the
referencedside, rather than the normal case of column</span></div><div class="ace-line" id="magicdomid399"><span
class="">>values to column values.</span></div><div class="ace-line" id="magicdomid400"><br /></div><div
class="ace-line"id="magicdomid401"><span class="">The specification that Gianni posted applies only to v5 of the
patch.<br/> The original idea was indeed to have the whole foreign key to be defined with an EACH property (initially
wewere actually thinking of the ARRAY keyword following your advice, then for grammar reasons we opted for
EACH).</span></div><divclass="ace-line" id="magicdomid402"><span class="">However, during the actual development we
facedsome difficulties with multi-column foreign keys.<br /> Through discussions on this list and with the reviewer we
optedto allow the EACH keyword at column level.</span></div><div class="ace-line" id="magicdomid403"><span class="">We
startedwith the case where at most one column is EACH, which is easier to understand.<br /> The case of two or more
EACHcolumns in the same foreign key has been left open for future development.</span></div><div class="ace-line"
id="magicdomid404"><br/></div><div class="ace-line" id="magicdomid405"><span class="">> Why would the possible
actionsbe affected, and why only these?</span></div><div class="ace-line" id="magicdomid406"><br /></div><div
class="ace-line"id="magicdomid407"><span class="">We  had to add the EACH variant to two actions (EACH CASCADE and EACH
SET NULL), in order to leave users the flexibility to choose the operation  to be performed in case of delete or update
ofone or more elements from  the referenced table.</span></div><div class="ace-line" id="magicdomid408"><span
class="">Some users indeed might prefer that, in case a referenced row is deleted,  the whole row is deleted (therefore
they'duse the standard CASCADE  action). Others mights simply require that references to that row is  removed from the
referencingarray (therefore they'd use the variant  EACH CASCADE action). The same concept applies for SET NULL (the
whole array is set to NULL) and EACH SET NULL (referencing elements are set to  NULL).</span></div><div
class="ace-line"id="magicdomid390"><br /></div> Thank you.<br /><br /> Cheers,<br /> Gabriele<br /><pre
class="moz-signature"cols="72">-- Gabriele Bartolini - 2ndQuadrant ItaliaPostgreSQL Training, Services and Support<a
class="moz-txt-link-abbreviated"href="mailto:gabriele.bartolini@2ndQuadrant.it">gabriele.bartolini@2ndQuadrant.it</a> |
<aclass="moz-txt-link-abbreviated" href="http://www.2ndQuadrant.it">www.2ndQuadrant.it</a> 
</pre>