Re: DELETE Weirdness

On Fri, 2010-01-15 at 13:55 +1300, Ravi Chemudugunta wrote:
> I cannot quite understand this; Are the contents of the IN query
> worked out ONCE per outer query and therefore become invalid when
> DELETE comes along and changes items that were part of the set ? (for
> e.g.)

The command itself gets one snapshot that sees tuples as they are just
before the command begins executing. That snapshot is used for the
predicate (WHERE clause) throughout the execution, so it will remain
static.

The triggered functions may execute commands that get their own
snapshots and see new tuples, but the outer command won't see those.

You can see this with an experiment:

create table mytable (i int);
insert into mytable values(1);
create or replace function mytrfn() returns trigger as
$$ begin update mytable set i=-i; return new; end; $$
language plpgsql;
create trigger mytr before insert on mytable
for each row execute procedure mytrfn();
insert into mytable select i from mytable;
select * from mytable;
i
----
-1
1
(2 rows)

If the "select i from mytable" on the right side of the insert was
constantly being re-evaluated, there would be two "-1" values in
mytable.

Regards,
Jeff Davis