Re: COPY table FROM STDIN doesn't show count tag

Tom Lane-2 wrote
> Unfortunately, while testing it I noticed that there's a potentially
> fatal backwards-compatibility problem, namely that the "COPY n" status
> gets printed on stdout, which is the same place that COPY OUT data is
> going. While this isn't such a big problem for interactive use,
> usages like this one are pretty popular:
>
> psql -c 'copy mytable to stdout' mydatabase | some-program
>
> With the patch, "COPY n" gets included in the data sent to some-program,
> which never happened before and is surely not what the user wants.
> The same if the -c string uses \copy.
>
> There are several things we could do about this:
>
> 1. Treat this as a non-backwards-compatible change, and document that
> people have to use -q if they don't want the COPY tag in the output.
> I'm not sure this is acceptable.

I've mostly used copy to with files and so wouldn't mind if STDOUT had the
COPY n sent to it as long as the target file is just the copy contents.

> 2. Kluge ProcessResult so that it continues to not pass back a PGresult
> for the COPY TO STDOUT case, or does so only in limited circumstances
> (perhaps only if isatty(stdout), for instance).

The main problem with this is that people will test by sending output to a
TTY and see the COPY n. Although if it can be done consistently then you
minimize backward incompatibility and encourage people to enforce quiet mode
while the command runs...

> 3. Modify PrintQueryStatus so that command status goes to stderr not
> stdout. While this is probably how it should've been done in the first
> place, this would be a far more severe compatibility break than #1.
> (For one thing, there are probably scripts out there that think that any
> output to stderr is an error message.) I'm afraid this one is definitely
> not acceptable, though it would be by far the cleanest solution were it
> not for compatibility concerns.

Yes, it's a moot point but I'm not sure it would be best anyway.

> 4. As #3, but print the command status to stderr only if it's "COPY n",
> otherwise to stdout. This is a smaller compatibility break than #3,
> but still a break since COPY status was formerly issued to stdout
> in non TO STDOUT/FROM STDIN cases. (Note that PrintQueryStatus can't
> tell whether it was COPY TO STDOUT rather than any other kind of COPY;
> if we want that to factor into the behavior, we need ProcessResult to
> do it.)

Since we are considering stderr my (inexperienced admittedly) gut says that
using stderr for this is generally undesirable and especially given our
existing precedence. stdout is the seemingly correct target, typically, and
the existing quiet-mode toggle provides sufficient control for typical
needs.

> 5. Give up on the print-the-tag aspect of the change, and just fix the
> wrong-line-number issue (so we'd still introduce the copyStream variable,
> but not change how PGresults are passed around).
>
> I'm inclined to think #2 is the best answer if we can't stomach #1.
> But the exact rule for when to print a COPY OUT result probably
> still requires some debate. Or maybe someone has another idea?
>
> Also, I'm thinking we should back-patch the aspects of the patch
> needed to fix the wrong-line-number issue. That appears to have been
> introduced in 9.2; older versions of PG get the above example right.
>
> Comments?

I'd like COPY TO to anything but STDOUT to emit a "COPY n" on STDOUT -
unless suppressed by -q(uiet)

Document that COPY TO STDOUT does not emit "COPY n" because STDOUT is
already assigned for data and so is not available for notifications. Since
COPY is more typically used for ETL than a bare-select, in addition to
back-compatibility concerns, this default behavior seems reasonable.

Would it be possible to store the "n" somewhere and provide a command - like
GET DIAGNOSTICS in pl/pgsql - if the user really wants to know how many rows
were sent to STDOUT? I'm doubt this is even useful in the typical use-case
for COPY TO STDOUT but figured I'd toss the idea out there.

Is there anything besides a desire for consistency that anyone has or can
put forth as a use-case for COPY TO STDOUT emitting "COPY n" on STDOUT as
well? If you are going to view the content inline, and also want a quick
count, ISTM you would be more likely to use SELECT to take advantage of all
its pretty-print features.

If we really need to cater to this use then maybe a "--loud-copy-to-stdout"
switch can be provided to override its default quiet-mode.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/COPY-table-FROM-STDIN-doesn-t-show-count-tag-tp5775018p5795611.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.