This a great solution to this problem, one I've found to be very common in
web development. The technique will work to add RI to Django's generic
foreign keys[1], which are implemented with an id column and a type-flag
column.
[1]:
On Tue, Dec 3, 2013 at 5:57 PM, Tom Dunstan pg...@tomd.cc wrote:
On 4 December 2013 01:24, Robert Haas robertmh...@gmail.com wrote:
Yeah, more or less, but the key is ensuring that it wouldn't let you
create the constraint in the first place if the partial index
specified *didn't* match the
On 12/04/2013 11:25 AM, Robert Haas wrote:
On Tue, Dec 3, 2013 at 5:57 PM, Tom Dunstan pg...@tomd.cc wrote:
On 4 December 2013 01:24, Robert Haas robertmh...@gmail.com wrote:
Yeah, more or less, but the key is ensuring that it wouldn't let you
create the constraint in the first place if the
On Wed, Dec 4, 2013 at 11:44 AM, Andrew Dunstan and...@dunslane.net wrote:
Oh. I misinterpreted what this feature was about, then. I thought it
was about restricting the reference to a subset of the *referenced*
table, but it seems to be about restricting the constraint to a subset
of the
On 12/04/2013 12:00 PM, Robert Haas wrote:
On Wed, Dec 4, 2013 at 11:44 AM, Andrew Dunstan and...@dunslane.net wrote:
Oh. I misinterpreted what this feature was about, then. I thought it
was about restricting the reference to a subset of the *referenced*
table, but it seems to be about
On Wed, Dec 4, 2013 at 12:18 PM, Andrew Dunstan and...@dunslane.net wrote:
Interestingly, the variant for which you can't think of a use case is
the one I've missed most. Typical examples in my experience are
things like project.project_manager_id references person (id) where
Andrew Dunstan and...@dunslane.net writes:
Well I guess we could say something like:
FOREIGN KEY (a-col) WHERE (a-condition) REFERENCES b(b-col) WHERE
(b-condition)
But it's somewhat ugly.
OK, those make sense. I wonder whether this should be done via a USING
clause on the constraint
On 12/04/2013 02:40 PM, Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
Well I guess we could say something like:
FOREIGN KEY (a-col) WHERE (a-condition) REFERENCES b(b-col) WHERE
(b-condition)
But it's somewhat ugly.
OK, those make sense. I wonder whether this should be done via
On 5 Dec 2013, at 06:10, Tom Lane t...@sss.pgh.pa.us wrote:
Andrew Dunstan and...@dunslane.net writes:
Well I guess we could say something like:
FOREIGN KEY (a-col) WHERE (a-condition) REFERENCES b(b-col) WHERE
(b-condition)
I like what you have above.
Yeah. Given both the apparent
On 5 Dec 2013, at 03:48, Andrew Dunstan and...@dunslane.net wrote:
Well I guess we could say something like:
FOREIGN KEY (a-col) WHERE (a-condition) REFERENCES b(b-col) WHERE
(b-condition)
OK, those make sense. I wonder whether this should be done via a USING clause
on the
On Mon, Dec 2, 2013 at 6:08 PM, Tom Dunstan pg...@tomd.cc wrote:
On 3 Dec 2013, at 03:37, Robert Haas robertmh...@gmail.com wrote:
I also like this feature. It would be really neat if a FOREIGN KEY
constraint with a WHERE clause could use a *partial* index on the
foreign table provided that
On 4 December 2013 01:24, Robert Haas robertmh...@gmail.com wrote:
Yeah, more or less, but the key is ensuring that it wouldn't let you
create the constraint in the first place if the partial index
specified *didn't* match the WHERE clause. For example, suppose the
partial index says WHERE
Tom Dunstan wrote:
The Problem
-
One case that traditional SQL doesn't handle very well is when you have a
child entity which can be
attached to a number of different parent entities. Examples might be
comments, tags or file
attachments - we might have 20 different
On 2013-12-02 08:57:01 +, Albe Laurenz wrote:
What strikes me is that since foreign key constraints are implemented
as triggers in PostgreSQL, this solution would probably not have many
performance benefits over a self-written trigger that implements the
same functionality. Since you need
On Dec2, 2013, at 10:06 , Andres Freund and...@2ndquadrant.com wrote:
On 2013-12-02 08:57:01 +, Albe Laurenz wrote:
What strikes me is that since foreign key constraints are implemented
as triggers in PostgreSQL, this solution would probably not have many
performance benefits over a
On 2013-12-02 12:10:32 +, Florian Pflug wrote:
On Dec2, 2013, at 10:06 , Andres Freund and...@2ndquadrant.com wrote:
On 2013-12-02 08:57:01 +, Albe Laurenz wrote:
What strikes me is that since foreign key constraints are implemented
as triggers in PostgreSQL, this solution would
Hi Laurenz!
On 2 Dec 2013, at 19:27, Albe Laurenz laurenz.a...@wien.gv.at wrote:
What strikes me is that since foreign key constraints are implemented
as triggers in PostgreSQL, this solution would probably not have many
performance benefits over a self-written trigger that implements the
On 12/02/2013 05:06 AM, Andres Freund wrote:
On 2013-12-02 08:57:01 +, Albe Laurenz wrote:
What strikes me is that since foreign key constraints are implemented
as triggers in PostgreSQL, this solution would probably not have many
performance benefits over a self-written trigger that
On Mon, Dec 2, 2013 at 10:04 AM, Andrew Dunstan and...@dunslane.net wrote:
The only way I have thought of as an alternative to this proposal is to use
a partitioned table with different FK constraints for each child. That's
certainly doable, but not without a deal of work, and even then you'd
On 3 Dec 2013, at 01:34, Andrew Dunstan and...@dunslane.net wrote:
We wanted to apply FK constraints to a very large table, but grandfather in
certain cases that didn't meet the constraint. That could have been done very
simply using this feature.
Yeah, references to old data is the other
On 3 Dec 2013, at 03:37, Robert Haas robertmh...@gmail.com wrote:
I also like this feature. It would be really neat if a FOREIGN KEY
constraint with a WHERE clause could use a *partial* index on the
foreign table provided that the index would be guaranteed to be predOK
for all versions of
On 11/28/2013 09:15 AM, Tom Dunstan wrote:
Feature Proposal: Selective foreign keys.
-
Allow foreign keys to have where clauses.
I haven't caught up on the discussion yet, but: if you can swing this,
it'd also be very useful for RLS, providing a
Tom Dunstan pg...@tomd.cc writes:
Well, with this patch, under the hood the FK query is doing (in the case of
RESTRICT):
SELECT 1 FROM ONLY public.comment x WHERE (the id) OPERATOR(pg_catalog.=)
parent_id AND (parent_entity = 'event') FOR KEY SHARE OF x;
Hm. The RI trigger code goes to
On 3 Dec 2013, at 12:37, Tom Lane t...@sss.pgh.pa.us wrote:
Tom Dunstan pg...@tomd.cc writes:
Well, with this patch, under the hood the FK query is doing (in the case of
RESTRICT):
SELECT 1 FROM ONLY public.comment x WHERE (the id)
OPERATOR(pg_catalog.=) parent_id AND (parent_entity =
Hi all!
The Problem
-
One case that traditional SQL doesn't handle very well is when you have a child
entity which can be attached to a number of different parent entities. Examples
might be comments, tags or file attachments - we might have 20 different
entities in the system
25 matches
Mail list logo
