Hello, this is about the second issue.
SELECT FROM <inheritance parent> WHERE <cond> FOR UPDATE may
return results which does not match the <cond>. The following
steps will reproduce the problematic behavior (A and B are
individual sessions) on master and back to 9.0 but 8.4 gives
correct result. I haven't checked on 8.3.
- Reproducing the symptom
A=# SET enable_seqscan TO false;
A=# SET enable_bitmapscan TO false;
A=# DROP TABLE IF EXISTS p CASCADE;
A=# CREATE TABLE p (id text, a text, b text, c text);
A=# CREATE INDEX p_i1 ON p (a, b, c) WHERE b IN ('0', '1') AND c = '0';
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 1 + a, 0, a % 4, 0 FROM generate_series(0, 7) a);
A=# INSERT INTO c2 (SELECT 11 + a, 1, a % 4, 0 FROM generate_series(0, 7) a);
A=# INSERT INTO c3 (SELECT 21 + a, 2, a % 4, 0 FROM generate_series(0, 7) a);
A=# ANALYZE;
A=# BEGIN;
A=# CREATE TEMP TABLE tt1 AS
A=# SELECT id FROM p WHERE b IN ('0', '1') AND c = '0' ORDER BY id LIMIT 1 FOR
UPDATE;
A=# UPDATE p SET b = -1 WHERE id IN (SELECT id FROM tt1) RETURNING id;
A=# DROP TABLE tt1;
A=# SET enable_seqscan TO false;
A=# SET enable_bitmapscan TO false;
B=# SELECT tableoid, ctid, * FROM p WHERE b IN ('0', '1') AND c = '0' ORDER BY
id LIMIT 1 FOR UPDATE;
A=# COMMIT;
On session B.
| tableoid | ctid | id | a | b | c
| ----------+-------+----+---+----+---
| 34316 | (0,9) | 1 | 0 | -1 | 0
b = -1 apparently contradicts the WHERE clause.
The plan for the query is as following. The part "b IN ('0',
'1')" in the WHERE clause is omitted even though required by EPQ
recheck.
Limit
-> LockRows
-> Sort
Sort Key: p.id
-> Result
-> Append
-> Index Scan using p_i1 on p
Index Cond: (c = '0'::text)
-> Index Scan using c1_a_b_c_idx on c1
Index Cond: (c = '0'::text)
-> Index Scan using c2_a_b_c_idx on c2
Index Cond: (c = '0'::text)
-> Index Scan using c3_a_b_c_idx on c3
Index Cond: (c = '0'::text)
- Analysys and solution
This is caused by that IndexRecheck examines the test tuple with
a qual "c = '0'" without "b IN ('0', '1')". The part has been
removed in create_indexscan_plan. It decieds whether to remove a
qual or not using get_parse_rowmark(root->parse(->rowMarks)) and
predicate_implied_by(). But the former always says no (NULL) for
child relations even if the parent has rowMarks.
On the other hand, rowmarks on children is already distributed at
the time by expand_inherited_rtentry() into root->rowMarks.
So I replaced the get_parse_rowmark() with get_plan_rowmark() as
the attached patch and the problem disappeared.
By the way, get_plan_rowmark() has the comment like this,
> * This probably ought to be elsewhere, but there's no very good place
I haven't moved it anywhere but createplan.c might be the good plance.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
>From a0e26063bd412ce06df7dc252b8881e51af10f35 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp>
Date: Thu, 11 Dec 2014 18:20:37 +0900
Subject: [PATCH] Fix bogus tuples for inhertance and FOR UPDATE
---
src/backend/optimizer/plan/createplan.c | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index bf8dbe0..30df3ce 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -29,6 +29,7 @@
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/paths.h"
+#include "optimizer/prep.h"
#include "optimizer/placeholder.h"
#include "optimizer/plancat.h"
#include "optimizer/planmain.h"
@@ -1231,7 +1232,7 @@ create_indexscan_plan(PlannerInfo *root,
if (best_path->indexinfo->indpred)
{
if (baserelid != root->parse->resultRelation &&
- get_parse_rowmark(root->parse, baserelid) == NULL)
+ get_plan_rowmark(root->rowMarks, baserelid) == NULL)
if (predicate_implied_by(clausel,
best_path->indexinfo->indpred))
continue; /* implied by index predicate */
--
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
