0011-COPY_IGNORE_ERRORS.patch

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c25b52d0cb..50151aec54 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>
+      Drops rows that contain malformed data while copying. These are rows
+      containing syntax errors in data, rows with too many or too few columns,
+      rows containing columns where the data type's input function raises an error.
+      Logs errors to system logfile and outputs the total number of errors.
+     </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 e34f583ea7..e741ce3e5a 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -407,6 +407,7 @@ ProcessCopyOptions(ParseState *pstate,
 				   bool is_from,
 				   List *options)
 {
+	bool		ignore_errors_specified = false;
 	bool		format_specified = false;
 	bool		freeze_specified = false;
 	bool		header_specified = false;
@@ -449,6 +450,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 af52faca6d..657fa44e0b 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -107,6 +107,9 @@ static char *limit_printout_length(const char *str);
 
 static void ClosePipeFromProgram(CopyFromState cstate);
 
+static bool SafeCopying(CopyFromState cstate, ExprContext *econtext,
+						TupleTableSlot *myslot);
+
 /*
  * error context callback for COPY FROM
  *
@@ -625,6 +628,173 @@ CopyMultiInsertInfoStore(CopyMultiInsertInfo *miinfo, ResultRelInfo *rri,
 	miinfo->bufferedBytes += tuplen;
 }
 
+/*
+ * Safely reads source data, converts to a tuple and fills tuple buffer.
+ * Skips some data in the case of failed conversion if data source for
+ * a next tuple can be surely read without a danger.
+ */
+static bool
+SafeCopying(CopyFromState cstate, ExprContext *econtext, TupleTableSlot *myslot)
+{
+	SafeCopyFromState  *sfcstate = cstate->sfcstate;
+	bool 				valid_row = true;
+
+	/* Standard COPY if IGNORE_ERRORS is disabled */
+	if (!cstate->sfcstate)
+		/* Directly stores the values/nulls array in the slot */
+		return NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull);
+
+	if (sfcstate->replayed_tuples < sfcstate->saved_tuples)
+	{
+		Assert(sfcstate->saved_tuples > 0);
+
+		/* Prepare to replay the tuple */
+		heap_deform_tuple(sfcstate->safe_buffer[sfcstate->replayed_tuples++], RelationGetDescr(cstate->rel),
+						  myslot->tts_values, myslot->tts_isnull);
+		return true;
+	}
+	else
+	{
+		/* All tuples from buffer were replayed, clean it up */
+		MemoryContextReset(sfcstate->safe_cxt);
+
+		sfcstate->saved_tuples = sfcstate->replayed_tuples = 0;
+		sfcstate->safeBufferBytes = 0;
+	}
+
+	BeginInternalSubTransaction(NULL);
+	CurrentResourceOwner = sfcstate->oldowner;
+
+	while (sfcstate->saved_tuples < SAFE_BUFFER_SIZE &&
+		   sfcstate->safeBufferBytes < MAX_SAFE_BUFFER_BYTES)
+	{
+		bool	tuple_is_valid = true;
+
+		PG_TRY();
+		{
+			MemoryContext cxt = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
+
+			valid_row = NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull);
+			tuple_is_valid = valid_row;
+
+			if (valid_row)
+				sfcstate->safeBufferBytes += cstate->line_buf.len;
+
+			MemoryContextSwitchTo(cxt);
+		}
+		PG_CATCH();
+		{
+			MemoryContext ecxt = MemoryContextSwitchTo(sfcstate->oldcontext);
+			ErrorData 	 *errdata = CopyErrorData();
+
+			tuple_is_valid = false;
+
+			Assert(IsSubTransaction());
+
+			RollbackAndReleaseCurrentSubTransaction();
+			CurrentResourceOwner = sfcstate->oldowner;
+
+			switch (errdata->sqlerrcode)
+			{
+				/* Ignore data exceptions */
+				case ERRCODE_CHARACTER_NOT_IN_REPERTOIRE:
+				case ERRCODE_DATA_EXCEPTION:
+				case ERRCODE_ARRAY_ELEMENT_ERROR:
+				case ERRCODE_DATETIME_VALUE_OUT_OF_RANGE:
+				case ERRCODE_INTERVAL_FIELD_OVERFLOW:
+				case ERRCODE_INVALID_CHARACTER_VALUE_FOR_CAST:
+				case ERRCODE_INVALID_DATETIME_FORMAT:
+				case ERRCODE_INVALID_ESCAPE_CHARACTER:
+				case ERRCODE_INVALID_ESCAPE_SEQUENCE:
+				case ERRCODE_NONSTANDARD_USE_OF_ESCAPE_CHARACTER:
+				case ERRCODE_INVALID_PARAMETER_VALUE:
+				case ERRCODE_INVALID_TABLESAMPLE_ARGUMENT:
+				case ERRCODE_INVALID_TIME_ZONE_DISPLACEMENT_VALUE:
+				case ERRCODE_NULL_VALUE_NOT_ALLOWED:
+				case ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE:
+				case ERRCODE_SEQUENCE_GENERATOR_LIMIT_EXCEEDED:
+				case ERRCODE_STRING_DATA_LENGTH_MISMATCH:
+				case ERRCODE_STRING_DATA_RIGHT_TRUNCATION:
+				case ERRCODE_INVALID_TEXT_REPRESENTATION:
+				case ERRCODE_INVALID_BINARY_REPRESENTATION:
+				case ERRCODE_BAD_COPY_FILE_FORMAT:
+				case ERRCODE_UNTRANSLATABLE_CHARACTER:
+				case ERRCODE_DUPLICATE_JSON_OBJECT_KEY_VALUE:
+				case ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION:
+				case ERRCODE_INVALID_JSON_TEXT:
+				case ERRCODE_INVALID_SQL_JSON_SUBSCRIPT:
+				case ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM:
+				case ERRCODE_NO_SQL_JSON_ITEM:
+				case ERRCODE_NON_NUMERIC_SQL_JSON_ITEM:
+				case ERRCODE_NON_UNIQUE_KEYS_IN_A_JSON_OBJECT:
+				case ERRCODE_SINGLETON_SQL_JSON_ITEM_REQUIRED:
+				case ERRCODE_SQL_JSON_ARRAY_NOT_FOUND:
+				case ERRCODE_SQL_JSON_MEMBER_NOT_FOUND:
+				case ERRCODE_SQL_JSON_NUMBER_NOT_FOUND:
+				case ERRCODE_SQL_JSON_OBJECT_NOT_FOUND:
+				case ERRCODE_TOO_MANY_JSON_ARRAY_ELEMENTS:
+				case ERRCODE_TOO_MANY_JSON_OBJECT_MEMBERS:
+				case ERRCODE_SQL_JSON_SCALAR_REQUIRED:
+				case ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE:
+					/* If the error can be processed, begin a new subtransaction */
+					BeginInternalSubTransaction(NULL);
+					CurrentResourceOwner = sfcstate->oldowner;
+
+					sfcstate->errors++;
+
+					ereport(LOG, (errmsg("%s", errdata->context),
+							errhidecontext(true), errhidestmt(true)));
+
+					break;
+				default:
+					MemoryContextSwitchTo(ecxt);
+					PG_RE_THROW();
+					break;
+			}
+
+			FlushErrorState();
+			FreeErrorData(errdata);
+			errdata = NULL;
+
+			MemoryContextSwitchTo(ecxt);
+		}
+		PG_END_TRY();
+
+		if (tuple_is_valid)
+		{
+			/* Add tuple to safe_buffer in Safe_context */
+			HeapTuple 	  saved_tuple;
+
+			MemoryContextSwitchTo(sfcstate->safe_cxt);
+
+			saved_tuple = heap_form_tuple(RelationGetDescr(cstate->rel), myslot->tts_values, myslot->tts_isnull);
+			sfcstate->safe_buffer[sfcstate->saved_tuples++] = saved_tuple;
+		}
+
+		ExecClearTuple(myslot);
+
+		if (!valid_row)
+			break;
+	}
+
+	ReleaseCurrentSubTransaction();
+	CurrentResourceOwner = sfcstate->oldowner;
+
+	/* Prepare to replay the first tuple from safe_buffer */
+	if (sfcstate->saved_tuples != 0)
+	{
+		heap_deform_tuple(sfcstate->safe_buffer[sfcstate->replayed_tuples++], RelationGetDescr(cstate->rel),
+						  myslot->tts_values, myslot->tts_isnull);
+		return true;
+	}
+
+	/* End of file and nothing to replay? */
+	if (!valid_row && sfcstate->replayed_tuples == sfcstate->saved_tuples)
+		return false;
+
+	return true;
+}
+
 /*
  * Copy FROM file to relation.
  */
