How do I use a server side cursor to postgresql using Go?

[saolof]

Most ORMs I have used provide some way of choosing between a client side cursor (which fetches everything and caches it locally) or a server side cursor (which keeps an open connection as you scroll through the data). The former is the sane default the vast majority of the time, while the latter is important to use when the output of a join is larger than RAM and you want to stream it over using a buffered stream of some kind.

What is the most straightforward way to do the latter using pgx (or some other common library including database/sql )? The desired behaviour is to have the program keep a connection open where it keeps streaming the data over while doing a consistency check between a DB with an index of a filesystem and the actual filesystem it refers to.

[jackc]

That depends on what you mean by cursor. If you mean a true server side cursor using DECLARE and FETCH then you call the normal Exec, Query, and QueryRow methods with those SQL statements.

But I'm guessing that's not what you mean. I think what you are asking is for the client to only store / process one row at a time. This is the default behavior of pgx whether in its native interface or through database/sql. Do the normal rows.Next() / rows.Scan() and you will have minimal client side memory usage.

[saolof]

The desired behaviour is for it to run a single query (that returns a large result set) and process that chunk by chunk , to replace a previous system that achieves that LIMIT and OFFSET to get a set number of pages of the query at once, which is quadratic for large result sets since OFFSET runs the entire query and skips past the first part while still having to do the work to produce them. My first thought about the underlying implementation would be the FETCH <count> form of fetch but I'm agnostic about how it's implemented.

So it is literally the default behaviour? That would be perfect! Does the Postgres driver (for this and/or for database/sql) do some reasonable amount of work buffering the rows behind the scenes to avoid network round trips, but without fetching everything?

[jackc]

There is only one round trip. The query is sent and PG will send all the results back. The driver will only read 8KB at a time, beyond that it is standard TCP.

[saolof]

So what does the driver do if the query returns, say, 20 GB of results? The database sends it 8KB at a time on a pull basis as you iterate over the rows?

[jackc]

The driver handles 8KB at a time. Everything else is TCP.