> > > Thank you for feedback. I improved my patch recently and tested it on different sizes of MAX_BUFFERED_TUPLES and REPLAY_BUFFER_SIZE.
> I loaded 10000 rows which contained 1 wrong row. > I expected I could see 9999 rows after COPY, but just saw 999 rows. Also I implemented your case and it worked correctly. > BTW I may be overlooking it, but have you submit this proposal to the next CommitFest? Good idea. Haven't done it yet. Regards, Damir Postgres Professional
From fa6b99c129eb890b25f006bb7891a247c8a431a7 Mon Sep 17 00:00:00 2001 From: Damir Belyalov <dam.be...@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