Обсуждение: DELETE Weirdness

There are two ways of deleting things,

DELETE FROM <table> WHERE PK IN ( ...SET... );

DELETE FROM <table> USING <table2>
WHERE <join condition> AND <filter clause>;

I am deleting from a table where the rows are inter-related (it is a
tree); using the first version with IN, it does not delete all rows;
there is a trigger that rearranges the rows when a row is deleted
(updating references etc.).  Disabling this trigger results in correct
behavoir.

However, using the second version (using USING) works as expected with
or without disabling the said triggers.

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



-ravi

--
:wq

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