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...
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 |