pg 日志记录异常处理

## 日志表

```sql
CREATE TABLE IF NOT EXISTS public.tbl_proc_log(
    run_id varchar primary key,
    proc_name varchar,
    start_time timestamptz,
    end_time timestamptz,
    exec_status text,
    returned_sqlstate text,
    column_name text,
    constraint_name text,
    pg_datatype_name text,
    message_text text,
    table_name text,
    schema_name text,
    pg_exception_detail text,
    pg_exception_hint text,
    pg_exception_context text
);
```

## 日志存储过程

```sql
CREATE OR REPLACE PROCEDURE public.pro_record_proc_log(p_run_id text, 
                                                      p_proc_name text, 
                                                      p_start_time timestamp with time zone, 
                                                      p_end_time timestamp with time zone, 
                                                      p_exec_status text, 
                                                      p_returned_sqlstate text, 
                                                      p_column_name text, 
                                                      p_constraint_name text, 
                                                      p_pg_datatype_name text, 
                                                      p_message_text text, 
                                                      p_table_name text, 
                                                      p_schema_name text, 
                                                      p_pg_exception_detail text, 
                                                      p_pg_exception_hint text, 
                                                      p_pg_exception_context text)
 LANGUAGE plpgsql
AS $procedure$
BEGIN
    INSERT INTO public.tbl_proc_log (
        run_id, 
        proc_name, 
        start_time, 
        end_time, 
        exec_status,
        returned_sqlstate, 
        column_name, 
        constraint_name, 
        pg_datatype_name, 
        message_text, 
        table_name, 
        schema_name, 
        pg_exception_detail, 
        pg_exception_hint, 
        pg_exception_context
    )
    VALUES(
        p_run_id, 
        p_proc_name, 
        p_start_time, 
        p_end_time, 
        p_exec_status,
        p_returned_sqlstate, 
        p_column_name, 
        p_constraint_name, 
        p_pg_datatype_name, 
        p_message_text, 
        p_table_name, 
        p_schema_name, 
        p_pg_exception_detail, 
        p_pg_exception_hint, 
        p_pg_exception_context
    )
    ON CONFLICT (run_id) DO
    UPDATE SET
        proc_name = p_proc_name,
        start_time = p_start_time,
        end_time = p_end_time,
        exec_status = p_exec_status,
        returned_sqlstate = p_returned_sqlstate,
        column_name = p_column_name,
        constraint_name = p_constraint_name,
        pg_datatype_name = p_pg_datatype_name,
        message_text = p_message_text,
        table_name = p_table_name,
        schema_name = p_schema_name,
        pg_exception_detail = p_pg_exception_detail,
        pg_exception_hint = p_pg_exception_hint,
        pg_exception_context = p_pg_exception_context
    ;
END;
$procedure$
;
```

## 测试

