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