granicus.if.org Git - postgresql/blob - src/bin/psql/copy.c

   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 free_copy_options(struct copy_options * ptr)
  67 {
  68         if (!ptr)
  69                 return;
  70         free(ptr->before_tofrom);
  71         free(ptr->after_tofrom);
  72         free(ptr->file);
  73         free(ptr);
  74 }
  75
  76
  77 /* concatenate "more" onto "var", freeing the original value of *var */
  78 static void
  79 xstrcat(char **var, const char *more)
  80 {
  81         char       *newvar;
  82
  83         newvar = psprintf("%s%s", *var, more);
  84         free(*var);
  85         *var = newvar;
  86 }
  87
  88
  89 static struct copy_options *
  90 parse_slash_copy(const char *args)
  91 {
  92         struct copy_options *result;
  93         char       *token;
  94         const char *whitespace = " \t\n\r";
  95         char            nonstd_backslash = standard_strings() ? 0 : '\\';
  96
  97         if (!args)
  98         {
  99                 psql_error("\\copy: arguments required\n");
 100                 return NULL;
 101         }
 102
 103         result = pg_malloc0(sizeof(struct copy_options));
 104
 105         result->before_tofrom = pg_strdup("");          /* initialize for appending */
 106
 107         token = strtokx(args, whitespace, ".,()", "\"",
 108                                         0, false, false, pset.encoding);
 109         if (!token)
 110                 goto error;
 111
 112         /* The following can be removed when we drop 7.3 syntax support */
 113         if (pg_strcasecmp(token, "binary") == 0)
 114         {
 115                 xstrcat(&result->before_tofrom, token);
 116                 token = strtokx(NULL, whitespace, ".,()", "\"",
 117                                                 0, false, false, pset.encoding);
 118                 if (!token)
 119                         goto error;
 120         }
 121
 122         /* Handle COPY (query) case */
 123         if (token[0] == '(')
 124         {
 125                 int                     parens = 1;
 126
 127                 while (parens > 0)
 128                 {
 129                         xstrcat(&result->before_tofrom, " ");
 130                         xstrcat(&result->before_tofrom, token);
 131                         token = strtokx(NULL, whitespace, "()", "\"'",
 132                                                         nonstd_backslash, true, false, pset.encoding);
 133                         if (!token)
 134                                 goto error;
 135                         if (token[0] == '(')
 136                                 parens++;
 137                         else if (token[0] == ')')
 138                                 parens--;
 139                 }
 140         }
 141
 142         xstrcat(&result->before_tofrom, " ");
 143         xstrcat(&result->before_tofrom, token);
 144         token = strtokx(NULL, whitespace, ".,()", "\"",
 145                                         0, false, false, pset.encoding);
 146         if (!token)
 147                 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         if (token[0] == '.')
 154         {
 155                 /* handle schema . table */
 156                 xstrcat(&result->before_tofrom, token);
 157                 token = strtokx(NULL, whitespace, ".,()", "\"",
 158                                                 0, false, false, pset.encoding);
 159                 if (!token)
 160                         goto error;
 161                 xstrcat(&result->before_tofrom, token);
 162                 token = strtokx(NULL, whitespace, ".,()", "\"",
 163                                                 0, false, false, pset.encoding);
 164                 if (!token)
 165                         goto error;
 166         }
 167
 168         if (token[0] == '(')
 169         {
 170                 /* handle parenthesized column list */
 171                 for (;;)
 172                 {
 173                         xstrcat(&result->before_tofrom, " ");
 174                         xstrcat(&result->before_tofrom, token);
 175                         token = strtokx(NULL, whitespace, "()", "\"",
 176                                                         0, false, false, pset.encoding);
 177                         if (!token)
 178                                 goto error;
 179                         if (token[0] == ')')
 180                                 break;
 181                 }
 182                 xstrcat(&result->before_tofrom, " ");
 183                 xstrcat(&result->before_tofrom, token);
 184                 token = strtokx(NULL, whitespace, ".,()", "\"",
 185                                                 0, false, false, pset.encoding);
 186                 if (!token)
 187                         goto error;
 188         }
 189
 190         if (pg_strcasecmp(token, "from") == 0)
 191                 result->from = true;
 192         else if (pg_strcasecmp(token, "to") == 0)
 193                 result->from = false;
 194         else
 195                 goto error;
 196
 197         /* { 'filename' | PROGRAM 'command' | STDIN | STDOUT | PSTDIN | PSTDOUT } */
 198         token = strtokx(NULL, whitespace, ";", "'",
 199                                         0, false, false, pset.encoding);
 200         if (!token)
 201                 goto error;
 202
 203         if (pg_strcasecmp(token, "program") == 0)
 204         {
 205                 int                     toklen;
 206
 207                 token = strtokx(NULL, whitespace, ";", "'",
 208                                                 0, false, false, pset.encoding);
 209                 if (!token)
 210                         goto error;
 211
 212                 /*
 213                  * The shell command must be quoted. This isn't fool-proof, but
 214                  * catches most quoting errors.
 215                  */
 216                 toklen = strlen(token);
 217                 if (token[0] != '\'' || toklen < 2 || token[toklen - 1] != '\'')
 218                         goto error;
 219
 220                 strip_quotes(token, '\'', 0, pset.encoding);
 221
 222                 result->program = true;
 223                 result->file = pg_strdup(token);
 224         }
 225         else if (pg_strcasecmp(token, "stdin") == 0 ||
 226                          pg_strcasecmp(token, "stdout") == 0)
 227         {
 228                 result->file = NULL;
 229         }
 230         else if (pg_strcasecmp(token, "pstdin") == 0 ||
 231                          pg_strcasecmp(token, "pstdout") == 0)
 232         {
 233                 result->psql_inout = true;
 234                 result->file = NULL;
 235         }
 236         else
 237         {
 238                 /* filename can be optionally quoted */
 239                 strip_quotes(token, '\'', 0, pset.encoding);
 240                 result->file = pg_strdup(token);
 241                 expand_tilde(&result->file);
 242         }
 243
 244         /* Collect the rest of the line (COPY options) */
 245         token = strtokx(NULL, "", NULL, NULL,
 246                                         0, false, false, pset.encoding);
 247         if (token)
 248                 result->after_tofrom = pg_strdup(token);
 249
 250         return result;
 251
 252 error:
 253         if (token)
 254                 psql_error("\\copy: parse error at \"%s\"\n", token);
 255         else
 256                 psql_error("\\copy: parse error at end of line\n");
 257         free_copy_options(result);
 258
 259         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 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         options = parse_slash_copy(args);
 278
 279         if (!options)
 280                 return false;
 281
 282         /* prepare to read or write the target file */
 283         if (options->file && !options->program)
 284                 canonicalize_path(options->file);
 285
 286         if (options->from)
 287         {
 288                 if (options->file)
 289                 {
 290                         if (options->program)
 291                         {
 292                                 fflush(stdout);
 293                                 fflush(stderr);
 294                                 errno = 0;
 295                                 copystream = popen(options->file, PG_BINARY_R);
 296                         }
 297                         else
 298                                 copystream = fopen(options->file, PG_BINARY_R);
 299                 }
 300                 else if (!options->psql_inout)
 301                         copystream = pset.cur_cmd_source;
 302                 else
 303                         copystream = stdin;
 304         }
 305         else
 306         {
 307                 if (options->file)
 308                 {
 309                         if (options->program)
 310                         {
 311                                 fflush(stdout);
 312                                 fflush(stderr);
 313                                 errno = 0;
 314                                 disable_sigpipe_trap();
 315                                 copystream = popen(options->file, PG_BINARY_W);
 316                         }
 317                         else
 318                                 copystream = fopen(options->file, PG_BINARY_W);
 319                 }
 320                 else if (!options->psql_inout)
 321                         copystream = pset.queryFout;
 322                 else
 323                         copystream = stdout;
 324         }
 325
 326         if (!copystream)
 327         {
 328                 if (options->program)
 329                         psql_error("could not execute command \"%s\": %s\n",
 330                                            options->file, strerror(errno));
 331                 else
 332                         psql_error("%s: %s\n",
 333                                            options->file, strerror(errno));
 334                 free_copy_options(options);
 335                 return false;
 336         }
 337
 338         if (!options->program)
 339         {
 340                 struct stat st;
 341                 int                     result;
 342
 343                 /* make sure the specified file is not a directory */
 344                 if ((result = fstat(fileno(copystream), &st)) < 0)
 345                         psql_error("could not stat file \"%s\": %s\n",
 346                                            options->file, strerror(errno));
 347
 348                 if (result == 0 && S_ISDIR(st.st_mode))
 349                         psql_error("%s: cannot copy from/to a directory\n",
 350                                            options->file);
 351
 352                 if (result < 0 || S_ISDIR(st.st_mode))
 353                 {
 354                         fclose(copystream);
 355                         free_copy_options(options);
 356                         return false;
 357                 }
 358         }
 359
 360         /* build the command we will send to the backend */
 361         initPQExpBuffer(&query);
 362         printfPQExpBuffer(&query, "COPY ");
 363         appendPQExpBufferStr(&query, options->before_tofrom);
 364         if (options->from)
 365                 appendPQExpBufferStr(&query, " FROM STDIN ");
 366         else
 367                 appendPQExpBufferStr(&query, " TO STDOUT ");
 368         if (options->after_tofrom)
 369                 appendPQExpBufferStr(&query, options->after_tofrom);
 370
 371         /* run it like a user command, but with copystream as data source/sink */
 372         pset.copyStream = copystream;
 373         success = SendQuery(query.data);
 374         pset.copyStream = NULL;
 375         termPQExpBuffer(&query);
 376
 377         if (options->file != NULL)
 378         {
 379                 if (options->program)
 380                 {
 381                         int                     pclose_rc = pclose(copystream);
 382
 383                         if (pclose_rc != 0)
 384                         {
 385                                 if (pclose_rc < 0)
 386                                         psql_error("could not close pipe to external command: %s\n",
 387                                                            strerror(errno));
 388                                 else
 389                                 {
 390                                         char       *reason = wait_result_to_str(pclose_rc);
 391
 392                                         psql_error("%s: %s\n", options->file,
 393                                                            reason ? reason : "");
 394                                         if (reason)
 395                                                 free(reason);
 396                                 }
 397                                 success = false;
 398                         }
 399                         restore_sigpipe_trap();
 400                 }
 401                 else
 402                 {
 403                         if (fclose(copystream) != 0)
 404                         {
 405                                 psql_error("%s: %s\n", options->file, strerror(errno));
 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  * 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 handleCopyOut(PGconn *conn, FILE *copystream, PGresult **res)
 436 {
 437         bool            OK = true;
 438         char       *buf;
 439         int                     ret;
 440
 441         for (;;)
 442         {
 443                 ret = PQgetCopyData(conn, &buf, 0);
 444
 445                 if (ret < 0)
 446                         break;                          /* done or server/connection error */
 447
 448                 if (buf)
 449                 {
 450                         if (OK && fwrite(buf, 1, ret, copystream) != ret)
 451                         {
 452                                 psql_error("could not write COPY data: %s\n",
 453                                                    strerror(errno));
 454                                 /* complain only once, keep reading data from server */
 455                                 OK = false;
 456                         }
 457                         PQfreemem(buf);
 458                 }
 459         }
 460
 461         if (OK && fflush(copystream))
 462         {
 463                 psql_error("could not write COPY data: %s\n",
 464                                    strerror(errno));
 465                 OK = false;
 466         }
 467
 468         if (ret == -2)
 469         {
 470                 psql_error("COPY data transfer failed: %s", PQerrorMessage(conn));
 471                 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         *res = PQgetResult(conn);
 487         if (PQresultStatus(*res) != PGRES_COMMAND_OK)
 488         {
 489                 psql_error("%s", PQerrorMessage(conn));
 490                 OK = false;
 491         }
 492
 493         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 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         if (sigsetjmp(sigint_interrupt_jmp, 1) != 0)
 525         {
 526                 /* got here with longjmp */
 527
 528                 /* Terminate data transfer */
 529                 PQputCopyEnd(conn,
 530                                          (PQprotocolVersion(conn) < 3) ? NULL :
 531                                          _("canceled by user"));
 532
 533                 OK = false;
 534                 goto copyin_cleanup;
 535         }
 536
 537         /* Prompt if interactive input */
 538         if (isatty(fileno(copystream)))
 539         {
 540                 showprompt = true;
 541                 if (!pset.quiet)
 542                         puts(_("Enter data to be copied followed by a newline.\n"
 543                                    "End with a backslash and a period on a line by itself."));
 544         }
 545         else
 546                 showprompt = false;
 547
 548         OK = true;
 549
 550         if (isbinary)
 551         {
 552                 /* interactive input probably silly, but give one prompt anyway */
 553                 if (showprompt)
 554                 {
 555                         const char *prompt = get_prompt(PROMPT_COPY);
 556
 557                         fputs(prompt, stdout);
 558                         fflush(stdout);
 559                 }
 560
 561                 for (;;)
 562                 {
 563                         int                     buflen;
 564
 565                         /* enable longjmp while waiting for input */
 566                         sigint_interrupt_enabled = true;
 567
 568                         buflen = fread(buf, 1, COPYBUFSIZ, copystream);
 569
 570                         sigint_interrupt_enabled = false;
 571
 572                         if (buflen <= 0)
 573                                 break;
 574
 575                         if (PQputCopyData(conn, buf, buflen) <= 0)
 576                         {
 577                                 OK = false;
 578                                 break;
 579                         }
 580                 }
 581         }
 582         else
 583         {
 584                 bool            copydone = false;
 585
 586                 while (!copydone)
 587                 {                                               /* for each input line ... */
 588                         bool            firstload;
 589                         bool            linedone;
 590
 591                         if (showprompt)
 592                         {
 593                                 const char *prompt = get_prompt(PROMPT_COPY);
 594
 595                                 fputs(prompt, stdout);
 596                                 fflush(stdout);
 597                         }
 598
 599                         firstload = true;
 600                         linedone = false;
 601
 602                         while (!linedone)
 603                         {                                       /* for each bufferload in line ... */
 604                                 int                     linelen;
 605                                 char       *fgresult;
 606
 607                                 /* enable longjmp while waiting for input */
 608                                 sigint_interrupt_enabled = true;
 609
 610                                 fgresult = fgets(buf, sizeof(buf), copystream);
 611
 612                                 sigint_interrupt_enabled = false;
 613
 614                                 if (!fgresult)
 615                                 {
 616                                         copydone = true;
 617                                         break;
 618                                 }
 619
 620                                 linelen = strlen(buf);
 621
 622                                 /* current line is done? */
 623                                 if (linelen > 0 && buf[linelen - 1] == '\n')
 624                                         linedone = true;
 625
 626                                 /* check for EOF marker, but not on a partial line */
 627                                 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@w
 633                                          * rigleys.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          * Check command status and return to normal libpq state.
 678          *
 679          * We do not want to return with the status still PGRES_COPY_IN: our
 680          * caller would be unable to distinguish that situation from reaching the
 681          * next COPY in a command string that happened to contain two consecutive
 682          * COPY FROM STDIN commands.  We keep trying PQputCopyEnd() in the hope
 683          * it'll work eventually.  (What's actually likely to happen is that in
 684          * attempting to flush the data, libpq will eventually realize that the
 685          * connection is lost.  But that's fine; it will get us out of COPY_IN
 686          * state, which is what we need.)
 687          */
 688         while (*res = PQgetResult(conn), PQresultStatus(*res) == PGRES_COPY_IN)
 689         {
 690                 OK = false;
 691                 PQclear(*res);
 692                 /* We can't send an error message if we're using protocol version 2 */
 693                 PQputCopyEnd(conn,
 694                                          (PQprotocolVersion(conn) < 3) ? NULL :
 695                                          _("trying to exit copy mode"));
 696         }
 697         if (PQresultStatus(*res) != PGRES_COMMAND_OK)
 698         {
 699                 psql_error("%s", PQerrorMessage(conn));
 700                 OK = false;
 701         }
 702
 703         return OK;
 704 }