Thread: unexpected effect of FOREIGN KEY ON CASCADE DELETE
consider following example: CREATE TABLE foob(id serial primary key, name varchar default ''); CREATE TABLE fooA(id serial primary key, fooB int not null references fooB(id) on update cascade on delete cascade, name varchar default ''); CREATE FUNCTION foobarrA() RETURNS trigger AS $_$ BEGIN RAISE NOTICE 'foobarred %', (SELECT name FROM fooB WHERE id = OLD.fooB); RETURN OLD; END; $_$ LANGUAGE 'plpgsql'; CREATE TRIGGER foobarrrred BEFORE DELETE ON fooA FOR EACH ROW EXECUTE PROCEDURE foobarrA(); insert into foob(name) select random()::varchar FROM generate_series(1,100); insert into fooa(name, foob) select random()::varchar, (select id from foob order by random() limit 1) FROM generate_series(1,100); select foob from fooa order by random() limit 1; foob ------ 70 (1 row) DELETE FROM foob where id =70; NOTICE: foobarred <NULL> CONTEXT: SQL statement "DELETE FROM ONLY "public"."fooa" WHERE $1 OPERATOR(pg_catalog.=) "foob"" NOTICE: foobarred <NULL> I always assumed, that since triggers are set to BEFORE, the data will still exist in the tables when they are fired, it will still be accessible. I looked in the manual, and there is no mention of that effect anywhere I can find. And here's the question, is there any way in which I can overcome that (to me) problem ? Other than, by substituting foreign key with my own trigger, to handle that situation and than delete data. thank you . -- GJ
2010/6/23 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > consider following example: > > > > CREATE TABLE foob(id serial primary key, name varchar default ''); > CREATE TABLE fooA(id serial primary key, fooB int not null references > fooB(id) on update cascade on delete cascade, name varchar default > ''); > > CREATE FUNCTION foobarrA() RETURNS trigger AS > $_$ > BEGIN > RAISE NOTICE 'foobarred %', (SELECT name FROM fooB WHERE id = OLD.fooB); > RETURN OLD; > END; > $_$ LANGUAGE 'plpgsql'; > > CREATE TRIGGER foobarrrred BEFORE DELETE ON fooA FOR EACH ROW EXECUTE > PROCEDURE foobarrA(); > insert into foob(name) select random()::varchar FROM generate_series(1,100); > insert into fooa(name, foob) select random()::varchar, (select id from > foob order by random() limit 1) FROM generate_series(1,100); > > select foob from fooa order by random() limit 1; > foob > ------ > 70 > (1 row) > > DELETE FROM foob where id =70; > NOTICE: foobarred <NULL> > CONTEXT: SQL statement "DELETE FROM ONLY "public"."fooa" WHERE $1 > OPERATOR(pg_catalog.=) "foob"" > NOTICE: foobarred <NULL> > > > > I always assumed, that since triggers are set to BEFORE, the data will > still exist in the tables when they are fired, it will still be > accessible. I looked in the manual, and there is no mention of that > effect anywhere I can find. It is in there: http://www.postgresql.org/docs/8.4/static/sql-createtrigger.html "SQL specifies that BEFORE DELETE triggers on cascaded deletes fire after the cascaded DELETE completes. The PostgreSQL behavior is for BEFORE DELETE to always fire before the delete action, even a cascading one. This is considered more consistent. There is also unpredictable behavior when BEFORE triggers modify rows that are later to be modified by referential actions. This can lead to constraint violations or stored data that does not honor the referential constraint. " But it sounds like it's not doing that. Thom
this is 8.3.7, for the record. And no, They won't let me update it to 8.3.11 :/
2010/6/23 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > this is 8.3.7, for the record. And no, They won't let me update it to 8.3.11 :/ > Well, same applies: http://www.postgresql.org/docs/8.3/static/sql-createtrigger.html I've just run the same set of statements you specified against 8.4.1, 8.4.4 and 9.0 beta2 and the same thing happens: NOTICE: foobarred <NULL> CONTEXT: SQL statement "DELETE FROM ONLY "public"."fooa" WHERE $1 OPERATOR(pg_catalog.=) "foob"" Thom
any ideas than, how can make it actually do what I wanted it to do please ? Making FK deferrable doesn't help. thanks.
nope, that's not the thing. This is just specific to my example. But production code I have, doesn't have such confusing name, and still fails. Plus postgresql doesn't rely on names, but on oids rather.
2010/6/23 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > any ideas than, how can make it actually do what I wanted it to do please ? > Making FK deferrable doesn't help. > > > thanks. > Is it practical to put the trigger on the other table instead? Thom
not really, as it depends on pretty much both tables. This is where de-normalization would actually makes sens, except for that it wouldn't - because it will badly effect all my other queries (joining on varchar is so slow). I could drop FK, and replace that with my own trigger(s), but that's a lot of mess. Other idea, is to maintain some sort of a temp table that will hold all names deleted, and use it. But still, not very nice nor obviuos. -- GJ
On Wednesday 23 June 2010 5:35:52 am Grzegorz Jaśkiewicz wrote: > consider following example: > > > > CREATE TABLE foob(id serial primary key, name varchar default ''); > CREATE TABLE fooA(id serial primary key, fooB int not null references > fooB(id) on update cascade on delete cascade, name varchar default > ''); > > CREATE FUNCTION foobarrA() RETURNS trigger AS > $_$ > BEGIN > RAISE NOTICE 'foobarred %', (SELECT name FROM fooB WHERE id = OLD.fooB); > RETURN OLD; > END; > $_$ LANGUAGE 'plpgsql'; > > CREATE TRIGGER foobarrrred BEFORE DELETE ON fooA FOR EACH ROW EXECUTE > PROCEDURE foobarrA(); > insert into foob(name) select random()::varchar FROM > generate_series(1,100); insert into fooa(name, foob) select > random()::varchar, (select id from foob order by random() limit 1) FROM > generate_series(1,100); > > select foob from fooa order by random() limit 1; > foob > ------ > 70 > (1 row) > > DELETE FROM foob where id =70; > NOTICE: foobarred <NULL> > CONTEXT: SQL statement "DELETE FROM ONLY "public"."fooa" WHERE $1 > OPERATOR(pg_catalog.=) "foob"" > NOTICE: foobarred <NULL> > > > > I always assumed, that since triggers are set to BEFORE, the data will > still exist in the tables when they are fired, it will still be > accessible. I looked in the manual, and there is no mention of that > effect anywhere I can find. > > > And here's the question, is there any way in which I can overcome that > (to me) problem ? Other than, by substituting foreign key with my own > trigger, to handle that situation and than delete data. > > > thank you . > > -- > GJ My suspicion is that this is an identifier problem. See error below: CONTEXT: SQL statement "DELETE FROM ONLY "public"."fooa" WHERE $1 OPERATOR(pg_catalog.=) "foob"" <--- *** It would seem to me there is confusion between the table fooB(b) and the column foob. I am afraid at this point I can not be any more helpful. -- Adrian Klaver adrian.klaver@gmail.com
On Wednesday 23 June 2010 7:02:59 am Grzegorz Jaśkiewicz wrote: > nope, that's not the thing. This is just specific to my example. But > production code I have, doesn't have such confusing name, and still > fails. > Plus postgresql doesn't rely on names, but on oids rather. For what it worth I tried it on my versions of 8.3.7 and 8.4.3 and it worked, so there is something else going on here. As to Postgres not relying on names, how do you think the backend finds the OIDS? -- Adrian Klaver adrian.klaver@gmail.com
well, change foob column name to something else, and try yourself. It still fails.
On Wednesday 23 June 2010 7:57:22 am Grzegorz Jaśkiewicz wrote: > well, change foob column name to something else, and try yourself. It > still fails. As I said in my previous post it did not fail on my instance of 8.3.7. In other words the DELETE succeeded. At this point I do not have an explanation, other then something different between my installation and your and Thom's installations are causing the problem. For what it is worth I installed from source with locale of en_US and encoding of UTF8. -- Adrian Klaver adrian.klaver@gmail.com
the delete will succeed. That's not the point of the exercise tho. The point, is to print name in trigger, rather than null!
2010/6/23 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > well, change foob column name to something else, and try yourself. It > still fails. > Wait a minute... it's deleting from foob, which is considered deleted for the remainder of that transaction. This cascades to fooa which sets off the trigger before it does anything, and the result you're getting out says that the same value is no longer is foob, which, while not yet committed, is true as far as the transaction is concerned. This is confusing because I thought the default transaction isolation level was READ COMMITTED. :/ Regards Thom
it is confusing to me, because I thought triggers are firring BEFORE anything goes away. So I assume that all data is still going to be visible to the trigger, as it is firing BEFORE. The only thing is, it looks like the FKs are doing the deletion and than things are handed over to triggers.
2010/6/23 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > the delete will succeed. > That's not the point of the exercise tho. > > The point, is to print name in trigger, rather than null! > But if it's been deleted from foob already, how can it print it? So if foob has a row with an id of 5, then: DELETE FROM foob WHERE id = 5; That row is deleted from foob. This cascades to attempt to delete it from fooa. The trigger happens first though which tries to find the row from foob where id = 5... but it's already been deleted, so no name is selected. To demonstrate, change your trigger function to: create FUNCTION foobarrA() RETURNS trigger AS $_$ BEGIN RAISE NOTICE 'foobarred %', (SELECT name FROM fooB WHERE id = 999); RETURN OLD; END; $_$ LANGUAGE 'plpgsql'; and add in: insert into foob(id, name) values (999, 'stuff'); insert into fooa(id, foob) values (999, 999); after your inserts. This will successfully select the value because it's not deleted. And then running: DELETE FROM foob where id =999; Will return NULL again because it's just been deleted before the trigger on fooa. So cases where it's returning NULL is because there's been no match. Thom
2010/6/23 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > it is confusing to me, because I thought triggers are firring BEFORE > anything goes away. So I assume that all data is still going to be > visible to the trigger, as it is firing BEFORE. The only thing is, it > looks like the FKs are doing the deletion and than things are handed > over to triggers. > The trigger is on fooa though, not foob. foob's deletions occur before cascading to fooa, and only then does the function trigger. I still think tranaction isolation level should come into this somewhere. Thom
I do understand what you are saying, but still it is highly unintuitive. Since trigger is BEFORE, developer will expect that data to be there.
2010/6/23 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > I do understand what you are saying, but still it is highly > unintuitive. Since trigger is BEFORE, developer will expect that data > to be there. > Yes, I'm still not exactly sure why it's seeing uncommitted changes. :/ Thom
Thom Brown <thombrown@gmail.com> 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.
regards, tom lane
On 06/23/2010 08:22 AM, Grzegorz Jaśkiewicz wrote: > the delete will succeed. > That's not the point of the exercise tho. > > The point, is to print name in trigger, rather than null! Sorry about the noise, I completely missed what you where getting at. -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Jun 23, 2010 at 7:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Thom Brown <thombrown@gmail.com> 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. -- GJ
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@sss.pgh.pa.us> wrote: > > Thom Brown <thombrown@gmail.com> 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@gmail.com
because in my case I have many tables with FK pointing at foob. So writing that many triggers is going to be a royal pain.
On Thursday 24 June 2010 7:21:04 am Grzegorz Jaśkiewicz wrote: > because in my case I have many tables with FK pointing at foob. So > writing that many triggers is going to be a royal pain. I am trying to see how this is different from writing the triggers on the child tables :) In any case are they not all pointing the at the same value in foob? From what you have described you are trying to capture the name associated with the id deleted in foob, so I am not sure why the child tables need to be involved. -- Adrian Klaver adrian.klaver@gmail.com
that Id refers to 'name' column that I need. There still is FK on it, so basically it is broken inside transaction, from trigger's perspective.
On Thursday 24 June 2010 7:40:22 am Grzegorz Jaśkiewicz wrote: > that Id refers to 'name' column that I need. There still is FK on it, > so basically it is broken inside transaction, from trigger's > perspective. I understand this part. What I am saying is think about reversing your point of view. Instead of pulling the information from foob, push out the information from foob. -- Adrian Klaver adrian.klaver@gmail.com
it is slightly more complicated than that, cos I need information from fooA too. So we have a chicken and egg problem.
I'll fix it this way:
CREATE TABLE foob(id serial primary key, name varchar default '');
CREATE TABLE fooA(id serial primary key, fooBook int not null
references fooB(id) on update cascade on delete cascade DEFERRABLE,
name varchar default '');
CREATE FUNCTION foobarrB() RETURNS trigger AS
$_$
BEGIN
BEGIN
INSERT INTO foob_temp(id, name) VALUES(OLD.id, OLD.name);
EXCEPTION
WHEN undefined_table THEN
CREATE TEMP TABLE foob_temp(id bigint not null, name varchar not
null) ON COMMIT DROP;
INSERT INTO foob_temp(id, name) VALUES(OLD.id, OLD.name);
END;
RETURN OLD;
END;
$_$ LANGUAGE 'plpgsql';
CREATE TRIGGER foobbrrrred BEFORE DELETE ON fooB FOR EACH ROW EXECUTE
PROCEDURE foobarrB();
CREATE FUNCTION foobarrA() RETURNS trigger AS
$_$
DECLARE
_name varchar;
BEGIN
BEGIN
SELECT name INTO _name FROM foob_temp WHERE id = OLD.fooBook;
EXCEPTION
WHEN undefined_table THEN
SELECT name INTO _name FROM fooB WHERE id = OLD.fooBook;
END;
RAISE NOTICE 'foobarred %', _name;
RETURN OLD;
END;
$_$ LANGUAGE 'plpgsql';
CREATE TRIGGER fooaarrrred BEFORE DELETE ON fooA FOR EACH ROW EXECUTE
PROCEDURE foobarrA();
insert into foob(name) select random()::varchar FROM generate_series(1,1000);
insert into fooa(name, fooBook) select random()::varchar, bb.id FROM
(select id from foob order by random() limit 1) bb,
generate_series(1,100);
DELETE FROM foob where id in (select foobook from fooa order by
random() limit 3);