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