Lists: | pgsql-hackers |
---|
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | delete quite skip updated rows |
Date: | 2009-03-27 14:26:23 |
Message-ID: | 162867790903270726u3ca4d02fj2fb1336702f9c30c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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.00 rows=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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: delete quite skip updated rows |
Date: | 2009-03-27 14:43:53 |
Message-ID: | 8676.1238165033@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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