Horton
Menu

Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Kirill Reshke <reshkekirill@gmail.com>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>, Jim Jones <jim.jones@uni-muenster.de>, "David G. Johnston" <david.g.johnston@gmail.com>, Yugo NAGATA <nagata@sraoss.co.jp>, torikoshia <torikoshia@oss.nttdata.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-12-13T03:15:02Z
Lists: pgsql-hackers

Attachments

+ /*
+ * Here we end processing of current COPY row.
+ * Update copy state counter for number of erroneous rows.
+ */
+ cstate->num_errors++;
+ cstate->escontext->error_occurred = true;
+
+ /* Only print this NOTICE message, if it will not be followed by ERROR */
+ if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE &&
+ (
+ (cstate->opts.on_error == COPY_ON_ERROR_NULL &&
cstate->opts.reject_limit > 0 && cstate->num_errors <=
cstate->opts.reject_limit) ||
+ (cstate->opts.on_error == COPY_ON_ERROR_IGNORE &&
(cstate->opts.reject_limit ==  0 || cstate->num_errors <=
cstate->opts.reject_limit))
+ ))
  {
this is kind of hard to comprehend.
so attached is a simple version of it based on v8.

for copy (on_error set_to_null)
1. not allow specifying reject_limit option
2. ereport ERROR for not-null constraint violation for domain type.
for example:
CREATE DOMAIN d_int_not_null AS INT NOT NULL CHECK(value > 0);
CREATE TABLE t1 (a d_int_not_null);
COPY t1 FROM STDIN WITH (on_error set_to_null);

these 3 values: \N    a    -1
will error out, the error message will be:
ERROR:  domain d_int_not_null does not allow null values

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. file_fdw: Add regression test for file_fdw with ON_ERROR='set_null'

  2. Add COPY (on_error set_null) option

  3. Add REJECT_LIMIT option to the COPY command.

  4. Add log_verbosity = 'silent' support to COPY command.

  5. Add new COPY option LOG_VERBOSITY.