PostgreSQL Source Code  git master
copy.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * copy.c
4  * Implements the COPY utility command
5  *
6  * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
7  * Portions Copyright (c) 1994, Regents of the University of California
8  *
9  *
10  * IDENTIFICATION
11  * src/backend/commands/copy.c
12  *
13  *-------------------------------------------------------------------------
14  */
15 #include "postgres.h"
16 
17 #include <ctype.h>
18 #include <unistd.h>
19 #include <sys/stat.h>
20 
21 #include "access/sysattr.h"
22 #include "access/table.h"
23 #include "access/xact.h"
24 #include "catalog/pg_authid.h"
25 #include "commands/copy.h"
26 #include "commands/defrem.h"
27 #include "executor/executor.h"
28 #include "mb/pg_wchar.h"
29 #include "miscadmin.h"
30 #include "nodes/makefuncs.h"
31 #include "optimizer/optimizer.h"
32 #include "parser/parse_coerce.h"
33 #include "parser/parse_collate.h"
34 #include "parser/parse_expr.h"
35 #include "parser/parse_relation.h"
36 #include "utils/acl.h"
37 #include "utils/builtins.h"
38 #include "utils/lsyscache.h"
39 #include "utils/rel.h"
40 #include "utils/rls.h"
41 
42 /*
43  * DoCopy executes the SQL COPY statement
44  *
45  * Either unload or reload contents of table <relation>, depending on <from>.
46  * (<from> = true means we are inserting into the table.) In the "TO" case
47  * we also support copying the output of an arbitrary SELECT, INSERT, UPDATE
48  * or DELETE query.
49  *
50  * If <pipe> is false, transfer is between the table and the file named
51  * <filename>. Otherwise, transfer is between the table and our regular
52  * input/output stream. The latter could be either stdin/stdout or a
53  * socket, depending on whether we're running under Postmaster control.
54  *
55  * Do not allow a Postgres user without the 'pg_read_server_files' or
56  * 'pg_write_server_files' role to read from or write to a file.
57  *
58  * Do not allow the copy if user doesn't have proper permission to access
59  * the table or the specifically requested columns.
60  */
61 void
62 DoCopy(ParseState *pstate, const CopyStmt *stmt,
63  int stmt_location, int stmt_len,
64  uint64 *processed)
65 {
66  bool is_from = stmt->is_from;
67  bool pipe = (stmt->filename == NULL);
68  Relation rel;
69  Oid relid;
70  RawStmt *query = NULL;
71  Node *whereClause = NULL;
72 
73  /*
74  * Disallow COPY to/from file or program except to users with the
75  * appropriate role.
76  */
77  if (!pipe)
78  {
79  if (stmt->is_program)
80  {
81  if (!has_privs_of_role(GetUserId(), ROLE_PG_EXECUTE_SERVER_PROGRAM))
82  ereport(ERROR,
83  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
84  errmsg("permission denied to COPY to or from an external program"),
85  errdetail("Only roles with privileges of the \"%s\" role may COPY to or from an external program.",
86  "pg_execute_server_program"),
87  errhint("Anyone can COPY to stdout or from stdin. "
88  "psql's \\copy command also works for anyone.")));
89  }
90  else
91  {
92  if (is_from && !has_privs_of_role(GetUserId(), ROLE_PG_READ_SERVER_FILES))
93  ereport(ERROR,
94  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
95  errmsg("permission denied to COPY from a file"),
96  errdetail("Only roles with privileges of the \"%s\" role may COPY from a file.",
97  "pg_read_server_files"),
98  errhint("Anyone can COPY to stdout or from stdin. "
99  "psql's \\copy command also works for anyone.")));
100 
101  if (!is_from && !has_privs_of_role(GetUserId(), ROLE_PG_WRITE_SERVER_FILES))
102  ereport(ERROR,
103  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
104  errmsg("permission denied to COPY to a file"),
105  errdetail("Only roles with privileges of the \"%s\" role may COPY to a file.",
106  "pg_write_server_files"),
107  errhint("Anyone can COPY to stdout or from stdin. "
108  "psql's \\copy command also works for anyone.")));
109  }
110  }
111 
112  if (stmt->relation)
113  {
114  LOCKMODE lockmode = is_from ? RowExclusiveLock : AccessShareLock;
115  ParseNamespaceItem *nsitem;
116  RTEPermissionInfo *perminfo;
117  TupleDesc tupDesc;
118  List *attnums;
119  ListCell *cur;
120 
121  Assert(!stmt->query);
122 
123  /* Open and lock the relation, using the appropriate lock type. */
124  rel = table_openrv(stmt->relation, lockmode);
125 
126  relid = RelationGetRelid(rel);
127 
128  nsitem = addRangeTableEntryForRelation(pstate, rel, lockmode,
129  NULL, false, false);
130 
131  perminfo = nsitem->p_perminfo;
132  perminfo->requiredPerms = (is_from ? ACL_INSERT : ACL_SELECT);
133 
134  if (stmt->whereClause)
135  {
136  /* add nsitem to query namespace */
137  addNSItemToQuery(pstate, nsitem, false, true, true);
138 
139  /* Transform the raw expression tree */
140  whereClause = transformExpr(pstate, stmt->whereClause, EXPR_KIND_COPY_WHERE);
141 
142  /* Make sure it yields a boolean result. */
143  whereClause = coerce_to_boolean(pstate, whereClause, "WHERE");
144 
145  /* we have to fix its collations too */
146  assign_expr_collations(pstate, whereClause);
147 
148  whereClause = eval_const_expressions(NULL, whereClause);
149 
150  whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false);
151  whereClause = (Node *) make_ands_implicit((Expr *) whereClause);
152  }
153 
154  tupDesc = RelationGetDescr(rel);
155  attnums = CopyGetAttnums(tupDesc, rel, stmt->attlist);
156  foreach(cur, attnums)
157  {
158  int attno;
159  Bitmapset **bms;
160 
162  bms = is_from ? &perminfo->insertedCols : &perminfo->selectedCols;
163 
164  *bms = bms_add_member(*bms, attno);
165  }
166  ExecCheckPermissions(pstate->p_rtable, list_make1(perminfo), true);
167 
168  /*
169  * Permission check for row security policies.
170  *
171  * check_enable_rls will ereport(ERROR) if the user has requested
172  * something invalid and will otherwise indicate if we should enable
173  * RLS (returns RLS_ENABLED) or not for this COPY statement.
174  *
175  * If the relation has a row security policy and we are to apply it
176  * then perform a "query" copy and allow the normal query processing
177  * to handle the policies.
178  *
179  * If RLS is not enabled for this, then just fall through to the
180  * normal non-filtering relation handling.
181  */
182  if (check_enable_rls(relid, InvalidOid, false) == RLS_ENABLED)
183  {
185  ColumnRef *cr;
186  ResTarget *target;
187  RangeVar *from;
188  List *targetList = NIL;
189 
190  if (is_from)
191  ereport(ERROR,
192  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
193  errmsg("COPY FROM not supported with row-level security"),
194  errhint("Use INSERT statements instead.")));
195 
196  /*
197  * Build target list
198  *
199  * If no columns are specified in the attribute list of the COPY
200  * command, then the target list is 'all' columns. Therefore, '*'
201  * should be used as the target list for the resulting SELECT
202  * statement.
203  *
204  * In the case that columns are specified in the attribute list,
205  * create a ColumnRef and ResTarget for each column and add them
206  * to the target list for the resulting SELECT statement.
207  */
208  if (!stmt->attlist)
209  {
210  cr = makeNode(ColumnRef);
212  cr->location = -1;
213 
214  target = makeNode(ResTarget);
215  target->name = NULL;
216  target->indirection = NIL;
217  target->val = (Node *) cr;
218  target->location = -1;
219 
220  targetList = list_make1(target);
221  }
222  else
223  {
224  ListCell *lc;
225 
226  foreach(lc, stmt->attlist)
227  {
228  /*
229  * Build the ColumnRef for each column. The ColumnRef
230  * 'fields' property is a String node that corresponds to
231  * the column name respectively.
232  */
233  cr = makeNode(ColumnRef);
234  cr->fields = list_make1(lfirst(lc));
235  cr->location = -1;
236 
237  /* Build the ResTarget and add the ColumnRef to it. */
238  target = makeNode(ResTarget);
239  target->name = NULL;
240  target->indirection = NIL;
241  target->val = (Node *) cr;
242  target->location = -1;
243 
244  /* Add each column to the SELECT statement's target list */
245  targetList = lappend(targetList, target);
246  }
247  }
248 
249  /*
250  * Build RangeVar for from clause, fully qualified based on the
251  * relation which we have opened and locked. Use "ONLY" so that
252  * COPY retrieves rows from only the target table not any
253  * inheritance children, the same as when RLS doesn't apply.
254  */
257  -1);
258  from->inh = false; /* apply ONLY */
259 
260  /* Build query */
262  select->targetList = targetList;
263  select->fromClause = list_make1(from);
264 
265  query = makeNode(RawStmt);
266  query->stmt = (Node *) select;
267  query->stmt_location = stmt_location;
268  query->stmt_len = stmt_len;
269 
270  /*
271  * Close the relation for now, but keep the lock on it to prevent
272  * changes between now and when we start the query-based COPY.
273  *
274  * We'll reopen it later as part of the query-based COPY.
275  */
276  table_close(rel, NoLock);
277  rel = NULL;
278  }
279  }
280  else
281  {
282  Assert(stmt->query);
283 
284  query = makeNode(RawStmt);
285  query->stmt = stmt->query;
286  query->stmt_location = stmt_location;
287  query->stmt_len = stmt_len;
288 
289  relid = InvalidOid;
290  rel = NULL;
291  }
292 
293  if (is_from)
294  {
295  CopyFromState cstate;
296 
297  Assert(rel);
298 
299  /* check read-only transaction and parallel mode */
300  if (XactReadOnly && !rel->rd_islocaltemp)
301  PreventCommandIfReadOnly("COPY FROM");
302 
303  cstate = BeginCopyFrom(pstate, rel, whereClause,
304  stmt->filename, stmt->is_program,
305  NULL, stmt->attlist, stmt->options);
306  *processed = CopyFrom(cstate); /* copy from file to database */
307  EndCopyFrom(cstate);
308  }
309  else
310  {
311  CopyToState cstate;
312 
313  cstate = BeginCopyTo(pstate, rel, query, relid,
314  stmt->filename, stmt->is_program,
315  NULL, stmt->attlist, stmt->options);
316  *processed = DoCopyTo(cstate); /* copy from database to file */
317  EndCopyTo(cstate);
318  }
319 
320  if (rel != NULL)
321  table_close(rel, NoLock);
322 }
323 
324 /*
325  * Extract a CopyHeaderChoice value from a DefElem. This is like
326  * defGetBoolean() but also accepts the special value "match".
327  */
328 static CopyHeaderChoice
329 defGetCopyHeaderChoice(DefElem *def, bool is_from)
330 {
331  /*
332  * If no parameter value given, assume "true" is meant.
333  */
334  if (def->arg == NULL)
335  return COPY_HEADER_TRUE;
336 
337  /*
338  * Allow 0, 1, "true", "false", "on", "off", or "match".
339  */
340  switch (nodeTag(def->arg))
341  {
342  case T_Integer:
343  switch (intVal(def->arg))
344  {
345  case 0:
346  return COPY_HEADER_FALSE;
347  case 1:
348  return COPY_HEADER_TRUE;
349  default:
350  /* otherwise, error out below */
351  break;
352  }
353  break;
354  default:
355  {
356  char *sval = defGetString(def);
357 
358  /*
359  * The set of strings accepted here should match up with the
360  * grammar's opt_boolean_or_string production.
361  */
362  if (pg_strcasecmp(sval, "true") == 0)
363  return COPY_HEADER_TRUE;
364  if (pg_strcasecmp(sval, "false") == 0)
365  return COPY_HEADER_FALSE;
366  if (pg_strcasecmp(sval, "on") == 0)
367  return COPY_HEADER_TRUE;
368  if (pg_strcasecmp(sval, "off") == 0)
369  return COPY_HEADER_FALSE;
370  if (pg_strcasecmp(sval, "match") == 0)
371  {
372  if (!is_from)
373  ereport(ERROR,
374  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
375  errmsg("cannot use \"%s\" with HEADER in COPY TO",
376  sval)));
377  return COPY_HEADER_MATCH;
378  }
379  }
380  break;
381  }
382  ereport(ERROR,
383  (errcode(ERRCODE_SYNTAX_ERROR),
384  errmsg("%s requires a Boolean value or \"match\"",
385  def->defname)));
386  return COPY_HEADER_FALSE; /* keep compiler quiet */
387 }
388 
389 /*
390  * Extract a CopyOnErrorChoice value from a DefElem.
391  */
392 static CopyOnErrorChoice
393 defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
394 {
395  char *sval;
396 
397  if (!is_from)
398  ereport(ERROR,
399  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
400  errmsg("COPY ON_ERROR cannot be used with COPY TO"),
401  parser_errposition(pstate, def->location)));
402 
403  /*
404  * If no parameter value given, assume the default value.
405  */
406  if (def->arg == NULL)
407  return COPY_ON_ERROR_STOP;
408 
409  /*
410  * Allow "stop", or "ignore" values.
411  */
412  sval = defGetString(def);
413  if (pg_strcasecmp(sval, "stop") == 0)
414  return COPY_ON_ERROR_STOP;
415  if (pg_strcasecmp(sval, "ignore") == 0)
416  return COPY_ON_ERROR_IGNORE;
417 
418  ereport(ERROR,
419  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
420  errmsg("COPY ON_ERROR \"%s\" not recognized", sval),
421  parser_errposition(pstate, def->location)));
422  return COPY_ON_ERROR_STOP; /* keep compiler quiet */
423 }
424 
425 /*
426  * Process the statement option list for COPY.
427  *
428  * Scan the options list (a list of DefElem) and transpose the information
429  * into *opts_out, applying appropriate error checking.
430  *
431  * If 'opts_out' is not NULL, it is assumed to be filled with zeroes initially.
432  *
433  * This is exported so that external users of the COPY API can sanity-check
434  * a list of options. In that usage, 'opts_out' can be passed as NULL and
435  * the collected data is just leaked until CurrentMemoryContext is reset.
436  *
437  * Note that additional checking, such as whether column names listed in FORCE
438  * QUOTE actually exist, has to be applied later. This just checks for
439  * self-consistency of the options list.
440  */
441 void
443  CopyFormatOptions *opts_out,
444  bool is_from,
445  List *options)
446 {
447  bool format_specified = false;
448  bool freeze_specified = false;
449  bool header_specified = false;
450  bool on_error_specified = false;
451  ListCell *option;
452 
453  /* Support external use for option sanity checking */
454  if (opts_out == NULL)
455  opts_out = (CopyFormatOptions *) palloc0(sizeof(CopyFormatOptions));
456 
457  opts_out->file_encoding = -1;
458 
459  /* Extract options from the statement node tree */
460  foreach(option, options)
461  {
462  DefElem *defel = lfirst_node(DefElem, option);
463 
464  if (strcmp(defel->defname, "format") == 0)
465  {
466  char *fmt = defGetString(defel);
467 
468  if (format_specified)
469  errorConflictingDefElem(defel, pstate);
470  format_specified = true;
471  if (strcmp(fmt, "text") == 0)
472  /* default format */ ;
473  else if (strcmp(fmt, "csv") == 0)
474  opts_out->csv_mode = true;
475  else if (strcmp(fmt, "binary") == 0)
476  opts_out->binary = true;
477  else
478  ereport(ERROR,
479  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
480  errmsg("COPY format \"%s\" not recognized", fmt),
481  parser_errposition(pstate, defel->location)));
482  }
483  else if (strcmp(defel->defname, "freeze") == 0)
484  {
485  if (freeze_specified)
486  errorConflictingDefElem(defel, pstate);
487  freeze_specified = true;
488  opts_out->freeze = defGetBoolean(defel);
489  }
490  else if (strcmp(defel->defname, "delimiter") == 0)
491  {
492  if (opts_out->delim)
493  errorConflictingDefElem(defel, pstate);
494  opts_out->delim = defGetString(defel);
495  }
496  else if (strcmp(defel->defname, "null") == 0)
497  {
498  if (opts_out->null_print)
499  errorConflictingDefElem(defel, pstate);
500  opts_out->null_print = defGetString(defel);
501  }
502  else if (strcmp(defel->defname, "default") == 0)
503  {
504  if (opts_out->default_print)
505  errorConflictingDefElem(defel, pstate);
506  opts_out->default_print = defGetString(defel);
507  }
508  else if (strcmp(defel->defname, "header") == 0)
509  {
510  if (header_specified)
511  errorConflictingDefElem(defel, pstate);
512  header_specified = true;
513  opts_out->header_line = defGetCopyHeaderChoice(defel, is_from);
514  }
515  else if (strcmp(defel->defname, "quote") == 0)
516  {
517  if (opts_out->quote)
518  errorConflictingDefElem(defel, pstate);
519  opts_out->quote = defGetString(defel);
520  }
521  else if (strcmp(defel->defname, "escape") == 0)
522  {
523  if (opts_out->escape)
524  errorConflictingDefElem(defel, pstate);
525  opts_out->escape = defGetString(defel);
526  }
527  else if (strcmp(defel->defname, "force_quote") == 0)
528  {
529  if (opts_out->force_quote || opts_out->force_quote_all)
530  errorConflictingDefElem(defel, pstate);
531  if (defel->arg && IsA(defel->arg, A_Star))
532  opts_out->force_quote_all = true;
533  else if (defel->arg && IsA(defel->arg, List))
534  opts_out->force_quote = castNode(List, defel->arg);
535  else
536  ereport(ERROR,
537  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
538  errmsg("argument to option \"%s\" must be a list of column names",
539  defel->defname),
540  parser_errposition(pstate, defel->location)));
541  }
542  else if (strcmp(defel->defname, "force_not_null") == 0)
543  {
544  if (opts_out->force_notnull || opts_out->force_notnull_all)
545  errorConflictingDefElem(defel, pstate);
546  if (defel->arg && IsA(defel->arg, A_Star))
547  opts_out->force_notnull_all = true;
548  else if (defel->arg && IsA(defel->arg, List))
549  opts_out->force_notnull = castNode(List, defel->arg);
550  else
551  ereport(ERROR,
552  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
553  errmsg("argument to option \"%s\" must be a list of column names",
554  defel->defname),
555  parser_errposition(pstate, defel->location)));
556  }
557  else if (strcmp(defel->defname, "force_null") == 0)
558  {
559  if (opts_out->force_null || opts_out->force_null_all)
560  errorConflictingDefElem(defel, pstate);
561  if (defel->arg && IsA(defel->arg, A_Star))
562  opts_out->force_null_all = true;
563  else if (defel->arg && IsA(defel->arg, List))
564  opts_out->force_null = castNode(List, defel->arg);
565  else
566  ereport(ERROR,
567  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
568  errmsg("argument to option \"%s\" must be a list of column names",
569  defel->defname),
570  parser_errposition(pstate, defel->location)));
571  }
572  else if (strcmp(defel->defname, "convert_selectively") == 0)
573  {
574  /*
575  * Undocumented, not-accessible-from-SQL option: convert only the
576  * named columns to binary form, storing the rest as NULLs. It's
577  * allowed for the column list to be NIL.
578  */
579  if (opts_out->convert_selectively)
580  errorConflictingDefElem(defel, pstate);
581  opts_out->convert_selectively = true;
582  if (defel->arg == NULL || IsA(defel->arg, List))
583  opts_out->convert_select = castNode(List, defel->arg);
584  else
585  ereport(ERROR,
586  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
587  errmsg("argument to option \"%s\" must be a list of column names",
588  defel->defname),
589  parser_errposition(pstate, defel->location)));
590  }
591  else if (strcmp(defel->defname, "encoding") == 0)
592  {
593  if (opts_out->file_encoding >= 0)
594  errorConflictingDefElem(defel, pstate);
595  opts_out->file_encoding = pg_char_to_encoding(defGetString(defel));
596  if (opts_out->file_encoding < 0)
597  ereport(ERROR,
598  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
599  errmsg("argument to option \"%s\" must be a valid encoding name",
600  defel->defname),
601  parser_errposition(pstate, defel->location)));
602  }
603  else if (strcmp(defel->defname, "on_error") == 0)
604  {
605  if (on_error_specified)
606  errorConflictingDefElem(defel, pstate);
607  on_error_specified = true;
608  opts_out->on_error = defGetCopyOnErrorChoice(defel, pstate, is_from);
609  }
610  else
611  ereport(ERROR,
612  (errcode(ERRCODE_SYNTAX_ERROR),
613  errmsg("option \"%s\" not recognized",
614  defel->defname),
615  parser_errposition(pstate, defel->location)));
616  }
617 
618  /*
619  * Check for incompatible options (must do these two before inserting
620  * defaults)
621  */
622  if (opts_out->binary && opts_out->delim)
623  ereport(ERROR,
624  (errcode(ERRCODE_SYNTAX_ERROR),
625  errmsg("cannot specify DELIMITER in BINARY mode")));
626 
627  if (opts_out->binary && opts_out->null_print)
628  ereport(ERROR,
629  (errcode(ERRCODE_SYNTAX_ERROR),
630  errmsg("cannot specify NULL in BINARY mode")));
631 
632  if (opts_out->binary && opts_out->default_print)
633  ereport(ERROR,
634  (errcode(ERRCODE_SYNTAX_ERROR),
635  errmsg("cannot specify DEFAULT in BINARY mode")));
636 
637  if (opts_out->binary && opts_out->on_error != COPY_ON_ERROR_STOP)
638  ereport(ERROR,
639  (errcode(ERRCODE_SYNTAX_ERROR),
640  errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
641 
642  /* Set defaults for omitted options */
643  if (!opts_out->delim)
644  opts_out->delim = opts_out->csv_mode ? "," : "\t";
645 
646  if (!opts_out->null_print)
647  opts_out->null_print = opts_out->csv_mode ? "" : "\\N";
648  opts_out->null_print_len = strlen(opts_out->null_print);
649 
650  if (opts_out->csv_mode)
651  {
652  if (!opts_out->quote)
653  opts_out->quote = "\"";
654  if (!opts_out->escape)
655  opts_out->escape = opts_out->quote;
656  }
657 
658  /* Only single-byte delimiter strings are supported. */
659  if (strlen(opts_out->delim) != 1)
660  ereport(ERROR,
661  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
662  errmsg("COPY delimiter must be a single one-byte character")));
663 
664  /* Disallow end-of-line characters */
665  if (strchr(opts_out->delim, '\r') != NULL ||
666  strchr(opts_out->delim, '\n') != NULL)
667  ereport(ERROR,
668  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
669  errmsg("COPY delimiter cannot be newline or carriage return")));
670 
671  if (strchr(opts_out->null_print, '\r') != NULL ||
672  strchr(opts_out->null_print, '\n') != NULL)
673  ereport(ERROR,
674  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
675  errmsg("COPY null representation cannot use newline or carriage return")));
676 
677  if (opts_out->default_print)
678  {
679  opts_out->default_print_len = strlen(opts_out->default_print);
680 
681  if (strchr(opts_out->default_print, '\r') != NULL ||
682  strchr(opts_out->default_print, '\n') != NULL)
683  ereport(ERROR,
684  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
685  errmsg("COPY default representation cannot use newline or carriage return")));
686  }
687 
688  /*
689  * Disallow unsafe delimiter characters in non-CSV mode. We can't allow
690  * backslash because it would be ambiguous. We can't allow the other
691  * cases because data characters matching the delimiter must be
692  * backslashed, and certain backslash combinations are interpreted
693  * non-literally by COPY IN. Disallowing all lower case ASCII letters is
694  * more than strictly necessary, but seems best for consistency and
695  * future-proofing. Likewise we disallow all digits though only octal
696  * digits are actually dangerous.
697  */
698  if (!opts_out->csv_mode &&
699  strchr("\\.abcdefghijklmnopqrstuvwxyz0123456789",
700  opts_out->delim[0]) != NULL)
701  ereport(ERROR,
702  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
703  errmsg("COPY delimiter cannot be \"%s\"", opts_out->delim)));
704 
705  /* Check header */
706  if (opts_out->binary && opts_out->header_line)
707  ereport(ERROR,
708  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
709  errmsg("cannot specify HEADER in BINARY mode")));
710 
711  /* Check quote */
712  if (!opts_out->csv_mode && opts_out->quote != NULL)
713  ereport(ERROR,
714  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
715  errmsg("COPY QUOTE requires CSV mode")));
716 
717  if (opts_out->csv_mode && strlen(opts_out->quote) != 1)
718  ereport(ERROR,
719  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
720  errmsg("COPY quote must be a single one-byte character")));
721 
722  if (opts_out->csv_mode && opts_out->delim[0] == opts_out->quote[0])
723  ereport(ERROR,
724  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
725  errmsg("COPY delimiter and quote must be different")));
726 
727  /* Check escape */
728  if (!opts_out->csv_mode && opts_out->escape != NULL)
729  ereport(ERROR,
730  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
731  errmsg("COPY ESCAPE requires CSV mode")));
732 
733  if (opts_out->csv_mode && strlen(opts_out->escape) != 1)
734  ereport(ERROR,
735  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
736  errmsg("COPY escape must be a single one-byte character")));
737 
738  /* Check force_quote */
739  if (!opts_out->csv_mode && (opts_out->force_quote || opts_out->force_quote_all))
740  ereport(ERROR,
741  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
742  errmsg("COPY FORCE_QUOTE requires CSV mode")));
743  if ((opts_out->force_quote || opts_out->force_quote_all) && is_from)
744  ereport(ERROR,
745  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
746  errmsg("COPY FORCE_QUOTE cannot be used with COPY FROM")));
747 
748  /* Check force_notnull */
749  if (!opts_out->csv_mode && opts_out->force_notnull != NIL)
750  ereport(ERROR,
751  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
752  errmsg("COPY FORCE_NOT_NULL requires CSV mode")));
753  if (opts_out->force_notnull != NIL && !is_from)
754  ereport(ERROR,
755  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
756  errmsg("COPY FORCE_NOT_NULL cannot be used with COPY TO")));
757 
758  /* Check force_null */
759  if (!opts_out->csv_mode && opts_out->force_null != NIL)
760  ereport(ERROR,
761  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
762  errmsg("COPY FORCE_NULL requires CSV mode")));
763 
764  if (opts_out->force_null != NIL && !is_from)
765  ereport(ERROR,
766  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
767  errmsg("COPY FORCE_NULL cannot be used with COPY TO")));
768 
769  /* Don't allow the delimiter to appear in the null string. */
770  if (strchr(opts_out->null_print, opts_out->delim[0]) != NULL)
771  ereport(ERROR,
772  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
773  errmsg("COPY delimiter character must not appear in the NULL specification")));
774 
775  /* Don't allow the CSV quote char to appear in the null string. */
776  if (opts_out->csv_mode &&
777  strchr(opts_out->null_print, opts_out->quote[0]) != NULL)
778  ereport(ERROR,
779  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
780  errmsg("CSV quote character must not appear in the NULL specification")));
781 
782  /* Check freeze */
783  if (opts_out->freeze && !is_from)
784  ereport(ERROR,
785  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
786  errmsg("COPY FREEZE cannot be used with COPY TO")));
787 
788  if (opts_out->default_print)
789  {
790  if (!is_from)
791  ereport(ERROR,
792  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
793  errmsg("COPY DEFAULT only available using COPY FROM")));
794 
795  /* Don't allow the delimiter to appear in the default string. */
796  if (strchr(opts_out->default_print, opts_out->delim[0]) != NULL)
797  ereport(ERROR,
798  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
799  errmsg("COPY delimiter must not appear in the DEFAULT specification")));
800 
801  /* Don't allow the CSV quote char to appear in the default string. */
802  if (opts_out->csv_mode &&
803  strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
804  ereport(ERROR,
805  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
806  errmsg("CSV quote character must not appear in the DEFAULT specification")));
807 
808  /* Don't allow the NULL and DEFAULT string to be the same */
809  if (opts_out->null_print_len == opts_out->default_print_len &&
810  strncmp(opts_out->null_print, opts_out->default_print,
811  opts_out->null_print_len) == 0)
812  ereport(ERROR,
813  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
814  errmsg("NULL specification and DEFAULT specification cannot be the same")));
815  }
816 }
817 
818 /*
819  * CopyGetAttnums - build an integer list of attnums to be copied
820  *
821  * The input attnamelist is either the user-specified column list,
822  * or NIL if there was none (in which case we want all the non-dropped
823  * columns).
824  *
825  * We don't include generated columns in the generated full list and we don't
826  * allow them to be specified explicitly. They don't make sense for COPY
827  * FROM, but we could possibly allow them for COPY TO. But this way it's at
828  * least ensured that whatever we copy out can be copied back in.
829  *
830  * rel can be NULL ... it's only used for error reports.
831  */
832 List *
833 CopyGetAttnums(TupleDesc tupDesc, Relation rel, List *attnamelist)
834 {
835  List *attnums = NIL;
836 
837  if (attnamelist == NIL)
838  {
839  /* Generate default column list */
840  int attr_count = tupDesc->natts;
841  int i;
842 
843  for (i = 0; i < attr_count; i++)
844  {
845  if (TupleDescAttr(tupDesc, i)->attisdropped)
846  continue;
847  if (TupleDescAttr(tupDesc, i)->attgenerated)
848  continue;
849  attnums = lappend_int(attnums, i + 1);
850  }
851  }
852  else
853  {
854  /* Validate the user-supplied list and extract attnums */
855  ListCell *l;
856 
857  foreach(l, attnamelist)
858  {
859  char *name = strVal(lfirst(l));
860  int attnum;
861  int i;
862 
863  /* Lookup column name */
865  for (i = 0; i < tupDesc->natts; i++)
866  {
867  Form_pg_attribute att = TupleDescAttr(tupDesc, i);
868 
869  if (att->attisdropped)
870  continue;
871  if (namestrcmp(&(att->attname), name) == 0)
872  {
873  if (att->attgenerated)
874  ereport(ERROR,
875  (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
876  errmsg("column \"%s\" is a generated column",
877  name),
878  errdetail("Generated columns cannot be used in COPY.")));
879  attnum = att->attnum;
880  break;
881  }
882  }
883  if (attnum == InvalidAttrNumber)
884  {
885  if (rel != NULL)
886  ereport(ERROR,
887  (errcode(ERRCODE_UNDEFINED_COLUMN),
888  errmsg("column \"%s\" of relation \"%s\" does not exist",
889  name, RelationGetRelationName(rel))));
890  else
891  ereport(ERROR,
892  (errcode(ERRCODE_UNDEFINED_COLUMN),
893  errmsg("column \"%s\" does not exist",
894  name)));
895  }
896  /* Check for duplicates */
897  if (list_member_int(attnums, attnum))
898  ereport(ERROR,
899  (errcode(ERRCODE_DUPLICATE_COLUMN),
900  errmsg("column \"%s\" specified more than once",
901  name)));
902  attnums = lappend_int(attnums, attnum);
903  }
904  }
905 
906  return attnums;
907 }
bool has_privs_of_role(Oid member, Oid role)
Definition: acl.c:5128
#define InvalidAttrNumber
Definition: attnum.h:23
static CopyHeaderChoice defGetCopyHeaderChoice(DefElem *def, bool is_from)
Definition: copy.c:329
List * CopyGetAttnums(TupleDesc tupDesc, Relation rel, List *attnamelist)
Definition: copy.c:833
void DoCopy(ParseState *pstate, const CopyStmt *stmt, int stmt_location, int stmt_len, uint64 *processed)
Definition: copy.c:62
static CopyOnErrorChoice defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
Definition: copy.c:393
void ProcessCopyOptions(ParseState *pstate, CopyFormatOptions *opts_out, bool is_from, List *options)
Definition: copy.c:442
Bitmapset * bms_add_member(Bitmapset *a, int x)
Definition: bitmapset.c:815
Node * eval_const_expressions(PlannerInfo *root, Node *node)
Definition: clauses.c:2254
CopyFromState BeginCopyFrom(ParseState *pstate, Relation rel, Node *whereClause, const char *filename, bool is_program, copy_data_source_cb data_source_cb, List *attnamelist, List *options)
Definition: copyfrom.c:1370
uint64 CopyFrom(CopyFromState cstate)
Definition: copyfrom.c:630
void EndCopyFrom(CopyFromState cstate)
Definition: copyfrom.c:1787
uint64 DoCopyTo(CopyToState cstate)
Definition: copyto.c:742
CopyToState BeginCopyTo(ParseState *pstate, Relation rel, RawStmt *raw_query, Oid queryRelId, const char *filename, bool is_program, copy_data_dest_cb data_dest_cb, List *attnamelist, List *options)
Definition: copyto.c:350
void EndCopyTo(CopyToState cstate)
Definition: copyto.c:721
bool defGetBoolean(DefElem *def)
Definition: define.c:107
char * defGetString(DefElem *def)
Definition: define.c:48
void errorConflictingDefElem(DefElem *defel, ParseState *pstate)
Definition: define.c:384
struct cursor * cur
Definition: ecpg.c:28
int errdetail(const char *fmt,...)
Definition: elog.c:1205
int errhint(const char *fmt,...)
Definition: elog.c:1319
int errcode(int sqlerrcode)
Definition: elog.c:859
int errmsg(const char *fmt,...)
Definition: elog.c:1072
#define ERROR
Definition: elog.h:39
#define ereport(elevel,...)
Definition: elog.h:149
bool ExecCheckPermissions(List *rangeTable, List *rteperminfos, bool ereport_on_violation)
Definition: execMain.c:579
CopyOnErrorChoice
Definition: copy.h:38
@ COPY_ON_ERROR_IGNORE
Definition: copy.h:40
@ COPY_ON_ERROR_STOP
Definition: copy.h:39
CopyHeaderChoice
Definition: copy.h:27
@ COPY_HEADER_TRUE
Definition: copy.h:29
@ COPY_HEADER_FALSE
Definition: copy.h:28
@ COPY_HEADER_MATCH
Definition: copy.h:30
#define stmt
Definition: indent_codes.h:59
int i
Definition: isn.c:73
static void const char * fmt
Assert(fmt[strlen(fmt) - 1] !='\n')
List * lappend(List *list, void *datum)
Definition: list.c:339
List * lappend_int(List *list, int datum)
Definition: list.c:357
bool list_member_int(const List *list, int datum)
Definition: list.c:702
int LOCKMODE
Definition: lockdefs.h:26
#define NoLock
Definition: lockdefs.h:34
#define AccessShareLock
Definition: lockdefs.h:36
#define RowExclusiveLock
Definition: lockdefs.h:38
char * get_namespace_name(Oid nspid)
Definition: lsyscache.c:3344
List * make_ands_implicit(Expr *clause)
Definition: makefuncs.c:721
RangeVar * makeRangeVar(char *schemaname, char *relname, int location)
Definition: makefuncs.c:424
char * pstrdup(const char *in)
Definition: mcxt.c:1683
void * palloc0(Size size)
Definition: mcxt.c:1334
Oid GetUserId(void)
Definition: miscinit.c:514
int namestrcmp(Name name, const char *str)
Definition: name.c:247
#define IsA(nodeptr, _type_)
Definition: nodes.h:158
#define nodeTag(nodeptr)
Definition: nodes.h:133
#define makeNode(_type_)
Definition: nodes.h:155
#define castNode(_type_, nodeptr)
Definition: nodes.h:176
Node * coerce_to_boolean(ParseState *pstate, Node *node, const char *constructName)
void assign_expr_collations(ParseState *pstate, Node *expr)
Node * transformExpr(ParseState *pstate, Node *expr, ParseExprKind exprKind)
Definition: parse_expr.c:121
int parser_errposition(ParseState *pstate, int location)
Definition: parse_node.c:106
@ EXPR_KIND_COPY_WHERE
Definition: parse_node.h:82
ParseNamespaceItem * addRangeTableEntryForRelation(ParseState *pstate, Relation rel, int lockmode, Alias *alias, bool inh, bool inFromCl)
void addNSItemToQuery(ParseState *pstate, ParseNamespaceItem *nsitem, bool addToJoinList, bool addToRelNameSpace, bool addToVarNameSpace)
#define ACL_INSERT
Definition: parsenodes.h:76
#define ACL_SELECT
Definition: parsenodes.h:77
int16 attnum
Definition: pg_attribute.h:74
FormData_pg_attribute * Form_pg_attribute
Definition: pg_attribute.h:209
#define lfirst(lc)
Definition: pg_list.h:172
#define lfirst_node(type, lc)
Definition: pg_list.h:176
#define NIL
Definition: pg_list.h:68
#define lfirst_int(lc)
Definition: pg_list.h:173
#define list_make1(x1)
Definition: pg_list.h:212
#define pg_char_to_encoding
Definition: pg_wchar.h:629
int pg_strcasecmp(const char *s1, const char *s2)
Definition: pgstrcasecmp.c:36
#define InvalidOid
Definition: postgres_ext.h:36
unsigned int Oid
Definition: postgres_ext.h:31
Expr * canonicalize_qual(Expr *qual, bool is_check)
Definition: prepqual.c:293
#define RelationGetRelid(relation)
Definition: rel.h:507
#define RelationGetDescr(relation)
Definition: rel.h:533
#define RelationGetRelationName(relation)
Definition: rel.h:541
#define RelationGetNamespace(relation)
Definition: rel.h:548
int check_enable_rls(Oid relid, Oid checkAsUser, bool noError)
Definition: rls.c:52
@ RLS_ENABLED
Definition: rls.h:45
ParseLoc location
Definition: parsenodes.h:294
List * fields
Definition: parsenodes.h:293
int default_print_len
Definition: copy.h:61
bool force_notnull_all
Definition: copy.h:69
bool force_quote_all
Definition: copy.h:66
bool freeze
Definition: copy.h:54
bool binary
Definition: copy.h:53
int null_print_len
Definition: copy.h:58
bool convert_selectively
Definition: copy.h:74
char * quote
Definition: copy.h:63
CopyOnErrorChoice on_error
Definition: copy.h:75
CopyHeaderChoice header_line
Definition: copy.h:56
List * force_quote
Definition: copy.h:65
char * escape
Definition: copy.h:64
char * null_print
Definition: copy.h:57
List * force_null
Definition: copy.h:71
char * delim
Definition: copy.h:62
List * convert_select
Definition: copy.h:76
bool force_null_all
Definition: copy.h:72
bool csv_mode
Definition: copy.h:55
int file_encoding
Definition: copy.h:51
char * default_print
Definition: copy.h:60
List * force_notnull
Definition: copy.h:68
char * defname
Definition: parsenodes.h:811
ParseLoc location
Definition: parsenodes.h:815
Node * arg
Definition: parsenodes.h:812
Definition: pg_list.h:54
Definition: nodes.h:129
RTEPermissionInfo * p_perminfo
Definition: parse_node.h:289
List * p_rtable
Definition: parse_node.h:194
Bitmapset * selectedCols
Definition: parsenodes.h:1280
AclMode requiredPerms
Definition: parsenodes.h:1278
Bitmapset * insertedCols
Definition: parsenodes.h:1281
bool inh
Definition: primnodes.h:85
ParseLoc stmt_location
Definition: parsenodes.h:1944
ParseLoc stmt_len
Definition: parsenodes.h:1945
Node * stmt
Definition: parsenodes.h:1943
bool rd_islocaltemp
Definition: rel.h:61
Node * val
Definition: parsenodes.h:518
ParseLoc location
Definition: parsenodes.h:519
List * indirection
Definition: parsenodes.h:517
char * name
Definition: parsenodes.h:516
#define FirstLowInvalidHeapAttributeNumber
Definition: sysattr.h:27
void table_close(Relation relation, LOCKMODE lockmode)
Definition: table.c:126
Relation table_openrv(const RangeVar *relation, LOCKMODE lockmode)
Definition: table.c:83
#define TupleDescAttr(tupdesc, i)
Definition: tupdesc.h:92
void PreventCommandIfReadOnly(const char *cmdname)
Definition: utility.c:404
#define intVal(v)
Definition: value.h:79
#define strVal(v)
Definition: value.h:82
const char * name
#define select(n, r, w, e, timeout)
Definition: win32_port.h:495
bool XactReadOnly
Definition: xact.c:80