【PostgreSQL17新特性之-COPY FROM的ON_ERROR容错选项】

一、ON_ERROR容错选项

1.ON_ERROR容错选项使用方式

PostgreSQL17-beta1针对COPY FROM增加的ON_ERROR选项使得Copy From语句在执行过程中部分解析、数据格式、字符集等相关的报错可以根据需求选择报错回滚事务里的全部记录/丢弃输入行并继续处理下一行。

error_action可以选择的值为stop和ignore，具体的作用如下：

选项	作用
stop	使命令失败，ON_ERROR该选项的的默认值。和PostgreSQL-16及以前的默认行为一致。
ignore	丢弃输入行并继续处理下一行。

针对ignore的这个作用，我测试过程发现有时候可以丢弃输入行并继续处理下一行。有时候并没有丢弃输入行并继续处理下一行，而是在错误行中断了。把报错行之前的数据写入到表里，然后提交，报错行之后的数据全部忽略，即使报错行后有正确数据的行，也不能copy进表里。出现了两种现象，具体可看后边测试及总结部分

大致的使用方式为如下所示：

copy test_copy_onerror  from '/home/postgres/1.sql' (ON_ERROR ignore);
copy test_copy_onerror  from '/home/postgres/1.sql' (ON_ERROR stop);

COPY的默认行为是"ON_ERROR stop"，通过源码可以看到ON_ERROR选项的error_action目前也是仅有两种，源码注释里写着日后可能会添加更多的选项。

src/include/commands/copy.h

/*
 * Represents where to save input processing errors.  More values to be added
 * in the future.
 */
typedef enum CopyOnErrorChoice
{
	COPY_ON_ERROR_STOP = 0,		/* immediately throw errors, default */
	COPY_ON_ERROR_IGNORE,		/* ignore errors */
} CopyOnErrorChoice;

----------------------------------------------------------------------------------------
src/backend/commands/copy.c

/*
 * Extract a CopyOnErrorChoice value from a DefElem.
 */
static CopyOnErrorChoice
defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
{
	char	   *sval = defGetString(def);

	if (!is_from)
		ereport(ERROR,
				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
				 errmsg("COPY ON_ERROR cannot be used with COPY TO"),
				 parser_errposition(pstate, def->location)));

	/*
	 * Allow "stop", or "ignore" values.
	 */
	if (pg_strcasecmp(sval, "stop") == 0)
		return COPY_ON_ERROR_STOP;
	if (pg_strcasecmp(sval, "ignore") == 0)
		return COPY_ON_ERROR_IGNORE;

	ereport(ERROR,
			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
			 errmsg("COPY ON_ERROR \"%s\" not recognized", sval),
			 parser_errposition(pstate, def->location)));
	return COPY_ON_ERROR_STOP;	/* keep compiler quiet */
}

2.测试案例

（1）创建测试表test_copy_onerror

创建一个测试表test_copy_onerror，name的类型为varchar(5)，插入超过5个字符的则会报错。

postgres<17beta1>(ConnAs[postgres]:PID[22701] 2024-05-28/19:17:48)=# create table test_copy_onerror(id int,name varchar(5));
CREATE TABLE
postgres<17beta1>(ConnAs[postgres]:PID[22701] 2024-05-28/19:17:55)=# \d test_copy_onerror
                 Table "public.test_copy_onerror"
+--------+------------------