Run code block in a transaction in postgres

Question

I want to run the following code block in a transaction so that if any of the sql statements fail the entire transaction is aborted. If I run the following block as it is, does it run in a transaction or do I need to run it inside BEGIN; ... COMMIT;

DO $$
  DECLARE
    readonly_exists int;
BEGIN
  SELECT COUNT(*) INTO readonly_exists FROM information_schema.enabled_roles
    WHERE role_name = 'readonly';
  IF readonly_exists = 0 THEN
    <SQL STATEMENT 1>
    <SQL STATEMENT 2>
    <SQL STATEMENT 3>
  ELSE
    RAISE EXCEPTION 'readonly role already exists';
  END IF;
END$$;

Laurenz Albe · Accepted Answer · 2020-02-28 07:35:27Z

5

Any SQL statement always runs in a single transaction (the exception to this rule is CALL).

So your DO statement will run in a single transaction, and either all three SQL statements are successful, or all will be rolled back.

answered Feb 28, 2020 at 7:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Monku Over a year ago

I saw at couple of places where DO statement was run inside BEGIN COMMIT . Is that a good practice to do ? I mean if DO statement is itself run in a transaction what's the point of running it inside BEGIN COMMIT

Laurenz Albe Over a year ago

Since PostgreSQL uses autocommit mode, the DO statement will run in its own transaction by default. You start a transaction explicitly with BEGIN if you want other SQL statements to run in the same transaction as the DO statement.

Collectives™ on Stack Overflow

Run code block in a transaction in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related