Re: Proposed new libpq API

The Hermit Hacker wrote:

> What is the PQflush() for here?  I took it to mean that it was required,
> but then reading further down, it just sounds like it flushs what's
> already been used and would be optional?
> 
> Doesn't this just do what CURSORs already do then?  Run the query, fetch
> what you need, etc?

There is similarity to cursors, but there is no need to go to the
trouble of using a cursor to get a lot of the benefits which is that you
don't have to slurp it all into memory at once. I believe this is how
most DBMS interfaces work, like MySQL, you can only fetch the next
record, you can't get random access to the whole result set. This means
memory usage is very small. Postgres memory usage will be huge. It
shouldn't be necessary to resort to cursors to scale.

So what PQflush is proposed to do is limit the amount that is cached. It
discards earlier results. If you flush after every sequential access
then you only have to use enough memory for a single record. If you use
PQflush you no longer have random access to earlier results.

Other designs are possible, like some interface for getting the next
record one at a time and examining it. The idea of this proposal is to
make the current random access interface and a streaming interface very
interoperable and be able to mix and match them together. You can take a
current postgres app, and provided it doesn't actually rely on random
access, which I would hazard to say most don't, and just by adding the
one line of code PQflush greatly reduce memory consumption. Or you can
mix and match and see a sliding window of the most recent X tuples. Or
you can just ignore this and use the current features.