search

brianc / node-pg-cursor

Query cursor extension for node-postgres
78 stars 30 forks source link

Ability to keep cursor open for later queries #15

Closed kgryte closed 8 years ago

kgryte commented 8 years ago

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?

brianc commented 8 years ago

Not really - a cursor is going to give you a transactional view of the data at the point in time when you initialize the cursor with the query. If you want to stream rows as they become live you'll have to build a postgres WAL parser + streaming library - definitely nothing I've built does that at this point. You might wanna look into using a message bus like kafka or nsq if you want to do alerting or streaming - postgres isn't really a streaming data system.

On Mon, Nov 2, 2015 at 12:51 PM, Athan notifications@github.com wrote:

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 https://github.com/brianc/node-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?

— Reply to this email directly or view it on GitHub https://github.com/brianc/node-pg-cursor/issues/15.

kgryte commented 8 years ago

Was not aware that creating a cursor is transactional. The use case is not alerting. More like a temporary solution to support a lambda-like architecture where event data is sent to two different dbs. Currently migrating event data from PG to a TSDB. Was hoping to minimize the amount of code needing to be written before cutover by pulling and transforming the data from PG. Will probably stream and then switch to polling once the stream ends.