Jesus Gomez Jesus Gomez - 2 months ago 25
Node.js Question

Sequelize and WITH HOLD cursors

I'm trying to implement cursor pagination with postgresql and sequelize, I have tried with success creating a cursor WITH HOLD on a transaction and fetching data from it.

sequelize.transaction(function (t) {

const query = "DECLARE my_cur CURSOR WITH HOLD FOR SELECT foo, bar FROM x " +
"WHERE time=\'2016-09-16\'::date;"

return sequelize.query(query, {transaction: t}).spread(function (results,metadata) {
console.log("Cursor created!")
});

}).then(function (result) {
console.log(result)
console.log("Transaction commited")
sequelize.query("FETCH NEXT FROM my_cur").spread(function (results,metadata) {
console.log("Fetching from previously created cursor:")
console.log(results)
console.log(metadata)
}).catch(function(err){
console.log("Failed to fetch from cursor")
console.log(err)
});
}).catch(function (err) {
throw err
console.log("Failed to create a cursor")
});


If I try fetching from the cursor in a different session I get:

FETCH NEXT FROM my_cur;
ERROR: <<my_cur>> doesn't exist.


Cursors even if declared WITH HOLD are destroyed after a session closes and aren't shared between sessions, my question is, how does sequelize handle sessions with postgreSQL, will I be able to FETCH from this cursor on a separate API call?

socket.on("fetch_next_page", function()){
var cursor = socket.session.cursor
var pageSize = 10
sequelize.query("FETCH +"pageSize"+ FROM my_cur").spread(function (results,metadata) {
console.log("Fetching from previously created cursor:")
console.log(results)
socket.emit("page",results)
console.log(metadata)
}).catch(function(err){
console.log("Failed to fetch from cursor")
console.log(err)
});
}

Answer

The easiest way to execute a sequence of queries within the same session is via a task or transaction as implemented within pg-promise.

See Tasks and Transactions.

You would either promise-chain your queries, if they have a dependency between them, or execute them as a batch, if they don't.

And if you need to iterate N times inside the session, like calling FETCH till certain condition, you can also make use of method sequence.