Обсуждение: BUG ? ... CURSOR AS VIEW ... MOVE ... CRASH

Hi,

I'm using postgresql-7.1RC4 (BTW: are there any changes in the release
?)
(regression tests were OK)

The following script crashes the backend (when run with psql).

A similar query (using libpq++) returns 0 rows after FETCH 1[... till
end] ... MOVE BACKWARD ALL ... FECTH 1 ...
(Which seems to be correct)

BTW: Is it o.k. to use MOVE to re-use a query ? Or is there no speed
benefit ?

==============================8<===============================
begin;
CREATE TABLE a (id_a int primary key, a text);
CREATE TABLE b (id_a int references a, b text);
INSERT INTO a values (1, 'a');
INSERT INTO a values (2, 'b');
INSERT INTO a values (3, 'c');
INSERT INTO b values (1, '1');
INSERT INTO b values (1, '2');
INSERT INTO b values (2, '1');
INSERT INTO b values (2, '2');
INSERT INTO b values (3, '1');
INSERT INTO b values (3, '2');
SELECT * from a natural left join b;
CREATE VIEW demo AS SELECT * from a natural left join b;
DECLARE my_portal CURSOR FOR SELECT * from demo;
FETCH 1 in my_portal;
MOVE BACKWARD ALL in my_portal;
FETCH 1 in my_portal;
===============================8<==============================

psql -f demo.sql
-------------------------------------------------------
BEGIN
psql:demo.sql:2: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
index 'a_pkey' for table 'a'
CREATE
psql:demo.sql:3: NOTICE:  CREATE TABLE will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE
INSERT 178874 1
INSERT 178875 1
INSERT 178876 1
INSERT 178877 1
INSERT 178878 1
INSERT 178879 1
INSERT 178880 1
INSERT 178881 1
INSERT 178882 1
id_a | a | b
------+---+---
   1 | a | 1
   1 | a | 2
   2 | b | 1
   2 | b | 2
   3 | c | 1
   3 | c | 2
(6 rows)

CREATE
SELECT
id_a | a | b
------+---+---
   1 | a | 1
(1 row)

MOVE
psql:demo.sql:18: pqReadData() -- backend closed the channel
unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:demo.sql:18: connection to server was lost
-------------------------------------------------------

cu

--
Nabil Sayegh

Nabil Sayegh <nsmail@sayegh.de> writes:
> The following script crashes the backend (when run with psql).

Yes, this is a bug.  I can't get excited about fixing it though,
because there are a huge pile of other bugs associated with changing
fetch directions in complex queries.  The amount of work needed to make
the world safe for this sort of thing is far out of proportion to the
benefit.  Maybe someday someone will get around to it --- but we have
a lot of higher-priority problems...

> BTW: Is it o.k. to use MOVE to re-use a query ? Or is there no speed
> benefit ?

There isn't any real speed benefit; besides, what's the point of
refetching tuples you already fetched?  If you want random access
to a query result, libpq does that just fine on the client side.

            regards, tom lane