node-pg does not allow using connection while cursor is open.
Here an example scenario which works with some other database libraries but with node-pg.

1. create connection > 2. start transaction > 3. create cursor > 4. execute update > 5.commit > 6.close cursor > 7. close connection.

application is getting locked in 4. step. it waits until cursor is closed. This limits usage.

Is there any way to get a total count of the records returned by the query? With node-postgres you would just get result.rowCount but I don't see a way to access that property with node-pg-cursor.

Following this issue: brianc/node-postgres#1476

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 query SELECT * FROM get_all(), I am getting the following data back:

[ anonymous { get_all: '<unnamed portal 1>' },
  anonymous { get_all: '<unnamed portal 2>' } ]

How can I initiate a cursor read from the cursor names, using this library?

Please note that when I am executing the following in pgAdmin:

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

I am getting all the data correctly for the 2 tables.

can you throw some light on how cursor works here ? In my usecase i have some 100 million rows over which i need to process and order by a column .. so my query is ,will that happen everytime when i fetch next row of cursor or postgres uses some temporary table to store that processed data

Hi, I just tried to use pg-cursor with a simple select:

/foo/tools/lib/node_modules/pg-cursor/index.js:51
setImmediate(function() {
^
ReferenceError: setImmediate is not defined
at Cursor._sendRows (/foo/tools/lib/node_modules/pg-cursor/index.js:51:3)
at Cursor.handlePortalSuspended (/foo/tools/lib/node_modules/pg-cursor/index.js:64:8)
at Client.connect.con.once.con.on.self.constructor.port (/foo/tools/lib/node_modules/pg/lib/client.js:112:24)
at EventEmitter.emit (events.js:96:17)
at Connection.attachListeners (/foo/tools/lib/node_modules/pg/lib/connection.js:97:12)
at Socket.EventEmitter.emit (events.js:96:17)
at TCP.onread (net.js:397:14)

According to the API docs, the result from any of the client.query(...) variants is always a query object.

However, when using a Cursor, the result has none of the events prescribed by the API. In fact, it is not even an event emitter. This is not a purely academic issue as querying with a cursor causes an error in newrelic instrumentation which is built around this spec.

Do you agree that the query(Cursor) method should conform to the PG API spec by returning an EventEmitter? If so would you accept a PR to add event emitter behavior and the emit the required Query events from the Cursor object?

Hey Brian,

I tweeted you about trying to get next results and you asked me to gist and open an issue here. I already solved my problem in code, but I wasn't able to us pg-cursor to do it.

Basically I had this:

async function test() {
  console.log("THIS IS THE TEST METHOD");
  const client = await pool.connect();
  const renditionsCursor = client.query(new Cursor("select * from object_stacks"));
  renditionsCursor.read(10, (err, rows) => {
      if (err) {
          throw err;
      }
      for (var row of rows) {
          console.log(`Checking: ${row.object_name}`);
      }
      renditionsCursor.read(10, (err, rows) => {
          for (var row of rows) {
              console.log(`Checking: ${row.object_name}`);
          }
      });
  });
}

And I was trying to figure out a way to programmatically do another renditionsCursor.read() and drain the cursor. The scenario is that I have about 1.5million rows I wanted to process and I thought pg-cursor might be able to help. It did seem like it could do the job but at the fault of my own I was unable to accomplish the goal with pg-cursor. I tried several tactics but I was never able to have one block of renditionsCursor.read() and then iterate it several times to get the next result sets.

On your NPM wiki you have a comment that says:
//Also - you probably want to use some sort of async or promise library to deal with paging //through your cursor results. node-pg-cursor makes no asumptions for you on that front.

So I was trying to use async/await to handle this but I just couldn't get it to grab the next results. Not really a big deal as I got this taken care of but I was curious to see if you've implemented what I was trying to do or if you have seen any examples of other people implementing the next results scenario.

Thanks for your time.

Hi @brianc

Not sure if I'm using pg cursor incorrectly with pg pool or if I discovered a valid edge case.

It seems that calling cursor.close on an exhausted cursor using a pool connection results in an uncaught TypeError exception within the pg module. What is strange about this is that it only seems to happen when the pool is saturated with other queries? Calling cursor.close on an exhausted cursor using a connection from a single-connection pool does not seem to experience the issue.

I've gone ahead and created some tests that showcase this behavior in #26 .

If the error is due to incorrect use of the 2 modules together, or if they are simply not compatible let me know and I'll close this issue and related pr.

Thanks for all your work on node-pg things!

Not sure where/how to PR documentation updates, or whether it is possible at all.

This code below in the documentation:

cursor.read(100, function (err, rows) => {
  cursor.close(() => {
    client.release()
  })
})

is invalid JavaScript for the callback function. You cannot combine function style with => arrow style.

Is it possible to use this module with your node-pg-query module?

The .read method would be great if it returned a Promise instead of void to handle all the errors and resolved on the Cursor's 'done' state. I can maybe add this on the weekend if you think it's a valuable addition. Adding it to the current method might be considered "breaking" the API, but no one should be using the return type anyway, so you could reuse the method.

I could be doing something wrong in my error handling, but it appears that pg-cursor doesn't behave nicely if there's an error with the initial query. Specifically, the connection is not freed immediately, or at all. See demo below.

In this particular case it would actually be useful to obtain the error immediately, without waiting for a cursor.read() call. For example, a callback parameter to the Cursor constructor (or the query call) that is called when we know the status of the query, without retrieving any rows.

var pg = require('pg');
var Cursor = require('pg-cursor');
var conString = "postgres://username:password@localhost/database";

pg.connect(conString, function(err, client, done) {
    if (err) {
        return console.error('error fetching client from pool', err);
    }

    var cursor = client.query(new Cursor('SELECT * FROM invalid')); // This query is invalid
    cursor.read(10, function(err, rows) {
        if (err) {
            console.log("Found an error"); // Base Test (never exits)
       /* // ALT Test: If I call done() instead of cursor.close() the script will exit, but only after a 30-second delay (some sort of timeout I'd assume).
            console.log("Found an error, calling done()");
            done();
            return;
        */
        } else {
            console.log("No error");
        }
        // If we reach this point with a cursor.read err, cursor.close's callback will never be called and the script will not exit
        cursor.close(function(err) {
            if (err) {
                console.log("Close Error");
            } else {
                console.log("Close Succeeded, calling done");
                done();
            }
        });
    });

});

It seems that if the query is badly written and error occured, the handleError does not clean up the listener for the said events.

We're encountering an issue with cursors on Redshift, which seems to manifest itself specifically when we only request a single row from the cursor. Requesting more than 1 seems to be working fine and as expected.

The full error:

{ error: Invalid protocol sequence 'P' while in PortalSuspended state.
    at Connection.parseE (.../node_modules/pg/lib/connection.js:604:11)
    at Connection.parseMessage (.../node_modules/pg/lib/connection.js:401:19)
    at TLSSocket.<anonymous> (.../node_modules/pg/lib/connection.js:121:22)
    at TLSSocket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:283:12)
    at readableAddChunk (_stream_readable.js:264:11)
    at TLSSocket.Readable.push (_stream_readable.js:219:10)
    at TLSWrap.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  name: 'error',
  length: 143,
  severity: 'ERROR',
  code: 'XX000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: '/home/ec2-user/padb/src/sys/xen_execute.cpp',
  line: '8205',
  routine: 'pg_throw' }

We're not 100% convinced that just requesting always a minimum of 2 rows is necessarily a real fix for this, so would love to hear any thoughts if you have them! It's also somewhat flaky, it doesn't always happen, and happens for a mixture of different query types.

I have a use case where I want to stream an entire table in order to migrate the data to another database and also continuously query (poll) the same table and stream any new rows (shadow).

I created a Stream constructor similar to that outlined in pg-query-stream, but which polls and is meant to never close, even when a query does not return any more results.

Based on the README,

If the cursor has read to the end of the result sets all subsequent calls to cursor#read will return a 0 length array of rows.

this use case does not seem to be supported by pg-cursor. I believe this stems from how state is handled: https://github.com/brianc/node-pg-cursor/blob/master/index.js#L149.

Any way to keep a cursor open and stream results if and when they become available?

so, i have some migrations that opens a stream to generate some work, and then goes on later to run some alter statements. something like this:

  const work = [];
  for await (const form of db.select('*').from('forms').stream()) {
    work.push(
      db.insert({
        formId: form.id,
        xml: form.xml,
        hash: form.hash, sha: shasum(form.xml), sha256: sha256sum(form.xml),
        version: form.version,
        createdAt: form.createdAt
      }).into('form_defs')
    );
  }
  await Promise.all(work);

  /* a lot of other stuffs that all run fine.. */

  await db.schema.table('forms', (forms) => {
    forms.integer('currentDefId');

    forms.foreign('currentDefId').references('form_defs.id');

    forms.dropUnique([ 'xmlFormId', 'version', 'projectId' ]);
    forms.dropColumn('xml');
    forms.dropColumn('version');
    forms.dropColumn('hash');
  });

this all works fine with pg-query-stream 1.1.2 / pg-cursor 1.3.0. but if i upgrade both to 2.0.0, i get the following error:

  1) "before all" hook: initialize:
     alter table "forms" add column "currentDefId" integer - cannot ALTER TABLE "forms" because it is being used by active queries in this session
  error: cannot ALTER TABLE "forms" because it is being used by active queries in this session
      at Connection.parseE (node_modules/pg/lib/connection.js:604:11)
      at Connection.parseMessage (node_modules/pg/lib/connection.js:401:19)
      at Socket.<anonymous> (node_modules/pg/lib/connection.js:121:22)
      at addChunk (_stream_readable.js:294:12)
      at readableAddChunk (_stream_readable.js:275:11)
      at Socket.Readable.push (_stream_readable.js:210:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:166:17)

