Re: RFC: Async query processing

On 01/04/2014 01:06 AM, Claudio Freire wrote:
> You're forgetting ORM workloads.

I'm impressed that you've come up with an area where ORMs are beneficial ;-)

JDBC also has a statement batching interface. Right now PgJDBC just
unwraps the batch and runs each query individually. Any async-support
improvements server-side should probably consider the need of executing
a batch. The batch might be one PreparedStatement with many different
parameters, or it might be a series of unrelated statements. A way for
PgJDBC to run the batch without syncing with the server after each query
would be really helpful.

So would a way to BIND an array of parameters, so we could execute a
prepared statmenet once with multiple parameters and then sync up with
the server after all executions.

As for ORMs benefitting from this: Remember that nPgSQL and PgJDBC don't
use libpq. So the libpq changes would only help ORMs based on things
like Python (psycopg2), Ruby (Pg gem), etc, where they're using libpq
wrapper drivers.

> Execute-many of prepared statements is another one, quite common.

That's the case I'd really love to see proper server-side batch support
for. BIND_MULTIPLE, EXECUTE.

> I'm not sure what would happen if one of the queries returned an
> error. If in a transaction, all the following queries would error out
> I'd imagine. If not, they would simply be executed blindly.. am I
> correct?

It's not just dealing with erroring out. Many ORMs look at the count of
rows affected to detect whether an operation conflicted with another
concurrent operation when optimistic concurrency control is in use. E.g.

UPDATE t SET x = 'fred' WHERE rowversion = 4;

will be seen to "fail" if it reports that it's affected zero rows. This
is one of the reasons ORM users have such serious problems with the
write side of our partitioning support - we discard affected row counts,
and many ORMs don't deal well with that.

At least in JDBC, executeBatch returns an array of rowcounts. So you
can't throw away affected row counts when running batches, they must be
returned to the client. Doesn't matter if it's a single protocol message
with a list of IDs, or a series of individual messages, though.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services