Hello devs,
As pointed out by Kyotaro Horiguchi in
https://www.postgresql.org/message-id/20190726.131704.86173346.horikyota.ntt@gmail.com
FETCH_COUNT does not work with combined queries, and probably has never
worked since 2006.
What seems to happen is that ExecQueryUsingCursor is hardcoded to handle
one simple query. It simply inserts the cursor generation in front of the
query believed to be a select:
DECLARE ... <query>
For combined queries, say two selects, it results in:
DECLARE ... <first select>; <second select>
Then PQexec returns the result of the second one, and nothing is printed.
However, if the second query is not a select, eg: "select ... \; update
... ;", the result of the *first* query is shown.
How fun!
This is because PQexec returns the second result. The cursor declaration
expects a PGRES_COMMAND_OK before proceeding. With a select it gets
PGRES_TUPLES_OK so decides it is an error and silently skips to the end.
With the update it indeed obtains the expected PGRES_COMMAND_OK, not
really for the command it sent but who cares, and proceeds to show the
cursor results.
Basically, the whole logic is broken.
The minimum is to document that it does not work properly with combined
queries. Attached patch does that, so that the bug becomes a documented
bug, aka a feature:-)
Otherwise, probably psql lexer could detect, with some efforts, that it is
a combined query (detect embedded ; and check that they are not empty
queries), so that it could skip the feature if it is the case.
Another approach would be to try to detect if the returned result does not
correspond to the cursor one reliably. Maybe some result counting could be
added somewhere so that the number of results under PQexec is accessible
to the user, i.e. result struct would contain its own number. Hmmm.
A more complex approach would be to keep the position of embedded queries,
and to insert cursor declarations where needed, currently the last one if
it is a SELECT. However, for the previous ones the allocation and such
could be prohibitive as no cursor would be used. Not sure it is worth the
effort as the bug has not been detected for 13 years.
--
Fabien.
