Re: [HACKERS] GSoC 2017: Foreign Key Arrays - Mailing list pgsql-hackers

From Mark Rofail
Subject Re: [HACKERS] GSoC 2017: Foreign Key Arrays
Date
Msg-id CAJvoCuv8w9Tzz4zrK84haWkKDpUGVvPiz1E4_EKYT-FnLZn=rw@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] GSoC 2017: Foreign Key Arrays  (Mark Rofail <markm.rofail@gmail.com>)
Responses Re: [HACKERS] GSoC 2017: Foreign Key Arrays
List pgsql-hackers
These are limitations of the patch ordered by importance:

✗ presupposes that count(distinct y) has exactly the same notion of equality that the PK unique index has. In reality, count(distinct) will fall back to the default btree opclass for the array element type. 

- Supported actions:
 ✔ NO ACTION
 ✔ RESTRICT
 ✗ CASCADE
 ✗ SET NULL
 ✗ SET DEFAULT 

✗ coercion is unsopported. i.e. a numeric can't refrence int8

✗ Only one "ELEMENT" column allowed in a multi-column key

✗ undesirable dependency on default opclass semantics in the patch, which is that it supposes it can use array_eq() to detect whether or not the referencing column has changed.  But I think that can be fixed without undue pain by providing a refactored version of array_eq() that can be told which element-comparison function to use

✗ cross-type FKs are unsupported

-- Resolved limitations =============================

✔ fatal performance issues.  If you issue any UPDATE or DELETE against the PK table, you get a query like this for checking to see if the RI constraint would be violated: 
SELECT 1 FROM ONLY fktable x WHERE $1 = ANY (fkcol) FOR SHARE OF x;
/* Changed into SELECT 1 FROM ONLY fktable x WHERE $1 @> fkcol FOR SHARE OF x; */

-- ============================================

Can someone help me understand the first limitation? 

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] segfault in HEAD when too many nested functions call
Next
From: Peter Geoghegan
Date:
Subject: Re: In-place index updates and HOT (Was: [HACKERS] Patch: WriteAmplification Reduction Method (WARM))