Hi all
I see this sort of question quite a bit:
http://stackoverflow.com/q/38903811/398670
where the user wonders why
COPY gemeenten
FROM 'D:\CBS_woningcijfers_2014.csv'
DELIMITER ';' CSV
fails with
ERROR: could not open file "D:\CBS_woningcijfers_2014.csv" for reading: No
such file or directory'
and as usual, it's because the path is on their local host not the Pg
server.
I think we should emit a HINT here, something like:
ERROR: could not open file "D:\CBS_woningcijfers_2014.csv" for reading: No
such file or directory'
HINT: Paths for COPY are on the PostgreSQL server, not the client. You may
want psql's \copy or a driver COPY ... FROM STDIN wrapper
as a usability improvement. Trivial patch attached.
I'm not sure how to avoid the need to translate the string multiple times,
or if the tooling will automatically flatten them. I haven't bothered with
the stat() failure or isdir cases, since they seem less likely to be the
cause of users being confused between client and server.
Sample output:
postgres=# COPY x FROM '/tmp/somepath';
ERROR: could not open file "/tmp/somepath" for reading: No such file or
directory
HINT: Paths for COPY are on the PostgreSQL server, not the client. You may
want psql's \copy or a driver COPY ... FROM STDIN wrapper
postgres=# COPY x TO '/root/nopermissions';
ERROR: could not open file "/root/nopermissions" for writing: Permission
denied
HINT: Paths for COPY are on the PostgreSQL server, not the client. You may
want psql's \copy or a driver COPY ... FROM STDIN wrapper
postgres=# COPY x TO 'relpath';
ERROR: relative path not allowed for COPY to file
HINT: Paths for COPY are on the PostgreSQL server, not the client. You may
want psql's \copy or a driver COPY ... FROM STDIN wrapper
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From 8e9df8a8874e3ae7446c0e6ebbf4da75889d6027 Mon Sep 17 00:00:00 2001
From: Craig Ringer <cr...@2ndquadrant.com>
Date: Fri, 12 Aug 2016 15:42:12 +0800
Subject: [PATCH] Emit a HINT when COPY can't find a file
Users often get confused between COPY and \copy and try to use client-side
paths with COPY. The server of course cannot find the file.
Emit a HINT in the most common cases to help users out.
---
src/backend/commands/copy.c | 12 +++++++++---
1 file changed, 9 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index f45b330..69d2047 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1761,7 +1761,9 @@ BeginCopyTo(Relation rel,
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("Paths for COPY are on the PostgreSQL server, not the client. "
+ "You may want psql's \\copy or a driver COPY ... FROM STDIN wrapper")));
oumask = umask(S_IWGRP | S_IWOTH);
cstate->copy_file = AllocateFile(cstate->filename, PG_BINARY_W);
@@ -1770,7 +1772,9 @@ BeginCopyTo(Relation rel,
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not open file \"%s\" for writing: %m",
- cstate->filename)));
+ cstate->filename),
+ errhint("Paths for COPY are on the PostgreSQL server, not the client. "
+ "You may want psql's \\copy or a driver COPY ... FROM STDIN wrapper")));
if (fstat(fileno(cstate->copy_file), &st))
ereport(ERROR,
@@ -2796,7 +2800,9 @@ BeginCopyFrom(Relation rel,
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not open file \"%s\" for reading: %m",
- cstate->filename)));
+ cstate->filename),
+ errhint("Paths for COPY are on the PostgreSQL server, not the client. "
+ "You may want psql's \\copy or a driver COPY ... FROM STDIN wrapper")));
if (fstat(fileno(cstate->copy_file), &st))
ereport(ERROR,
--
2.5.5
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
