Thread: More FK patches
This patch should help fix cases with two separate fk constraints in a row that happen to reference the same pk constraint with an on update cascade and similar cases. It now should detect correctly that a pk row was added later after a delete or update on a no action deferred fk and not incorrect error as well as dropping a check on insert/update to the fk table if the row we're being referred to no longer is valid (I'm using HeapTupleSatisfiesItself because the comment implied it was what I was looking for and it appears to work :) ). I've got regression tests but I'm holding off on those until we decide whether or not we're dropping the triggered data change errors since a couple of the tests would hit that case and I'll either change them or drop them if we're not dropping the error.
Attachment
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> This patch should help fix cases with two separate fk constraints
> in a row that happen to reference the same pk constraint with
> an on update cascade and similar cases.
Aren't those NOT EXISTS clauses going to cause a humungous
performance hit?
Seems it would be better for the RI triggers to do more in C code
and stop expecting the query engine to handle these things. I've
always thought that ReferentialIntegritySnapshotOverride was an
absolutely unacceptable kluge, not least because it's turned on
*before* we do parsing/planning of the RI queries, and so is
likely to screw up system catalog checks.
regards, tom lane
Was this resolved? --------------------------------------------------------------------------- > > This patch should help fix cases with two separate fk constraints > in a row that happen to reference the same pk constraint with > an on update cascade and similar cases. It now should detect > correctly that a pk row was added later after a delete or update > on a no action deferred fk and not incorrect error as well > as dropping a check on insert/update to the fk table if the > row we're being referred to no longer is valid (I'm using > HeapTupleSatisfiesItself because the comment implied it was > what I was looking for and it appears to work :) ). > > I've got regression tests but I'm holding off on those until > we decide whether or not we're dropping the triggered data change > errors since a couple of the tests would hit that case and I'll > either change them or drop them if we're not dropping the error. Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Wed, 21 Nov 2001, Bruce Momjian wrote: > Was this resolved? Not yet. Per Tom's message on this, I've started looking at working on doing more of the fk stuff without relying on SPI. I could do a version of the patch that did a single query for the two no action cases rather than the not exists, but I'm not sure if that's worth it then.
Is there a TODO item to add then? --------------------------------------------------------------------------- > > On Wed, 21 Nov 2001, Bruce Momjian wrote: > > > Was this resolved? > > Not yet. Per Tom's message on this, I've started looking at working > on doing more of the fk stuff without relying on SPI. I could do > a version of the patch that did a single query for the two no action > cases rather than the not exists, but I'm not sure if that's worth > it then. > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Thu, 22 Nov 2001, Bruce Momjian wrote: > Is there a TODO item to add then? Fix foreign key constraints to not error on intermediate states of the database.
> > On Thu, 22 Nov 2001, Bruce Momjian wrote: > > > Is there a TODO item to add then? > > Fix foreign key constraints to not error on intermediate states > of the database. Added to TODO. Thanks. I put your name on it. :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026