unexpected effect of FOREIGN KEY ON CASCADE DELETE

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.

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

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 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