PostgreSQL 异常处理

Question

我是PostgreSQL的新手。请问有人能纠正这个查询吗？

BEGIN TRANSACTION;

BEGIN;
    CREATE TABLE "Logs"."Events"
    (
        EventId BIGSERIAL NOT NULL PRIMARY KEY,
        PrimaryKeyId bigint NOT NULL,
        EventDateTime date NOT NULL DEFAULT(now()),
        Action varchar(12) NOT NULL,
        UserId integer NOT NULL REFERENCES "Office"."Users"(UserId),
        PrincipalUserId varchar(50) NOT NULL DEFAULT(user)
    );

    CREATE TABLE "Logs"."EventDetails"
    (
        EventDetailId BIGSERIAL NOT NULL PRIMARY KEY,
        EventId bigint NOT NULL REFERENCES "Logs"."Events"(EventId),
        Resource varchar(64) NOT NULL,
        OldVal varchar(4000) NOT NULL,
        NewVal varchar(4000) NOT NULL
    );


    COMMIT TRANSACTION;
    RAISE NOTICE 'Task completed sucessfully.'
EXCEPTION;
    ROLLBACK TRANSACTION;
    RAISE ERROR @ErrorMessage, @LineNumber, @ErrorState --how to catch errors and display them????
END;

问题：

1. 如何在 T-SQL 中打印消息，例如“PRINT”？
2. 如何引发带有异常信息的错误？

- Nick Binnet

Answer 1

要捕获错误消息及其代码：

do $$       
begin

    create table yyy(a int);
    create table yyy(a int); -- this will cause an error

exception when others then 

    raise notice 'The transaction is in an uncommittable state. '
                 'Transaction was rolled back';

    raise notice '% %', SQLERRM, SQLSTATE;

end; $$ 
language 'plpgsql';

还没有找到行号

2019年4月16日更新

如Diego Scaravaggi所建议的那样，对于Postgres 9.2及以上版本，请使用GET STACKED DIAGNOSTICS：

do language plpgsql $$
declare
    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
begin

    create table yyy(a int);
    create table yyy(a int); -- this will cause an error

exception when others then 

    get stacked diagnostics
        v_state   = returned_sqlstate,
        v_msg     = message_text,
        v_detail  = pg_exception_detail,
        v_hint    = pg_exception_hint,
        v_context = pg_exception_context;

    raise notice E'Got exception:
        state  : %
        message: %
        detail : %
        hint   : %
        context: %', v_state, v_msg, v_detail, v_hint, v_context;

    raise notice E'Got exception:
        SQLSTATE: % 
        SQLERRM: %', SQLSTATE, SQLERRM;     

    raise notice '%', message_text; -- invalid. message_text is contextual to GET STACKED DIAGNOSTICS only

end; $$;

结果：

NOTICE:  Got exception:
        state  : 42P07
        message: relation "yyy" already exists
        detail : 
        hint   : 
        context: SQL statement "create table yyy(a int)"
PL/pgSQL function inline_code_block line 11 at SQL statement
NOTICE:  Got exception:
        SQLSTATE: 42P07 
        SQLERRM: relation "yyy" already exists

ERROR:  column "message_text" does not exist
LINE 1: SELECT message_text
               ^
QUERY:  SELECT message_text
CONTEXT:  PL/pgSQL function inline_code_block line 33 at RAISE
SQL state: 42703

除了 GET STACKED DIAGNOSTICS 符合 SQL 标准之外，它的诊断变量（例如message_text）仅适用于 GSD 的上下文。因此，如果您的表中有一个名为 message_text 的字段，那么 GSD 不可能干扰该字段的值。

但是仍然没有行号。

- Michael Buen

---

如果一条语句失败了，PostgreSQL会自动发出回滚吗？是否不需要显式地回滚？ - Jonas Stawski

是的。如果您在交易中（在“begin”之后但在“commit”或“rollback”之前）发生错误，则自“begin”以来的所有内容都将被回滚。 - gr5

如何将 raise notice '% %', SQLERRM, SQLSTATE; 中的数据放入一个表中？ - Daniel L. VanDenBosch

自9.2版本以来，“GET STACKED DIAGNOSTICS myVariable = PG_EXCEPTION_CONTEXT;”非常实用。 - Diego Scaravaggi

Answer 2

使用DO语句，这是9.0版本中的新选项：

DO LANGUAGE plpgsql
$$
BEGIN
CREATE TABLE "Logs"."Events"
    (
        EventId BIGSERIAL NOT NULL PRIMARY KEY,
        PrimaryKeyId bigint NOT NULL,
        EventDateTime date NOT NULL DEFAULT(now()),
        Action varchar(12) NOT NULL,
        UserId integer NOT NULL REFERENCES "Office"."Users"(UserId),
        PrincipalUserId varchar(50) NOT NULL DEFAULT(user)
    );

    CREATE TABLE "Logs"."EventDetails"
    (
        EventDetailId BIGSERIAL NOT NULL PRIMARY KEY,
        EventId bigint NOT NULL REFERENCES "Logs"."Events"(EventId),
        Resource varchar(64) NOT NULL,
        OldVal varchar(4000) NOT NULL,
        NewVal varchar(4000) NOT NULL
    );

    RAISE NOTICE 'Task completed sucessfully.';    
END;
$$;

- Frank Heikens

Answer 3

您可以将此编写为psql脚本，例如：

START TRANSACTION;
CREATE TABLE ...
CREATE TABLE ...
COMMIT;
\echo 'Task completed sucessfully.'

并且运行

psql -f somefile.sql

在PostgreSQL中，直接使用参数引发错误是不可能的。在移植此类代码时，有些人会将必要的信息编码到错误字符串中，并在需要时解析出来。

一切都有所不同，因此请准备好重新学习/重新思考/重写很多东西。

- Peter Eisentraut

Answer 4

只是想在这篇旧文章中添加我的意见：

我认为，几乎所有的关系型数据库引擎在执行 DDL 命令后都会自动执行提交事务，即使您将 autocommit 设置为 false，因此您不需要开始事务以避免可能的截断对象创建，因为这完全没有必要。

- jhon Carrillo

---

这对于Postgres是错误的。DDL与UPDATE语句一样是事务性的。 - user330315

对于 Db2 和 SQL Server 也不是这样，所以几乎所有的都可能有点误导。 - Lennart - Slava Ukraini

Postgres、Oracle、Sybase、SQL Server 和 DB2 都被排除在外，因此我认为这个说法对于 Oracle 和 Sybase 以及其他评论者提到的数据库引擎是错误的。但我想知道你所说的“几乎全部”是什么意思。 - Dawood ibn Kareem