if i manually dig into node_modules and set a portal of '' as it was before, everything starts working again. i have postgres dumping all queries to a logfile and there is no discernible difference in the queries being executed.

is there some way i can work around this problem? i am only trying to upgrade because there is a memory leak issue i've been having with Buffers returned over the stream that seems to go away magically when i upgrade to 2.0.0. but when i do, the migrations don't work anymore.

thanks! i am happy to open a PR given guidance on where to go with this. i am not sure why setting the portal label causes a problem.

I'm running into similar memory issues with pg-cursor and pg-query-stream. It seems like the cursors aren't being used, and I'm getting everything at once, which is resulting in a JavaScript heap out of memory error.

My query results in 1 million+ rows. It seems pg-cursor doesn't care the number of rows I give it to read, because it always returns the maximum number of rows. For a query like:

SELECT user
FROM events
ORDER BY created LIMIT 500;

I call cursor.read(100, (err, rows) => console.log(rows.length)) gives 500 instead of the expected 100.

Likewise, with pg-query-stream, my Node process eventually runs out of memory. I am using the latest versions of pg, pg-query-stream and pg-cursor. The only thing I can think of that might be throwing things off is that this is inside a Docker container.

function Cursor (text, values, config) - Cursor has config and rowMode, but this is not mentioned in docs

