On Thursday 24 June 2010 1:48:12 am Grzegorz Jaśkiewicz wrote: > On Wed, Jun 23, 2010 at 7:31 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Thom Brown <thombrown@xxxxxxxxx> writes: > >> Yes, I'm still not exactly sure why it's seeing uncommitted changes. :/ > > > > Because it's all one transaction. A transaction that couldn't see its > > own changes wouldn't be very useful. > > > > I think what the OP is unhappy about is that he imagines that the ON > > CASCADE DELETE action is part of the original DELETE on the primary-key > > table. But it is not: per SQL spec, it is a separate operation > > happening after the original DELETE. (In fact, it might be quite a lot > > after the original delete, if you have the FK constraint set as > > deferred.) The trigger on the referencing table fires before the actual > > delete of the referencing row, but it's going to see the original DELETE > > statement as already completed, because it was a previous operation > > within the current transaction. > > That's all great Tom, but it breaks useful example like mine, and > gives no other benefits. > > I will have to do something ugly, and create temp table to hold fooB > deleted values, for reference from other threads. > Temp, on commit drop. Not a very nice programming trick, but cleanest > I can come up with. I know I was confused before, but now I am not sure. Unless there is more to this problem, why not put the trigger on fooB? CREATE FUNCTION foobarrb() RETURNS trigger AS $_$ BEGIN RAISE NOTICE 'foobarred %', (OLD.name ); RETURN OLD; END; $_$ LANGUAGE 'plpgsql'; CREATE TRIGGER foobarrrred BEFORE DELETE ON foob FOR EACH ROW EXECUTE PROCEDURE foobarrb(); test=> DELETE FROM foob where id=8; NOTICE: foobarred 0.37025912059471 DELETE 1 -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
