Hi!
Improved my patch by adding block subtransactions.
The block size is determined by the REPLAY_BUFFER_SIZE parameter.
I used the idea of a buffer for accumulating tuples in it.
If we read REPLAY_BUFFER_SIZE rows without errors, the subtransaction will
be committed.
If we find an error, the subtransaction will rollback and the buffer will
be replayed containing tuples.
In the patch REPLAY_BUFFER_SIZE equals 3, but it can be changed to any
other number (for example 1000).
There is an idea to create a GUC parameter for it.
Also maybe create a GUC parameter for the number of occurring WARNINGS by
rows with errors.
For CIM_MULTI and CIM_MULTI_CONDITIONAL cases the buffer is not needed.
It is needed for the CIM_SINGLE case.
Tests:
-- CIM_MULTI case
CREATE TABLE check_ign_err (n int, m int, k int);
COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS;
WARNING: COPY check_ign_err, line 2: "2 2 2 2"
WARNING: COPY check_ign_err, line 3: "3 3"
WARNING: COPY check_ign_err, line 4, column n: "a"
WARNING: COPY check_ign_err, line 5, column m: "b"
WARNING: COPY check_ign_err, line 6, column n: ""
1 1 1
2 2 2 2
3 3
a 4 4
5 b b
7 7 7
\.
SELECT * FROM check_ign_err;
WARNING: COPY check_ign_err, line 2: "2 2 2 2"
WARNING: COPY check_ign_err, line 3: "3 3"
WARNING: COPY check_ign_err, line 4, column n: "a"
WARNING: COPY check_ign_err, line 5, column m: "b"
WARNING: COPY check_ign_err, line 6, column n: ""
n | m | k
---+---+---
1 | 1 | 1
7 | 7 | 7
(2 rows)
##################################################
-- CIM_SINGLE case
-- BEFORE row trigger
CREATE TABLE trig_test(n int, m int);
CREATE FUNCTION fn_trig_before () RETURNS TRIGGER AS '
BEGIN
INSERT INTO trig_test VALUES(NEW.n, NEW.m);
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER trig_before BEFORE INSERT ON check_ign_err
FOR EACH ROW EXECUTE PROCEDURE fn_trig_before();
COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS;
WARNING: COPY check_ign_err, line 2: "2 2 2 2"
WARNING: COPY check_ign_err, line 3: "3 3"
WARNING: COPY check_ign_err, line 4, column n: "a"
WARNING: COPY check_ign_err, line 5, column m: "b"
WARNING: COPY check_ign_err, line 6, column n: ""
1 1 1
2 2 2 2
3 3
a 4 4
5 b b
7 7 7
\.
SELECT * FROM check_ign_err;
n | m | k
---+---+---
1 | 1 | 1
7 | 7 | 7
(2 rows)
##################################################
-- INSTEAD OF row trigger
CREATE VIEW check_ign_err_view AS SELECT * FROM check_ign_err;
CREATE FUNCTION fn_trig_instead_of () RETURNS TRIGGER AS '
BEGIN
INSERT INTO check_ign_err VALUES(NEW.n, NEW.m, NEW.k);
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER trig_instead_of INSTEAD OF INSERT ON check_ign_err_view
FOR EACH ROW EXECUTE PROCEDURE fn_trig_instead_of();
COPY check_ign_err_view FROM STDIN WITH IGNORE_ERRORS;
WARNING: COPY check_ign_err, line 2: "2 2 2 2"
WARNING: COPY check_ign_err, line 3: "3 3"
WARNING: COPY check_ign_err, line 4, column n: "a"
WARNING: COPY check_ign_err, line 5, column m: "b"
WARNING: COPY check_ign_err, line 6, column n: ""
SELECT * FROM check_ign_err;
1 1 1
2 2 2 2
3 3
a 4 4
5 b b
7 7 7
\.
SELECT * FROM check_ign_err_view;
n | m | k
---+---+---
1 | 1 | 1
7 | 7 | 7
(2 rows)
##################################################
-- foreign table case in postgres_fdw extension
##################################################
-- volatile function in WHERE clause
COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS
WHERE n = floor(random()*(1-1+1))+1; /* find values equal 1 */
WARNING: COPY check_ign_err, line 2: "2 2 2 2"
WARNING: COPY check_ign_err, line 3: "3 3"
WARNING: COPY check_ign_err, line 4, column n: "a"
WARNING: COPY check_ign_err, line 5, column m: "b"
WARNING: COPY check_ign_err, line 6, column n: ""
SELECT * FROM check_ign_err;
1 1 1
2 2 2 2
3 3
a 4 4
5 b b
7 7 7
\.
SELECT * FROM check_ign_err;
n | m | k
---+---+---
1 | 1 | 1
(1 row)
##################################################
-- CIM_MULTI_CONDITIONAL case
-- INSERT triggers for partition tables
CREATE TABLE check_ign_err (n int, m int, k int) PARTITION BY RANGE (n);
CREATE TABLE check_ign_err_part1 PARTITION OF check_ign_err
FOR VALUES FROM (1) TO (4);
CREATE TABLE check_ign_err_part2 PARTITION OF check_ign_err
FOR VALUES FROM (4) TO (8);
CREATE FUNCTION fn_trig_before_part () RETURNS TRIGGER AS '
BEGIN
INSERT INTO trig_test VALUES(NEW.n, NEW.m);
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER trig_before_part BEFORE INSERT ON check_ign_err
FOR EACH ROW EXECUTE PROCEDURE fn_trig_before_part();
COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS;
WARNING: COPY check_ign_err, line 2: "2 2 2 2"
WARNING: COPY check_ign_err, line 3: "3 3"
WARNING: COPY check_ign_err, line 4, column n: "a"
WARNING: COPY check_ign_err, line 5, column m: "b"
WARNING: COPY check_ign_err, line 6, column n: ""
SELECT * FROM check_ign_err;
1 1 1
2 2 2 2
3 3
a 4 4
5 b b
7 7 7
\.
n | m | k
---+---+---
1 | 1 | 1
7 | 7 | 7
(2 rows)
Thanks for feedback.
Regards, Damir
From 6bf2168cd962b3cce666a2cabf082f558eec848c Mon Sep 17 00:00:00 2001
From: Damir Belyalov <dam.bel07@gmail.com>
Date: Fri, 15 Oct 2021 11:55:18 +0300
Subject: [PATCH] COPY IGNORE_ERRORS
---
doc/src/sgml/ref/copy.sgml | 13 +++
src/backend/commands/copy.c | 8 ++
src/backend/commands/copyfrom.c | 138 +++++++++++++++++++++++++++-
src/backend/parser/gram.y | 8 +-
src/bin/psql/tab-complete.c | 3 +-
src/include/commands/copy.h | 1 +
src/include/parser/kwlist.h | 1 +
src/test/regress/expected/copy2.out | 118 ++++++++++++++++++++++++
src/test/regress/sql/copy2.sql | 110 ++++++++++++++++++++++
9 files changed, 395 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 8aae711b3b..7d20b1649e 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -34,6 +34,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
FORMAT <replaceable class="parameter">format_name</replaceable>
FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
+ IGNORE_ERRORS [ <replaceable class="parameter">boolean</replaceable> ]
DELIMITER '<replaceable class="parameter">delimiter_character</replaceable>'
NULL '<replaceable class="parameter">null_string</replaceable>'
HEADER [ <replaceable class="parameter">boolean</replaceable> | MATCH ]
@@ -233,6 +234,18 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>IGNORE_ERRORS</literal></term>
+ <listitem>
+ <para>
+ Drop rows that contain malformed data while copying. That is rows
+ containing syntax errors in data, rows with too many or too few columns,
+ rows that result in constraint violations, rows containing columns where
+ the data type's input function raises an error.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>DELIMITER</literal></term>
<listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 3ac731803b..fead1aba46 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -402,6 +402,7 @@ ProcessCopyOptions(ParseState *pstate,
{
bool format_specified = false;
bool freeze_specified = false;
+ bool ignore_errors_specified = false;
bool header_specified = false;
ListCell *option;
@@ -442,6 +443,13 @@ ProcessCopyOptions(ParseState *pstate,
freeze_specified = true;
opts_out->freeze = defGetBoolean(defel);
}
+ else if (strcmp(defel->defname, "ignore_errors") == 0)
+ {
+ if (ignore_errors_specified)
+ errorConflictingDefElem(defel, pstate);
+ ignore_errors_specified = true;
+ opts_out->ignore_errors = defGetBoolean(defel);
+ }
else if (strcmp(defel->defname, "delimiter") == 0)
{
if (opts_out->delim)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index a976008b3d..b994697b9d 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -535,6 +535,7 @@ CopyFrom(CopyFromState cstate)
ExprContext *econtext;
TupleTableSlot *singleslot = NULL;
MemoryContext oldcontext = CurrentMemoryContext;
+ ResourceOwner oldowner = CurrentResourceOwner;
PartitionTupleRouting *proute = NULL;
ErrorContextCallback errcallback;
@@ -549,6 +550,17 @@ CopyFrom(CopyFromState cstate)
bool has_instead_insert_row_trig;
bool leafpart_use_multi_insert = false;
+ /* variables for copy from ignore_errors option */
+#define REPLAY_BUFFER_SIZE 3
+ HeapTuple replay_buffer[REPLAY_BUFFER_SIZE];
+ HeapTuple replay_tuple;
+ int saved_tuples = 0;
+ int replayed_tuples = 0;
+ bool replay_is_active = false;
+ bool begin_subtransaction = true;
+ bool find_error = false;
+ bool last_replaying = false;
+
Assert(cstate->rel);
Assert(list_length(cstate->range_table) == 1);
@@ -855,9 +867,129 @@ CopyFrom(CopyFromState cstate)
ExecClearTuple(myslot);
- /* Directly store the values/nulls array in the slot */
- if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull))
- break;
+ /*
+ * If option IGNORE_ERRORS is enabled, COPY skip rows with errors.
+ * NextCopyFrom() directly store the values/nulls array in the slot.
+ */
+ if (cstate->opts.ignore_errors)
+ {
+ bool valid_row = true;
+ bool skip_row = false;
+
+ PG_TRY();
+ {
+ if (!replay_is_active)
+ {
+ if (begin_subtransaction)
+ {
+ BeginInternalSubTransaction(NULL);
+ CurrentResourceOwner = oldowner;
+ }
+
+ if (saved_tuples < REPLAY_BUFFER_SIZE)
+ {
+ valid_row = NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull);
+ if (valid_row)
+ {
+ if (insertMethod == CIM_SINGLE)
+ {
+ MemoryContextSwitchTo(oldcontext);
+
+ replay_tuple = heap_form_tuple(RelationGetDescr(cstate->rel), myslot->tts_values, myslot->tts_isnull);
+ replay_buffer[saved_tuples++] = replay_tuple;
+
+ if (find_error)
+ skip_row = true;
+ }
+
+ begin_subtransaction = false;
+ }
+ }
+ else
+ {
+ ReleaseCurrentSubTransaction();
+
+ replay_is_active = true;
+ begin_subtransaction = true;
+ skip_row = true;
+ }
+ }
+ else
+ {
+ if (insertMethod == CIM_SINGLE && find_error && replayed_tuples < saved_tuples)
+ {
+ heap_deform_tuple(replay_buffer[replayed_tuples], RelationGetDescr(cstate->rel), myslot->tts_values, myslot->tts_isnull);
+ replayed_tuples++;
+ }
+ else
+ {
+ MemSet(replay_buffer, 0, REPLAY_BUFFER_SIZE * sizeof(HeapTuple));
+ saved_tuples = 0;
+ replayed_tuples = 0;
+
+ replay_is_active = false;
+ find_error = false;
+ skip_row = true;
+ }
+ }
+ }
+ PG_CATCH();
+ {
+ ErrorData *errdata;
+ MemoryContextSwitchTo(oldcontext);
+ errdata = CopyErrorData();
+
+ switch (errdata->sqlerrcode)
+ {
+ case ERRCODE_BAD_COPY_FILE_FORMAT:
+ case ERRCODE_INVALID_TEXT_REPRESENTATION:
+ RollbackAndReleaseCurrentSubTransaction();
+ elog(WARNING, "%s", errdata->context);
+
+ begin_subtransaction = true;
+ find_error = true;
+ skip_row = true;
+
+ break;
+
+ default:
+ PG_RE_THROW();
+ }
+
+ FlushErrorState();
+ FreeErrorData(errdata);
+ errdata = NULL;
+ }
+ PG_END_TRY();
+
+ if (!valid_row)
+ {
+ if (!last_replaying)
+ {
+ ReleaseCurrentSubTransaction();
+ CurrentResourceOwner = oldowner;
+
+ if (replayed_tuples < saved_tuples)
+ {
+ replay_is_active = true;
+ skip_row = true;
+ last_replaying = true;
+ }
+ else
+ break;
+ }
+ else
+ break;
+ }
+
+ if (skip_row)
+ continue;
+ }
+ else
+ {
+ if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull))
+ break;
+ }
ExecStoreVirtualTuple(myslot);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index df5ceea910..3bb7235b34 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -800,7 +800,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
HANDLER HAVING HEADER_P HOLD HOUR_P
- IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
+ IDENTITY_P IF_P IGNORE_ERRORS ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -3456,6 +3456,10 @@ copy_opt_item:
{
$$ = makeDefElem("freeze", (Node *) makeBoolean(true), @1);
}
+ | IGNORE_ERRORS
+ {
+ $$ = makeDefElem("ignore_errors", (Node *)makeInteger(true), @1);
+ }
| DELIMITER opt_as Sconst
{
$$ = makeDefElem("delimiter", (Node *) makeString($3), @1);
@@ -17814,6 +17818,7 @@ unreserved_keyword:
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE_ERRORS
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
@@ -18393,6 +18398,7 @@ bare_label_keyword:
| HOLD
| IDENTITY_P
| IF_P
+ | IGNORE_ERRORS
| ILIKE
| IMMEDIATE
| IMMUTABLE
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index e572f585ef..feaf18b043 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2742,7 +2742,8 @@ psql_completion(const char *text, int start, int end)
else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
"HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
- "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING");
+ "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING",
+ "IGNORE_ERRORS");
/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index cb0096aeb6..2b696f99bc 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -42,6 +42,7 @@ typedef struct CopyFormatOptions
* -1 if not specified */
bool binary; /* binary format? */
bool freeze; /* freeze rows on loading? */
+ bool ignore_errors; /* ignore rows with errors */
bool csv_mode; /* Comma Separated Value format? */
CopyHeaderChoice header_line; /* header line? */
char *null_print; /* NULL marker string (server encoding!) */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index ae35f03251..2af11bd359 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -201,6 +201,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("ignore_errors", IGNORE_ERRORS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 5f3685e9ef..74827ecca0 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -649,6 +649,124 @@ SELECT * FROM instead_of_insert_tbl;
(2 rows)
COMMIT;
+-- tests for IGNORE_ERRORS option
+-- CIM_MULTI case
+CREATE TABLE check_ign_err (n int, m int, k int);
+COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS;
+WARNING: COPY check_ign_err, line 2: "2 2 2 2"
+WARNING: COPY check_ign_err, line 3: "3 3"
+WARNING: COPY check_ign_err, line 4, column n: "a"
+WARNING: COPY check_ign_err, line 5, column m: "b"
+WARNING: COPY check_ign_err, line 6, column n: ""
+SELECT * FROM check_ign_err;
+ n | m | k
+---+---+---
+ 1 | 1 | 1
+ 7 | 7 | 7
+(2 rows)
+
+-- CIM_SINGLE case
+-- BEFORE row trigger
+TRUNCATE check_ign_err;
+CREATE TABLE trig_test(n int, m int);
+CREATE FUNCTION fn_trig_before () RETURNS TRIGGER AS '
+ BEGIN
+ INSERT INTO trig_test VALUES(NEW.n, NEW.m);
+ RETURN NEW;
+ END;
+' LANGUAGE plpgsql;
+CREATE TRIGGER trig_before BEFORE INSERT ON check_ign_err
+FOR EACH ROW EXECUTE PROCEDURE fn_trig_before();
+COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS;
+WARNING: COPY check_ign_err, line 2: "2 2 2 2"
+WARNING: COPY check_ign_err, line 3: "3 3"
+WARNING: COPY check_ign_err, line 4, column n: "a"
+WARNING: COPY check_ign_err, line 5, column m: "b"
+WARNING: COPY check_ign_err, line 6, column n: ""
+SELECT * FROM check_ign_err;
+ n | m | k
+---+---+---
+ 1 | 1 | 1
+ 7 | 7 | 7
+(2 rows)
+
+DROP TRIGGER trig_before on check_ign_err;
+-- INSTEAD OF row trigger
+TRUNCATE check_ign_err;
+TRUNCATE trig_test;
+CREATE VIEW check_ign_err_view AS SELECT * FROM check_ign_err;
+CREATE FUNCTION fn_trig_instead_of () RETURNS TRIGGER AS '
+ BEGIN
+ INSERT INTO check_ign_err VALUES(NEW.n, NEW.m, NEW.k);
+ RETURN NEW;
+ END;
+' LANGUAGE plpgsql;
+CREATE TRIGGER trig_instead_of INSTEAD OF INSERT ON check_ign_err_view
+FOR EACH ROW EXECUTE PROCEDURE fn_trig_instead_of();
+COPY check_ign_err_view FROM STDIN WITH IGNORE_ERRORS;
+WARNING: COPY check_ign_err_view, line 2: "2 2 2 2"
+WARNING: COPY check_ign_err_view, line 3: "3 3"
+WARNING: COPY check_ign_err_view, line 4, column n: "a"
+WARNING: COPY check_ign_err_view, line 5, column m: "b"
+WARNING: COPY check_ign_err_view, line 6, column n: ""
+SELECT * FROM check_ign_err_view;
+ n | m | k
+---+---+---
+ 1 | 1 | 1
+ 7 | 7 | 7
+(2 rows)
+
+DROP TRIGGER trig_instead_of ON check_ign_err_view;
+DROP VIEW check_ign_err_view;
+-- foreign table case in postgres_fdw extension
+-- volatile function in WHERE clause
+TRUNCATE check_ign_err;
+COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS
+ WHERE n = floor(random()*(1-1+1))+1; /* find values equal 1 */
+WARNING: COPY check_ign_err, line 2: "2 2 2 2"
+WARNING: COPY check_ign_err, line 3: "3 3"
+WARNING: COPY check_ign_err, line 4, column n: "a"
+WARNING: COPY check_ign_err, line 5, column m: "b"
+WARNING: COPY check_ign_err, line 6, column n: ""
+SELECT * FROM check_ign_err;
+ n | m | k
+---+---+---
+ 1 | 1 | 1
+(1 row)
+
+DROP TABLE check_ign_err;
+-- CIM_MULTI_CONDITIONAL case
+-- INSERT triggers for partition tables
+TRUNCATE trig_test;
+CREATE TABLE check_ign_err (n int, m int, k int) PARTITION BY RANGE (n);
+CREATE TABLE check_ign_err_part1 PARTITION OF check_ign_err
+ FOR VALUES FROM (1) TO (4);
+CREATE TABLE check_ign_err_part2 PARTITION OF check_ign_err
+ FOR VALUES FROM (4) TO (8);
+CREATE FUNCTION fn_trig_before_part () RETURNS TRIGGER AS '
+ BEGIN
+ INSERT INTO trig_test VALUES(NEW.n, NEW.m);
+ RETURN NEW;
+ END;
+' LANGUAGE plpgsql;
+CREATE TRIGGER trig_before_part BEFORE INSERT ON check_ign_err
+FOR EACH ROW EXECUTE PROCEDURE fn_trig_before_part();
+COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS;
+WARNING: COPY check_ign_err, line 2: "2 2 2 2"
+WARNING: COPY check_ign_err, line 3: "3 3"
+WARNING: COPY check_ign_err, line 4, column n: "a"
+WARNING: COPY check_ign_err, line 5, column m: "b"
+WARNING: COPY check_ign_err, line 6, column n: ""
+SELECT * FROM check_ign_err;
+ n | m | k
+---+---+---
+ 1 | 1 | 1
+ 7 | 7 | 7
+(2 rows)
+
+DROP TRIGGER trig_before_part on check_ign_err;
+DROP TABLE trig_test;
+DROP TABLE check_ign_err CASCADE;
-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index b3c16af48e..7eee78bccd 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -454,6 +454,116 @@ test1
SELECT * FROM instead_of_insert_tbl;
COMMIT;
+-- tests for IGNORE_ERRORS option
+-- CIM_MULTI case
+CREATE TABLE check_ign_err (n int, m int, k int);
+COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS;
+1 1 1
+2 2 2 2
+3 3
+a 4 4
+5 b b
+
+7 7 7
+\.
+SELECT * FROM check_ign_err;
+
+-- CIM_SINGLE case
+-- BEFORE row trigger
+TRUNCATE check_ign_err;
+CREATE TABLE trig_test(n int, m int);
+CREATE FUNCTION fn_trig_before () RETURNS TRIGGER AS '
+ BEGIN
+ INSERT INTO trig_test VALUES(NEW.n, NEW.m);
+ RETURN NEW;
+ END;
+' LANGUAGE plpgsql;
+CREATE TRIGGER trig_before BEFORE INSERT ON check_ign_err
+FOR EACH ROW EXECUTE PROCEDURE fn_trig_before();
+COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS;
+1 1 1
+2 2 2 2
+3 3
+a 4 4
+5 b b
+
+7 7 7
+\.
+SELECT * FROM check_ign_err;
+DROP TRIGGER trig_before on check_ign_err;
+
+-- INSTEAD OF row trigger
+TRUNCATE check_ign_err;
+TRUNCATE trig_test;
+CREATE VIEW check_ign_err_view AS SELECT * FROM check_ign_err;
+CREATE FUNCTION fn_trig_instead_of () RETURNS TRIGGER AS '
+ BEGIN
+ INSERT INTO check_ign_err VALUES(NEW.n, NEW.m, NEW.k);
+ RETURN NEW;
+ END;
+' LANGUAGE plpgsql;
+CREATE TRIGGER trig_instead_of INSTEAD OF INSERT ON check_ign_err_view
+FOR EACH ROW EXECUTE PROCEDURE fn_trig_instead_of();
+COPY check_ign_err_view FROM STDIN WITH IGNORE_ERRORS;
+1 1 1
+2 2 2 2
+3 3
+a 4 4
+5 b b
+
+7 7 7
+\.
+SELECT * FROM check_ign_err_view;
+DROP TRIGGER trig_instead_of ON check_ign_err_view;
+DROP VIEW check_ign_err_view;
+
+-- foreign table case in postgres_fdw extension
+
+-- volatile function in WHERE clause
+TRUNCATE check_ign_err;
+COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS
+ WHERE n = floor(random()*(1-1+1))+1; /* find values equal 1 */
+1 1 1
+2 2 2 2
+3 3
+a 4 4
+5 b b
+
+7 7 7
+\.
+SELECT * FROM check_ign_err;
+DROP TABLE check_ign_err;
+
+-- CIM_MULTI_CONDITIONAL case
+-- INSERT triggers for partition tables
+TRUNCATE trig_test;
+CREATE TABLE check_ign_err (n int, m int, k int) PARTITION BY RANGE (n);
+CREATE TABLE check_ign_err_part1 PARTITION OF check_ign_err
+ FOR VALUES FROM (1) TO (4);
+CREATE TABLE check_ign_err_part2 PARTITION OF check_ign_err
+ FOR VALUES FROM (4) TO (8);
+CREATE FUNCTION fn_trig_before_part () RETURNS TRIGGER AS '
+ BEGIN
+ INSERT INTO trig_test VALUES(NEW.n, NEW.m);
+ RETURN NEW;
+ END;
+' LANGUAGE plpgsql;
+CREATE TRIGGER trig_before_part BEFORE INSERT ON check_ign_err
+FOR EACH ROW EXECUTE PROCEDURE fn_trig_before_part();
+COPY check_ign_err FROM STDIN WITH IGNORE_ERRORS;
+1 1 1
+2 2 2 2
+3 3
+a 4 4
+5 b b
+
+7 7 7
+\.
+SELECT * FROM check_ign_err;
+DROP TRIGGER trig_before_part on check_ign_err;
+DROP TABLE trig_test;
+DROP TABLE check_ign_err CASCADE;
+
-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
--
2.25.1
