FETCH doesn't work

[vitaly-t]

This is kind of reviving the old and forgotten #1137, but with new specifics.

---

I have two tables: users + products, and I have a function that returns two cursors for those:

CREATE or replace FUNCTION get_all() RETURNS SETOF refcursor AS
$BODY$
DECLARE
    u refcursor;
    p refcursor;
BEGIN
    OPEN u FOR
    SELECT * FROM users;
    RETURN NEXT u;

    OPEN p FOR
    SELECT * FROM products;
    RETURN NEXT p;
END
$BODY$ LANGUAGE plpgsql;

When I execute the following inside pgAdmin:

SELECT * FROM get_all();
FETCH ALL IN "<unnamed cursor 1>";
FETCH ALL IN "<unnamed cursor 2>";

it just works. Note - the unnamed cursors are updated on every run.

But when I do exactly the same in node-postgres, I'm getting this -

{ error: cursor "<unnamed portal 1>" does not exist
    at Connection.parseE (D:\NodeJS\tests\node_modules\pg\lib\connection.js:546:11)
    at Connection.parseMessage (D:\NodeJS\tests\node_modules\pg\lib\connection.js:371:19)
    at Socket.<anonymous> (D:\NodeJS\tests\node_modules\pg\lib\connection.js:114:22)
    at emitOne (events.js:115:13)
    at Socket.emit (events.js:210:7)
    at addChunk (_stream_readable.js:264:12)
    at readableAddChunk (_stream_readable.js:251:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onread (net.js:587:20)
  name: 'error',
  length: 102,
  severity: 'ERROR',
  code: '34000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'portalcmds.c',
  line: '168',
  routine: 'PerformPortalFetch' }

The cursor definitely exists, and I'm executing it against the same connection.

Why doesn't it work?

Do I really need the additional library to fetch the data?

Should it not be simpler, especially in the context of now supporting multiple results? One of the most important examples is to be able to return multiple cursors, i.e. when we now can concatenate several FETCH queries into one, execute it and presumably get all records from all the cursors fetched?

[vitaly-t]

As an update, I did try to use node-pg-cursor, but without success. I'm not even sure it supports reading cursors from their names, so I opened an issues there also: brianc/node-pg-cursor#34

[langpavel]

Did you try open transaction explicitly by BEGIN?

[SunilManthenaG01]

@vitaly-t did you make this one work ?

[vitaly-t]

@SunilManthenaG01 I can't remember what I did or didn't make work 4 years ago 😄

[gustavofiaschi]

Hi. I had the same issue.
The solution is to run SELECT and FETCHES over the same transaction.

[sampok]

@vitaly-t Did you try using a transaction? I've been successfully using FETCH with pg-promise, something like this:

db.tx(async (transaction) => {
  await transaction.any("DECLARE my_cursor CURSOR FOR SELECT * FROM table");

  let rows = [];
  do {
    rows = await transaction.manyOrNone("FETCH 100 FROM my_cursor");
    // do something with the batch
  } while (rows.length > 0);
});

Also according to PostgreSQL docs it should be possible to create cursors outside transactions if you use WITH HOLD, but then you need to remember to manually close them.

With this working, do you think there's any performance or other reason to use pg-cursor?

[vitaly-t]

@sampok It's a 5-year old issue, I cannot recall anything about it at this point. But if it works for you, great, maybe will help somebody else ;)