主页 / dba / 问题 / 249446
Accepted
Damien Sawyer
Asked: 2019-09-24 16:35:57 +0800 CST

无法在 PostgreSQL 11.5 过程中创建 COMMIT

  • 772

我正在尝试学习 PostgreSQL 存储过程。具体在 PSQL 中创建以下过程。

CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor) 
    LANGUAGE PLPGSQL 
    AS $$
    BEGIN
        BEGIN; -- I've tried removing this but the behaviour is the same
        cresults:= 'cur';
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;  
        COMMIT;
        OPEN cresults FOR SELECT * FROM procsampledata;  
    END;
$$;

然后我像这样执行它，但收到一个错误：

postgres=# call build_and_populate(null);
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function build_and_populate(refcursor) line 6 at COMMIT

我已经尝试将 AUTOCOMMIT 设置为打开和关闭。

这是我的 Postgres 版本

 PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

谁能看到我做错了什么？谢谢！

postgresql plpgsql

2 个回答

  1. Best Answer

    这

    BEGIN;

    是错误的，会导致错误。您不能在过程中启动事务，因为已经有一个活动事务。

    您可以结束一个事务，这意味着立即开始一个新事务。

    没有BEGIN;，您的程序就可以正常工作。

    问题一定出在你调用它的方式上。正如文档所说：

    事务控制只能在顶层调用或嵌套调用或 DO 调用中进行，而无需任何其他干预命令CALL。DO例如，如果调用堆栈是CALL proc1()→ CALL proc2()→ CALL proc3()，那么第二个和第三个过程可以执行事务控制动作。但是如果调用堆栈是CALL proc1()→ SELECT func2()→ CALL proc3()，那么最后一个过程不能进行事务控制，因为SELECT介于两者之间。

    您的调用堆栈中可能有一个SELECT。

    另一种可能性是您在调用存储过程BEGIN 之前显式启动了事务。

    这也不起作用，这是一个未记录的实现限制，将来可能会修复。请参阅此线程以供参考。

    • 5

  2. 在确定BEGIN;过程中不允许这样做并且客户端CALL不能从已经开始的事务中发生之后，问题归结为（正如您在评论中所写的那样）：

    “这是否意味着不可能从过程中返回游标，因为（在我的理解中）它们无法在交易后存活”

    这仍然是可能的。有两种 CURSOR：WITH HOLD过期事务和WITHOUT HOLD（默认）不存活。您可以使用一些动态 SQL 将 refcursor 变量指向WITH HOLDplpgsql 中的游标。这是您的程序的修改版本：

    CREATE OR REPLACE PROCEDURE public.build_and_populate(INOUT cresults refcursor)
 LANGUAGE plpgsql
AS $procedure$
    BEGIN
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;
        COMMIT;
        EXECUTE 'DECLARE ' || quote_ident(cresults::text) || ' CURSOR WITH HOLD FOR SELECT * FROM procsampledata';                                               
    END;
$procedure$

    postgres=# call BUILD_AND_POPULATE('res');
 cresults 
----------
 res
(1 row)

postgres=# fetch all from res;
 x  | c2 | c3 |               md5                
----+----+----+----------------------------------
  1 |  1 |  2 | 11a3d2e637332a25118c2f4d5dac49c0
  2 |  1 |  2 | 4c5b9cb5ec79479a9daa9ae7a131a078
  3 |  1 |  2 | 320b49912e94de90c3370836706c5494
  4 |  1 |  2 | a9ccf45dbbbbec19d18b69cf4a0fc26b
  5 |  1 |  2 | 1a52801f1761cb0e357a4468803473e1
  6 |  1 |  2 | 96c395dad11ee19526bc08b5ad114905
  7 |  1 |  2 | 7d0da9a0cf2520ee871185677d7062cf
  8 |  1 |  2 | 65ecf1e68806a0b9133fa0e47056574e
  9 |  1 |  2 | bae5c688e1ef6b9c389b37ecc7a18342
 10 |  1 |  2 | ed07142f3b51a282c05370890a8af468
(10 rows)

postgres=# close res;
CLOSE CURSOR
    • 2

相关问题