Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-08-01 Thread Gabriele Bartolini
Il 30/07/12 19:16, Gabriele Bartolini ha scritto: And it can be also interchanged with Array element Foreign Key. As promised, we have sent a patch for the Array ELEMENT foreign key support. We are discontinuing this thread here and continue discussing the former Foreign Keys with

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-07-30 Thread Gabriele Bartolini
Hi guys, it is time to give another go to this patch. I would like to thank everyone for suggestions and ideas expressed through this list. We are happy that Part 0 of the patch has been committed (array_remove() and array_replace() functions), which will be useful in Part 2 (too

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-07-30 Thread Simon Riggs
On 30 July 2012 16:12, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: * Array Foreign Key * Foreign Key Arrays * ELEMENT Foreign Keys * ... Which one is your favourite? Array Element Foreign Key -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-07-30 Thread Robert Haas
On Mon, Jul 30, 2012 at 11:12 AM, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: Hi guys, it is time to give another go to this patch. I would like to thank everyone for suggestions and ideas expressed through this list. We are happy that Part 0 of the patch has been

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-07-30 Thread Alvaro Herrera
Excerpts from Simon Riggs's message of lun jul 30 11:21:46 -0400 2012: On 30 July 2012 16:12, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: * Array Foreign Key * Foreign Key Arrays * ELEMENT Foreign Keys * ... Which one is your favourite? Array Element Foreign

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-07-30 Thread Gabriele Bartolini
Il 30/07/12 19:11, Alvaro Herrera ha scritto: I was going to say the same, except I had ELEMENT as a capitalized word in my mind (and in the docs it'd be within literal). So: Array ELEMENT Foreign Key +1 for me And it can be also interchanged with Array element Foreign Key. -- Gabriele

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-18 Thread Kevin Grittner
Misa Simic misa.si...@gmail.com wrote: 2012/6/17 Kevin Grittner kevin.gritt...@wicourts.gov Can someone provide a practical example of a foreign key with array use case? The only situations I'm able to think of right now are the same cases where you would now use a table with primary keys

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-18 Thread Misa Simic
2012/6/18 Kevin Grittner kevin.gritt...@wicourts.gov The many-to-one case seems like it is better handled in the other direction -- with the referenced table holding the set of valid keys and the referencing table holding the single key. (I believe the general case of this is what Jeff

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-17 Thread Jeff Davis
On Fri, 2011-11-04 at 13:48 +0100, Gabriele Bartolini wrote: This patch adds basic support of arrays in foreign keys, by allowing to define a referencing column as an array of elements having the same type as the referenced column in the referenced table. Every NOT NULL element in the

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-17 Thread Misa Simic
: Re: [HACKERS] [PATCH] Support for foreign keys with arrays -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-17 Thread Simon Riggs
On 17 June 2012 19:16, Misa Simic misa.si...@gmail.com wrote: IMO, both approaches make sense... Agreed. It's also a good reason to do as Peter suggests and come up with a better description than just EACH. Do we need something like Exclusion FKs? i.e. the FK partner of Exclusion Constraints?

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-17 Thread Kevin Grittner
Simon Riggs wrote: Misa Simic wrote: IMO, both approaches make sense... Agreed. Can someone provide a practical example of a foreign key with array use case? The only situations I'm able to think of right now are the same cases where you would now use a table with primary keys of two

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-17 Thread Misa Simic
2012/6/17 Simon Riggs si...@2ndquadrant.com Do we need something like Exclusion FKs? i.e. the FK partner of Exclusion Constraints? +1 Definatelly it would be something usefull... Today's workaround to achieve that with additional table, and additional column in Key is a bit awkward...

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-17 Thread Misa Simic
2012/6/17 Kevin Grittner kevin.gritt...@wicourts.gov Can someone provide a practical example of a foreign key with array use case? The only situations I'm able to think of right now are the same cases where you would now use a table with primary keys of two tables to provide a many-to-many

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-17 Thread Gianni Ciolli
On Sun, Jun 17, 2012 at 09:58:17AM -0500, Kevin Grittner wrote: Simon Riggs wrote: Misa Simic wrote: IMO, both approaches make sense... Agreed. Can someone provide a practical example of a foreign key with array use case? The only situations I'm able to think of right now are

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-17 Thread Jeff Davis
On Sun, 2012-06-17 at 21:10 +0800, Simon Riggs wrote: Do we need something like Exclusion FKs? i.e. the FK partner of Exclusion Constraints? Yes, Inclusion Constraints. I've known we need something like that since I did Exclusion Constraints, but I haven't gotten further than that. Regards,

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-15 Thread Simon Riggs
On 6 April 2012 07:21, Peter Eisentraut pete...@gmx.net wrote: On lör, 2012-03-24 at 10:01 +, Gianni Ciolli wrote: ON (DELETE | UPDATE) actions for EACH foreign keys == -- --- ---                   |    ON    

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-14 Thread Marco Nenciarini
Il giorno mer, 13/06/2012 alle 18.07 -0400, Noah Misch ha scritto: On Wed, Jun 13, 2012 at 10:12:18PM +0200, Gabriele Bartolini wrote: Our goal is to work on this patch from the next commit fest. What we are about to do for this commit fest is to split the previous patch and send a

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-13 Thread Gabriele Bartolini
Hi Noah, Il 10/06/12 22:53, Noah Misch ha scritto: This has bitrotted; please refresh. Also, please evaluate Peter's feedback: http://archives.postgresql.org/message-id/1333693277.32606.9.ca...@vanquo.pezone.net Our goal is to work on this patch from the next commit fest. What we are about

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-13 Thread Noah Misch
On Wed, Jun 13, 2012 at 10:12:18PM +0200, Gabriele Bartolini wrote: Our goal is to work on this patch from the next commit fest. What we are about to do for this commit fest is to split the previous patch and send a small one just for the array_remove() and array_replace() functions.

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-06-10 Thread Noah Misch
On Wed, Mar 28, 2012 at 04:25:06PM +0200, Marco Nenciarini wrote: Il giorno lun, 19/03/2012 alle 18.41 +0100, Marco Nenciarini ha scritto: Attached is v5, which should address all the remaining issues. Please find attached v6 of the EACH Foreign Key patch. From v5 only cosmetic changes

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-04-06 Thread Peter Eisentraut
On lör, 2012-03-24 at 10:01 +, Gianni Ciolli wrote: ON (DELETE | UPDATE) actions for EACH foreign keys == -- --- --- |ON |ON | Action| DELETE | UPDATE |

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-04-06 Thread Noah Misch
On Fri, Apr 06, 2012 at 09:21:17AM +0300, Peter Eisentraut wrote: On l??r, 2012-03-24 at 10:01 +, Gianni Ciolli wrote: ON (DELETE | UPDATE) actions for EACH foreign keys == -- --- ---

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-28 Thread Marco Nenciarini
Il giorno lun, 19/03/2012 alle 18.41 +0100, Marco Nenciarini ha scritto: Attached is v5, which should address all the remaining issues. Please find attached v6 of the EACH Foreign Key patch. From v5 only cosmetic changes to the documentation were made. Regards, Marco -- Marco Nenciarini -

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-26 Thread Gabriele Bartolini
Hello Tom, I started to look at this patch a bit. I'm quite confused by the fact that some, but not all, of the possible FK action types now come in an EACH variant. This makes no sense at all to me. ISTM that EACH is a property of the FK constraint as a whole, that is that it says the

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-24 Thread Gianni Ciolli
On Thu, Mar 22, 2012 at 03:02:45PM -0400, Tom Lane wrote: It's even less clear about what the semantics are in multi-key cases. Right offhand I would say that multi-key cases are nonsensical and should be forbidden outright, because there is no way to figure out which collections of elements

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-22 Thread Tom Lane
Marco Nenciarini marco.nenciar...@2ndquadrant.it writes: Attached is v5, which should address all the remaining issues. I started to look at this patch a bit. I'm quite confused by the fact that some, but not all, of the possible FK action types now come in an EACH variant. This makes no sense

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-19 Thread Marco Nenciarini
Hi Noah, thank you again for your thorough review, which is much appreciated. I think the patch has reached the stage where a committer can review it without wasting much time on things that might change radically. So, I'm marking it Ready for Committer. Please still submit an update

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-19 Thread Noah Misch
On Mon, Mar 19, 2012 at 06:41:39PM +0100, Marco Nenciarini wrote: Attached is v5, which should address all the remaining issues. Looks clean to me. On Fri, Mar 16, 2012 at 11:33:12PM -0400, Noah Misch wrote: If the cost doesn't exceed O(F log P), where F is the size of the FK table and

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-16 Thread Noah Misch
[used followup EACH-foreign-key.v4b.patch.bz2 for review] On Wed, Mar 14, 2012 at 07:03:08PM +0100, Marco Nenciarini wrote: please find attached v4 of the EACH Foreign Key patch (formerly known also as Foreign Key Array). On Fri, Feb 24, 2012 at 09:01:35PM -0500, Noah Misch wrote: We can

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-15 Thread Gabriele Bartolini
Hello, Il 15/03/12 05:03, Marco Nenciarini ha scritto: please find attached v4 of the EACH Foreign Key patch (formerly known also as Foreign Key Array). Please find attached version v4b which replaces v4 and fixes a bug in array_replace() and adds further regression tests on array_replace()

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-09 Thread Marco Nenciarini
Il giorno gio, 08/03/2012 alle 08.11 -0500, Robert Haas ha scritto: On Fri, Feb 24, 2012 at 9:01 PM, Noah Misch n...@leadboat.com wrote: I consider these the core changes needed to reach Ready for Committer: - Fix crash in array_replace(arr, null, null). - Don't look through the domain

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-03-08 Thread Robert Haas
On Fri, Feb 24, 2012 at 9:01 PM, Noah Misch n...@leadboat.com wrote: I consider these the core changes needed to reach Ready for Committer: - Fix crash in array_replace(arr, null, null). - Don't look through the domain before calling can_coerce_type(). - Compare operator family of pfeqop and

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-02-24 Thread Noah Misch
Hi Marco, This version fixes everything I noted in my last review. Apart from corner cases I note, the patch is technically sound. I pose a few policy-type questions; comments from a broader audience would help those. On Mon, Feb 06, 2012 at 07:04:42PM +0100, Marco Nenciarini wrote: Please

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-02-21 Thread Erik Rijkers
(I reply to an older message but I did use the newest patch, version 3) I wanted to have a look at v3 of this patch today, but it seems it won't apply and compile anymore. Here are the protestations of patch: patching file src/include/catalog/pg_proc.h Hunk #1 FAILED at 868. Hunk #2 FAILED at

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-02-21 Thread Peter Geoghegan
On 21 February 2012 15:22, Erik Rijkers e...@xs4all.nl wrote: (I reply to an older message but I did use the newest patch, version 3) I wanted to have a look at v3 of this patch today, but it seems it won't apply and compile anymore. That's just because a new column has been added to pg_proc

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-02-21 Thread Gabriele Bartolini
Hello Erik, Il 21/02/12 16:22, Erik Rijkers ha scritto: (I reply to an older message but I did use the newest patch, version 3) I wanted to have a look at v3 of this patch today, but it seems it won't apply and compile anymore. As Peter pointed out, it is due to a new Boolean field added in

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-02-06 Thread Marco Nenciarini
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.

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-01-31 Thread Gabriele Bartolini
Hi Noah, Il 21/01/12 21:42, Noah Misch ha scritto: On Sat, Jan 14, 2012 at 08:18:48PM +0100, Marco Nenciarini wrote: I greatly like that name; it would still make sense for other aggregate types, should we ever expand its use. Please complete the name change: the documentation, catalog

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-01-22 Thread Simon Riggs
On Sat, Jan 21, 2012 at 8:42 PM, Noah Misch n...@leadboat.com wrote: You currently forbid multi-column EACH FKs.  I agree that we should allow only one array column per FK; with more, the set of required PK rows would be something like the Cartesian product of the elements of array columns.

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-01-22 Thread Noah Misch
On Sun, Jan 22, 2012 at 09:06:49PM +, Simon Riggs wrote: On Sat, Jan 21, 2012 at 8:42 PM, Noah Misch n...@leadboat.com wrote: You currently forbid multi-column EACH FKs. ?I agree that we should allow only one array column per FK; with more, the set of required PK rows would be

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-01-21 Thread Noah Misch
On Sat, Jan 14, 2012 at 08:18:48PM +0100, Marco Nenciarini wrote: This is our latest version of the patch. Gabriele, Gianni and I have discussed a lot and decided to send an initial patch which uses EACH REFERENCES instead of ARRAY REFERENCES. The reason behind this is that ARRAY REFERENCES

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-01-15 Thread Marco Nenciarini
Hello, Il giorno dom, 11/12/2011 alle 19.45 -0500, Noah Misch ha scritto: On Sat, Dec 10, 2011 at 09:47:53AM +0100, Gabriele Bartolini wrote: So, here is a summary: --- - - | ON| ON| Action | DELETE | UPDATE |

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2012-01-14 Thread Marco Nenciarini
Hello, Il giorno dom, 11/12/2011 alle 19.45 -0500, Noah Misch ha scritto: On Sat, Dec 10, 2011 at 09:47:53AM +0100, Gabriele Bartolini wrote: So, here is a summary: --- - - | ON| ON| Action | DELETE | UPDATE |

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-12-11 Thread Noah Misch
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 plainaction always applies to entire rows. SET

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-12-11 Thread Noah Misch
On Thu, Nov 17, 2011 at 12:08:32AM -0500, Tom Lane wrote: The least we could do is invent some non-spec syntax that makes the intention clear, rather than having the system assume that an error case was intended to mean something else. Maybe pids INTEGER[] ARRAY REFERENCES pt, +1.

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-12-10 Thread Gabriele Bartolini
Hi Noah, thanks for your feedback. 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 plainaction always applies to entire rows. SET DEFAULT should now be fine to allow. It's

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-24 Thread Peter Eisentraut
On mån, 2011-11-21 at 10:30 -0600, Merlin Moncure wrote: I like the idea of being able to define more flexible foreign keys, but are we gilding the lily here? The proposed solution is really quite specific to the nuances of arrays. Perhaps there is a more general expression based syntax that

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-21 Thread Merlin Moncure
On Fri, Nov 4, 2011 at 7:48 AM, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: This patch adds basic support of arrays in foreign keys, by allowing to define a referencing column as an array of elements having the same type as the referenced column in the referenced table. Every

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-20 Thread Gabriele Bartolini
Hi Noah, thanks for your unvaluable review, rich of useful and thorough comments and notes. Marco and myself will add your proposed tests as soon as possible (most likely after the Italian PGDay which is this week). However, given the feedback received from other developers too (including

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-20 Thread Gianni Ciolli
Hi Gabriele and Marco, On Sun, Nov 20, 2011 at 10:36:15AM +0100, Gabriele Bartolini wrote: --- - - | ON| ON| Action | DELETE | UPDATE | --- - - CASCADE| Row | Element | SET NULL |

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-20 Thread Noah Misch
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

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-16 Thread Noah Misch
Hi Gabriele, On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote: CREATE TABLE pt ( id INTEGER PRIMARY KEY, ... ); CREATE TABLE ft ( id SERIAL PRIMARY KEY, pids INTEGER[] REFERENCES pt, ... ); This seems useful. I'm assuming the SQL spec says nothing about a

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-16 Thread Pavel Stehule
Hello 2011/11/17 Noah Misch n...@leadboat.com: Hi Gabriele, On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote: CREATE TABLE pt (   id INTEGER PRIMARY KEY,   ... ); CREATE TABLE ft (   id SERIAL PRIMARY KEY,   pids INTEGER[] REFERENCES pt,   ... ); This seems

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-16 Thread Tom Lane
Noah Misch n...@leadboat.com writes: On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote: CREATE TABLE pt ( id INTEGER PRIMARY KEY, CREATE TABLE ft ( id SERIAL PRIMARY KEY, pids INTEGER[] REFERENCES pt, I'm assuming the SQL spec says nothing about a feature like this? I'm

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-16 Thread Josh Berkus
BTW, has anyone thought through whether this is a sane idea at all? It seems to me to be full of cases that will require rather arbitrary decisions, like whether ON DELETE CASCADE should involve deleting the whole row or just one array element. One array element, presumably. Does the patch

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-16 Thread Pavel Stehule
2011/11/17 Tom Lane t...@sss.pgh.pa.us: Noah Misch n...@leadboat.com writes: On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote: CREATE TABLE pt ( id INTEGER PRIMARY KEY, CREATE TABLE ft ( id SERIAL PRIMARY KEY, pids INTEGER[] REFERENCES pt, I'm assuming the SQL spec says

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-16 Thread Josh Berkus
Folks, BTW, I don't want to block this patch. However, it occurs to me that a more generalized FK based on non-equality conditions (i.e. expressions) would be nice if it were possible. Then we could have FKs from all kinds of complex structures. -- Josh Berkus PostgreSQL Experts Inc.

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-16 Thread Jaime Casanova
On Wed, Nov 16, 2011 at 11:28 PM, Noah Misch n...@leadboat.com wrote: Removing values from the array seems best to me.  There's no doubt about what ON UPDATE CASCADE should do, and having ON DELETE CASCADE excise individual array elements is consistent with that.  It's less clear for SET NULL,

Re: [HACKERS] [PATCH] Support for foreign keys with arrays

2011-11-16 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: BTW, has anyone thought through whether this is a sane idea at all? It seems to me to be full of cases that will require rather arbitrary decisions, like whether ON DELETE CASCADE should involve deleting the whole row or just one array element. One array