@@ -991,8 +1161,8 @@ CopyFrom(CopyFromState cstate)
 
 		ExecClearTuple(myslot);
 
-		/* Directly store the values/nulls array in the slot */
-		if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull))
+		/* Standard copying with option "safe copying" enabled by IGNORE_ERRORS. */
+		if (!SafeCopying(cstate, econtext, myslot))
 			break;
 
 		ExecStoreVirtualTuple(myslot);
@@ -1276,6 +1446,11 @@ CopyFrom(CopyFromState cstate)
 		}
 	}
 
+	if (cstate->sfcstate && cstate->sfcstate->errors > 0)
+		ereport(WARNING,
+				errmsg("Errors: %d", cstate->sfcstate->errors),
+				errhidecontext(true), errhidestmt(true));
+
 	/* Flush any remaining buffered tuples */
 	if (insertMethod != CIM_SINGLE)
 	{
@@ -1704,6 +1879,25 @@ BeginCopyFrom(ParseState *pstate,
 		cstate->raw_fields = (char **) palloc(attr_count * sizeof(char *));
 	}
 
+	/* Initialize safeCopyFromState for IGNORE_ERRORS option */
+	if (cstate->opts.ignore_errors)
+	{
+		MemoryContextSwitchTo(cstate->copycontext);
+
+		cstate->sfcstate = palloc(sizeof(SafeCopyFromState));
+
+		cstate->sfcstate->safe_cxt = AllocSetContextCreate(cstate->copycontext,
+									 "COPY_safe_context",
+									 ALLOCSET_DEFAULT_SIZES);
+		cstate->sfcstate->saved_tuples = 0;
+		cstate->sfcstate->replayed_tuples = 0;
+		cstate->sfcstate->safeBufferBytes = 0;
+		cstate->sfcstate->errors = 0;
+
+		cstate->sfcstate->oldowner = CurrentResourceOwner;
+		cstate->sfcstate->oldcontext = cstate->copycontext;
+	}
+
 	MemoryContextSwitchTo(oldcontext);
 
 	return cstate;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a0138382a1..7d3e0c6ba8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -701,7 +701,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
@@ -3378,6 +3378,10 @@ copy_opt_item:
 				{
 					$$ = makeDefElem("freeze", (Node *) makeBoolean(true), @1);
 				}
+			| IGNORE_ERRORS
+				{
+					$$ = makeDefElem("ignore_errors", (Node *)makeBoolean(true), @1);
+				}
 			| DELIMITER opt_as Sconst
 				{
 					$$ = makeDefElem("delimiter", (Node *) makeString($3), @1);
@@ -16821,6 +16825,7 @@ unreserved_keyword:
 			| HOUR_P
 			| IDENTITY_P
 			| IF_P
+			| IGNORE_ERRORS
 			| IMMEDIATE
 			| IMMUTABLE
 			| IMPLICIT_P
@@ -17375,6 +17380,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 5e1882eaea..63d56018dd 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2857,7 +2857,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 8e5f6ff148..c3796c9d37 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 7b1c4327bd..e90aa47076 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -16,6 +16,7 @@
 
 #include "commands/copy.h"
 #include "commands/trigger.h"
+#include "utils/resowner.h"
 
 /*
  * Represents the different source cases we need to worry about at
@@ -52,6 +53,25 @@ typedef enum CopyInsertMethod
 								 * ExecForeignBatchInsert only if valid */
 } CopyInsertMethod;
 
+/*
+ * Struct that holding fields for safe copying option enabled by IGNORE_ERRORS.
+ */
+typedef struct SafeCopyFromState
+{
+#define		SAFE_BUFFER_SIZE	1000
+#define		MAX_SAFE_BUFFER_BYTES	65535
+
+	HeapTuple	safe_buffer[SAFE_BUFFER_SIZE]; 	/* accumulates valid tuples */
+	int			saved_tuples;					/* # of tuples in safe_buffer */
+	int 		replayed_tuples;				/* # of tuples were replayed from buffer */
+	int			safeBufferBytes;				/* # of bytes from all buffered tuples */
+	int			errors;							/* total # of errors */
+
+	MemoryContext	safe_cxt;
+	MemoryContext	oldcontext;
+	ResourceOwner	oldowner;
+} SafeCopyFromState;
+
 /*
  * This struct contains all the state variables used throughout a COPY FROM
  * operation.
@@ -74,6 +94,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 *sfcstate; /* 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 bb36213e6f..4f27d49567 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -196,6 +196,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 090ef6c7a8..27512d8e41 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -666,6 +666,105 @@ 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 WHERE n < 9;
+WARNING:  Errors: 6
+SELECT * FROM check_ign_err;
+ n |  m  | k 
+---+-----+---
+ 1 | {1} | 1
+ 8 | {8} | 8
+(2 rows)
+
+-- CIM_SINGLE cases
+-- BEFORE row trigger
+TRUNCATE check_ign_err;
+CREATE TABLE trig_test(n int, m int[], k int);
+CREATE FUNCTION fn_trig_before () RETURNS TRIGGER AS '
+  BEGIN
+    INSERT INTO trig_test VALUES(NEW.n, NEW.m, NEW.k);
+    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 WHERE n < 9;
+WARNING:  Errors: 6
+SELECT * FROM check_ign_err;
+ n |  m  | k 
+---+-----+---
+ 1 | {1} | 1
+ 8 | {8} | 8
+(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 trig_test 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 WHERE n < 9;
+WARNING:  Errors: 6
+SELECT * FROM trig_test;
+ n |  m  | k 
+---+-----+---
+ 1 | {1} | 1
+ 8 | {8} | 8
+(2 rows)
+
+DROP TRIGGER trig_instead_of ON check_ign_err_view;
+DROP VIEW check_ign_err_view;
+-- foreign table case is 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; /* finds values equal 1 */
+WARNING:  Errors: 6
+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 (k);
+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 (9);
+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 WHERE n < 9;
+WARNING:  Errors: 6
+SELECT * FROM check_ign_err;
+ n |  m  | k 
+---+-----+---
+ 1 | {1} | 1
+ 8 | {8} | 8
+(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 b0de82c3aa..c77cfaaf4d 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -464,6 +464,122 @@ 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 WHERE n < 9;
+1	{1}	1
+2	{2}	2	2
+3	{3}
+a	{4}	4
+5	{5}	5555555555
+
+7	{a, 7}	7
+8	{8}	8
+\.
+SELECT * FROM check_ign_err;
+
+-- CIM_SINGLE cases
+-- BEFORE row trigger
+TRUNCATE check_ign_err;
+CREATE TABLE trig_test(n int, m int[], k int);
+CREATE FUNCTION fn_trig_before () RETURNS TRIGGER AS '
+  BEGIN
+    INSERT INTO trig_test VALUES(NEW.n, NEW.m, NEW.k);
+    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 WHERE n < 9;
+1	{1}	1
+2	{2}	2	2
+3	{3}
+a	{4}	4
+5	{5}	5555555555
+
+7	{a, 7}	7
+8	{8}	8
+\.
+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 trig_test 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 WHERE n < 9;
+1	{1}	1
+2	{2}	2	2
+3	{3}
+a	{4}	4
+5	{5}	5555555555
+
+7	{a, 7}	7
+8	{8}	8
+\.
+SELECT * FROM trig_test;
+DROP TRIGGER trig_instead_of ON check_ign_err_view;
+DROP VIEW check_ign_err_view;
+
+-- foreign table case is 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; /* finds values equal 1 */
+1	{1}	1
+2	{2}	2	2
+3	{3}
+a	{4}	4
+5	{5}	5555555555
+
+7	{a, 7}	7
+8	{8}	8
+\.
+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 (k);
+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 (9);
+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 WHERE n < 9;
+1	{1}	1
+2	{2}	2	2
+3	{3}
+a	{4}	4
+5	{5}	5555555555
+
+7	{a, 7}	7
+8	{8}	8
+\.
+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;