Thread: Complex cursor won't rewind to 0-th row
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=3D> 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=
=20
AND ( TRUE AND (text ILIKE '%destruction%') AND (text ~*
'destruction.{0,20}Iraq') ) ) AND article_word.id =3D
article_word_frequency.word AND article_word_frequency.article =3D
article.id AND (FALSE OR article_word.word =3D 'iraq' ) ORDER BY points
desc, volume, number, article.article ;
DECLARE CURSOR
fetch 1 in foo;
id | volume | number | article | title=20=20=20=20=
=20=20=20=20=20=20=20=20=20
| points
-------+--------+--------+---------+---------------------------------------=
------+--------
10068 | 038 | 003 | 021 | International Agreements on Nuclear
Weapons | 4
(1 row)
fetch 9 in foo;
id | volume | number | article |=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
title=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20
| points
-------+--------+--------+---------+---------------------------------------=
---------------------------------------------------------------------------=
------------+--------
14422 | 048 | 001 | 018 | Iraq's Bomb: Blueprints and
Artifacts=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
| 4
14555 | 048 | 005 | 013 | It's All over at Al Atheer=20=20=20=20=
=20=20=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
| 4
15636 | 051 | 006 | 033 | Has Iraq Come Clean at Last?=20=20=20=
=20=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
| 4
11320 | 041 | 001 | 009 | 1985 Outlook: Nuclear Proliferation:
The Pace Quickens=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20
| 2
14426 | 048 | 001 | 022 | The Vision Thing: David Shorr
reviews The Future Belongs to Freedom by Eduard Shevardnadze=20=20=20=20=20=
=20=20=20=20=20=20=20=20
| 2
14559 | 048 | 005 | 017 | U.N. Dues: The Price of Peace=20=20=
=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
| 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=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
| 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=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20
| 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=3D> create sequence test_id;
CREATE SEQUENCE
archive=3D> create table test (test_id int4 default nextval('test_id'), test
text);
CREATE TABLE
archive=3D> insert into test(test) values( '1');
INSERT 25170548 1
archive=3D> insert into test(test) values( '2');
INSERT 25170549 1
archive=3D> insert into test(test) values( '3');
INSERT 25170550 1
archive=3D> insert into test(test) values( '4');
INSERT 25170551 1
archive=3D> insert into test(test) values( '5');
INSERT 25170552 1
archive=3D> insert into test(test) values( '6');
INSERT 25170553 1
archive=3D> insert into test(test) values( '7');
INSERT 25170554 1
archive=3D> insert into test(test) values( '8');
INSERT 25170555 1
archive=3D> insert into test(test) values( '9');
INSERT 25170556 1
archive=3D> insert into test(test) values( '10');
INSERT 25170557 1
archive=3D> begin;
BEGIN
archive=3D> 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=3D> fetch 1 in foo;
test_id | test
---------+------
6 | 6
(1 row)
archive=3D> fetch 9 in foo;
test_id | test
---------+------
7 | 7
8 | 8
(2 rows)
archive=3D> move backward 10 in foo;
MOVE 3
archive=3D> fetch 1 in foo;
test_id | test
---------+------
6 | 6
(1 row)
archive=3D> fetch 1 in foo;
test_id | test
---------+------
7 | 7
(1 row)
archive=3D> move backward 0 in foo;
MOVE 1
archive=3D> move backward 2 in foo;
MOVE 0
archive=3D> fetch 1 in foo;
test_id | test
---------+------
6 | 6
(1 row)
archive=3D> rollback;
ROLLBACK
archive=3D>
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...
<typea@l-i-e.com> writes:
> 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.
It didn't work that way for me, but I do see a bug here: reversing
direction after reaching either end of the query result misses the
last or first row, if the top plan node is a UNIQUE.
In general, a lot of complex plans do not work very well in the backward
direction. UNIQUE seems easy to fix, however. Attached is a patch for
7.3.
regards, tom lane
*** src/backend/executor/nodeUnique.c.orig Thu Jun 20 16:29:28 2002
--- src/backend/executor/nodeUnique.c Sun Feb 2 14:02:57 2003
***************
*** 58,63 ****
--- 58,68 ----
/*
* now loop, returning only non-duplicate tuples. We assume that the
* tuples arrive in sorted order so we can detect duplicates easily.
+ *
+ * We return the first tuple from each group of duplicates (or the
+ * last tuple of each group, when moving backwards). At either end
+ * of the subplan, clear priorTuple so that we correctly return the
+ * first/last tuple when reversing direction.
*/
for (;;)
{
***************
*** 66,75 ****
*/
slot = ExecProcNode(outerPlan, (Plan *) node);
if (TupIsNull(slot))
return NULL;
/*
! * Always return the first tuple from the subplan.
*/
if (uniquestate->priorTuple == NULL)
break;
--- 71,86 ----
*/
slot = ExecProcNode(outerPlan, (Plan *) node);
if (TupIsNull(slot))
+ {
+ /* end of subplan; reset in case we change direction */
+ if (uniquestate->priorTuple != NULL)
+ heap_freetuple(uniquestate->priorTuple);
+ uniquestate->priorTuple = NULL;
return NULL;
+ }
/*
! * Always return the first/last tuple from the subplan.
*/
if (uniquestate->priorTuple == NULL)
break;
So I've finally gotten around to applying this patch for cursors... http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/executor/nodeUnique.c 1.34.2.1 Now it's POSSIBLE that I've managed to screw up something fundamentally simple in the patch/make/install process, so step by step... I downloaded src/backend/executor/nodeUnique.c version 1.34.2.1 and put it into the source tree. The "tab" character and newlines got munged along the way, but that was easy enough to fix even for an idiot like me :-) I did make and various files got re-compiled including, but not limited to, nodeUnique.o, postgres, pg_dump, psql, etc. Pretty much what this naive user would expect to be re-compiled got re-compiled... I stopped Postgres, and used ps auxwww | grep post to be damn sure it was stopped, then restarted it. I've triple checked that the 'postgres' binary being called is the one that just got installed, and not some old old version or something. Ditto for psql monitor used to test. I still can't seem to get cursors to behave correctly: archive=> begin; BEGIN archive=> declare foo cursor for select id, title from article where id = 1; DECLARE CURSOR archive=> fetch 1 in foo; id | title ----+------------------------------------ 1 | The American and Russian Proposals (1 row) archive=> move backward 1 in foo; MOVE 0 archive=> fetch 1 in foo; id | title ----+------- (0 rows) archive=> After moving backward 1, I ought to be able to fetch the first (and only) row again, and I can't. In the event that my example query above is "too simple" for nodeUnique.c to be relevant, here is the REAL query I'm running: SELECT DISTINCT ON (points, volume, number, article.article) article.ID, article.abstract, substring(article.text, 1, 250) AS text, article.cover_date, article.volume, article.number, article.article, article.title , 0 + 3 * int4( (title ILIKE '%albert einstein%') ) + (1 * int4( title ILIKE '%albert%' )) + (3 * int4( (title ILIKE '%albert%' AND title ILIKE '%einstein%' AND ((title ~* 'albert.{0,20}einstein') OR (title ~* 'einstein.{0,20}albert'))) )) + (1 * int4( title ILIKE '%einstein%' )) + (3 * int4( (title ILIKE '%einstein%' AND title ILIKE '%albert%' AND ((title ~* 'einstein.{0,20}albert') OR (title ~* 'albert.{0,20}einstein'))) )) + 2 * int4(substring(text, 1, 20) ILIKE '%dead%') AS points, int4(length(artwork) > 0) as has_artwork FROM article , article_word_frequency AS article_word_frequency_dead WHERE (TRUE AND ( title ILIKE '%albert%' ) AND ( title ILIKE '%einstein%' ) AND article_word_frequency_dead.article = article.id AND article_word_frequency_dead.word = 44667 ) ORDER BY points desc, volume, number, article.article int4() is a user-defined function that simply returns 1/0 for true/false The above query returns exactly one row in our database. Attempting a binary search on the cursor to find the last row lets me determine how many rows there are very rapidly. Alas, then I can't actually GET my row to display it. :-( Please CC me on replies, as I'm not really a hard-core reader here... I'll have to revert once again to doing a select count(*) before the cursor which makes the application about 1.5 times slower than it should be... Sorry to be such a PITA. PostgreSQL Rocks! Tom Lane wrote: > <typea@l-i-e.com> writes: >> 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. > > It didn't work that way for me, but I do see a bug here: reversing > direction after reaching either end of the query result misses the > last or first row, if the top plan node is a UNIQUE. > > In general, a lot of complex plans do not work very well in the backward > direction. UNIQUE seems easy to fix, however. Attached is a patch for > 7.3. > > regards, tom lane > > > *** src/backend/executor/nodeUnique.c.orig Thu Jun 20 16:29:28 2002 > --- src/backend/executor/nodeUnique.c Sun Feb 2 14:02:57 2003 > *************** > *** 58,63 **** > --- 58,68 ---- > /* > * now loop, returning only non-duplicate tuples. We assume that the > * tuples arrive in sorted order so we can detect duplicates easily. > + * > + * We return the first tuple from each group of duplicates (or the > + * last tuple of each group, when moving backwards). At either end > + * of the subplan, clear priorTuple so that we correctly return the > + * first/last tuple when reversing direction. > */ > for (;;) > { > *************** > *** 66,75 **** > */ > slot = ExecProcNode(outerPlan, (Plan *) node); > if (TupIsNull(slot)) > return NULL; > > /* > ! * Always return the first tuple from the subplan. > */ > if (uniquestate->priorTuple == NULL) > break; > --- 71,86 ---- > */ > slot = ExecProcNode(outerPlan, (Plan *) node); > if (TupIsNull(slot)) > + { > + /* end of subplan; reset in case we change direction */ > + if (uniquestate->priorTuple != NULL) > + heap_freetuple(uniquestate->priorTuple); > + uniquestate->priorTuple = NULL; > return NULL; > + } > > /* > ! * Always return the first/last tuple from the subplan. > */ > if (uniquestate->priorTuple == NULL) > break; >
typea@l-i-e.com writes: > So I've finally gotten around to applying this patch for cursors... > http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/executor/nodeUnique.c > 1.34.2.1 Why would you think that patch alone would solve all the issues with running cursors backwards? It certainly will not make the world safe for backing up cursors on joins. 7.4 should fix this for you, otherwise not. regards, tom lane