Re: [PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Thomas F. O'Connell
On Oct 29, 2005, at 9:48 AM, Bruno Wolff III wrote: On Sat, Oct 29, 2005 at 13:10:31 +0200, Martin Lesser <[EMAIL PROTECTED]> wrote: Which effects have UPDATEs on REFERENCEd TABLEs when only columns in the referenced table are updated which are not part of the FOREIGN KEY constraint? In

Re: [PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Tom Lane
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 > comm

Re: [PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Tom Lane
Michael Fuhr <[EMAIL PROTECTED]> 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 tim

Re: [PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Bruce Momjian
Michael Fuhr wrote: > On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote: > > On Sat, Oct 29, 2005 at 08:24:32 -0600, Michael Fuhr <[EMAIL PROTECTED]> > > wrote: > > > My tests suggest that a lookup on the referring key is done only > > > if the referenced key is changed. Here's an e

Re: [PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Michael Fuhr
On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote: > On Sat, Oct 29, 2005 at 08:24:32 -0600, Michael Fuhr <[EMAIL PROTECTED]> > wrote: > > My tests suggest that a lookup on the referring key is done only > > if the referenced key is changed. Here's an example from 8.1beta4; > > I us

Re: [PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Bruce Momjian
Bruno Wolff III wrote: > On Sat, Oct 29, 2005 at 13:10:31 +0200, > Martin Lesser <[EMAIL PROTECTED]> wrote: > > Which effects have UPDATEs on REFERENCEd TABLEs when only columns in the > > referenced table are updated which are not part of the FOREIGN KEY > > constraint? > > In 8.1 there is a ch

Re: [PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Bruno Wolff III
On Sat, Oct 29, 2005 at 08:24:32 -0600, Michael Fuhr <[EMAIL PROTECTED]> wrote: > > Does an UPDATE of e.g. m_fld1 in t_master cause a 'lookup' in all tables > > which have a cascading update-rule or is this 'lookup' only triggered if > > the referenced column in t_master is explicitly updated? >

Re: [PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Bruno Wolff III
On Sat, Oct 29, 2005 at 13:10:31 +0200, Martin Lesser <[EMAIL PROTECTED]> wrote: > Which effects have UPDATEs on REFERENCEd TABLEs when only columns in the > referenced table are updated which are not part of the FOREIGN KEY > constraint? In 8.1 there is a check to see if the foreign key value h

Re: [PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Michael Fuhr
> Does an UPDATE of e.g. m_fld1 in t_master cause a 'lookup' in all tables > which have a cascading update-rule or is this 'lookup' only triggered if > the referenced column in t_master is explicitly updated? My tests suggest that a lookup on the referring key is done only if the referenced key is

[PERFORM] Effects of cascading references in foreign keys

2005-10-29 Thread Martin Lesser
Which effects have UPDATEs on REFERENCEd TABLEs when only columns in the referenced table are updated which are not part of the FOREIGN KEY constraint? I have one "master"-table like create table t_master ( m_id serial primary key, m_fld1 ..., m_fld2 ..., ... ) The table above is