Performing FETCH ALL from a SCROLL CURSOR failing to return results

From: Eliot Gable <egable+pgsql-general(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Performing FETCH ALL from a SCROLL CURSOR failing to return results
Date: 2010-03-25 21:10:46
Message-ID: bf6923ed1003251410hf49b170j7f73989af579fe9c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This issue is on PostgreSQL 8.4.1 under CentOS 5.3 x86_64.

I have a scroll cursor defined like so:

source_host SCROLL CURSOR IS
SELECT ....;

Inside my PL/PGSQL stored procedure, I am opening the cursor like so:

OPEN source_host;
FETCH source_host INTO src_host;
result.source_host_refcurs := source_host;
...
blah blah blah
....
RETURN result;

Then, I execute the stored procedure like this:

SELECT * FROM MyStoredProc(blah);
FETCH ALL FROM source_host;

The stored procedure returns a complex data type (result) with a refcursor
set up as source_host. When I use the "FETCH ALL" syntax, I get no results.
However, if I use any of these, I get the one and only record that is
returned:

FETCH FIRST FROM source_host;
FETCH LAST FROM source_host;
FETCH ABSOLUTE 1 FROM source_host;

Any of these fail:

FETCH NEXT
FETCH PRIOR
FETCH RELATIVE x where x is any number
FETCH x where x is any number
FETCH ALL
FETCH FORWARD
FETCH FORWARD x where x is any number
FETCH FORWARD ALL
FETCH BACKWARD
FETCH BACKWARD x where x is any number
FETCH BACKWARD ALL

Now, if I comment out the 'FETCH source_host INTO src_host' line inside the
stored procedure, then ALL of these work:

FETCH FIRST
FETCH LAST
FETCH ABSOLUTE x
FETCH RELATIVE x
FETCH NEXT
FETCH ALL
FETCH FORWARD
FETCH FORWARD x
FETCH FORWARD ALL
FETCH x

I have attempted to perform a MOVE FIRST aftering doing the 'FETCH
source_host INTO src_host' line, as well as MOVE LAST, MOVE ABSOLUTE 1, etc.
No attempt at doing a MOVE allows the FETCH ALL and the like to work. Only
FETCH FIRST, FETCH LAST, and FETCH ABSOLUTE seem to work after I have
touched the cursor inside the stored procedure. In fact, I can remove the
'FETCH source_host INTO src_host' line and replace it with a MOVE statement
and it results in the same problem.

I absolutely need to have FETCH ALL working. I don't care about anything
else other than FETCH ALL. I actually have about 10 cursors that are
returned like this from the stored procedure, and most of them have several
dozen records that need to be retrieved. I execute a single transaction
where I run the stored procedure and fetch all results all at once. This was
working just fine a couple of days ago. Not sure what broke.

If anyone has any ideas on what might be going wrong here, I would really
appreciate some assistance.

Thanks in advance.

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be a
crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not
live to eat.) ~Marcus Tullius Cicero

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Fetter 2010-03-25 21:17:31 Re: [GENERAL] question (or feature-request): over ( partition by ... order by LIMIT N)
Previous Message Frans Hals 2010-03-25 20:31:40 Re: Large index operation crashes postgres