Re: RFC: Async query processing

On Fri, Jan 03, 2014 at 04:46:23PM +0100, Florian Weimer wrote:
> On 01/03/2014 04:20 PM, Tom Lane wrote:
>
> >I think Florian has a good point there, and the reason is this: what
> >you are talking about will be of exactly zero use to applications that
> >want to see the results of one query before launching the next. Which
> >eliminates a whole lot of apps. I suspect that almost the *only*
> >common use case in which a stream of queries can be launched without
> >feedback is going to be bulk data loading. It's not clear at all
> >that pipelining the PQexec code path is the way to better performance
> >for that --- why not use COPY, instead?
>
> The data I encounter has to be distributed across multiple tables.
> Switching between the COPY TO commands would again need client-side
> buffering and heuristics for sizing these buffers. Lengths of runs
> vary a lot in my case.

Why switch between COPY commands, why could you not do it in one? For
example:

COPY table1(col1, col2, ...),
table2(col1, col2, ...)
FROM STDIN WITH (tableoids);
tableoid1<tab>col1<tab>col2...
tableoid2<tab>...
...
\.

There's no especially good reason why a COPY can only write to one
table. In this way you provide, per row, which table this row should go
to.

There's always the issue of generated primary keys. So you could, if
you wanted to, do:

COPY table1(col1, col2, ...) RETURNING pkey1,
table2(col1, col2, ...)
FROM STDIN WITH (tableoids);
tableoid1<tab>col1<tab>col2...
tableoid2<tab>\K <tableoid><tab>...
...
\.

So, like we have \N for NULL, a \K oid which be the value of the
RETURNING column for the table with that oid.

This may be way outfield, but we have a streaming interface, there's no
reason why we can't extend it.

There's also the idea of WITH x AS (COPY ... FROM STDIN) which you
could do with a similar multiplexing of rows in one stream. This would
be extremely powerful together with MERGE.

Have a nice way,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer