Added to TODO:

o Fix problem when cascading referential triggers make changes on
cascaded tables, seeing the tables in an intermediate state

http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php
http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php


---------------------------------------------------------------------------

Stephan Szabo wrote:
[Hackers now seems more appropriate]
On Thu, 1 Sep 2005, Stephan Szabo wrote:

On Tue, 23 Aug 2005, Stephan Szabo wrote:

Here's my current work in progress for 8.1 devel related to fixing the
timing issues with referential actions having their checks run on
intermediate states. I've only put in a simple test that failed against
8.0 in the regression patch and regression still passes for me. There's
still an outstanding question of whether looping gives the correct result
in the presence of explicit inserts and set constraints immediate in
before triggers.
As Darcy noticed, the patch as given does definately still have problems
with before triggers. I was able to construct a case that violates the
constraint with an update in a before delete trigger. I think this might
be why the spec has the wierd timing rules for before triggers on cascaded
deletes such that the deletions happen before the before triggers.

We have a similar problem for before triggers that update the rows that
are being cascade updated. The following seems to violate the constraint
for me on 8.0.3:

drop table pk cascade;
drop table fk cascade;
drop function fk_move();

create table pk(a int primary key);
create table fk(a int references pk on delete cascade on update cascade, b
int);
create function fk_move() returns trigger as '
begin
raise notice '' about to move for % '', old.b;
update fk set b=b-1 where b > old.b;
return new;
end;' language 'plpgsql';
create trigger fkmovetrig before update on fk for each row execute
procedure fk_move();
insert into pk values(1);
insert into pk values(2);
insert into fk values(1,1);
insert into fk values(1,2);
insert into fk values(2,3);
select * from pk;
select * from fk;
update pk set a = 3 where a = 1;
select * from pk;
select * from fk;

This gives me (3,1), (1,1) and (2,2) as the rows in fk where the (1,1) row
is invalid. This is obviously wrong, but the question is, what is the
correct answer? Should the update in the before trigger trying to change
b on a row that no longer has a reference have errored?
Well, the spec seems to get out of this simply. I read SQL2003's trigger
execution information (specifically 14.27 GR5g*) to say that before
triggers that call data changing statements are invalid.

We can't do that for compatibility reasons, but it would allow us to say
that modifying a row in a before trigger that is also a row selected in
the outer statement is an error for this update case. It'd presumably be
an error for a normal delete as well, although I think it might be
relaxable for cascaded deletes because the spec seems to say that the
before triggers for deletions caused by the cascade are actually run after
the removals. I'm not sure whether we could easily differentiate this case
from any other cases where the row was modified twice either yet.

---
* "If TR is a BEFORE trigger and if, before the completion of the
execution of an <SQL procedure statement> simply contained in TSS, an
attempt is made to execute an SQL-data change statement or an SQL-invoked
routine that possibly modifies SQL-data, then an exception condition is
raised: prohibited statement encountered during trigger execution."

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 18 of 18 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 3, '05 at 5:16a
activeJun 14, '06 at 6:36p
posts18
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2018 Grokbase