Hello, we found (and maybe fixed) two wrong behavior related to inheritance and FOR UPDATE. This report is about one of them.
This behavior could be observed on master and back to 8.4 but 8.4 behaves a bit more funny. I haven't checked on 8.3. This issue is that some query returns duplicate rows after FOR UPDATE was blocked, in other words, after getting HeapTupleUpdated in ExecLockRows. - Reproducing the symptom This behavior is easy to reproduce as following. A=# DROP TABLE IF EXISTS p CASCADE; A=# CREATE TABLE p (a int, b int, c int); A=# CREATE TABLE c1 (LIKE p INCLUDING INDEXES) INHERITS (p); A=# CREATE TABLE c2 (LIKE p INCLUDING INDEXES) INHERITS (p); A=# CREATE TABLE c3 (LIKE p INCLUDING INDEXES) INHERITS (p); A=# INSERT INTO c1 (SELECT 0, a / 3, a % 3 FROM generate_series(0, 9) a); A=# INSERT INTO c2 (SELECT 1, a / 3, a % 3 FROM generate_series(0, 9) a); A=# INSERT INTO c3 (SELECT 2, a / 3, a % 3 FROM generate_series(0, 9) a); A=# ANALYZE; A=# BEGIN; A=# SELECT tableoid, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; A=# UPDATE p SET b = -1 WHERE a = 1 AND b = 1 AND c = 0; B=# SELECT tableoid, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; -- the above is blocked by session A until it commits. A=# COMMIT; Where A and B are individual sessions. You will get the follwing result on sesison B just after the COMMIT on session A. | tableoid | ctid | a | b | c | ----------+-------+---+---+--- * 33834 | (0,1) | 0 | 0 | 0 | 33834 | (0,4) | 0 | 1 | 0 | 33838 | (0,1) | 1 | 0 | 0 * 33834 | (0,1) | 0 | 0 | 0 | 33842 | (0,1) | 2 | 0 | 0 | 33842 | (0,4) | 2 | 1 | 0 | (6 rows) The lines prefixed with '*' appear twice in the result. The plan for the query is as following, LockRows -> Result -> Append -> Seq Scan on p Filter: ((b = ANY ('{0,1}'::integer[])) AND (c = 0)) -> Seq Scan on c1 Filter: ((b = ANY ('{0,1}'::integer[])) AND (c = 0)) -> Seq Scan on c2 Filter: ((b = ANY ('{0,1}'::integer[])) AND (c = 0)) -> Seq Scan on c3 Filter: ((b = ANY ('{0,1}'::integer[])) AND (c = 0)) - Analysys and solution This seems to be caused in ExecScanFetch under EvalPlanQualNext, when es_epqTuleSet[scanrelid - 1] is false (p and c1 in the above example). In that case, execution confluents onto non-EPQ route. As the result, the accessMtd (= SeqScan, IndexScan will behaves as the same) retunes null slot at the first iteration on p. Then the second iteration on c1, it reuturns the c1's first tuple (33834:(0,1)) and EvalPlanQualNext is satisfied with the wrong tuple. In the EPQ block in ExecScanFetch, it seems should return NULL if the relation does not has test tuple. The attached patch does so on the current master and the problem has disappeared. regards, -- Kyotaro Horiguchi NTT Open Source Software Center
>From 79c201539abdc59400af8339795c6c63c2980278 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp> Date: Thu, 11 Dec 2014 09:06:59 +0900 Subject: [PATCH] Fix duplicate tuples for inheritance and FOR UPDATE --- src/backend/executor/execScan.c | 5 +++++ 1 file changed, 5 insertions(+) diff --git a/src/backend/executor/execScan.c b/src/backend/executor/execScan.c index 1319519..d5c491c 100644 --- a/src/backend/executor/execScan.c +++ b/src/backend/executor/execScan.c @@ -74,6 +74,11 @@ ExecScanFetch(ScanState *node, return slot; } + else + { + /* This relation has no tuple to recheck.*/ + return NULL; + } } /* -- 2.1.0.GIT
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers