[Libpqxx-general] pqxx::pipeline as a means of "posting" a call to a pl/pgSQL function

[Jeroen Vermeulen]

Peter Geoghegan wrote:

> I am considering doing the following. Please give opinions on this approach:

> When the time comes to call my pl/pgSQL function, something that
> occurs much less frequently then the SELECT queries, I use my
> program's pipeline object, which is also on the heap. I call its
> complete() , just in case it hasn't finished from last time
> (unlikely), and I then call its flush(), so that my pipeline doesn't
> eventually end up containing a whole bunch of pqxx::result containers
> that just contain voids. Finally, I call its insert(const
> PGSTD::string& q), and insert my query. It's possible, though
> unlikely, that the program would attempt to do another raw SELECT on
> the non-transaction object itself *before* the pl/pgSQL function
> called by the pipeline acting on that same non-transaction returns. Is
> this dangerous or impossible? Should I just do *everything* with the
> pipeline, and call its insert(const PGSTD::string& q) followed by its
> retrieve( query_id qid) for the raw SELECT queries?

The pipeline grabs the "transaction focus," that is, you can't execute 
regular queries, open other pipelines, use table streams etc. while it's 
still open.

So yes, better to go with the pipeline all the way.  That way you also 
don't need to call complete() or flush(); just retrieve the result from 
your procedure call once you actually need a result from a query that 
came after it.

In fact the pipeline will hold results that have arrived but not been 
retrieved yet.  So if you have a SELECT after your procedure call, you 
can retrieve the SELECT result when you really need it to move on, and 
then retrieve the procedure call's result as an afterthought!


> I have another concern. The pipeline class is implied to be unstable,
> as it is not "a stable part of the library". Is it unstable in the
> sense that it is subject to change, or in the sense that it is liable
> to cause problems, or both? Do you think that it would be reasonable
> to use it in a production system? These queries should never throw
> exceptions.

Unstable in the sense that I'm not saying the API won't change.  In 
practice it's been quite stable, but I think that's mainly because it's 
rarely used.  So you could be the primary driver of those changes if, as 
& when you need them!

In fact I'd love to hear what results you get from using the pipeline: 
does it simplify your program, save you real time etc.  So far the whole 
idea's usefulness has been one big guess for me.


Jeroen