Execute postgreSQL stored procedure as one transaction

Question

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.

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.

How can I do this? Perhaps deactivating autocommit on the JDBC level?

Disable autocommit and call the function after starting a transaction. — user330315
– user330315, Commented Oct 16, 2014 at 14:01
@a_horse_with_no_name Ahh you mean first dbConnection.setAutoCommit(false); (transaction block starts) then I do the prepared statement and then I do dbConnection.commit();. Is this right? Do I have to care about rollback? — machinery
– machinery, Commented Oct 16, 2014 at 14:12

Kouber Saparev · Accepted Answer · 2014-10-16 14:41:19Z

16

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;

Then try invoking 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)

edited Oct 16, 2014 at 14:41

answered Oct 16, 2014 at 14:13

Kouber Saparev

8,2552 gold badges32 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick Barnes Over a year ago

And not only that, but you couldn't COMMIT in a stored procedure even if you wanted to.

Collectives™ on Stack Overflow

Execute postgreSQL stored procedure as one transaction

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related