PostgreSQL序列化失败的通用处理程序 [英] PostgreSQL generic handler for serialization failure

问题描述

这是来自一个的后续问题，所以我知道我可以使用（阻止）锁，但是我想使用谓词锁和可序列化的事务隔离。

This is a followup question from this one so I know I can use (blocking) LOCKs but I want to use predicate locks and serializable transaction isolation.

我想要的是一个通用的序列化失败处理程序，该函数将重试函数/查询的X数量。

What I'd like to have is a generic handler of serialization failures that would retry the function/query X number of times.

例如，我有：

CREATE SEQUENCE account_id_seq;

CREATE TABLE account
(
  id integer NOT NULL DEFAULT nextval('account_id_seq'),
  title character varying(40) NOT NULL,
  balance integer NOT NULL DEFAULT 0,
  CONSTRAINT account_pkey PRIMARY KEY (id)
);

INSERT INTO account (title) VALUES ('Test Account');

CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$
DECLARE
    cc integer;
BEGIN
    cc := balance from account where id=1;

    RAISE NOTICE 'Balance: %', cc;
    perform pg_sleep(3);

    update account set balance = cc+10 where id=1 RETURNING balance INTO cc;

    return cc;
END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION myretest() RETURNS integer AS $$
DECLARE
    tries integer := 5;
BEGIN
    WHILE TRUE LOOP
        BEGIN -- nested block for exception
            RETURN mytest();
        EXCEPTION
            WHEN SQLSTATE '40001' THEN
                IF tries > 0 THEN
                    tries := tries - 1;
                    RAISE NOTICE 'Restart! % left', tries;
                ELSE
                    RAISE EXCEPTION 'NO RESTARTS LEFT';
                END IF;
        END;
    END LOOP;
END
$$
LANGUAGE plpgsql;

因此，如果直接同时调用 mytest（）我在最后一次提交时遇到序列化失败：

So if call mytest() directly concurrently I get a serialization failure on the last commit:

4SO$ psql -c "select mytest()" & PIDA=$! && psql -c "select mytest()" && wait $PIDA
[1] 4909
NOTICE:  Balance: 0
NOTICE:  Balance: 0
 mytest 
--------
     10
(1 row)

ERROR:  could not serialize access due to concurrent update
CONTEXT:  SQL statement "update account set balance = cc+10 where id=1 RETURNING balance"
PL/pgSQL function mytest() line 10 at SQL statement

如果我调用 myretest（），它应该尝试执行 mytest（）直到第五次尝试都会引发异常。

If I call myretest() it should try to execute mytest() up until the 5th try where it would raise the exception.

所以我在这里有两点（也许第2点也会使第1点无效）：

So I have two points here (where maybe point 2 also invalidates point 1):

• myretest（）不能按预期工作，即使在并发线程完成后，每次迭代也会导致serialiation_failure异常：我应该在 reset中添加一些内容

• myretest() does not work as expected, every iteration results in serialiation_failure exception even after the concurrent thread finishes: is there something I should add to "reset" the transaction?

我如何使这个（ myretest（）逻辑）通用，以便它能适用于sy中的每个调用函数是否不需要这样的包装功能？

how could I make this (myretest() logic) generic so that it would apply to every called function in the system without the need for "wrapper" functions as such?

推荐答案

可序列化只要您使用某个框架，当收到错误且 40001 SQLSTATE 的错误时重新启动事务，事务就可以提供所需的内容/ code>或 40P01 。

Serializable transactions provide exactly what you are looking for as long as you use some framework that starts the transaction over when it receives an error with a SQLSTATE of 40001 or 40P01.

在PostgreSQL中，函数始终在事务上下文中运行。您无法在包装器功能的上下文中开始新的交易。这将需要稍有不同的功能，通常称为存储过程-PostgreSQL中不存在。因此，您需要将管理重启的逻辑放入将事务提交到数据库的代码中。幸运的是，有许多用于此的连接器-Java，perl，python，tcl，ODBC等。甚至还有一个连接器，用于在PostgreSQL过程语言中建立到PostgreSQL数据库的单独连接，这可能使您可以执行以下操作您想要什么：

In PostgreSQL a function always runs in the context of a transaction. You can't start a new transaction within the context of a "wrapper" function. That would require a slightly different feature, which is commonly called a "stored procedure" -- something which doesn't exist in PostgreSQL. Therefore, you need to put the logic to manage the restart into code which submits the transaction to the database. Fortunately, there are many connectors for that -- Java, perl, python, tcl, ODBC, etc. There is even a connector for making a separate connection to a PostgreSQL database within a PostgreSQL procedural language, which might allow you to do something like what you want:

http://www.postgresql.org/docs/current/static/dblink.html

我已经在各种客户端框架中看到了这一点。显然，将其散布到应用程序在逻辑上与数据库进行处理的所有位置都是一个坏主意，但是有许多充分的理由通过一个访问器方法（或者至少是很少的一个）路由所有数据库请求。 ），并且大多数框架都提供了在该层处理此问题的方法。 （例如，在Spring中，您可能希望使用依赖项注入来创建一个事务管理器。）这可能属于您用于应用程序逻辑的某种语言，但是如果您确实想要，则可以使用plpgsql和dblink;。不过，这可能不是您最简单的方法。

I have seen this done in various "client" frameworks. Clearly it is a bad idea to spread this around to all locations where the application is logically dealing with the database, but there are many good reasons to route all database requests through one "accessor" method (or at least a very small number of them), and most frameworks provide a way to deal with this at that layer. (For example, in Spring you would want to create a transaction manager using dependency injection.) That probably belongs in some language you are using for your application logic, but if you really wanted to you could probably use plpgsql and dblink; that's probably not going to be your easiest path, though.

这篇关于PostgreSQL序列化失败的通用处理程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！