Thread: delete quite skip updated rows

Hello

one my customer reported following problem (checked on 8.4 and 8.3.6)

test case:

create table test(a integer);

create or replace function getid(_a integer) returns integer as $$
begin update test set a = _a where a = _a; return _a;
end;
$$ language plpgsql;

insert into test values(20);
select * from test where a = 20; --> one row
select getid(20); --> 20

the problem:

postgres=# delete from test where a = getid(20);
DELETE 0
Time: 1,510 ms

but

postgres=# explain analyze delete from test where a = getid(20);                                          QUERY PLAN
-------------------------------------------------------------------------------------------------Seq Scan on test
(cost=0.00..640.00rows=12 width=6) (actual
 
time=0.149..0.154 rows=1 loops=1)  Filter: (a = getid(20))Total runtime: 0.221 ms
(3 rows)


explain analyze signalise one deleted row, but this is not true

regards
Pavel Stehule

Pavel Stehule <pavel.stehule@gmail.com> writes:
> explain analyze signalise one deleted row, but this is not true

This is not a bug.  The explain output tells you that one row was
found by the seqscan, which is true.  It doesn't tell you whether
the delete machinery did anything with that row.  The row could
be suppressed from deletion by a BEFORE DELETE trigger, or because
it was already updated in the current command (as is the case here).
        regards, tom lane