Hi,
The psql improvement in v15 to output multiple result sets does not
behave as one might expect with \g: the output file or program
to pipe into is opened/closed on each result set, overwriting the
previous ones in the case of \g file.
Example:
psql -At <<EOF
-- good (two results output)
select 1\; select 2;
-- bad: ends up with only "2" in the file
select 1\; select 2 \g file
EOF
That problem with \g is due to PrintQueryTuples() an HandleCopyResult()
still having the responsibility to open/close the output stream.
I think this code should be moved upper in the call stack, in
ExecQueryAndProcessResults().
The first attached patch implements a fix that way.
When testing this I've stumbled on another issue nearby: COPY TO
STDOUT followed by \watch should normally produce the error message
"\watch cannot be used with COPY", but the execution goes into a
infinite busy loop instead.
This is because ClearOrSaveAllResults() loops over PQgetResult() until
it returns NULL, but as it turns out, that never happens: it seems
stuck on a PGRES_COPY_OUT result.
While looking to fix that, it occurred to me that it would be
simpler to allow \watch to deal with COPY results rather than
continuing to disallow it. ISTM that before v15, the reason
why PSQLexecWatch() did not want to deal with COPY was to not
bother with a niche use case, rather than because of some
specific impossibility with it.
Now that it calls the generic ExecQueryAndProcessResults() code
that can handle COPY transfers, \watch on a COPY query seems to work
fine if not disallowed.
Besides, v15 adds the possibility to feed \watch output into
a program through PSQL_WATCH_PAGER, and since the copy format is
the best format to be consumed by programs, this seems like
a good reason to allow COPY TO STDOUT with it.
\watch on a COPY FROM STDIN query doesn't make much sense,
but it can be interrupted with ^C if run by mistake, so I don't see a
need to disallow it specifically.
So the second patch fixes the infinite loop problem like that, on top of
the first patch.
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite