API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 23rd, 2017
464
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 38.44 KB | None | 0 0
raw download clone embed print report
  1. From 18f328c8a20dfdbd805921c6dcb6d665067507a5 Mon Sep 17 00:00:00 2001
  2. From: Alex K <alex.lumir@gmail.com>
  3. Date: Fri, 9 Jun 2017 23:41:51 +0300
  4. Subject: [PATCH 1/8] Allow ignoring some errors during COPY FROM
  5.  
  6. ---
  7. contrib/file_fdw/file_fdw.c | 4 +-
  8. src/backend/commands/copy.c | 352 +++++++++++++++++++++++++-------------------
  9. src/include/commands/copy.h | 2 +-
  10. 3 files changed, 207 insertions(+), 151 deletions(-)
  11.  
  12. diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
  13. index 2396bd442f..c83887944a 100644
  14. --- a/contrib/file_fdw/file_fdw.c
  15. +++ b/contrib/file_fdw/file_fdw.c
  16. @@ -689,7 +689,7 @@ fileIterateForeignScan(ForeignScanState *node)
  17. {
  18. FileFdwExecutionState *festate = (FileFdwExecutionState *) node->fdw_state;
  19. TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
  20. - bool found;
  21. + int found;
  22. ErrorContextCallback errcallback;
  23.  
  24. /* Set up callback to identify error line number. */
  25. @@ -1080,7 +1080,7 @@ file_acquire_sample_rows(Relation onerel, int elevel,
  26. TupleDesc tupDesc;
  27. Datum *values;
  28. bool *nulls;
  29. - bool found;
  30. + int found;
  31. char *filename;
  32. bool is_program;
  33. List *options;
  34. diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
  35. index fc5f4f66ea..935ef960b0 100644
  36. --- a/src/backend/commands/copy.c
  37. +++ b/src/backend/commands/copy.c
  38. @@ -54,6 +54,16 @@
  39. #define OCTVALUE(c) ((c) - '0')
  40.  
  41. /*
  42. + NextCopyFrom states:
  43. + 0 – Error or stop
  44. + 1 – Successfully read data
  45. + 2 – Data with errors, skip
  46. +*/
  47. +#define NCF_STOP 0
  48. +#define NCF_SUCCESS 1
  49. +#define NCF_SKIP 2
  50. +
  51. +/*
  52. * Represents the different source/dest cases we need to worry about at
  53. * the bottom level
  54. */
  55. @@ -139,6 +149,7 @@ typedef struct CopyStateData
  56. int cur_lineno; /* line number for error messages */
  57. const char *cur_attname; /* current att for error messages */
  58. const char *cur_attval; /* current att value for error messages */
  59. + bool ignore_errors; /* ignore errors during COPY FROM */
  60.  
  61. /*
  62. * Working state for COPY TO/FROM
  63. @@ -2340,6 +2351,7 @@ CopyFrom(CopyState cstate)
  64. bool useHeapMultiInsert;
  65. int nBufferedTuples = 0;
  66. int prev_leaf_part_index = -1;
  67. + int next_cf_state; /* NextCopyFrom return state */
  68.  
  69. #define MAX_BUFFERED_TUPLES 1000
  70. HeapTuple *bufferedTuples = NULL; /* initialize to silence warning */
  71. @@ -2549,145 +2561,154 @@ CopyFrom(CopyState cstate)
  72. /* Switch into its memory context */
  73. MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
  74.  
  75. - if (!NextCopyFrom(cstate, econtext, values, nulls, &loaded_oid))
  76. - break;
  77. -
  78. - /* And now we can form the input tuple. */
  79. - tuple = heap_form_tuple(tupDesc, values, nulls);
  80. -
  81. - if (loaded_oid != InvalidOid)
  82. - HeapTupleSetOid(tuple, loaded_oid);
  83. -
  84. - /*
  85. - * Constraints might reference the tableoid column, so initialize
  86. - * t_tableOid before evaluating them.
  87. - */
  88. - tuple->t_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
  89. -
  90. - /* Triggers and stuff need to be invoked in query context. */
  91. - MemoryContextSwitchTo(oldcontext);
  92. -
  93. - /* Place tuple in tuple slot --- but slot shouldn't free it */
  94. - slot = myslot;
  95. - ExecStoreTuple(tuple, slot, InvalidBuffer, false);
  96. -
  97. - /* Determine the partition to heap_insert the tuple into */
  98. - if (cstate->partition_dispatch_info)
  99. - {
  100. - int leaf_part_index;
  101. - TupleConversionMap *map;
  102. -
  103. - /*
  104. - * Away we go ... If we end up not finding a partition after all,
  105. - * ExecFindPartition() does not return and errors out instead.
  106. - * Otherwise, the returned value is to be used as an index into
  107. - * arrays mt_partitions[] and mt_partition_tupconv_maps[] that
  108. - * will get us the ResultRelInfo and TupleConversionMap for the
  109. - * partition, respectively.
  110. - */
  111. - leaf_part_index = ExecFindPartition(resultRelInfo,
  112. - cstate->partition_dispatch_info,
  113. - slot,
  114. - estate);
  115. - Assert(leaf_part_index >= 0 &&
  116. - leaf_part_index < cstate->num_partitions);
  117. -
  118. - /*
  119. - * If this tuple is mapped to a partition that is not same as the
  120. - * previous one, we'd better make the bulk insert mechanism gets a
  121. - * new buffer.
  122. - */
  123. - if (prev_leaf_part_index != leaf_part_index)
  124. - {
  125. - ReleaseBulkInsertStatePin(bistate);
  126. - prev_leaf_part_index = leaf_part_index;
  127. - }
  128. -
  129. - /*
  130. - * Save the old ResultRelInfo and switch to the one corresponding
  131. - * to the selected partition.
  132. - */
  133. - saved_resultRelInfo = resultRelInfo;
  134. - resultRelInfo = cstate->partitions + leaf_part_index;
  135. -
  136. - /* We do not yet have a way to insert into a foreign partition */
  137. - if (resultRelInfo->ri_FdwRoutine)
  138. - ereport(ERROR,
  139. - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
  140. - errmsg("cannot route inserted tuples to a foreign table")));
  141. -
  142. - /*
  143. - * For ExecInsertIndexTuples() to work on the partition's indexes
  144. - */
  145. - estate->es_result_relation_info = resultRelInfo;
  146. -
  147. - /*
  148. - * If we're capturing transition tuples, we might need to convert
  149. - * from the partition rowtype to parent rowtype.
  150. - */
  151. - if (cstate->transition_capture != NULL)
  152. - {
  153. - if (resultRelInfo->ri_TrigDesc &&
  154. - (resultRelInfo->ri_TrigDesc->trig_insert_before_row ||
  155. - resultRelInfo->ri_TrigDesc->trig_insert_instead_row))
  156. - {
  157. - /*
  158. - * If there are any BEFORE or INSTEAD triggers on the
  159. - * partition, we'll have to be ready to convert their
  160. - * result back to tuplestore format.
  161. - */
  162. - cstate->transition_capture->tcs_original_insert_tuple = NULL;
  163. - cstate->transition_capture->tcs_map =
  164. - cstate->transition_tupconv_maps[leaf_part_index];
  165. - }
  166. - else
  167. - {
  168. - /*
  169. - * Otherwise, just remember the original unconverted
  170. - * tuple, to avoid a needless round trip conversion.
  171. - */
  172. - cstate->transition_capture->tcs_original_insert_tuple = tuple;
  173. - cstate->transition_capture->tcs_map = NULL;
  174. - }
  175. - }
  176. - /*
  177. - * We might need to convert from the parent rowtype to the
  178. - * partition rowtype.
  179. - */
  180. - map = cstate->partition_tupconv_maps[leaf_part_index];
  181. - if (map)
  182. - {
  183. - Relation partrel = resultRelInfo->ri_RelationDesc;
  184. -
  185. - tuple = do_convert_tuple(tuple, map);
  186. -
  187. - /*
  188. - * We must use the partition's tuple descriptor from this
  189. - * point on. Use a dedicated slot from this point on until
  190. - * we're finished dealing with the partition.
  191. - */
  192. - slot = cstate->partition_tuple_slot;
  193. - Assert(slot != NULL);
  194. - ExecSetSlotDescriptor(slot, RelationGetDescr(partrel));
  195. - ExecStoreTuple(tuple, slot, InvalidBuffer, true);
  196. - }
  197. + next_cf_state = NextCopyFrom(cstate, econtext, values, nulls, &loaded_oid);
  198.  
  199. - tuple->t_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
  200. - }
  201. -
  202. - skip_tuple = false;
  203. -
  204. - /* BEFORE ROW INSERT Triggers */
  205. - if (resultRelInfo->ri_TrigDesc &&
  206. - resultRelInfo->ri_TrigDesc->trig_insert_before_row)
  207. - {
  208. - slot = ExecBRInsertTriggers(estate, resultRelInfo, slot);
  209. -
  210. - if (slot == NULL) /* "do nothing" */
  211. - skip_tuple = true;
  212. - else /* trigger might have changed tuple */
  213. - tuple = ExecMaterializeSlot(slot);
  214. + if (!next_cf_state) {
  215. + break;
  216. }
  217. + else if (next_cf_state == NCF_SUCCESS)
  218. + {
  219. + /* And now we can form the input tuple. */
  220. + tuple = heap_form_tuple(tupDesc, values, nulls);
  221. +
  222. + if (loaded_oid != InvalidOid)
  223. + HeapTupleSetOid(tuple, loaded_oid);
  224. +
  225. + /*
  226. + * Constraints might reference the tableoid column, so initialize
  227. + * t_tableOid before evaluating them.
  228. + */
  229. + tuple->t_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
  230. +
  231. + /* Triggers and stuff need to be invoked in query context. */
  232. + MemoryContextSwitchTo(oldcontext);
  233. +
  234. + /* Place tuple in tuple slot --- but slot shouldn't free it */
  235. + slot = myslot;
  236. + ExecStoreTuple(tuple, slot, InvalidBuffer, false);
  237. +
  238. + /* Determine the partition to heap_insert the tuple into */
  239. + if (cstate->partition_dispatch_info)
  240. + {
  241. + int leaf_part_index;
  242. + TupleConversionMap *map;
  243. +
  244. + /*
  245. + * Away we go ... If we end up not finding a partition after all,
  246. + * ExecFindPartition() does not return and errors out instead.
  247. + * Otherwise, the returned value is to be used as an index into
  248. + * arrays mt_partitions[] and mt_partition_tupconv_maps[] that
  249. + * will get us the ResultRelInfo and TupleConversionMap for the
  250. + * partition, respectively.
  251. + */
  252. + leaf_part_index = ExecFindPartition(resultRelInfo,
  253. + cstate->partition_dispatch_info,
  254. + slot,
  255. + estate);
  256. + Assert(leaf_part_index >= 0 &&
  257. + leaf_part_index < cstate->num_partitions);
  258. +
  259. + /*
  260. + * If this tuple is mapped to a partition that is not same as the
  261. + * previous one, we'd better make the bulk insert mechanism gets a
  262. + * new buffer.
  263. + */
  264. + if (prev_leaf_part_index != leaf_part_index)
  265. + {
  266. + ReleaseBulkInsertStatePin(bistate);
  267. + prev_leaf_part_index = leaf_part_index;
  268. + }
  269. +
  270. + /*
  271. + * Save the old ResultRelInfo and switch to the one corresponding
  272. + * to the selected partition.
  273. + */
  274. + saved_resultRelInfo = resultRelInfo;
  275. + resultRelInfo = cstate->partitions + leaf_part_index;
  276. +
  277. + /* We do not yet have a way to insert into a foreign partition */
  278. + if (resultRelInfo->ri_FdwRoutine)
  279. + ereport(ERROR,
  280. + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
  281. + errmsg("cannot route inserted tuples to a foreign table")));
  282. +
  283. + /*
  284. + * For ExecInsertIndexTuples() to work on the partition's indexes
  285. + */
  286. + estate->es_result_relation_info = resultRelInfo;
  287. +
  288. + /*
  289. + * If we're capturing transition tuples, we might need to convert
  290. + * from the partition rowtype to parent rowtype.
  291. + */
  292. + if (cstate->transition_capture != NULL)
  293. + {
  294. + if (resultRelInfo->ri_TrigDesc &&
  295. + (resultRelInfo->ri_TrigDesc->trig_insert_before_row ||
  296. + resultRelInfo->ri_TrigDesc->trig_insert_instead_row))
  297. + {
  298. + /*
  299. + * If there are any BEFORE or INSTEAD triggers on the
  300. + * partition, we'll have to be ready to convert their
  301. + * result back to tuplestore format.
  302. + */
  303. + cstate->transition_capture->tcs_original_insert_tuple = NULL;
  304. + cstate->transition_capture->tcs_map =
  305. + cstate->transition_tupconv_maps[leaf_part_index];
  306. + }
  307. + else
  308. + {
  309. + /*
  310. + * Otherwise, just remember the original unconverted
  311. + * tuple, to avoid a needless round trip conversion.
  312. + */
  313. + cstate->transition_capture->tcs_original_insert_tuple = tuple;
  314. + cstate->transition_capture->tcs_map = NULL;
  315. + }
  316. + }
  317. + /*
  318. + * We might need to convert from the parent rowtype to the
  319. + * partition rowtype.
  320. + */
  321. + map = cstate->partition_tupconv_maps[leaf_part_index];
  322. + if (map)
  323. + {
  324. + Relation partrel = resultRelInfo->ri_RelationDesc;
  325. +
  326. + tuple = do_convert_tuple(tuple, map);
  327. +
  328. + /*
  329. + * We must use the partition's tuple descriptor from this
  330. + * point on. Use a dedicated slot from this point on until
  331. + * we're finished dealing with the partition.
  332. + */
  333. + slot = cstate->partition_tuple_slot;
  334. + Assert(slot != NULL);
  335. + ExecSetSlotDescriptor(slot, RelationGetDescr(partrel));
  336. + ExecStoreTuple(tuple, slot, InvalidBuffer, true);
  337. + }
  338. +
  339. + tuple->t_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
  340. + }
  341. +
  342. + skip_tuple = false;
  343. +
  344. + /* BEFORE ROW INSERT Triggers */
  345. + if (resultRelInfo->ri_TrigDesc &&
  346. + resultRelInfo->ri_TrigDesc->trig_insert_before_row)
  347. + {
  348. + slot = ExecBRInsertTriggers(estate, resultRelInfo, slot);
  349. +
  350. + if (slot == NULL) /* "do nothing" */
  351. + skip_tuple = true;
  352. + else /* trigger might have changed tuple */
  353. + tuple = ExecMaterializeSlot(slot);
  354. + }
  355. + }
  356. + else
  357. + {
  358. + skip_tuple = true;
  359. + }
  360.  
  361. if (!skip_tuple)
  362. {
  363. @@ -2985,6 +3006,7 @@ BeginCopyFrom(ParseState *pstate,
  364. cstate->cur_lineno = 0;
  365. cstate->cur_attname = NULL;
  366. cstate->cur_attval = NULL;
  367. + cstate->ignore_errors = true;
  368.  
  369. /* Set up variables to avoid per-attribute overhead. */
  370. initStringInfo(&cstate->attribute_buf);
  371. @@ -3262,7 +3284,7 @@ NextCopyFromRawFields(CopyState cstate, char ***fields, int *nfields)
  372. * relation passed to BeginCopyFrom. This function fills the arrays.
  373. * Oid of the tuple is returned with 'tupleOid' separately.
  374. */
  375. -bool
  376. +int
  377. NextCopyFrom(CopyState cstate, ExprContext *econtext,
  378. Datum *values, bool *nulls, Oid *tupleOid)
  379. {
  380. @@ -3280,11 +3302,20 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  381. int *defmap = cstate->defmap;
  382. ExprState **defexprs = cstate->defexprs;
  383.  
  384. + int error_level = ERROR; /* Error level for COPY FROM input data errors */
  385. + int exec_state = NCF_SUCCESS; /* Return code */
  386. +
  387. tupDesc = RelationGetDescr(cstate->rel);
  388. attr = tupDesc->attrs;
  389. num_phys_attrs = tupDesc->natts;
  390. attr_count = list_length(cstate->attnumlist);
  391. nfields = file_has_oids ? (attr_count + 1) : attr_count;
  392. +
  393. + /* Set error level to WARNING, if errors handling is turned on */
  394. + if (cstate->ignore_errors)
  395. + {
  396. + error_level = WARNING;
  397. + }
  398.  
  399. /* Initialize all values for row to NULL */
  400. MemSet(values, 0, num_phys_attrs * sizeof(Datum));
  401. @@ -3300,13 +3331,17 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  402.  
  403. /* read raw fields in the next line */
  404. if (!NextCopyFromRawFields(cstate, &field_strings, &fldct))
  405. - return false;
  406. + return NCF_STOP;
  407.  
  408. /* check for overflowing fields */
  409. if (nfields > 0 && fldct > nfields)
  410. - ereport(ERROR,
  411. + {
  412. + exec_state = NCF_SKIP;
  413. + ereport(error_level,
  414. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  415. errmsg("extra data after last expected column")));
  416. + }
  417. +
  418.  
  419. fieldno = 0;
  420.  
  421. @@ -3314,15 +3349,22 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  422. if (file_has_oids)
  423. {
  424. if (fieldno >= fldct)
  425. - ereport(ERROR,
  426. + {
  427. + exec_state = NCF_SKIP;
  428. + ereport(error_level,
  429. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  430. errmsg("missing data for OID column")));
  431. + }
  432. +
  433. string = field_strings[fieldno++];
  434.  
  435. if (string == NULL)
  436. - ereport(ERROR,
  437. + {
  438. + exec_state = NCF_SKIP;
  439. + ereport(error_level,
  440. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  441. errmsg("null OID in COPY data")));
  442. + }
  443. else if (cstate->oids && tupleOid != NULL)
  444. {
  445. cstate->cur_attname = "oid";
  446. @@ -3330,9 +3372,13 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  447. *tupleOid = DatumGetObjectId(DirectFunctionCall1(oidin,
  448. CStringGetDatum(string)));
  449. if (*tupleOid == InvalidOid)
  450. - ereport(ERROR,
  451. + {
  452. + exec_state = NCF_SKIP;
  453. + ereport(error_level,
  454. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  455. errmsg("invalid OID in COPY data")));
  456. + }
  457. +
  458. cstate->cur_attname = NULL;
  459. cstate->cur_attval = NULL;
  460. }
  461. @@ -3345,10 +3391,14 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  462. int m = attnum - 1;
  463.  
  464. if (fieldno >= fldct)
  465. - ereport(ERROR,
  466. + {
  467. + exec_state = NCF_SKIP;
  468. + ereport(error_level,
  469. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  470. errmsg("missing data for column \"%s\"",
  471. NameStr(attr[m]->attname))));
  472. + }
  473. +
  474. string = field_strings[fieldno++];
  475.  
  476. if (cstate->convert_select_flags &&
  477. @@ -3431,14 +3481,17 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  478. ereport(ERROR,
  479. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  480. errmsg("received copy data after EOF marker")));
  481. - return false;
  482. + return NCF_STOP;
  483. }
  484.  
  485. if (fld_count != attr_count)
  486. - ereport(ERROR,
  487. + {
  488. + exec_state = NCF_SKIP;
  489. + ereport(error_level,
  490. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  491. errmsg("row field count is %d, expected %d",
  492. (int) fld_count, attr_count)));
  493. + }
  494.  
  495. if (file_has_oids)
  496. {
  497. @@ -3453,9 +3506,12 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  498. -1,
  499. &isnull));
  500. if (isnull || loaded_oid == InvalidOid)
  501. - ereport(ERROR,
  502. + {
  503. + exec_state = NCF_SKIP;
  504. + ereport(error_level,
  505. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  506. errmsg("invalid OID in COPY data")));
  507. + }
  508. cstate->cur_attname = NULL;
  509. if (cstate->oids && tupleOid != NULL)
  510. *tupleOid = loaded_oid;
  511. @@ -3497,7 +3553,7 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  512. &nulls[defmap[i]]);
  513. }
  514.  
  515. - return true;
  516. + return exec_state;
  517. }
  518.  
  519. /*
  520. diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
  521. index 8b2971d287..bba64fc3d7 100644
  522. --- a/src/include/commands/copy.h
  523. +++ b/src/include/commands/copy.h
  524. @@ -31,7 +31,7 @@ extern void ProcessCopyOptions(ParseState *pstate, CopyState cstate, bool is_fro
  525. extern CopyState BeginCopyFrom(ParseState *pstate, Relation rel, const char *filename,
  526. bool is_program, copy_data_source_cb data_source_cb, List *attnamelist, List *options);
  527. extern void EndCopyFrom(CopyState cstate);
  528. -extern bool NextCopyFrom(CopyState cstate, ExprContext *econtext,
  529. +extern int NextCopyFrom(CopyState cstate, ExprContext *econtext,
  530. Datum *values, bool *nulls, Oid *tupleOid);
  531. extern bool NextCopyFromRawFields(CopyState cstate,
  532. char ***fields, int *nfields);
  533. --
  534. 2.11.0
  535.  
  536.  
  537. From 9b19c9e365c019ae0e3e56d05502044c5683fd09 Mon Sep 17 00:00:00 2001
  538. From: Alex K <alex.lumir@gmail.com>
  539. Date: Sun, 11 Jun 2017 16:48:37 +0300
  540. Subject: [PATCH 2/8] Report line number on each ERROR/WARNING
  541.  
  542. ---
  543. src/backend/commands/copy.c | 12 ++++++------
  544. 1 file changed, 6 insertions(+), 6 deletions(-)
  545.  
  546. diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
  547. index 935ef960b0..4dd9acd899 100644
  548. --- a/src/backend/commands/copy.c
  549. +++ b/src/backend/commands/copy.c
  550. @@ -3339,7 +3339,7 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  551. exec_state = NCF_SKIP;
  552. ereport(error_level,
  553. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  554. - errmsg("extra data after last expected column")));
  555. + errmsg("extra data after last expected column at line %d", cstate->cur_lineno)));
  556. }
  557.  
  558.  
  559. @@ -3353,7 +3353,7 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  560. exec_state = NCF_SKIP;
  561. ereport(error_level,
  562. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  563. - errmsg("missing data for OID column")));
  564. + errmsg("missing data for OID column at line %d", cstate->cur_lineno)));
  565. }
  566.  
  567. string = field_strings[fieldno++];
  568. @@ -3363,7 +3363,7 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  569. exec_state = NCF_SKIP;
  570. ereport(error_level,
  571. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  572. - errmsg("null OID in COPY data")));
  573. + errmsg("null OID in COPY data at line %d", cstate->cur_lineno)));
  574. }
  575. else if (cstate->oids && tupleOid != NULL)
  576. {
  577. @@ -3376,7 +3376,7 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  578. exec_state = NCF_SKIP;
  579. ereport(error_level,
  580. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  581. - errmsg("invalid OID in COPY data")));
  582. + errmsg("invalid OID in COPY data at line %d", cstate->cur_lineno)));
  583. }
  584.  
  585. cstate->cur_attname = NULL;
  586. @@ -3395,8 +3395,8 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  587. exec_state = NCF_SKIP;
  588. ereport(error_level,
  589. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  590. - errmsg("missing data for column \"%s\"",
  591. - NameStr(attr[m]->attname))));
  592. + errmsg("missing data for column \"%s\" at line %d",
  593. + NameStr(attr[m]->attname), cstate->cur_lineno)));
  594. }
  595.  
  596. string = field_strings[fieldno++];
  597. --
  598. 2.11.0
  599.  
  600.  
  601. From 9e395007d1b35dcaaa90fe884514091b6dec5dd9 Mon Sep 17 00:00:00 2001
  602. From: Alex K <alex.lumir@gmail.com>
  603. Date: Tue, 13 Jun 2017 14:57:59 +0300
  604. Subject: [PATCH 3/8] Catch errors at InputFunctionCall inside NextCopyFrom
  605.  
  606. ---
  607. src/backend/commands/copy.c | 29 +++++++++++++++++++++++++++++
  608. 1 file changed, 29 insertions(+)
  609.  
  610. diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
  611. index 4dd9acd899..be76c0df1e 100644
  612. --- a/src/backend/commands/copy.c
  613. +++ b/src/backend/commands/copy.c
  614. @@ -3304,6 +3304,7 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  615.  
  616. int error_level = ERROR; /* Error level for COPY FROM input data errors */
  617. int exec_state = NCF_SUCCESS; /* Return code */
  618. + MemoryContext oldcontext = CurrentMemoryContext;
  619.  
  620. tupDesc = RelationGetDescr(cstate->rel);
  621. attr = tupDesc->attrs;
  622. @@ -3434,10 +3435,38 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  623.  
  624. cstate->cur_attname = NameStr(attr[m]->attname);
  625. cstate->cur_attval = string;
  626. +
  627. + PG_TRY();
  628. + {
  629. values[m] = InputFunctionCall(&in_functions[m],
  630. string,
  631. typioparams[m],
  632. attr[m]->atttypmod);
  633. + }
  634. + PG_CATCH();
  635. + {
  636. + if (cstate->ignore_errors)
  637. + {
  638. + ErrorData *edata;
  639. +
  640. + /* Save error info */
  641. + MemoryContextSwitchTo(oldcontext);
  642. + edata = CopyErrorData();
  643. + FlushErrorState();
  644. +
  645. + /* TODO Find an appropriate errcode */
  646. + ereport(WARNING,
  647. + (errcode(ERRCODE_TOO_MANY_COLUMNS),
  648. + errmsg("%s at line %d col %d", edata->message, cstate->cur_lineno, attnum)));
  649. + return NCF_SKIP;
  650. + }
  651. + else
  652. + {
  653. + PG_RE_THROW();
  654. + }
  655. + }
  656. + PG_END_TRY();
  657. +
  658. if (string != NULL)
  659. nulls[m] = false;
  660. cstate->cur_attname = NULL;
  661. --
  662. 2.11.0
  663.  
  664.  
  665. From c5074cf2b4d7595a770c9ac891bf78386532b9f8 Mon Sep 17 00:00:00 2001
  666. From: Alex K <alex.lumir@gmail.com>
  667. Date: Tue, 13 Jun 2017 16:30:50 +0300
  668. Subject: [PATCH 4/8] Tabs vs spaces consistency
  669.  
  670. ---
  671. src/backend/commands/copy.c | 12 ++++++------
  672. 1 file changed, 6 insertions(+), 6 deletions(-)
  673.  
  674. diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
  675. index be76c0df1e..f1042f2f41 100644
  676. --- a/src/backend/commands/copy.c
  677. +++ b/src/backend/commands/copy.c
  678. @@ -3447,17 +3447,17 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  679. {
  680. if (cstate->ignore_errors)
  681. {
  682. - ErrorData *edata;
  683. + ErrorData *edata;
  684.  
  685. - /* Save error info */
  686. + /* Save error info */
  687. MemoryContextSwitchTo(oldcontext);
  688. - edata = CopyErrorData();
  689. + edata = CopyErrorData();
  690. FlushErrorState();
  691.  
  692. /* TODO Find an appropriate errcode */
  693. - ereport(WARNING,
  694. - (errcode(ERRCODE_TOO_MANY_COLUMNS),
  695. - errmsg("%s at line %d col %d", edata->message, cstate->cur_lineno, attnum)));
  696. + ereport(WARNING,
  697. + (errcode(ERRCODE_TOO_MANY_COLUMNS),
  698. + errmsg("%s at line %d col %d", edata->message, cstate->cur_lineno, attnum)));
  699. return NCF_SKIP;
  700. }
  701. else
  702. --
  703. 2.11.0
  704.  
  705.  
  706. From 9a8c8ebed9f6722738bc3bb3132d488074ef9654 Mon Sep 17 00:00:00 2001
  707. From: Alex K <alex.lumir@gmail.com>
  708. Date: Thu, 15 Jun 2017 20:35:51 +0300
  709. Subject: [PATCH 5/8] Fixed possible Segmentation fault during malformed lines
  710. processing
  711.  
  712. ---
  713. src/backend/commands/copy.c | 20 ++++++++++----------
  714. 1 file changed, 10 insertions(+), 10 deletions(-)
  715.  
  716. diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
  717. index f1042f2f41..cb5b4ad302 100644
  718. --- a/src/backend/commands/copy.c
  719. +++ b/src/backend/commands/copy.c
  720. @@ -3303,7 +3303,7 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  721. ExprState **defexprs = cstate->defexprs;
  722.  
  723. int error_level = ERROR; /* Error level for COPY FROM input data errors */
  724. - int exec_state = NCF_SUCCESS; /* Return code */
  725. + // int exec_state = NCF_SUCCESS; /* Return code */
  726. MemoryContext oldcontext = CurrentMemoryContext;
  727.  
  728. tupDesc = RelationGetDescr(cstate->rel);
  729. @@ -3337,10 +3337,10 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  730. /* check for overflowing fields */
  731. if (nfields > 0 && fldct > nfields)
  732. {
  733. - exec_state = NCF_SKIP;
  734. ereport(error_level,
  735. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  736. errmsg("extra data after last expected column at line %d", cstate->cur_lineno)));
  737. + return NCF_SKIP;
  738. }
  739.  
  740.  
  741. @@ -3351,20 +3351,20 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  742. {
  743. if (fieldno >= fldct)
  744. {
  745. - exec_state = NCF_SKIP;
  746. ereport(error_level,
  747. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  748. errmsg("missing data for OID column at line %d", cstate->cur_lineno)));
  749. + return NCF_SKIP;
  750. }
  751.  
  752. string = field_strings[fieldno++];
  753.  
  754. if (string == NULL)
  755. {
  756. - exec_state = NCF_SKIP;
  757. ereport(error_level,
  758. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  759. errmsg("null OID in COPY data at line %d", cstate->cur_lineno)));
  760. + return NCF_SKIP;
  761. }
  762. else if (cstate->oids && tupleOid != NULL)
  763. {
  764. @@ -3374,10 +3374,10 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  765. CStringGetDatum(string)));
  766. if (*tupleOid == InvalidOid)
  767. {
  768. - exec_state = NCF_SKIP;
  769. ereport(error_level,
  770. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  771. errmsg("invalid OID in COPY data at line %d", cstate->cur_lineno)));
  772. + return NCF_SKIP;
  773. }
  774.  
  775. cstate->cur_attname = NULL;
  776. @@ -3393,11 +3393,11 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  777.  
  778. if (fieldno >= fldct)
  779. {
  780. - exec_state = NCF_SKIP;
  781. ereport(error_level,
  782. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  783. errmsg("missing data for column \"%s\" at line %d",
  784. NameStr(attr[m]->attname), cstate->cur_lineno)));
  785. + return NCF_SKIP;
  786. }
  787.  
  788. string = field_strings[fieldno++];
  789. @@ -3486,7 +3486,7 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  790. if (!CopyGetInt16(cstate, &fld_count))
  791. {
  792. /* EOF detected (end of file, or protocol-level EOF) */
  793. - return false;
  794. + return NCF_STOP;
  795. }
  796.  
  797. if (fld_count == -1)
  798. @@ -3515,11 +3515,11 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  799.  
  800. if (fld_count != attr_count)
  801. {
  802. - exec_state = NCF_SKIP;
  803. ereport(error_level,
  804. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  805. errmsg("row field count is %d, expected %d",
  806. (int) fld_count, attr_count)));
  807. + return NCF_SKIP;
  808. }
  809.  
  810. if (file_has_oids)
  811. @@ -3536,10 +3536,10 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  812. &isnull));
  813. if (isnull || loaded_oid == InvalidOid)
  814. {
  815. - exec_state = NCF_SKIP;
  816. ereport(error_level,
  817. (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
  818. errmsg("invalid OID in COPY data")));
  819. + return NCF_SKIP;
  820. }
  821. cstate->cur_attname = NULL;
  822. if (cstate->oids && tupleOid != NULL)
  823. @@ -3582,7 +3582,7 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  824. &nulls[defmap[i]]);
  825. }
  826.  
  827. - return exec_state;
  828. + return NCF_SUCCESS;
  829. }
  830.  
  831. /*
  832. --
  833. 2.11.0
  834.  
  835.  
  836. From c4c93114eae5bbe6850e07c79f21be51f3f9b76c Mon Sep 17 00:00:00 2001
  837. From: Alex K <alex.lumir@gmail.com>
  838. Date: Fri, 16 Jun 2017 13:14:29 +0300
  839. Subject: [PATCH 6/8] Propagate catched SQL error code to warning
  840.  
  841. ---
  842. src/backend/commands/copy.c | 15 ++++++++++-----
  843. 1 file changed, 10 insertions(+), 5 deletions(-)
  844.  
  845. diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
  846. index cb5b4ad302..6305b3b0c5 100644
  847. --- a/src/backend/commands/copy.c
  848. +++ b/src/backend/commands/copy.c
  849. @@ -3302,8 +3302,8 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  850. int *defmap = cstate->defmap;
  851. ExprState **defexprs = cstate->defexprs;
  852.  
  853. - int error_level = ERROR; /* Error level for COPY FROM input data errors */
  854. - // int exec_state = NCF_SUCCESS; /* Return code */
  855. + /* Error level for COPY FROM input data errors */
  856. + int error_level = ERROR;
  857. MemoryContext oldcontext = CurrentMemoryContext;
  858.  
  859. tupDesc = RelationGetDescr(cstate->rel);
  860. @@ -3436,6 +3436,10 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  861. cstate->cur_attname = NameStr(attr[m]->attname);
  862. cstate->cur_attval = string;
  863.  
  864. + /*
  865. + * Catch errors inside InputFunctionCall to handle
  866. + * errors due to the type invalid syntax.
  867. + */
  868. PG_TRY();
  869. {
  870. values[m] = InputFunctionCall(&in_functions[m],
  871. @@ -3453,15 +3457,16 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
  872. MemoryContextSwitchTo(oldcontext);
  873. edata = CopyErrorData();
  874. FlushErrorState();
  875. -
  876. - /* TODO Find an appropriate errcode */
  877. +
  878. + /* Propagate catched ERROR sqlerrcode and message as WARNING */
  879. ereport(WARNING,
  880. - (errcode(ERRCODE_TOO_MANY_COLUMNS),
  881. + (errcode(edata->sqlerrcode),
  882. errmsg("%s at line %d col %d", edata->message, cstate->cur_lineno, attnum)));
  883. return NCF_SKIP;
  884. }
  885. else
  886. {
  887. + /* Propagate ERROR as is if errors handling is not turned on */
  888. PG_RE_THROW();
  889. }
  890. }
  891. --
  892. 2.11.0
  893.  
  894.  
  895. From cc2c804122693ca9caa7f49dc19bbb7c43dbe87c Mon Sep 17 00:00:00 2001
  896. From: Alex K <alex.lumir@gmail.com>
  897. Date: Mon, 19 Jun 2017 20:59:34 +0300
  898. Subject: [PATCH 7/8] IGNORE_ERRORS COPY SQL option added (only new syntax
  899. supported)
  900.  
  901. ---
  902. doc/src/sgml/ref/copy.sgml | 12 ++++++++++++
  903. src/backend/commands/copy.c | 10 +++++++++-
  904. src/backend/parser/gram.y | 6 +++++-
  905. 3 files changed, 26 insertions(+), 2 deletions(-)
  906.  
  907. diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
  908. index 8de1150dfb..5e322308d6 100644
  909. --- a/doc/src/sgml/ref/copy.sgml
  910. +++ b/doc/src/sgml/ref/copy.sgml
  911. @@ -44,6 +44,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
  912. FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
  913. FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
  914. ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
  915. + IGNORE_ERRORS [ <replaceable class="parameter">boolean</replaceable> ]
  916. </synopsis>
  917. </refsynopsisdiv>
  918.  
  919. @@ -249,6 +250,17 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
  920. </varlistentry>
  921.  
  922. <varlistentry>
  923. + <term><literal>IGNORE_ERRORS</literal></term>
  924. + <listitem>
  925. + <para>
  926. + Specifies ignoring errors in the input data. If TRUE, then all
  927. + errors due to the extra or missing columns and inappropriate type
  928. + format will be ignored with WARNING.
  929. + </para>
  930. + </listitem>
  931. + </varlistentry>
  932. +
  933. + <varlistentry>
  934. <term><literal>NULL</literal></term>
  935. <listitem>
  936. <para>
  937. diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
  938. index 6305b3b0c5..29c5376b2d 100644
  939. --- a/src/backend/commands/copy.c
  940. +++ b/src/backend/commands/copy.c
  941. @@ -1214,6 +1214,15 @@ ProcessCopyOptions(ParseState *pstate,
  942. defel->defname),
  943. parser_errposition(pstate, defel->location)));
  944. }
  945. + else if (strcmp(defel->defname, "ignore_errors") == 0)
  946. + {
  947. + if (cstate->ignore_errors)
  948. + ereport(ERROR,
  949. + (errcode(ERRCODE_SYNTAX_ERROR),
  950. + errmsg("conflicting or redundant options"),
  951. + parser_errposition(pstate, defel->location)));
  952. + cstate->ignore_errors = defGetBoolean(defel);
  953. + }
  954. else
  955. ereport(ERROR,
  956. (errcode(ERRCODE_SYNTAX_ERROR),
  957. @@ -3006,7 +3015,6 @@ BeginCopyFrom(ParseState *pstate,
  958. cstate->cur_lineno = 0;
  959. cstate->cur_attname = NULL;
  960. cstate->cur_attval = NULL;
  961. - cstate->ignore_errors = true;
  962.  
  963. /* Set up variables to avoid per-attribute overhead. */
  964. initStringInfo(&cstate->attribute_buf);
  965. diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
  966. index 0f3998ff89..83ff1ab72b 100644
  967. --- a/src/backend/parser/gram.y
  968. +++ b/src/backend/parser/gram.y
  969. @@ -636,7 +636,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
  970. IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P
  971. INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
  972. INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
  973. - INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
  974. + INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION IGNORE_ERRORS
  975.  
  976. JOIN
  977.  
  978. @@ -2977,6 +2977,10 @@ copy_opt_item:
  979. {
  980. $$ = makeDefElem("encoding", (Node *)makeString($2), @1);
  981. }
  982. + | IGNORE_ERRORS
  983. + {
  984. + $$ = makeDefElem("ignore_errors", (Node *)makeInteger(TRUE), @1);
  985. + }
  986. ;
  987.  
  988. /* The following exist for backward compatibility with very old versions */
  989. --
  990. 2.11.0
  991.  
  992.  
  993. From 659aec15f7fcc391daa084f43dc1e1a66bdbe48e Mon Sep 17 00:00:00 2001
  994. From: Alex K <alex.lumir@gmail.com>
  995. Date: Wed, 21 Jun 2017 14:32:44 +0300
  996. Subject: [PATCH 8/8] Regression test updated to handle new COPY functionality
  997.  
  998. ---
  999. src/test/regress/expected/alter_table.out | 2 +-
  1000. src/test/regress/expected/copy2.out | 35 +++++++++++++++++++++++++++----
  1001. src/test/regress/sql/copy2.sql | 23 ++++++++++++++++++++
  1002. 3 files changed, 55 insertions(+), 5 deletions(-)
  1003.  
  1004. diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
  1005. index 13d6a4b747..078ab27359 100644
  1006. --- a/src/test/regress/expected/alter_table.out
  1007. +++ b/src/test/regress/expected/alter_table.out
  1008. @@ -1431,7 +1431,7 @@ ERROR: column "a" of relation "test" does not exist
  1009. copy test("........pg.dropped.1........") to stdout;
  1010. ERROR: column "........pg.dropped.1........" of relation "test" does not exist
  1011. copy test from stdin;
  1012. -ERROR: extra data after last expected column
  1013. +ERROR: extra data after last expected column at line 1
  1014. CONTEXT: COPY test, line 1: "10 11 12"
  1015. select * from test;
  1016. b | c
  1017. diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
  1018. index 65e9c626b3..ab4d47b351 100644
  1019. --- a/src/test/regress/expected/copy2.out
  1020. +++ b/src/test/regress/expected/copy2.out
  1021. @@ -36,15 +36,26 @@ COPY x from stdin;
  1022. ERROR: invalid input syntax for integer: ""
  1023. CONTEXT: COPY x, line 1, column a: ""
  1024. COPY x from stdin;
  1025. -ERROR: missing data for column "e"
  1026. +ERROR: missing data for column "e" at line 1
  1027. CONTEXT: COPY x, line 1: "2000 230 23 23"
  1028. COPY x from stdin;
  1029. -ERROR: missing data for column "e"
  1030. +ERROR: missing data for column "e" at line 1
  1031. CONTEXT: COPY x, line 1: "2001 231 \N \N"
  1032. -- extra data: should fail
  1033. COPY x from stdin;
  1034. -ERROR: extra data after last expected column
  1035. +ERROR: extra data after last expected column at line 1
  1036. CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80"
  1037. +-- missing data: should be able to ignore errors
  1038. +COPY x from stdin with (ignore_errors);
  1039. +WARNING: missing data for column "e" at line 1
  1040. +COPY x from stdin with (ignore_errors);
  1041. +WARNING: missing data for column "e" at line 1
  1042. +-- extra data: should be able to ignore errors
  1043. +COPY x from stdin with (ignore_errors);
  1044. +WARNING: extra data after last expected column at line 1
  1045. +-- data type inappropriate format: should be able to ignore errors
  1046. +COPY x from stdin with (ignore_errors);
  1047. +WARNING: invalid input syntax for integer: "'24'" at line 2 col 2
  1048. -- various COPY options: delimiters, oids, NULL string, encoding
  1049. COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
  1050. COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
  1051. @@ -60,6 +71,10 @@ SELECT * FROM x;
  1052. 10003 | 24 | 34 | 44 | before trigger fired
  1053. 10004 | 25 | 35 | 45 | before trigger fired
  1054. 10005 | 26 | 36 | 46 | before trigger fired
  1055. + 2004 | 27 | 37 | 47 | before trigger fired
  1056. + 2006 | 28 | 38 | 48 | before trigger fired
  1057. + 2008 | 29 | 39 | 49 | before trigger fired
  1058. + 2009 | 30 | 40 | 50 | before trigger fired
  1059. 6 | | 45 | 80 | before trigger fired
  1060. 7 | | x | \x | before trigger fired
  1061. 8 | | , | \, | before trigger fired
  1062. @@ -78,7 +93,7 @@ SELECT * FROM x;
  1063. 3 | 3 | stuff | test_3 | after trigger fired
  1064. 4 | 4 | stuff | test_4 | after trigger fired
  1065. 5 | 5 | stuff | test_5 | after trigger fired
  1066. -(25 rows)
  1067. +(29 rows)
  1068.  
  1069. -- COPY w/ oids on a table w/o oids should fail
  1070. CREATE TABLE no_oids (
  1071. @@ -101,6 +116,10 @@ COPY x TO stdout;
  1072. 10003 24 34 44 before trigger fired
  1073. 10004 25 35 45 before trigger fired
  1074. 10005 26 36 46 before trigger fired
  1075. +2004 27 37 47 before trigger fired
  1076. +2006 28 38 48 before trigger fired
  1077. +2008 29 39 49 before trigger fired
  1078. +2009 30 40 50 before trigger fired
  1079. 6 \N 45 80 before trigger fired
  1080. 7 \N x \\x before trigger fired
  1081. 8 \N , \\, before trigger fired
  1082. @@ -127,6 +146,10 @@ COPY x (c, e) TO stdout;
  1083. 34 before trigger fired
  1084. 35 before trigger fired
  1085. 36 before trigger fired
  1086. +37 before trigger fired
  1087. +38 before trigger fired
  1088. +39 before trigger fired
  1089. +40 before trigger fired
  1090. 45 before trigger fired
  1091. x before trigger fired
  1092. , before trigger fired
  1093. @@ -153,6 +176,10 @@ I'm null before trigger fired
  1094. 24 before trigger fired
  1095. 25 before trigger fired
  1096. 26 before trigger fired
  1097. +27 before trigger fired
  1098. +28 before trigger fired
  1099. +29 before trigger fired
  1100. +30 before trigger fired
  1101. I'm null before trigger fired
  1102. I'm null before trigger fired
  1103. I'm null before trigger fired
  1104. diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
  1105. index f3a6d228fa..692f4d2f29 100644
  1106. --- a/src/test/regress/sql/copy2.sql
  1107. +++ b/src/test/regress/sql/copy2.sql
  1108. @@ -72,6 +72,29 @@ COPY x from stdin;
  1109. 2002 232 40 50 60 70 80
  1110. \.
  1111.  
  1112. +-- missing data: should be able to ignore errors
  1113. +COPY x from stdin with (ignore_errors);
  1114. +2003 230 23 23
  1115. +2004 27 37 47 57
  1116. +\.
  1117. +COPY x from stdin with (ignore_errors);
  1118. +2005 231 \N \N
  1119. +2006 28 38 48 58
  1120. +\.
  1121. +
  1122. +-- extra data: should be able to ignore errors
  1123. +COPY x from stdin with (ignore_errors);
  1124. +2007 232 40 50 60 70 80
  1125. +2008 29 39 49 59
  1126. +\.
  1127. +
  1128. +-- data type inappropriate format: should be able to ignore errors
  1129. +
  1130. +COPY x from stdin with (ignore_errors);
  1131. +2009 30 40 50 60
  1132. +2010 '24' 34 44 54
  1133. +\.
  1134. +
  1135. -- various COPY options: delimiters, oids, NULL string, encoding
  1136. COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
  1137. 500000,x,45,80,90
  1138. --
  1139. 2.11.0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!