Re: Complex cursor won't rewind to 0-th row

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(at)l-i-e(dot)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;
>