So I've finally gotten around to applying this patch for cursors...

http://developer.postgresql.org/cvsw...r/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:

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

typea (AT) l-i-e (DOT) com writes:
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend