failed to fetch tuple for EvalPlanQual recheck

From: Kenichiro Tanaka <ketanaka(at)ashisuto(dot)co(dot)jp>
To: pgsql-bugs(at)postgresql(dot)org
Subject: failed to fetch tuple for EvalPlanQual recheck
Date: 2010-07-26 01:01:01
Message-ID: 4C4CDE4D.1030308@ashisuto.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello.

This is a bug report.
When I tested in PostgreSQL9 beta3,I got an error.

==================================================
select * from part_bug where HIRENUM=4 for update;
failed to fetch tuple for EvalPlanQual recheck
==================================================

Is this error assumed?

Environment.
Postgresql:9.0 beta3
OS :CentOS5.3

Reproduce Case
1)Make a partitioned table.

CREATE TABLE part_bug
(EMPNO INT,
ENAME VARCHAR(10),
HIRENUM int,
DEPTNO INT,
LOCATION VARCHAR(20));

CREATE TABLE part_bug_1 ( CHECK ( HIRENUM >= 1 AND HIRENUM < 2) )
INHERITS (part_bug);
CREATE TABLE part_bug_2 ( CHECK ( HIRENUM >= 2 AND HIRENUM < 3) )
INHERITS (part_bug);
CREATE TABLE part_bug_3 ( CHECK ( HIRENUM >= 3 AND HIRENUM < 4) )
INHERITS (part_bug);
CREATE TABLE part_bug_4 ( CHECK ( HIRENUM >= 4 AND HIRENUM < 5) )
INHERITS (part_bug);
CREATE TABLE part_bug_5 ( CHECK ( HIRENUM >= 5 AND HIRENUM < 6) )
INHERITS (part_bug);
CREATE TABLE part_bug_6 ( CHECK ( HIRENUM >= 6 AND HIRENUM < 7) )
INHERITS (part_bug);
CREATE TABLE part_bug_7 ( CHECK ( HIRENUM >= 7 AND HIRENUM < 8) )
INHERITS (part_bug);
CREATE TABLE part_bug_8 ( CHECK ( HIRENUM >= 8 AND HIRENUM < 9) )
INHERITS (part_bug);
CREATE TABLE part_bug_9 ( CHECK ( HIRENUM >= 9 AND HIRENUM < 10) )
INHERITS (part_bug);
CREATE TABLE part_bug_10 ( CHECK ( HIRENUM >= 10 AND HIRENUM < 11) )
INHERITS (part_bug);

CREATE OR REPLACE FUNCTION part_range_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.HIRENUM >= 1 AND NEW.HIRENUM < 2) THEN INSERT INTO part_bug_1
VALUES (NEW.*);
ELSIF ( NEW.HIRENUM >= 2 AND NEW.HIRENUM < 3) THEN INSERT INTO
part_bug_2 VALUES (NEW.*);
ELSIF ( NEW.HIRENUM >= 3 AND NEW.HIRENUM < 4) THEN INSERT INTO
part_bug_3 VALUES (NEW.*);
ELSIF ( NEW.HIRENUM >= 4 AND NEW.HIRENUM < 5) THEN INSERT INTO
part_bug_4 VALUES (NEW.*);
ELSIF ( NEW.HIRENUM >= 5 AND NEW.HIRENUM < 6) THEN INSERT INTO
part_bug_5 VALUES (NEW.*);
ELSIF ( NEW.HIRENUM >= 6 AND NEW.HIRENUM < 7) THEN INSERT INTO
part_bug_6 VALUES (NEW.*);
ELSIF ( NEW.HIRENUM >= 7 AND NEW.HIRENUM < 8) THEN INSERT INTO
part_bug_7 VALUES (NEW.*);
ELSIF ( NEW.HIRENUM >= 8 AND NEW.HIRENUM < 9) THEN INSERT INTO
part_bug_8 VALUES (NEW.*);
ELSIF ( NEW.HIRENUM >= 9 AND NEW.HIRENUM < 10) THEN INSERT INTO
part_bug_9 VALUES (NEW.*);
ELSIF ( NEW.HIRENUM >= 10 AND NEW.HIRENUM < 11) THEN INSERT INTO
part_bug_10 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'HIRENUM out of range. Fix the
part_range_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER part_range_insert_trigger
BEFORE INSERT ON part_bug
FOR EACH ROW EXECUTE PROCEDURE part_range_insert_trigger();

insert into part_bug values(4,'B',4,4,'B');

2)Reproduce the error.We need two sessions.

SESSION A)
begin;
select * from part_bug where HIRENUM=4 for update;

SESSION B)
begin;
select * from part_bug where HIRENUM=4 for update;
--lock wait

SESSION A)
update part_bug set ename='B' where HIRENUM in (4);
end;

Then the error is returned on session B.

The error needs a partition table and
never happens in 8.4.4.

Thank you.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message mile 2010-07-26 09:58:05 BUG #5571: global hash %_SHARED is not declared as global in the new version
Previous Message Bruce Momjian 2010-07-25 03:48:28 Re: pg_upgrade issues