Run the postgreSQL stored procedure as a single transaction

Question

I am using PostgreSQL 9.3, and I have several stored procedures that contain several statements. I call these stored procedures in a Java application using a prepared statement.

Now I read that each statement inside the stored procedure is executed as a transaction, i.e. one commit after each statement. But I want the whole stored procedure to be executed as a single transaction, i.e. Only one fix.

How can i do this? Is it possible to deactivate the autocommit at the JDBC level?

Answer 1

Well, basically stored procedures are atomic in nature and performed as a single 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; 

Then try calling f() from psql .

 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)