将PostgreSQL存储过程作为一个事务执行 [英] Execute postgreSQL stored procedure as one transaction
问题描述
我正在使用PostgreSQL 9.3,并且创建了一些包含几个语句的存储过程.我正在准备好的语句的帮助下在Java应用程序中调用此存储过程.
I'm using PostgreSQL 9.3 and I have some stored procedures created which contains several statements. I'm calling this stored procedures in a Java application with the help of a prepared statement.
现在,我已经阅读到存储过程中的每个语句都作为事务执行,即在每个语句之后执行一次commit.但是我想要的是将整个存储过程作为一个事务执行,即仅执行一次提交.
Now I've read that each statement inside the stored procedure is executed as a transaction, i.e. one commit after each statement. But what I want is to have the whole stored procedure executed as one transaction, i.e. only one commit.
我该怎么做?也许在JDBC级别上禁用自动提交?
How can I do this? Perhaps deactivating autocommit on the JDBC level?
推荐答案
基本上,存储过程是原子的,并且作为一个事务执行.
Well, basically stored procedures are atomic in nature and executed as one transaction.
CREATE TABLE xxx (id int PRIMARY KEY);
CREATE OR REPLACE FUNCTION f() RETURNS void AS $$
DECLARE
len int;
BEGIN
RAISE NOTICE 'Transaction ID: %', TXID_CURRENT();
INSERT INTO xxx VALUES (1);
RAISE NOTICE 'Transaction ID: %', TXID_CURRENT();
INSERT INTO xxx VALUES (2);
RAISE NOTICE 'Transaction ID: %', TXID_CURRENT();
SELECT COUNT(*) FROM xxx INTO len;
RAISE NOTICE 'Number of records: %', len;
RAISE NOTICE 'Transaction ID: %', TXID_CURRENT();
-- results in unique constraint violation
UPDATE xxx SET id = 3;
END;
$$ LANGUAGE plpgsql;
然后尝试从psql
调用f()
.
stackoverflow=# show autocommit;
autocommit
------------
on
(1 row)
stackoverflow=# SELECT f();
NOTICE: Transaction ID: 15086
NOTICE: Transaction ID: 15086
NOTICE: Transaction ID: 15086
NOTICE: Number of records: 2
NOTICE: Transaction ID: 15086
ERROR: duplicate key value violates unique constraint "xxx_pkey"
DETAIL: Key (id)=(3) already exists.
CONTEXT: SQL statement "UPDATE xxx SET id = 3"
PL/pgSQL function f() line 20 at SQL statement
stackoverflow=# SELECT * FROM xxx;
id
----
(0 rows)
这篇关于将PostgreSQL存储过程作为一个事务执行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!