COPY 中的错误日志

历史

COPY 中的错误日志是 Aster Data 基于 PostgreSQL 9.0 代码库开发的一项提议功能。它已提交并审核 (1)，但到目前为止，尚未被接受到核心产品中。

概述

COPY 中的错误日志的目的是防止后端在 COPY 操作中遇到格式错误的元组时出错。错误的元组可以跳过或记录到错误日志表中。

错误日志表的格式如下

 CREATE TABLE error_logging_table(
   tupletimestamp TIMESTAMP WITH TIME ZONE,
   targettable    VARCHAR,
   dmltype        CHAR(1),
   errmessage     VARCHAR,
   sqlerrcode     CHAR(5),
   label          VARCHAR,
   key            BIGINT,
   rawdata        BYTEA
 );

COPY 命令仅返回成功复制的元组数。

COPY 选项

错误日志通过向 COPY 命令添加选项来设置。以下是可用选项的列表

变量名称	描述	默认值
ERROR_LOGGING	为 COPY 命令启用错误处理（当设置为 true 时）。	true
ERROR_LOGGING_SKIP_BAD_ROWS	启用在 COPY 命令中遇到格式错误的元组时跳过它们的能力（当设置为 true 时）。	true
ERROR_LOGGING_MAX_ERRORS	在停止 COPY 操作之前记录的错误行最大数量（0 表示无限制）。	0
ERROR_LOGGING_SCHEMA_NAME	错误日志模块插入格式错误元组的表的模式名称	'public'
ERROR_LOGGING_TABLE_NAME	错误日志模块插入格式错误元组的关系名称。如果表不存在，则自动创建。	'error_table'
ERROR_LOGGING_LABEL	用于识别格式错误元组的可选标签	COPY 命令文本
ERROR_LOGGING_KEY	用于识别格式错误元组的可选键	COPY 流中元组的索引

错误的元组可能由于多种原因被拒绝（额外的或缺少的列，约束冲突，...）。错误表尝试捕获有关错误尽可能多的上下文。如果表不存在，则会自动创建。错误日志表的格式如下

 CREATE TABLE error_logging_table(
   tupletimestamp TIMESTAMP WITH TIME ZONE,
   targettable    VARCHAR,
   dmltype        CHAR(1),
   errmessage     VARCHAR,
   sqlerrcode     CHAR(5),
   label          VARCHAR,
   key            BIGINT,
   rawdata        BYTEA
 );

tupletimestamp 存储错误发生的时刻。targettable 描述了错误发生时插入行的表。确切的错误消息和 SQL 错误代码分别记录在 errmessage 和 sqlerrcode 中。行的原始数据可以在 rawdata 中找到。

示例

 CREATE TEMP TABLE foo (a bigint, b text);

-- input_file.txt --

 1	one
 2	
 3	three	111
 four    4
 5	five

-- end of input_file.txt --

关闭错误日志

 COPY foo FROM 'input_file.txt';

 ERROR:  missing data for column "b"
 CONTEXT:  COPY foo, line 2: "2"

跳过错误行

 --skip bad rows
 COPY foo FROM 'input_file.txt' (ERROR_LOGGING, ERROR_LOGGING_SKIP_BAD_ROWS);
 SELECT * from foo;

  a |  b   
 ---+------
  1 | one
  5 | five
 (2 rows)

开启错误日志（默认日志记录在 error_logging_table 中）

 --turn error logging on (default logs in error_logging_table)
 COPY foo FROM 'input_file.txt' (ERROR_LOGGING);
 SELECT * from foo;

  a |  b   
 ---+------
  1 | one
  5 | five
 (2 rows)

 SELECT * FROM error_logging_table;

  key |           tupletimestamp            |              label              |  targettable  | dmltype |                errmessage                | sqlerrcode |         rawdata          
 -----+-------------------------------------+---------------------------------+---------------+---------+------------------------------------------+------------+--------------------------
    2 | Thu Sep 10 07:09:17.869521 2009 PDT | COPY foo FROM 'input_file.txt'; | pg_temp_2.foo | C       | missing data for column "b"              | 22P04      | \x32
    3 | Thu Sep 10 07:09:17.86953 2009 PDT  | COPY foo FROM 'input_file.txt'; | pg_temp_2.foo | C       | extra data after last expected column    | 22P04      | \x3309746872656509313131
    4 | Thu Sep 10 07:09:17.869538 2009 PDT | COPY foo FROM 'input_file.txt'; | pg_temp_2.foo | C       | invalid input syntax for integer: "four" | 22P02      | \x666f75720934
 (3 rows)

重定向到另一个带有特定标签的表

 -- Redirect to another table with a specific label
 COPY foo FROM 'input_file.txt' (ERROR_LOGGING, ERROR_LOGGING_SCHEMA_NAME 'error', ERROR_LOGGING_TABLE_NAME 'table1', ERROR_LOGGING_LABEL 'batch1');
 SELECT * FROM error.table1;

  key |           tupletimestamp            | label  |  targettable  | dmltype |                errmessage                | sqlerrcode |         rawdata          
 -----+-------------------------------------+--------+---------------+---------+------------------------------------------+------------+--------------------------
    2 | Thu Sep 10 07:09:17.869521 2009 PDT | batch1 | pg_temp_2.foo | C       | missing data for column "b"              | 22P04      | \x32
    3 | Thu Sep 10 07:09:17.86953 2009 PDT  | batch1 | pg_temp_2.foo | C       | extra data after last expected column    | 22P04      | \x3309746872656509313131
    4 | Thu Sep 10 07:09:17.869538 2009 PDT | batch1 | pg_temp_2.foo | C       | invalid input syntax for integer: "four" | 22P02      | \x666f75720934
 (3 rows)

限制为 2 行错误

 -- Limit to 2 bad rows:  
 COPY foo FROM 'input_file.txt' (ERROR_LOGGING, ERROR_LOGGING_MAX_ERRORS 2);

 ERROR:  invalid input syntax for integer: "four"
 CONTEXT:  COPY foo, line 4, column a: "four"

 SELECT count(*) from error_logging_table;

  count 
  -------
       0
  (1 row)