```sql
CREATE OR REPLACE PROCEDURE public.test(p_param text)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    ----------------日志变量 start-------------
    v_run_id varchar := to_char(clock_timestamp(),'yyyymmddhh24missus')||'-'||txid_current();
    v_proc_name varchar := FORMAT('public.test(%s::text)',p_param);
    v_start_time timestamptz := clock_timestamp();
    v_end_time timestamptz;
    v_exec_status text := 'running';
    v_returned_sqlstate text := '';
    v_column_name text := '';
    v_constraint_name text := '';
    v_pg_datatype_name text := '';
    v_message_text text := '';
    v_table_name text := '';
    v_schema_name text := '';
    v_pg_exception_detail text := '';
    v_pg_exception_hint text := '';
    v_pg_exception_context text := '';
    ----------------日志变量 end  -------------
BEGIN
    --------------------- 日志记录 -------------------------------
    CALL public.pro_record_proc_log( p_run_id => v_run_id,
                        p_proc_name => v_proc_name,
                        p_start_time => v_start_time,
                        p_end_time => v_end_time,
                        p_exec_status => v_exec_status,
                        p_returned_sqlstate => v_returned_sqlstate,
                        p_column_name => v_column_name,
                        p_constraint_name => v_constraint_name,
                        p_pg_datatype_name => v_pg_datatype_name,
                        p_message_text => v_message_text,
                        p_table_name => v_table_name,
                        p_schema_name => v_schema_name,
                        p_pg_exception_detail => v_pg_exception_detail,
                        p_pg_exception_hint => v_pg_exception_hint,
                        p_pg_exception_context => v_pg_exception_context);

--------------------- 执行内容 -------------------------------
    PERFORM pg_sleep(3);
    CREATE TABLE test(id int);

--------------------- 成功日志记录 ----------------------------
    v_end_time := clock_timestamp();
    v_exec_status := 'success finished';
    CALL public.pro_record_proc_log( p_run_id => v_run_id,
                        p_proc_name => v_proc_name,
                        p_start_time => v_start_time,
                        p_end_time => v_end_time,
                        p_exec_status => v_exec_status,
                        p_returned_sqlstate => v_returned_sqlstate,
                        p_column_name => v_column_name,
                        p_constraint_name => v_constraint_name,
                        p_pg_datatype_name => v_pg_datatype_name,
                        p_message_text => v_message_text,
                        p_table_name => v_table_name,
                        p_schema_name => v_schema_name,
                        p_pg_exception_detail => v_pg_exception_detail,
                        p_pg_exception_hint => v_pg_exception_hint,
                        p_pg_exception_context => v_pg_exception_context);

-------------------- 异常处理 --------------------------
EXCEPTION
    WHEN OTHERS THEN
        v_end_time := clock_timestamp();
        v_exec_status := 'exception finished';
        GET STACKED DIAGNOSTICS v_returned_sqlstate := RETURNED_SQLSTATE;
        GET STACKED DIAGNOSTICS v_column_name := COLUMN_NAME;
        GET STACKED DIAGNOSTICS v_constraint_name := CONSTRAINT_NAME;
        GET STACKED DIAGNOSTICS v_pg_datatype_name := PG_DATATYPE_NAME;
        GET STACKED DIAGNOSTICS v_message_text := MESSAGE_TEXT;
        GET STACKED DIAGNOSTICS v_table_name := TABLE_NAME;
        GET STACKED DIAGNOSTICS v_schema_name := SCHEMA_NAME;
        GET STACKED DIAGNOSTICS v_pg_exception_detail := PG_EXCEPTION_DETAIL;
        GET STACKED DIAGNOSTICS v_pg_exception_hint := PG_EXCEPTION_HINT;
        GET STACKED DIAGNOSTICS v_pg_exception_context := PG_EXCEPTION_CONTEXT;
        CALL public.pro_record_proc_log( p_run_id => v_run_id,
                        p_proc_name => v_proc_name,
                        p_start_time => v_start_time,
                        p_end_time => v_end_time,
                        p_exec_status => v_exec_status,
                        p_returned_sqlstate => v_returned_sqlstate,
                        p_column_name => v_column_name,
                        p_constraint_name => v_constraint_name,
                        p_pg_datatype_name => v_pg_datatype_name,
                        p_message_text => v_message_text,
                        p_table_name => v_table_name,
                        p_schema_name => v_schema_name,
                        p_pg_exception_detail => v_pg_exception_detail,
                        p_pg_exception_hint => v_pg_exception_hint,
                        p_pg_exception_context => v_pg_exception_context);
END;
$procedure$
;
```

## 错误诊断项参考

| 名称                   | 类型   | 描述                                                         |
| ---------------------- | ------ | ------------------------------------------------------------ |
| `RETURNED_SQLSTATE`    | `text` | 该异常的 SQLSTATE 错误代码                                   |
| `COLUMN_NAME`          | `text` | 与异常相关的列名                                             |
| `CONSTRAINT_NAME`      | `text` | 与异常相关的约束名                                           |
| `PG_DATATYPE_NAME`     | `text` | 与异常相关的数据类型名                                       |
| `MESSAGE_TEXT`         | `text` | 该异常的主要消息的文本                                       |
| `TABLE_NAME`           | `text` | 与异常相关的表名                                             |
| `SCHEMA_NAME`          | `text` | 与异常相关的模式名                                           |
| `PG_EXCEPTION_DETAIL`  | `text` | 该异常的详细消息文本（如果有）                               |
| `PG_EXCEPTION_HINT`    | `text` | 该异常的提示消息文本（如果有）                               |
| `PG_EXCEPTION_CONTEXT` | `text` | 描述产生异常时调用栈的文本行（见[第 42.6.9 节](http://postgres.cn/docs/12/plpgsql-control-structures.html#PLPGSQL-CALL-STACK)） |

pg 日志记录 异常处理

pg 日志记录异常处理