Обсуждение: Bug #689: Bug at moving cursor on joined tables

Robert Grabowski (robert.grabowski@7bulls.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Bug at moving cursor on joined tables

Long Description
I have problems with moving cursors on joined tables. On Simple tables all is ok... See example code...

Sample Code
test=# create table t1 (id integer, name text);
CREATE
test=# create table t2 (id integer, name text);
CREATE
test=# insert into t1 values (1, 'a');
INSERT 3670086 1
test=# insert into t1 values (2, 'b');
INSERT 3670087 1
test=# insert into t1 values (3, 'd');
INSERT 3670088 1
test=# insert into t2 values (1, 'x');
INSERT 3670089 1
test=# insert into t2 values (2, 'y');
INSERT 3670090 1
test=# insert into t2 values (3, 'z');
INSERT 3670091 1
test=# begin;
BEGIN
test=# declare c cursor for select t1.id, t1.name, t2.name from t1 join t2 using (id);
DECLARE
test=# fetch all from c;
 id | name | name
----+------+------
  1 | a    | x
  2 | b    | y
  3 | d    | z
(3 rows)

test=# move backward all in c;
MOVE 0
test=# fetch all from c;
 id | name | name
----+------+------
(0 rows)

test=# rollback;
ROLLBACK
test=# begin;
BEGIN
test=# declare c cursor for select t1.id, t1.name from t1;
DECLARE
test=# fetch all from c;
 id | name
----+------
  1 | a
  2 | b
  3 | d
(3 rows)

test=# move backward all in c;
MOVE 3
test=# fetch all from c;
 id | name
----+------
  1 | a
  2 | b
  3 | d
(3 rows)

test=#


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> I have problems with moving cursors on joined tables.

Most of the more complex plan node types don't really support moving
backwards.  I suppose it'd be an idea to error out rather than silently
doing the wrong thing.  Fixing the code to actually do the right thing
is way down the priority list, unfortunately.

As a workaround you could modify the query to force a sort step after
the join, eg with an ORDER BY that doesn't match the join condition.
A sort node can cope with backwards scan of its output ...

            regards, tom lane