in postgresql wire protocol, how can I reach ReadyForQuery from a partially executed select?

Question

I am trying to debug an issue with the `node-pg-cursor' module in node.js against a postgresql server (version 9.3)

This module allows for sequential reads of N rows in a select and works by sending

cur.read(N): 'Execute' on portal=unnamed, rows=N

this command fetches up to N rows and we can continue fetching rows incrementally until the end, where we receive

CommandComplete
ReadyForQuery

Now my problem is that I want to bail out of the extended command before fetching all the rows and reaching the end of the Execute sequence: I would like to incrementally fetch N rows, N rows, N rows,.. and at one point decide that I have enough.

When I do that (stop fetching via Execute), the query seem to never reach CommandComplete or ReadyForQuery. This seems normal since nothing tells the extended query that I am never going to ask rows from it again.

Apart from closing the connection, is there a command to reach CommandComplete, or ReadyForQuery while not fetching all the rows from the portal ?

I tried to send Close and received CloseComplete, but it did not go to ReadyForQuery.

If I force an ErrorResponse by sending garbage on the protocol, I reach ReadyForQuery but that does not seem very clean ...

Answer 1

I think you're referring to this, in the documentation:

If Execute terminates before completing the execution of a portal (due to reaching a nonzero result-row count), it will send a PortalSuspended message; the appearance of this message tells the frontend that another Execute should be issued against the same portal to complete the operation. The CommandComplete message indicating completion of the source SQL command is not sent until the portal's execution is completed. Therefore, an Execute phase is always terminated by the appearance of exactly one of these messages: CommandComplete, EmptyQueryResponse (if the portal was created from an empty query string), ErrorResponse, or PortalSuspended.

Presumably, you're getting PortalSuspended and you want to discard the portal without executing any more of it or consuming any more results.

If so, I think you can just send a Sync message:

At completion of each series of extended-query messages, the frontend should issue a Sync message. This parameterless message causes the backend to close the current transaction if it's not inside a BEGIN/COMMIT transaction block ("close" meaning to commit if no error, or roll back if error). Then a ReadyForQuery response is issued.

You may wish to issue a Close against the portal first:

The Close message closes an existing prepared statement or portal and releases resources.

so what I think you need to do is, in message flow terms:

• Parse
• Bind a named portal
• Describe
• Loop:
  • Execute with rowcount limit to fetch some rows
  • If no more rows needed; then
    • Close the portal
    • Break out of the loop
  • If CommandComplete received:
    • Break out of the loop
• Sync
• Wait for ReadyForQuery