CODEWITHSUNDEEP
databasepostgresqltransactionsatomicpostgresql-11
ZORRO_BLANCO
ZORRO_BLANCO

Reputation: 909

How to implement `BEGIN ATOMIC` in PostgreSQL

What's the equivalent of Sybase BEGIN ATOMIC in PostgreSQL?

It should be something like:

CREATE OR REPLACE FUNCTION my_func()
RETURNS int
AS $$ 
BEGIN 
    BEGIN ATOMIC
        UPDATE stetment1;
        UPDATE stetment2;
        UPDATE stetment3;
    END;
END;
$$ LANGUAGE plpgsql;

The three update statements must either succeed or fail all together as one.

Upvotes: 3

Views: 2428

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can use BEGIN ATOMIC; ... END; statement in PostgreSQL as shown below. *BEGIN ATOMIC; ... END; statement works only for LANGUAGE SQL according to the doc and my answer gives some examples with BEGIN ATOMIC; ... END; statement:

CREATE FUNCTION my_func() RETURNS INTEGER LANGUAGE SQL
BEGIN ATOMIC; -- Here
SELECT 2 + 3;
END; -- Here

And actually, not using BEGIN ATOMIC; ... END; statement as shown below is same as above because in PostgreSQL, BEGIN ATOMIC; ... END; statement has nothing special, it is just a syntax and actually in PostgreSQL, a function is automatically run in a transaction to rollback if there is error:

CREATE FUNCTION my_func(value INTEGER) RETURNS INTEGER AS $$
SELECT 2 + 3;
$$ LANGUAGE SQL;

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656251

the three update statements must either succeed or fail all together as one..

The comments below the question are a misunderstanding. Every Postgres function is transactional and always either succeeds or fails alltogether as one. You'd only need a PROCEDURE for the opposite: to commit what's already done inside the body of a procedure.

The literal clause BEGIN ATOMIC is used for the new standard-SQL CREATE FUNCTION syntax, which has eventually been added with Postgres 14. But it's just as "atomic" as any other Postgres function.

Either way, with new syntax you could write:

CREATE OR REPLACE FUNCTION my_func()
  LANGUAGE plpgsql
  RETURNS void  -- since you are not returning anything
BEGIN ATOMIC
   update stetment1;
   update stetment2;
   update stetment3:
END;

See:

Upvotes: 1

Related Questions