Skip site navigation (1) Skip section navigation (2)

foreign key constraints referencing inherited relations - WIP

From: Matt Newell <newellm(at)blur(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Subject: foreign key constraints referencing inherited relations - WIP
Date: 2005-11-10 21:45:00
Message-ID: 200511101345.00513.newellm@blur.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Warning: This patch is only a work in work in progress.

Current Status:

This patch modifies postgres's current behavior by creating foreign-key 
triggers on not only the source and referenced tables, but also the children 
of the referenced table.  It modifies the source triggers, removing the ONLY 
keyword when looking up the referenced key.

Shortcomings:

The triggers are only created when the foreign key is created, so any new 
tables that inherit from the referenced table will not have the triggers.  
This can be fixed fairly easily.

When removing the ONLY clause on the foreign key checks, I also had to remove 
the FOR SHARE OF x part.  This was in the following functions: RI_FKey_check, 
ri_Check_Pk_Match, RI_FKey_noaction_del, RI_FKey_noaction_upd, 
RI_FKey_restrict_del. I don't really understand the significance of this.

There is no way to turn off the new behavior, which will likely cause 
breakages in existing applications.  If this behavior was made optional on a 
per foreign key basis, with the current syntax being backwards compatible, 
then this would not be an issue.

Inherited relations don't share primary key indexes, so there is the 
possibility of duplicates.  Can this be fixed by putting triggers on the 
inherited tables to ensure they aren't duplicating a primary key?

Can/should indexes, constraints, triggers be shared between inherited tables?  
Would this be a lot of work, and is it desired?  Does it need to be 
configurable per index, or per inheritance?

Matt Newell,
Blur Studio

Attachment: inherit_fkey.diff
Description: text/x-diff (21.5 KB)

Responses

pgsql-hackers by date

Next:From: Robert TreatDate: 2005-11-10 22:13:15
Subject: Re: Obtaining a source tree from CVS
Previous:From: Andrew DunstanDate: 2005-11-10 21:08:29
Subject: Re: generic builtin functions

pgsql-patches by date

Next:From: Tom LaneDate: 2005-11-10 23:44:19
Subject: Re: foreign key constraints referencing inherited relations - WIP
Previous:From: Andrew DunstanDate: 2005-11-10 14:04:27
Subject: Re: wishes correction of postgresql.conf.sample.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group