Обсуждение: Re: [PATCHES] More FK patches

On Mon, 12 Nov 2001, Tom Lane wrote:

> 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?

You're right. Thinking about it, it would make more sense to check it
once for the cases we support, since the only case where a different
row would come up would be in match partial.  So that should probably
go away to a direct search for a matching row.

> 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.
Well, would it time correctly if the override was only around the
actual execp rather than the prepare and such?

Do you think it would be better to directly implement the constraint
checks and actions using scans and C modifying rows rather than the
query planner and switch over in 7.3? Without looking too hard yet, I'd be
worried that it'd end  up reimplementing alot of glue code that already
exists, but maybe that's not so bad. I'm willing to give it a shot, but
I couldn't guarantee anything and I'd also like to know the reasoning Jan
had for his decisions so as to make an informed attempt. :)

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Well, would it time correctly if the override was only around the
> actual execp rather than the prepare and such?

That would definitely feel better, but ultimately global variables
changing the behavior of low-level subroutines are Bad News.

> Do you think it would be better to directly implement the constraint
> checks and actions using scans and C modifying rows rather than the
> query planner and switch over in 7.3?

I believe that's the way to go in the long run, but I don't have any
idea how much work might be involved.  What I don't like about the
present setup is (a) the overhead involved, and (b) the fact that we
can't implement quite the right semantics using only user-level queries.
SELECT FOR UPDATE doesn't get the kind of lock we want, and there are
these other issues too.

> I'd also like to know the reasoning Jan had for his decisions so as to
> make an informed attempt. :)

I should probably let Jan speak for himself, but I'm guessing that
it was an easy way to get a prototype implementation up and going.
That was fine at the time --- it was a pretty neat hack, in fact.
But we need to start thinking about an industrial-strength implementation.
        regards, tom lane