Re: [PATCH] COPY vs \copy HINT - Mailing list pgsql-hackers

Craig Ringer <craig@2ndquadrant.com> writes:
> On 2 September 2016 at 04:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 1. I don't really think the HINT is appropriate for the not-absolute-path
>> case.

> Why? If the user runs
> # COPY sometable FROM 'localfile.csv' WITH (FORMAT CSV);
> ERROR:  could not open file "localfile.csv" for reading: No such file
> or directory
> they're going to be just as confused by this error as by:
> # COPY batch_demo FROM '/tmp/localfile.csv' WITH (FORMAT CSV);
> ERROR:  could not open file "/tmp/localfile.csv" for reading: No such
> file or directory
> so I don't really see the rationale for this change.

I think you misinterpreted what I was complaining about; it's not the
FROM case but the TO case, specifically this addition:
            if (!is_absolute_path(filename))                ereport(ERROR,
(errcode(ERRCODE_INVALID_NAME),
-                      errmsg("relative path not allowed for COPY to file")));
+                         errmsg("relative path not allowed for COPY to file"),
+                         errhint("COPY copies to a file on the PostgreSQL server, not on the client. "
+                                 "You may want a client-side facility such as psql's \\copy.")));

The reason I'm not happy about that is that the hint is completely
unrelated to the error condition, and I think that such a hint is likely
to be more confusing than helpful.  What's likely to happen if we leave
it alone is that someone who's confused about client vs. server will do

=# COPY sometable TO 'localfile.csv' WITH (FORMAT CSV);
ERROR:  relative path not allowed for COPY to file

[ user mutters something about hopeless pedants and writes an absolute
path: ]

=# COPY sometable TO '/home/joe/localfile.csv' WITH (FORMAT CSV);
ERROR:  could not open file "/home/joe/localfile.csv" for writing: No such file or directory

At this point, it's appropriate to give the HINT, but I think doing it
for the first case is just confusing.  There's an opportunity cost to
hints like this, which is that if they are in fact wildly unrelated
to the real problem, they are more likely to send the user down a blind
alley than lead his thoughts to the right answer.  What you want to do
is essentially cutting one step out of the process by jumping to the
conclusion that if the user gave a relative path, it's because he's
confused about client vs. server, and I don't see how that follows.

>> 2. I don't think it's appropriate for all possible cases of AllocateFile
>> failure either, eg surely not for EACCES or similar situations where we
>> did find a file.  Maybe print it only for ENOENT?

> I thought about that but figured it didn't really matter too much,
> when thinking about examples like
> # COPY batch_demo FROM '/root/secret.csv' WITH (FORMAT CSV);
> ERROR:  could not open file "/root/secret.csv" for reading: Permission denied
> or whatever, where the user doesn't understand why they can't read the
> file given that their local client has permission to do so.

Meh.  Hinting in this case could be helpful only if the user in fact had
identical directory structures on client and server and a data file in the
right place on both.  That doesn't seem terribly likely, and again I argue
that the downsides of giving an irrelevant hint outweigh the occasional
benefit when it happens to be right.  I mean, taking this position just
one step further, we should annotate every COPY data error with this same
hint, arguing that maybe the user has the right data on one machine and
slightly wrong data on the other machine.  Once in a blue moon that would
indeed be the answer, but most of the time it would be wrong, and users
would soon start getting annoyed with the hint --- maybe to the point
where they automatically ignore that hint even when it's right.

In short, I like hints as long as they're carefully targeted, but if
they're not they are likely to be seen as a net negative by users.
I think giving this hint for the ENOENT case is probably fine, but
I'm much less convinced about other cases.

One additional case that I could believe is useful is whatever error
code Windows gives when you specify a drive letter that's not in use.
(Maybe that's ENOENT but I bet not.)
        regards, tom lane