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_
