[Libpqxx-general] Pipeline question

[Dragon Master]

Is there a way to use within a pipeline libpqxx's facilities for executing
prepared statements? The dire warnings about manually executing prepared
statements using SQL seems to preclude that use within a pipeline. Is
there some way to do it, and if not, should there be?

Thanks in advance.

[Jeroen Vermeulen]

Post by Dragon Master
Is there a way to use within a pipeline libpqxx's facilities for
executing prepared statements? The dire warnings about manually
executing prepared statements using SQL seems to preclude that use
within a pipeline. Is there some way to do it, and if not, should there be?

Frankly I don't even know what happens if you use libpqxx's built-in
facility for prepared statements. AFAICS it could sort of work, as long
as you don't unprepare the statement while the pipeline exists, though
it's never been a priority to make sure that it does.

There are really two reasons why things are so strict. First, some
actions can't conveniently be redirected through a pipeline so when
combined with pipelines they could happen in a different order than you
request them. Second, connections may break and be recovered
transparently between transactions (or statements within a
nontransaction), so the PREPARE you just issued may in fact have been on
a different connection than the one you're doing the EXECUTE on.

You can issue a PREPARE "manually" through the pipeline, as long as you
avoid these two hazards: start a proper transaction (i.e. not a
nontransaction), call prepare_now() on the connection to ensure that
your statement has been defined, and then create your pipeline.


Jeroen

[Dragon Master]

Post by Dragon Master
Is there a way to use within a pipeline libpqxx's facilities for
executing prepared statements? The dire warnings about manually
executing prepared statements using SQL seems to preclude that use
within a pipeline. Is there some way to do it, and if not, should there be?

Frankly I don't even know what happens if you use libpqxx's built-in facility
for prepared statements. AFAICS it could sort of work, as long as you don't
unprepare the statement while the pipeline exists, though it's never been a
priority to make sure that it does.
There are really two reasons why things are so strict. First, some actions
can't conveniently be redirected through a pipeline so when combined with
pipelines they could happen in a different order than you request them.
Second, connections may break and be recovered transparently between
transactions (or statements within a nontransaction), so the PREPARE you just
issued may in fact have been on a different connection than the one you're
doing the EXECUTE on.
You can issue a PREPARE "manually" through the pipeline, as long as you avoid
these two hazards: start a proper transaction (i.e. not a nontransaction),
call prepare_now() on the connection to ensure that your statement has been
defined, and then create your pipeline.

While informative, your response didn't really answer my question.
Apparently it was even more awkwardly worded than I knew...

I believe, after studying the code, that the answer is no, and the missing
API would look something like this:

pqxx::pipeline::query_id
pqxx::prepare::invocation::insert(pqxx::pipeline &pipe);

Does that clarify my question? So the question then becomes, is it
feasible to implement such a method? Without digging into the guts of
gate::connection_prepare_invocation(), I couldn't say.

[Jeroen Vermeulen]

Post by Dragon Master
I believe, after studying the code, that the answer is no, and the
pqxx::pipeline::query_id
pqxx::prepare::invocation::insert(pqxx::pipeline &pipe);

Indeed, the answer is no, but I believe it could be made to work. A
solution I toyed with once upon a time, but never implemented, was to
isolate an internal layer of abstraction that, essentially, pipelined
everything that libpqxx does internally. Another approach would be to
adapt any individual feature (such as prepared statements) to interact
with pipelines as needed.

For the immediate problem of using prepared statements inside a pipeline
though, you can use the workaround in my previous post.

For the record, I would expose a combination of prepared statements and
pipelines in the pipeline API, not the prepared-statements API. So
you'd say "pipe.insert(conn.prepared("foo"))" rather than
"conn.prepared("foo").insert(pipe)."

Post by Dragon Master
Does that clarify my question? So the question then becomes, is it
feasible to implement such a method? Without digging into the guts of
gate::connection_prepare_invocation(), I couldn't say.

I think it is feasible and it might be nice to have this work. There
are some limitations: libpq probably won't let us prepare or unprepare a
statement while a pipeline has statements in progress. (In truth
libpqxx can also generate PREPARE statements, to support ancient libpq
versions, but that's code I would dearly like to be rid of).

The simplest solution to that is to wait and serialize whenever a
statement needs to be prepared or deallocated; a more advanced solution
would defer it until a suitable time. And with recent libpq versions,
it could even prepare statements asynchronously.


Jeroen