On 2014-09-29 22:42:57 +1300, David Rowley wrote: > On Mon, Sep 29, 2014 at 2:41 AM, Andres Freund <and...@2ndquadrant.com> > wrote: > > > On 2014-09-28 17:32:21 +1300, David Rowley wrote: > > > My understanding of foreign keys is that any pending foreign key triggers > > > will be executed just before the query completes, so we should only ever > > > encounter pending foreign key triggers during planning when we're > > planning > > > a query that's being executed from somewhere like a volatile function or > > > trigger function, if the outer query has updated or deleted some records > > > which are referenced by a foreign key. > > > > Note that foreign key checks also can be deferred. So the window for > > these cases is actually larger. > > > > > Thanks Andres, I know you had said this before but I had previously failed > to realise exactly what you meant. I thought you were talking about > defining a foreign key to reference a column that has a deferrable unique > index. I now realise you were talking about making the foreign key itself > as deferrable.
Oh. Don't remember doing that ;) > I've made a change to the patch locally to ignore foreign > keys that are marked as deferrable. I have serious doubts about the general usefulness if this is onlyu going to be useable in a very restricted set of circumstances (only one time plans, no deferrable keys). I think it'd be awesome to have the capability, but I don't think it's ok to restrict it that much. To me that means you can't make the decision at plan time, but need to move it to execution time. It really doesn't sound that hard to short circuit the semi joins whenever, at execution time, there's no entries in the deferred trigger queue. It's a bit annoying to have to add code to all of nestloop/hashjoin/mergejoin to not check the outer tuple if there's no such entry. But I don't think it'll be too bad. That'd mean it can be used in prepared statements. What I think would be a bit finnicky is the costing... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers