Re: [HACKERS] Re: [INTERFACES] retrieving varchar size - Mailing list pgsql-interfaces

>
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Oh.  That prevents us from changing the backend to ignore returning more
> > than one result for multiple queries in a PQexec.  Perhaps we need a new
> > return query protocol character like 'J' to denote query returns that
> > are not the LAST return, so libpq can throw them away, and jdbc and
> > process them as normal, but also figure out when it gets the last one.
>
> That would require the code processing an individual command in the
> backend to know whether it was the last one or not, which seems like
> a very undesirable interaction.

I think it is pretty easy to do.

> Instead, I'd suggest we simply add a new BE->FE message that says
> "I'm done processing your query and am returning to idle state".
> This would be sent at the end of *every* query, correct or failing.
> Trivial to implement: send it at the bottom of the main loop in
> postgres.c.
>

If you are happy with this, it is certainly better than my idea.

> The more general question is whether we ought to redesign libpq's API
> to permit multiple command responses to be returned from one query.
> I think that would be a good idea, if we can do it in a way that doesn't
> break existing applications for the single-command-per-query case.
> (BTW, I'm defining "query" as "string handed to PQexec"; perhaps this
> is backwards from the usual terminology?)
>

My idea is to make a PQexecv() just like PQexec, except it returns an
array of results, with the end of the array terminated with a NULL, sort
of like readv(), except you return an array, rather than supplying one,
i.e.:

    PGresult *resarray;
    resarray = PQexecv('select * from test; select * from test2');

and it handles by:

    PGresult *res;
    for (res = resarray; res; res++)
        process_result_and_clear(res);
    free(resarray);

You also have to free the array that holds the result pointers, as well
as the result pointers themselves.

> Maybe have libpq queue up the results and return the first one, then
> provide a function to pull the rest from the queue:
>
>     result = PQexec(conn, query);
>     // process result, eventually free it with PQclear
>     while ((result = PQnextResult(conn)) != NULL)
>     {
>         // process result, eventually free it with PQclear
>     }
>     // ready to send new query
>
> An app that didn't use PQnextResult would still work as long as it
> never sent multiple commands per query.  (Question: if the app sends
> a multi-command query and doesn't call PQnextResult, the next PQexec
> will know it because the result queue is nonempty.  Should PQexec
> complain, or just silently clear the queue?)

With my idea, we can properly handle or discard multiple results
depending on whether they use PQexec() or PQexecv().

> One thing that likely would *not* work very nicely is copy in/out
> as part of a multi-command query, since there is currently no provision
> for PQendcopy to return result(s).  This is pretty braindead IMHO,
> but I'm not sure we can change PQendcopy's API.  Any thoughts?  What
> I'd really like to see is PQendcopy returning a PGresult that indicates
> success or failure of the copy, and then additional results could be
> queued up behind that for retrieval with PQnextResult.

Not sure on this one.  If we change the API, we have to have a good
reason to do it.  API additions are OK.

>
> >>>> Other front-end libraries reading this protocol will have to change
> >>>> to accept this field.
>
> And the end-of-query indicator.  I think now is the time to do it if
> we're gonna do it.  Right now, it seems most code is using libpq rather
> than seeing the protocol directly, so fixing these problems should be
> pretty painless.  But wasn't there some discussion recently of running
> the protocol directly from Tcl code?  If that gets popular it will
> become much harder to change the protocol.

Yep, let's change it now.

>
> As long as we are opening up the issue, there are some other bits of
> bad design in the FE/BE protocol:
>
> 1. 'B' and 'D' are used as message types for *both* result tuples and
> StartCopyIn/StartCopyOut messages.  You can only distinguish them by
> context, ie, have you seen a 'T' lately.  This is very bad.  It's not
> like we have to do this because we're out of possible message types.

Yep, let's use distinct ones.

>
> 2. Copy In and Copy Out data ought to be part of the protocol, that
> is every line of copy in/out data ought to be prefixed with a message
> type code.  Fixing this might be more trouble than its worth however,
> if there are any applications that don't go through PQgetline/PQputline.

Again, if we clearly document the change, we are far enough from 6.4
that perl and other people will handle the change by the time 6.4 is
released.  Changes the affect user apps is more difficult.

> BTW, I have made good progress with rewriting libpq in an asynchronous
> style; the new code ran the regression tests on Friday.  But I haven't
> tested any actual async behavior yet.

Good.  You may need a patch from me for the backend before you can test
some of your changes.  Let me know what you decide, and I will send you
a patch for testing.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)