From: | Thom Brown <thombrown(at)gmail(dot)com> |
---|---|
To: | Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com> |
Cc: | adrian(dot)klaver(at)gmail(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE |
Date: | 2010-06-23 15:33:10 |
Message-ID: | AANLkTinS_LBPGYIExNu-Amw16d9MGoDWtiiyrKv5tcVf@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2010/6/23 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2010-06-23 15:35:05 | Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE |
Previous Message | Craig Ringer | 2010-06-23 15:33:05 | Re: High Availability with Postgres |