PostgreSQL Source Code  git master
copy.c
Go to the documentation of this file.
1 /*
2  * psql - the PostgreSQL interactive terminal
3  *
4  * Copyright (c) 2000-2019, PostgreSQL Global Development Group
5  *
6  * src/bin/psql/copy.c
7  */
8 #include "postgres_fe.h"
9 
10 #include <signal.h>
11 #include <sys/stat.h>
12 #ifndef WIN32
13 #include <unistd.h> /* for isatty */
14 #else
15 #include <io.h> /* I think */
16 #endif
17 
18 #include "common.h"
19 #include "common/logging.h"
20 #include "copy.h"
21 #include "libpq-fe.h"
22 #include "pqexpbuffer.h"
23 #include "prompt.h"
24 #include "settings.h"
25 #include "stringutils.h"
26 
27 /*
28  * parse_slash_copy
29  * -- parses \copy command line
30  *
31  * The documented syntax is:
32  * \copy tablename [(columnlist)] from|to filename [options]
33  * \copy ( query stmt ) to filename [options]
34  *
35  * where 'filename' can be one of the following:
36  * '<file path>' | PROGRAM '<command>' | stdin | stdout | pstdout | pstdout
37  * and 'query' can be one of the following:
38  * SELECT | UPDATE | INSERT | DELETE
39  *
40  * An undocumented fact is that you can still write BINARY before the
41  * tablename; this is a hangover from the pre-7.3 syntax. The options
42  * syntax varies across backend versions, but we avoid all that mess
43  * by just transmitting the stuff after the filename literally.
44  *
45  * table name can be double-quoted and can have a schema part.
46  * column names can be double-quoted.
47  * filename can be single-quoted like SQL literals.
48  * command must be single-quoted like SQL literals.
49  *
50  * returns a malloc'ed structure with the options, or NULL on parsing error
51  */
52 
54 {
55  char *before_tofrom; /* COPY string before TO/FROM */
56  char *after_tofrom; /* COPY string after TO/FROM filename */
57  char *file; /* NULL = stdin/stdout */
58  bool program; /* is 'file' a program to popen? */
59  bool psql_inout; /* true = use psql stdin/stdout */
60  bool from; /* true = FROM, false = TO */
61 };
62 
63 
64 static void
66 {
67  if (!ptr)
68  return;
69  free(ptr->before_tofrom);
70  free(ptr->after_tofrom);
71  free(ptr->file);
72  free(ptr);
73 }
74 
75 
76 /* concatenate "more" onto "var", freeing the original value of *var */
77 static void
78 xstrcat(char **var, const char *more)
79 {
80  char *newvar;
81 
82  newvar = psprintf("%s%s", *var, more);
83  free(*var);
84  *var = newvar;
85 }
86 
87 
88 static struct copy_options *
89 parse_slash_copy(const char *args)
90 {
91  struct copy_options *result;
92  char *token;
93  const char *whitespace = " \t\n\r";
94  char nonstd_backslash = standard_strings() ? 0 : '\\';
95 
96  if (!args)
97  {
98  pg_log_error("\\copy: arguments required");
99  return NULL;
100  }
101 
102  result = pg_malloc0(sizeof(struct copy_options));
103 
104  result->before_tofrom = pg_strdup(""); /* initialize for appending */
105 
106  token = strtokx(args, whitespace, ".,()", "\"",
107  0, false, false, pset.encoding);
108  if (!token)
109  goto error;
110 
111  /* The following can be removed when we drop 7.3 syntax support */
112  if (pg_strcasecmp(token, "binary") == 0)
113  {
114  xstrcat(&result->before_tofrom, token);
115  token = strtokx(NULL, whitespace, ".,()", "\"",
116  0, false, false, pset.encoding);
117  if (!token)
118  goto error;
119  }
120 
121  /* Handle COPY (query) case */
122  if (token[0] == '(')
123  {
124  int parens = 1;
125 
126  while (parens > 0)
127  {
128  xstrcat(&result->before_tofrom, " ");
129  xstrcat(&result->before_tofrom, token);
130  token = strtokx(NULL, whitespace, "()", "\"'",
131  nonstd_backslash, true, false, pset.encoding);
132  if (!token)
133  goto error;
134  if (token[0] == '(')
135  parens++;
136  else if (token[0] == ')')
137  parens--;
138  }
139  }
140 
141  xstrcat(&result->before_tofrom, " ");
142  xstrcat(&result->before_tofrom, token);
143  token = strtokx(NULL, whitespace, ".,()", "\"",
144  0, false, false, pset.encoding);
145  if (!token)
146  goto error;
147 
148  /*
149  * strtokx() will not have returned a multi-character token starting with
150  * '.', so we don't need strcmp() here. Likewise for '(', etc, below.
151  */
152  if (token[0] == '.')
153  {
154  /* handle schema . table */
155  xstrcat(&result->before_tofrom, token);
156  token = strtokx(NULL, whitespace, ".,()", "\"",
157  0, false, false, pset.encoding);
158  if (!token)
159  goto error;
160  xstrcat(&result->before_tofrom, token);
161  token = strtokx(NULL, whitespace, ".,()", "\"",
162  0, false, false, pset.encoding);
163  if (!token)
164  goto error;
165  }
166 
167  if (token[0] == '(')
168  {
169  /* handle parenthesized column list */
170  for (;;)
171  {
172  xstrcat(&result->before_tofrom, " ");
173  xstrcat(&result->before_tofrom, token);
174  token = strtokx(NULL, whitespace, "()", "\"",
175  0, false, false, pset.encoding);
176  if (!token)
177  goto error;
178  if (token[0] == ')')
179  break;
180  }
181  xstrcat(&result->before_tofrom, " ");
182  xstrcat(&result->before_tofrom, token);
183  token = strtokx(NULL, whitespace, ".,()", "\"",
184  0, false, false, pset.encoding);
185  if (!token)
186  goto error;
187  }
188 
189  if (pg_strcasecmp(token, "from") == 0)
190  result->from = true;
191  else if (pg_strcasecmp(token, "to") == 0)
192  result->from = false;
193  else
194  goto error;
195 
196  /* { 'filename' | PROGRAM 'command' | STDIN | STDOUT | PSTDIN | PSTDOUT } */
197  token = strtokx(NULL, whitespace, ";", "'",
198  0, false, false, pset.encoding);
199  if (!token)
200  goto error;
201 
202  if (pg_strcasecmp(token, "program") == 0)
203  {
204  int toklen;
205 
206  token = strtokx(NULL, whitespace, ";", "'",
207  0, false, false, pset.encoding);
208  if (!token)
209  goto error;
210 
211  /*
212  * The shell command must be quoted. This isn't fool-proof, but
213  * catches most quoting errors.
214  */
215  toklen = strlen(token);
216  if (token[0] != '\'' || toklen < 2 || token[toklen - 1] != '\'')
217  goto error;
218 
219  strip_quotes(token, '\'', 0, pset.encoding);
220 
221  result->program = true;
222  result->file = pg_strdup(token);
223  }
224  else if (pg_strcasecmp(token, "stdin") == 0 ||
225  pg_strcasecmp(token, "stdout") == 0)
226  {
227  result->file = NULL;
228  }
229  else if (pg_strcasecmp(token, "pstdin") == 0 ||
230  pg_strcasecmp(token, "pstdout") == 0)
231  {
232  result->psql_inout = true;
233  result->file = NULL;
234  }
235  else
236  {
237  /* filename can be optionally quoted */
238  strip_quotes(token, '\'', 0, pset.encoding);
239  result->file = pg_strdup(token);
240  expand_tilde(&result->file);
241  }
242 
243  /* Collect the rest of the line (COPY options) */
244  token = strtokx(NULL, "", NULL, NULL,
245  0, false, false, pset.encoding);
246  if (token)
247  result->after_tofrom = pg_strdup(token);
248 
249  return result;
250 
251 error:
252  if (token)
253  pg_log_error("\\copy: parse error at \"%s\"", token);
254  else
255  pg_log_error("\\copy: parse error at end of line");
256  free_copy_options(result);
257 
258  return NULL;
259 }
260 
261 
262 /*
263  * Execute a \copy command (frontend copy). We have to open a file (or execute
264  * a command), then submit a COPY query to the backend and either feed it data
265  * from the file or route its response into the file.
266  */
267 bool
268 do_copy(const char *args)
269 {
270  PQExpBufferData query;
271  FILE *copystream;
272  struct copy_options *options;
273  bool success;
274 
275  /* parse options */
276  options = parse_slash_copy(args);
277 
278  if (!options)
279  return false;
280 
281  /* prepare to read or write the target file */
282  if (options->file && !options->program)
283  canonicalize_path(options->file);
284 
285  if (options->from)
286  {
287  if (options->file)
288  {
289  if (options->program)
290  {
291  fflush(stdout);
292  fflush(stderr);
293  errno = 0;
294  copystream = popen(options->file, PG_BINARY_R);
295  }
296  else
297  copystream = fopen(options->file, PG_BINARY_R);
298  }
299  else if (!options->psql_inout)
300  copystream = pset.cur_cmd_source;
301  else
302  copystream = stdin;
303  }
304  else
305  {
306  if (options->file)
307  {
308  if (options->program)
309  {
310  fflush(stdout);
311  fflush(stderr);
312  errno = 0;
314  copystream = popen(options->file, PG_BINARY_W);
315  }
316  else
317  copystream = fopen(options->file, PG_BINARY_W);
318  }
319  else if (!options->psql_inout)
320  copystream = pset.queryFout;
321  else
322  copystream = stdout;
323  }
324 
325  if (!copystream)
326  {
327  if (options->program)
328  pg_log_error("could not execute command \"%s\": %m",
329  options->file);
330  else
331  pg_log_error("%s: %m",
332  options->file);
333  free_copy_options(options);
334  return false;
335  }
336 
337  if (!options->program)
338  {
339  struct stat st;
340  int result;
341 
342  /* make sure the specified file is not a directory */
343  if ((result = fstat(fileno(copystream), &st)) < 0)
344  pg_log_error("could not stat file \"%s\": %m",
345  options->file);
346 
347  if (result == 0 && S_ISDIR(st.st_mode))
348  pg_log_error("%s: cannot copy from/to a directory",
349  options->file);
350 
351  if (result < 0 || S_ISDIR(st.st_mode))
352  {
353  fclose(copystream);
354  free_copy_options(options);
355  return false;
356  }
357  }
358 
359  /* build the command we will send to the backend */
360  initPQExpBuffer(&query);
361  printfPQExpBuffer(&query, "COPY ");
362  appendPQExpBufferStr(&query, options->before_tofrom);
363  if (options->from)
364  appendPQExpBufferStr(&query, " FROM STDIN ");
365  else
366  appendPQExpBufferStr(&query, " TO STDOUT ");
367  if (options->after_tofrom)
368  appendPQExpBufferStr(&query, options->after_tofrom);
369 
370  /* run it like a user command, but with copystream as data source/sink */
371  pset.copyStream = copystream;
372  success = SendQuery(query.data);
373  pset.copyStream = NULL;
374  termPQExpBuffer(&query);
375 
376  if (options->file != NULL)
377  {
378  if (options->program)
379  {
380  int pclose_rc = pclose(copystream);
381 
382  if (pclose_rc != 0)
383  {
384  if (pclose_rc < 0)
385  pg_log_error("could not close pipe to external command: %m");
386  else
387  {
388  char *reason = wait_result_to_str(pclose_rc);
389 
390  pg_log_error("%s: %s", options->file,
391  reason ? reason : "");
392  if (reason)
393  free(reason);
394  }
395  success = false;
396  }
398  }
399  else
400  {
401  if (fclose(copystream) != 0)
402  {
403  pg_log_error("%s: %m", options->file);
404  success = false;
405  }
406  }
407  }
408  free_copy_options(options);
409  return success;
410 }
411 
412 
413 /*
414  * Functions for handling COPY IN/OUT data transfer.
415  *
416  * If you want to use COPY TO STDOUT/FROM STDIN in your application,
417  * this is the code to steal ;)
418  */
419 
420 /*
421  * handleCopyOut
422  * receives data as a result of a COPY ... TO STDOUT command
423  *
424  * conn should be a database connection that you just issued COPY TO on
425  * and got back a PGRES_COPY_OUT result.
426  *
427  * copystream is the file stream for the data to go to.
428  * copystream can be NULL to eat the data without writing it anywhere.
429  *
430  * The final status for the COPY is returned into *res (but note
431  * we already reported the error, if it's not a success result).
432  *
433  * result is true if successful, false if not.
434  */
435 bool
436 handleCopyOut(PGconn *conn, FILE *copystream, PGresult **res)
437 {
438  bool OK = true;
439  char *buf;
440  int ret;
441 
442  for (;;)
443  {
444  ret = PQgetCopyData(conn, &buf, 0);
445 
446  if (ret < 0)
447  break; /* done or server/connection error */
448 
449  if (buf)
450  {
451  if (OK && copystream && fwrite(buf, 1, ret, copystream) != ret)
452  {
453  pg_log_error("could not write COPY data: %m");
454  /* complain only once, keep reading data from server */
455  OK = false;
456  }
457  PQfreemem(buf);
458  }
459  }
460 
461  if (OK && copystream && fflush(copystream))
462  {
463  pg_log_error("could not write COPY data: %m");
464  OK = false;
465  }
466 
467  if (ret == -2)
468  {
469  pg_log_error("COPY data transfer failed: %s", PQerrorMessage(conn));
470  OK = false;
471  }
472 
473  /*
474  * Check command status and return to normal libpq state.
475  *
476  * If for some reason libpq is still reporting PGRES_COPY_OUT state, we
477  * would like to forcibly exit that state, since our caller would be
478  * unable to distinguish that situation from reaching the next COPY in a
479  * command string that happened to contain two consecutive COPY TO STDOUT
480  * commands. However, libpq provides no API for doing that, and in
481  * principle it's a libpq bug anyway if PQgetCopyData() returns -1 or -2
482  * but hasn't exited COPY_OUT state internally. So we ignore the
483  * possibility here.
484  */
485  *res = PQgetResult(conn);
486  if (PQresultStatus(*res) != PGRES_COMMAND_OK)
487  {
488  pg_log_info("%s", PQerrorMessage(conn));
489  OK = false;
490  }
491 
492  return OK;
493 }
494 
495 /*
496  * handleCopyIn
497  * sends data to complete a COPY ... FROM STDIN command
498  *
499  * conn should be a database connection that you just issued COPY FROM on
500  * and got back a PGRES_COPY_IN result.
501  * copystream is the file stream to read the data from.
502  * isbinary can be set from PQbinaryTuples().
503  * The final status for the COPY is returned into *res (but note
504  * we already reported the error, if it's not a success result).
505  *
506  * result is true if successful, false if not.
507  */
508 
509 /* read chunk size for COPY IN - size is not critical */
510 #define COPYBUFSIZ 8192
511 
512 bool
513 handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
514 {
515  bool OK;
516  char buf[COPYBUFSIZ];
517  bool showprompt;
518 
519  /*
520  * Establish longjmp destination for exiting from wait-for-input. (This is
521  * only effective while sigint_interrupt_enabled is TRUE.)
522  */
523  if (sigsetjmp(sigint_interrupt_jmp, 1) != 0)
524  {
525  /* got here with longjmp */
526 
527  /* Terminate data transfer */
528  PQputCopyEnd(conn,
529  (PQprotocolVersion(conn) < 3) ? NULL :
530  _("canceled by user"));
531 
532  OK = false;
533  goto copyin_cleanup;
534  }
535 
536  /* Prompt if interactive input */
537  if (isatty(fileno(copystream)))
538  {
539  showprompt = true;
540  if (!pset.quiet)
541  puts(_("Enter data to be copied followed by a newline.\n"
542  "End with a backslash and a period on a line by itself, or an EOF signal."));
543  }
544  else
545  showprompt = false;
546 
547  OK = true;
548 
549  if (isbinary)
550  {
551  /* interactive input probably silly, but give one prompt anyway */
552  if (showprompt)
553  {
554  const char *prompt = get_prompt(PROMPT_COPY, NULL);
555 
556  fputs(prompt, stdout);
557  fflush(stdout);
558  }
559 
560  for (;;)
561  {
562  int buflen;
563 
564  /* enable longjmp while waiting for input */
566 
567  buflen = fread(buf, 1, COPYBUFSIZ, copystream);
568 
569  sigint_interrupt_enabled = false;
570 
571  if (buflen <= 0)
572  break;
573 
574  if (PQputCopyData(conn, buf, buflen) <= 0)
575  {
576  OK = false;
577  break;
578  }
579  }
580  }
581  else
582  {
583  bool copydone = false;
584 
585  while (!copydone)
586  { /* for each input line ... */
587  bool firstload;
588  bool linedone;
589 
590  if (showprompt)
591  {
592  const char *prompt = get_prompt(PROMPT_COPY, NULL);
593 
594  fputs(prompt, stdout);
595  fflush(stdout);
596  }
597 
598  firstload = true;
599  linedone = false;
600 
601  while (!linedone)
602  { /* for each bufferload in line ... */
603  int linelen;
604  char *fgresult;
605 
606  /* enable longjmp while waiting for input */
608 
609  fgresult = fgets(buf, sizeof(buf), copystream);
610 
611  sigint_interrupt_enabled = false;
612 
613  if (!fgresult)
614  {
615  copydone = true;
616  break;
617  }
618 
619  linelen = strlen(buf);
620 
621  /* current line is done? */
622  if (linelen > 0 && buf[linelen - 1] == '\n')
623  linedone = true;
624 
625  /* check for EOF marker, but not on a partial line */
626  if (firstload)
627  {
628  /*
629  * This code erroneously assumes '\.' on a line alone
630  * inside a quoted CSV string terminates the \copy.
631  * http://www.postgresql.org/message-id/E1TdNVQ-0001ju-GO@wrigleys.postgresql.org
632  */
633  if (strcmp(buf, "\\.\n") == 0 ||
634  strcmp(buf, "\\.\r\n") == 0)
635  {
636  copydone = true;
637  break;
638  }
639 
640  firstload = false;
641  }
642 
643  if (PQputCopyData(conn, buf, linelen) <= 0)
644  {
645  OK = false;
646  copydone = true;
647  break;
648  }
649  }
650 
651  if (copystream == pset.cur_cmd_source)
652  {
653  pset.lineno++;
654  pset.stmt_lineno++;
655  }
656  }
657  }
658 
659  /* Check for read error */
660  if (ferror(copystream))
661  OK = false;
662 
663  /*
664  * Terminate data transfer. We can't send an error message if we're using
665  * protocol version 2.
666  */
667  if (PQputCopyEnd(conn,
668  (OK || PQprotocolVersion(conn) < 3) ? NULL :
669  _("aborted because of read failure")) <= 0)
670  OK = false;
671 
672 copyin_cleanup:
673 
674  /*
675  * Clear the EOF flag on the stream, in case copying ended due to an EOF
676  * signal. This allows an interactive TTY session to perform another COPY
677  * FROM STDIN later. (In non-STDIN cases, we're about to close the file
678  * anyway, so it doesn't matter.) Although we don't ever test the flag
679  * with feof(), some fread() implementations won't read more data if it's
680  * set. This also clears the error flag, but we already checked that.
681  */
682  clearerr(copystream);
683 
684  /*
685  * Check command status and return to normal libpq state.
686  *
687  * We do not want to return with the status still PGRES_COPY_IN: our
688  * caller would be unable to distinguish that situation from reaching the
689  * next COPY in a command string that happened to contain two consecutive
690  * COPY FROM STDIN commands. We keep trying PQputCopyEnd() in the hope
691  * it'll work eventually. (What's actually likely to happen is that in
692  * attempting to flush the data, libpq will eventually realize that the
693  * connection is lost. But that's fine; it will get us out of COPY_IN
694  * state, which is what we need.)
695  */
696  while (*res = PQgetResult(conn), PQresultStatus(*res) == PGRES_COPY_IN)
697  {
698  OK = false;
699  PQclear(*res);
700  /* We can't send an error message if we're using protocol version 2 */
701  PQputCopyEnd(conn,
702  (PQprotocolVersion(conn) < 3) ? NULL :
703  _("trying to exit copy mode"));
704  }
705  if (PQresultStatus(*res) != PGRES_COMMAND_OK)
706  {
707  pg_log_info("%s", PQerrorMessage(conn));
708  OK = false;
709  }
710 
711  return OK;
712 }
int PQputCopyData(PGconn *conn, const char *buffer, int nbytes)
Definition: fe-exec.c:2317
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6631
void printfPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:237
volatile bool sigint_interrupt_enabled
Definition: common.c:245
PsqlSettings pset
Definition: startup.c:31
static void error(void)
Definition: sql-dyntest.c:147
void disable_sigpipe_trap(void)
Definition: print.c:2942
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
#define pg_log_error(...)
Definition: logging.h:79
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369
char * psprintf(const char *fmt,...)
Definition: psprintf.c:46
char * strtokx(const char *s, const char *whitespace, const char *delim, const char *quote, char escape, bool e_strings, bool del_quotes, int encoding)
Definition: stringutils.c:52
char * get_prompt(promptStatus_t status, ConditionalStack cstack)
Definition: prompt.c:70
static void free_copy_options(struct copy_options *ptr)
Definition: copy.c:65
FILE * queryFout
Definition: settings.h:84
void canonicalize_path(char *path)
Definition: path.c:254
#define PG_BINARY_W
Definition: c.h:1225
int PQputCopyEnd(PGconn *conn, const char *errormsg)
Definition: fe-exec.c:2384
bool do_copy(const char *args)
Definition: copy.c:268
int pg_strcasecmp(const char *s1, const char *s2)
Definition: pgstrcasecmp.c:36
#define PG_BINARY_R
Definition: c.h:1224
FILE * copyStream
Definition: settings.h:87
char * wait_result_to_str(int exitstatus)
Definition: wait_error.c:32
static struct copy_options * parse_slash_copy(const char *args)
Definition: copy.c:89
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2692
FILE * cur_cmd_source
Definition: settings.h:104
int PQgetCopyData(PGconn *conn, char **buffer, int async)
Definition: fe-exec.c:2473
char * before_tofrom
Definition: copy.c:55
sigjmp_buf sigint_interrupt_jmp
Definition: common.c:247
bool psql_inout
Definition: copy.c:59
#define COPYBUFSIZ
Definition: copy.c:510
void * pg_malloc0(size_t size)
Definition: fe_memutils.c:53
PGconn * conn
Definition: streamutil.c:54
void expand_tilde(char **filename)
Definition: common.c:2220
bool program
Definition: copy.c:58
static char * buf
Definition: pg_test_fsync.c:67
static void xstrcat(char **var, const char *more)
Definition: copy.c:78
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
bool handleCopyOut(PGconn *conn, FILE *copystream, PGresult **res)
Definition: copy.c:436
void restore_sigpipe_trap(void)
Definition: print.c:2965
bool from
Definition: copy.c:60
static char ** options
#define stat(a, b)
Definition: win32_port.h:255
void strip_quotes(char *source, char quote, char escape, int encoding)
Definition: stringutils.c:240
void PQclear(PGresult *res)
Definition: fe-exec.c:694
bool standard_strings(void)
Definition: common.c:2175
#define free(a)
Definition: header.h:65
char * after_tofrom
Definition: copy.c:56
bool handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
Definition: copy.c:513
#define S_ISDIR(m)
Definition: win32_port.h:296
uint64 lineno
Definition: settings.h:110
bool SendQuery(const char *query)
Definition: common.c:1181
int encoding
Definition: settings.h:83
static bool success
Definition: initdb.c:163
int PQprotocolVersion(const PGconn *conn)
Definition: fe-connect.c:6611
#define _(x)
Definition: elog.c:87
void PQfreemem(void *ptr)
Definition: fe-exec.c:3296
PGresult * PQgetResult(PGconn *conn)
Definition: fe-exec.c:1778
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:92
uint64 stmt_lineno
Definition: settings.h:111
#define pg_log_info(...)
Definition: logging.h:87
char * file
Definition: copy.c:57