Complex cursor won't rewind to 0-th row

From: <typea(at)l-i-e(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Complex cursor won't rewind to 0-th row
Date: 2003-02-02 02:36:26
Message-ID: 50204.216.80.95.13.1044153386.squirrel@www.l-i-e.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Please CC me off-list, if at all possible.

I don't think I'm being stupid.

I even read the FAQ and TODO list as well as the link from "cursor" in the
TODO list about potential cursors outside transactions.

archive=> select version();
version
-----------------------------------------------------------
PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

BEGIN
declare foo cursor for SELECT DISTINCT article.id, volume, number,
article.article, article.title , 0 + ( 0 + 1 * int4( (text ILIKE
'%destruction%') ) + 2 * int4(substring(text, 1, length(text)/10) ILIKE
'%destruction%') + 1 * int4( (text ~* 'destruction.{0,20}Iraq') ) ) AS
points FROM article , article_word, article_word_frequency WHERE (TRUE
AND ( TRUE AND (text ILIKE '%destruction%') AND (text ~*
'destruction.{0,20}Iraq') ) ) AND article_word.id =
article_word_frequency.word AND article_word_frequency.article =
article.id AND (FALSE OR article_word.word = 'iraq' ) ORDER BY points
desc, volume, number, article.article ;

DECLARE CURSOR
fetch 1 in foo;
id | volume | number | article | title
| points
-------+--------+--------+---------+---------------------------------------------+--------
10068 | 038 | 003 | 021 | International Agreements on Nuclear
Weapons | 4
(1 row)

fetch 9 in foo;

id | volume | number | article |
title
| points
-------+--------+--------+---------+------------------------------------------------------------------------------------------------------------------------------+--------
14422 | 048 | 001 | 018 | Iraq's Bomb: Blueprints and
Artifacts
| 4
14555 | 048 | 005 | 013 | It's All over at Al Atheer

| 4
15636 | 051 | 006 | 033 | Has Iraq Come Clean at Last?

| 4
11320 | 041 | 001 | 009 | 1985 Outlook: Nuclear Proliferation:
The Pace Quickens
| 2
14426 | 048 | 001 | 022 | The Vision Thing: David Shorr
reviews The Future Belongs to Freedom by Eduard Shevardnadze
| 2
14559 | 048 | 005 | 017 | U.N. Dues: The Price of Peace

| 2
15081 | 049 | 009 | 026 | Too Many Crooks?: Michael Krepon
reviews Arms Control by Committee: Managing Negotiations with the
Russians by George Bunn | 2
15773 | 052 | 003 | 035 | Nuclear Excuses

| 2
(8 rows)

NOTE: That would make 9 (not 8) rows total?

move backward 0 in foo;
MOVE 8

NOTE: Arooo? Shouldn't I have moved back 9 (not 8) rows?

move backward 1 in foo;
MOVE 0

I guess not...

fetch 1 in foo;
id | volume | number | article | title
| points
-------+--------+--------+---------+----------------------------------------+--------
14422 | 048 | 001 | 018 | Iraq's Bomb: Blueprints and
Artifacts | 4
(1 row)

Hey -- That wasn't my 0th row the first time around!

I simply CANNOT get back to the first article -- "International Agreements
on Nuclear Weapons" no matter what -- I can do all the "move" and "fetch"
I want, but after first going beyond the 0th row, PostgreSQL insists the
0th article is "Iraq's Bomb: Blueprints and Artifacts" which just ain't
so.

This was repeatable with at least one, possibly two, other queries in this
data set. (Though all involved 'Iraq' ...)

However, with a simpler data example, all goes well:

archive=> create sequence test_id;
CREATE SEQUENCE
archive=> create table test (test_id int4 default nextval('test_id'), test
text);
CREATE TABLE
archive=> insert into test(test) values( '1');
INSERT 25170548 1
archive=> insert into test(test) values( '2');
INSERT 25170549 1
archive=> insert into test(test) values( '3');
INSERT 25170550 1
archive=> insert into test(test) values( '4');
INSERT 25170551 1
archive=> insert into test(test) values( '5');
INSERT 25170552 1
archive=> insert into test(test) values( '6');
INSERT 25170553 1
archive=> insert into test(test) values( '7');
INSERT 25170554 1
archive=> insert into test(test) values( '8');
INSERT 25170555 1
archive=> insert into test(test) values( '9');
INSERT 25170556 1
archive=> insert into test(test) values( '10');
INSERT 25170557 1
archive=> begin;
BEGIN
archive=> declare foo cursor for select test_id, test from test where
test_id > 5 and test_id < 9 order by test_id;
DECLARE CURSOR
archive=> fetch 1 in foo;
test_id | test
---------+------
6 | 6
(1 row)

archive=> fetch 9 in foo;
test_id | test
---------+------
7 | 7
8 | 8
(2 rows)

archive=> move backward 10 in foo;
MOVE 3
archive=> fetch 1 in foo;
test_id | test
---------+------
6 | 6
(1 row)

archive=> fetch 1 in foo;
test_id | test
---------+------
7 | 7
(1 row)

archive=> move backward 0 in foo;
MOVE 1
archive=> move backward 2 in foo;
MOVE 0
archive=> fetch 1 in foo;
test_id | test
---------+------
6 | 6
(1 row)

archive=> rollback;
ROLLBACK
archive=>

I can only guess that either something horribly funky in the data
(non-ASCII characters?) or the JOIN of the tables is at fault.

Schema and gzipped data (92 Meg) available at:
http://bulletinarchive.org/pg_dump/

Any Ideas?
Please CC me off-list, if at all possible.
I really need to be able to reliably get back to the 0th row...

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2003-02-02 03:24:00 Re: Libpq is not a shared library on Mac OS X
Previous Message Insyde 2003-01-31 16:33:01 Problem when adding an existing primary key