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]:
https://docs.djangoproject.com/en/dev/ref/contrib/contenttypes/#ge
On 5 Dec 2013, at 03:48, Andrew Dunstan 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 constra
On 5 Dec 2013, at 06:10, Tom Lane wrote:
> Andrew Dunstan 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 ambiguity of the
On 12/04/2013 02:40 PM, Tom Lane wrote:
Andrew Dunstan 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 t
Andrew Dunstan 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 t
On Wed, Dec 4, 2013 at 12:18 PM, Andrew Dunstan 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
>> person.is_project_manager, o
On 12/04/2013 12:00 PM, Robert Haas wrote:
On Wed, Dec 4, 2013 at 11:44 AM, Andrew Dunstan 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 constrain
On Wed, Dec 4, 2013 at 11:44 AM, Andrew Dunstan 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 *referencing*
On 12/04/2013 11:25 AM, Robert Haas wrote:
On Tue, Dec 3, 2013 at 5:57 PM, Tom Dunstan wrote:
On 4 December 2013 01:24, Robert Haas 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* mat
On Tue, Dec 3, 2013 at 5:57 PM, Tom Dunstan wrote:
> On 4 December 2013 01:24, Robert Haas 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, su
On 4 December 2013 01:24, Robert Haas 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 parent_entity = 'eve
On Mon, Dec 2, 2013 at 6:08 PM, Tom Dunstan wrote:
> On 3 Dec 2013, at 03:37, Robert Haas 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 guarantee
On 3 Dec 2013, at 12:37, Tom Lane wrote:
> Tom Dunstan 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
Tom Dunstan 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 extrem
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
On 3 Dec 2013, at 03:37, Robert Haas 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 the foreign key che
On 3 Dec 2013, at 01:34, Andrew Dunstan 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 obvious case for
On Mon, Dec 2, 2013 at 10:04 AM, Andrew Dunstan 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 be
> giving up cer
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 implem
Hi Laurenz!
On 2 Dec 2013, at 19:27, 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. Si
On 2013-12-02 12:10:32 +, Florian Pflug wrote:
> On Dec2, 2013, at 10:06 , 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
On Dec2, 2013, at 10:06 , 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 t
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 n
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 e
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 t
25 matches
Mail list logo
