Re: [INTERFACES] Can a PGresult outlive a PGconn

"Bryan White" <bryan@arcamax.com> writes:
> I am doing some coding using libpq under Postgres 6.4.  I want to be able to
> cache the results of some queries.  These are mostly selects on small tables
> used to fill out combo boxes.  The tables don't change very often but get
> referenced a lot.

> My question is: is it better to execute the query and keep the PGresult
> around or extract the data from the PQresult into another structure?  A key
> point is that the PGconn will often be PQfinish'ed before the data is used.
> Does a PGresult remain valid after the PGconn that it is associated with is
> closed?  Does a PGresult keep any resources allocated in the backend?

You can keep a PGresult around longer than the source PGconn if you like
--- libpq won't stop you ;-).  The PGresult struct does contain a
pointer to the originating PGconn, but that pointer is only used to
access the PGconn's error message storage area and error processor
routine pointer, in order to generate complaints when an out-of-range
index is handed to PQgetvalue() and friends.  If you're certain your
code will never do that, you can safely keep the PGresult around longer
than the PGconn.

I don't particularly like the fact that PGresult has a pointer to the
originating PGconn --- I'd rather it was a totally independent object,
so that this sort of coding could be done with no fear.  That will
require changing the approach to error reporting in PQgetvalue() et al,
so I'm not quite sure how to handle it.

But to get back to your real question: is this a *reasonable* way to
proceed?  I kinda doubt it.  If you cache the data read from the
PGresult in a data structure of your own design, then you can do any
necessary parsing/conversion/preprocessing of the data before you put it
into that structure.  Even things as simple as converting numeric values
to int or float are worth doing before you cache the data, but you can't
do any of that if what you cache is the raw PGresult.

            regards, tom lane