Obviously it's not the purpose of pg-cursor, but sometimes you do not know type of query in advance, but need to implement a common query execution logic.

If noData (INSERT/UPDATE/DELETE without RETURNING clause) queries are run through cursor then dangling rowDescription events are left on connection. It leads to Node,js memory leak warning.

Simple solution is to clear rowDescription on noData event, like:

  const ifNoData = () => {
    this.state = 'idle'
    this._shiftQueue()
    con.removeListener('rowDescription', onRowData)
  }
  
  const onRowData = () => {
    con.removeListener('noData', ifNoData)
  }

  if (this._conf.types) {
    this._result._getTypeParser = this._conf.types.getTypeParser
  }

  con.once('noData', ifNoData)
  con.once('rowDescription', onRowData)

However, it seems the code is not completely safe. We need to ensure that all introduced event handlers are removed upon cursor close by (error, noData, done, forced close, etc.)

Hi Brian -

Would it be possible for this module to support multiple commands? Currently multiple-statement query text throws an error.

Here's a little test code snippet I was trying to get working with no luck:

  it('handles multiple commands', function(done) {
    var sql = "CREATE TEMPORARY TABLE temptable (word VARCHAR(10)); "
            + "INSERT INTO temptable (word) VALUES ('one'),('two'),('three'); "
            + "SELECT * FROM temptable; "
    var cursor = this.pgCursor(sql)
    cursor.read(10, function(err, res) {
      assert.ifError(err)
      assert.equal(res.length, 3)
      done()
    })
  })

Thanks for all your work on node-postgres!

Currently, the only publicly available API for Cursor data is Cursor#read, which returns the rows read. For my application, I need access to the column information in the result set which is only (as far as I can tell) available via the private property _result.fields. Could fields be exposed somehow exposed via the Cursor API? I realize that there is some tricky timing involved so I'm very flexible on how this is exposed. Would you accept a PR for this change?

Sorry if ignorant here. Since cursors open a transaction, I assume it will be left to the query planner to determine the best way to execute.

Just wondering about the server performance impact of doing a large cursor query with some joins. It should make an initial up-front temporary table if the result is large enough, then stream from that temp result set, right? It's not going to re-open a new transaction every time?

that is, is this a client-side only cursor, or does it issue a server-side cursor as well?

Hi,

I'm getting an error when trying to use the Cursor. I'm sure I'm doing something obviously wrong, but I'm at a loss. Mind taking a look?

https://gist.github.com/blissdev/25784b641d6ca41d4f7e

Thanks!

Hello,

I am using node-pg-cursor combined generic-poolwith and have encountered a problem today :

When I call read(100, cb) until the end of the cursor, everything works fine. After releasing the connection to the pool, it is reused correctly.

