Thomas F. O'Connell wrote:
> > In 8.1 there is a check to see if the foreign key value has changed
> > and if
> > not a trigger isn't queued. In the currently released versions any
> > update
> > will fire triggers.
> > The check in comment for trigger.c didn't say if this optimization
> > applied
> > to both referencing and referenced keys or just one of those.
> > If you need to know more you can look at the code at:
> > http://developer.postgresql.org/cvsw...kend/commands/
> > for trigger.c.
>
> It seems like this warrants an item somewhere in the release notes,
> and I'm not currently seeing it (or a related item) anywhere. Perhaps
> E.1.3.1 (Performance Improvements)? For some of the more extreme
> UPDATE scenarios I've seen, this could be a big win.

Hard to say, perhaps:

Prevent referential integrity triggers from firing if referenced
columns are not changed by an UPDATE

Previously, triggers would fire but do nothing.

However, the description seems more complex than it is worth.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Would someone add a comment in the code about this, or research it?

---------------------------------------------------------------------------

Tom Lane wrote:
> I wrote:
> > Looking at this, I wonder if there isn't a bug or at least an
> > inefficiency in 8.1. The KeysEqual short circuit tests are still there
> > in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> > And don't they need to account for the special case mentioned in the
> > comment in triggers.c, that the RI check must still be done if we are
> > looking at a row updated by the same transaction that created it?
>
> OK, I take back the possible-bug comment: the special case only applies
> to the FK-side triggers, which is to say RI_FKey_check, and that routine
> doesn't attempt to skip the check on equal old/new keys. I'm still
> wondering though if the KeysEqual tests in the other RI triggers aren't
> now a waste of cycles.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Would someone please find the answer to Tom's last question?

---------------------------------------------------------------------------

Tom Lane wrote:
> I wrote:
> > Looking at this, I wonder if there isn't a bug or at least an
> > inefficiency in 8.1. The KeysEqual short circuit tests are still there
> > in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> > And don't they need to account for the special case mentioned in the
> > comment in triggers.c, that the RI check must still be done if we are
> > looking at a row updated by the same transaction that created it?
>
> OK, I take back the possible-bug comment: the special case only applies
> to the FK-side triggers, which is to say RI_FKey_check, and that routine
> doesn't attempt to skip the check on equal old/new keys. I'm still
> wondering though if the KeysEqual tests in the other RI triggers aren't
> now a waste of cycles.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote:
> >> It looks like this feature was added last May, so I think it only applies
> >> to 8.1.
>
> > Earlier versions appear to have at least some kind of optimization.
>
> Yeah. IIRC, for quite some time we've had tests inside the FK update
> triggers to not bother to search the other table if the key value hasn't
> changed. What we did in 8.1 was to push that test further upstream, so
> that the trigger event isn't even queued if the key value hasn't
> changed. (This is why you don't see the trigger shown as being called
> even once.)
>
> Looking at this, I wonder if there isn't a bug or at least an
> inefficiency in 8.1. The KeysEqual short circuit tests are still there
> in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> And don't they need to account for the special case mentioned in the
> comment in triggers.c, that the RI check must still be done if we are
> looking at a row updated by the same transaction that created it?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Added to TODO:

* Improve referential integrity checks

http://archives.postgresql.org/pgsql...0/msg00458.php


---------------------------------------------------------------------------

Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote:
> >> It looks like this feature was added last May, so I think it only applies
> >> to 8.1.
>
> > Earlier versions appear to have at least some kind of optimization.
>
> Yeah. IIRC, for quite some time we've had tests inside the FK update
> triggers to not bother to search the other table if the key value hasn't
> changed. What we did in 8.1 was to push that test further upstream, so
> that the trigger event isn't even queued if the key value hasn't
> changed. (This is why you don't see the trigger shown as being called
> even once.)
>
> Looking at this, I wonder if there isn't a bug or at least an
> inefficiency in 8.1. The KeysEqual short circuit tests are still there
> in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> And don't they need to account for the special case mentioned in the
> comment in triggers.c, that the RI check must still be done if we are
> looking at a row updated by the same transaction that created it?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance