0003-COPY_IGNORE_ERRORS.patch

From fa6b99c129eb890b25f006bb7891a247c8a431a7 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 without GUC with function
 safeNextCopyFrom() with struct SafeCopyFromState with refactoring

---
 doc/src/sgml/ref/copy.sgml               |  13 ++
 src/backend/commands/copy.c              |   8 ++
 src/backend/commands/copyfrom.c          | 162 ++++++++++++++++++++++-
 src/backend/parser/gram.y                |   8 +-
 src/bin/psql/tab-complete.c              |   3 +-
 src/include/commands/copy.h              |   1 +
 src/include/commands/copyfrom_internal.h |  21 +++
 src/include/parser/kwlist.h              |   1 +
 src/test/regress/expected/copy2.out      | 123 +++++++++++++++++
 src/test/regress/sql/copy2.sql           | 110 +++++++++++++++
 10 files changed, 445 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..285c491ddd 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -106,6 +106,9 @@ static char *limit_printout_length(const char *str);
 
 static void ClosePipeFromProgram(CopyFromState cstate);
 
+static bool safeNextCopyFrom(CopyFromState cstate, ExprContext *econtext,
+					         Datum *values, bool *nulls);
+
 /*
  * error context callback for COPY FROM
  *
@@ -521,6 +524,125 @@ CopyMultiInsertInfoStore(CopyMultiInsertInfo *miinfo, ResultRelInfo *rri,
 	miinfo->bufferedBytes += tuplen;
 }
 
+/*
+ * Analog of NextCopyFrom() but ignore rows with errors while copying.
+ */
+static bool
+safeNextCopyFrom(CopyFromState cstate, ExprContext *econtext, Datum *values, bool *nulls)
+{
+	SafeCopyFromState *safecstate = cstate->safecstate;
+	bool valid_row = true;
+
+	safecstate->skip_row = false;
+
+	PG_TRY();
+	{
+		if (!safecstate->replay_is_active)
+		{
+			if (safecstate->begin_subtransaction)
+			{
+				BeginInternalSubTransaction(NULL);
+				CurrentResourceOwner = safecstate->oldowner;
+
+				safecstate->begin_subtransaction = false;
+			}
+
+			if (safecstate->saved_tuples < REPLAY_BUFFER_SIZE)
+			{
+				valid_row = NextCopyFrom(cstate, econtext, values, nulls);
+				if (valid_row)
+				{
+					/* Fill replay_buffer in oldcontext*/
+					MemoryContextSwitchTo(safecstate->oldcontext);
+					safecstate->replay_buffer[safecstate->saved_tuples++] = heap_form_tuple(RelationGetDescr(cstate->rel), values, nulls);
+
+					safecstate->skip_row = true;
+				}
+				else if (!safecstate->processed_remaining_tuples)
+				{
+					ReleaseCurrentSubTransaction();
+					CurrentResourceOwner = safecstate->oldowner;
+					if (safecstate->replayed_tuples < safecstate->saved_tuples)
+					{
+						/* Prepare to replay remaining tuples if they exist */
+						safecstate->replay_is_active = true;
+						safecstate->processed_remaining_tuples = true;
+						safecstate->skip_row = true;
+						return true;
+					}
+				}
+			}
+			else
+			{
+				/* Buffer was filled, commit subtransaction and prepare to replay */
+				ReleaseCurrentSubTransaction();
+				CurrentResourceOwner = safecstate->oldowner;
+
+				safecstate->replay_is_active = true;
+				safecstate->begin_subtransaction = true;
+				safecstate->skip_row = true;
+			}
+		}
+		else
+		{
+			if (safecstate->replayed_tuples < safecstate->saved_tuples)
+			{
+				/* Replaying tuple */
+				heap_deform_tuple(safecstate->replay_buffer[safecstate->replayed_tuples++], RelationGetDescr(cstate->rel), values, nulls);
+			}
+			else
+			{
+				/* Clean up replay_buffer */
+				MemSet(safecstate->replay_buffer, 0, REPLAY_BUFFER_SIZE * sizeof(HeapTuple));
+				safecstate->saved_tuples = safecstate->replayed_tuples = 0;
+
+				safecstate->replay_is_active = false;
+				safecstate->skip_row = true;
+			}
+		}
+	}
+	PG_CATCH();
+	{
+		ErrorData *errdata;
+		MemoryContextSwitchTo(safecstate->oldcontext);
+		errdata = CopyErrorData();
+
+		switch (errdata->sqlerrcode)
+		{
+			case ERRCODE_BAD_COPY_FILE_FORMAT:
+			case ERRCODE_INVALID_TEXT_REPRESENTATION:
+				RollbackAndReleaseCurrentSubTransaction();
+				CurrentResourceOwner = safecstate->oldowner;
+
+				safecstate->errors++;
+				if (safecstate->errors <= 100)
+					ereport(WARNING,
+							(errcode(errdata->sqlerrcode),
+							errmsg("%s", errdata->context)));
+
+				safecstate->begin_subtransaction = true;
+				safecstate->skip_row = true;
+				break;
+			default:
+				PG_RE_THROW();
+		}
+
+		FlushErrorState();
+		FreeErrorData(errdata);
+		errdata = NULL;
+	}
+	PG_END_TRY();
+
+	if (!valid_row)
+	{
+		ereport(WARNING,
+				errmsg("FIND %d ERRORS", safecstate->errors));
+		return false;
+	}
+
+	return true;
+}
+
 /*
  * Copy FROM file to relation.
  */