but if I stop reading the cursor and release the connection to the pool, when the pool re-attributes this connection it does not work. Something freezes and I have yet to understand what.

I am nearly sure that the problem comes from the fact that the cursor has not been fully read but I cannot find a way to cancel or terminate the cursor before releasing it to the pool.

In your example, you seem to always call done() and as I understand it it closes the connection.

Have you run into this issue and do you know how I can cleanly terminate the execution of the query ?

calling cur.end() does not seem to work because generic-pool expects to end the connections by itself after an idleTime. I haven't yet explored this thoroughly and I suspect that there should be a way to terminate the cursor cleanly instead of closing the connection.

Thanks for your help

Streams are widely supported concept. There are many libraries to operate on streams, manage backpressure, map, filter results, pause them etc.

Moreover, since Node 10 stream.Readable automatically support asynchronous iteration protocol.

Hello,

I am trying to call a stored proc from node which returns a refcursor, ideally I would like to FETCH ALL from the refcursor and utilize that result set for my front end dashboard data population.

Please let me know if this feature is supported and/or if there are any other packages that offer this functionality.

Regards,
Guru

Hello,

I have a quick question about how this cursor implementation works. Currently, it seems to me that it could be implemented in the following ways:

1. Cursor retrieves rows and then provides pagination over those rows.
2. Cursor fetches rows as needed in a lazy fashion.

Is one of these two understandings correct?

Thank you,
Eric

I get this error with webpack 4.16.5:

    ERROR in ./node_modules/pg-cursor/pg.js
    Module not found: Error: Can't resolve 'pg.js/lib/result.js' in 'C:\Users\Andreas\workspace\ubiq_reports\server\node_modules\pg-cursor'
     @ ./node_modules/pg-cursor/pg.js 8:26-56
     @ ./node_modules/pg-cursor/index.js
     @ ./report_main.js
     @ multi ./report_main.js

This problem has been around for a while, as this comment from 2017 attests:
af84d5c#commitcomment-22901440

Has anybody used this with Amazon Redshift? I use the pg module with great success and have tried this cursor module as well. Everything functions and I can read from the cursor but the entire result set is loaded into RAM when the query is executed so it doesn't solve my problem.

Hi,

I am looking for a way to accomplish select for updates in Postgresql from NodeJS.

Your module seems quite good for reading a cursor, but is there a way to update the cursor ?

I have looked your code, I didn't find a direct way of doing that. Probably a solution is to read Postgres protocol and implement something.

Have you a plan to add this fonctionnality ?

Many thanks anyway,

Olivier

Hi,

Is this module at all related to http://www.postgresql.org/docs/9.1/static/plpgsql-cursors.html ?
(i.e. is the OPEN ... mechanism used under the hood at all?)
I could not really see any query text in this module.

If so, it seems there ought to be some API for moving through the results - rewind, move forward, etc.

Is there a way to get cursor support on typescript?

currently getting this error:

new _pgCursor.Cursor(sqlQuery, '');
^

TypeError: _pgCursor.Cursor is not a constructor

Hello,
I was trying to use this against a redshift instance with the following test script:

const { Pool } = require('pg');
const config = require('./src/lib/config');
const credentials = config.get('warehousedb');

const pool = new Pool({
    user: credentials.user,
    host: credentials.host,
    database: credentials.name,
    password: credentials.pass,
    port: credentials.port
});

function consumeCursor(cursor, cb, done) {
    console.log('consumeCursor invocation');
    cursor.read(100, (error, rows) => {
        if (error) {
            done(error);
            return;
        }

        if (rows.length < 1 ) {
            console.log('Cursor is empty, all done!');
            done();
            return;
        }

        cb(rows);
        consumeCursor(cursor, cb, done);
    });
}

const query = 'select * from <table_name> LIMIT 1000';

pool.connect().then(function(client) {
    console.log('creating cursor');
    const cursor = client.query(new Cursor(query));

    function done(error) {
        if (error) {
            console.error('Cursor boom: ', error);
        }

        console.log('done!');
        cursor.close(() => client.release());
    }

    function push(result = []) {
        console.log('Got results: ', result.length);
    }

    consumeCursor(cursor, push, done);
});

and behold of the result:

creating cursor
consumeCursor invocation
Got results:  1000
consumeCursor invocation
Cursor is empty, all done!
done!

So here's what's confusing me:

• As shown from Got results: 1000 I guess Redshift might not be playing ball?
• Now, why Got results: 1000 if I'm asking to read 100 items? I tried removing the LIMIT 1000 as well but there was no change.

I haven't got any time yet to read node-pg-cursor yet so I def might be miss-understanding something, so apologie for something I might have missed.