@@ -535,6 +657,7 @@ CopyFrom(CopyFromState cstate)
 	ExprContext *econtext;
 	TupleTableSlot *singleslot = NULL;
 	MemoryContext oldcontext = CurrentMemoryContext;
+	ResourceOwner oldowner = CurrentResourceOwner;
 
 	PartitionTupleRouting *proute = NULL;
 	ErrorContextCallback errcallback;
@@ -819,6 +942,23 @@ CopyFrom(CopyFromState cstate)
 	errcallback.previous = error_context_stack;
 	error_context_stack = &errcallback;
 
+	/* Initialize safeCopyFromState for IGNORE_ERRORS option*/
+	if (cstate->opts.ignore_errors)
+	{
+		cstate->safecstate = palloc(sizeof(SafeCopyFromState));
+
+		cstate->safecstate->saved_tuples = 0;
+		cstate->safecstate->replayed_tuples = 0;
+		cstate->safecstate->errors = 0;
+		cstate->safecstate->replay_is_active = false;
+		cstate->safecstate->begin_subtransaction = true;
+		cstate->safecstate->processed_remaining_tuples = false;
+
+		cstate->safecstate->oldowner = oldowner;
+		cstate->safecstate->oldcontext = oldcontext;
+		cstate->safecstate->insertMethod = insertMethod;
+	}
+
 	for (;;)
 	{
 		TupleTableSlot *myslot;
@@ -855,9 +995,25 @@ 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 skips rows with errors.
+		 * NextCopyFrom() directly store the values/nulls array in the slot.
+		 */
+		if (cstate->safecstate)
+		{
+			bool valid_row = safeNextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull);
+
+			/* Cannot continue or break in PG_TRY in safeNextCopyFrom() */
+			if (cstate->safecstate->skip_row)
+				continue;
+			if (!valid_row)
+				break;
+		}
+		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/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index 3df1c5a97c..d9d3af1fb4 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -16,6 +16,8 @@
 
 #include "commands/copy.h"
 #include "commands/trigger.h"
+#include "utils/resowner.h"
+
 
 /*
  * Represents the different source cases we need to worry about at
@@ -49,6 +51,24 @@ typedef enum CopyInsertMethod
 	CIM_MULTI_CONDITIONAL		/* use table_multi_insert only if valid */
 } CopyInsertMethod;
 
+/* Struct that holding fields for ignore_errors option. */
+typedef struct SafeCopyFromState
+{
+#define			REPLAY_BUFFER_SIZE 1000
+	HeapTuple	    replay_buffer[REPLAY_BUFFER_SIZE]; /* accumulates tuples for replaying it after an error */
+	int				saved_tuples;				/* # of tuples in replay_buffer */
+	int 			replayed_tuples;			/* # of tuples was replayed from buffer */
+	int				errors;						/* total # of errors */
+	bool			replay_is_active;
+	bool			begin_subtransaction;
+	bool			processed_remaining_tuples;	/* for case of replaying last tuples */
+	bool			skip_row;
+
+	MemoryContext	oldcontext;
+	ResourceOwner	oldowner;
+	CopyInsertMethod insertMethod;
+} SafeCopyFromState;
+
 /*
  * This struct contains all the state variables used throughout a COPY FROM
  * operation.
@@ -71,6 +91,7 @@ typedef struct CopyFromStateData
 	char	   *filename;		/* filename, or NULL for STDIN */
 	bool		is_program;		/* is 'filename' a program to popen? */
 	copy_data_source_cb data_source_cb; /* function for reading data */
+	SafeCopyFromState *safecstate; /* struct for ignore_errors option */
 
 	CopyFormatOptions opts;
 	bool	   *convert_select_flags;	/* per-column CSV/TEXT CS flags */
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..ab1f059a02 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -649,6 +649,129 @@ 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: ""
+WARNING:  FIND 5 ERRORS
+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: ""
+WARNING:  FIND 5 ERRORS
+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: ""
+WARNING:  FIND 5 ERRORS
+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: ""
+WARNING:  FIND 5 ERRORS
+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: ""
+WARNING:  FIND 5 ERRORS
+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