CREATE DATABASE cannot run inside a transaction block in pgadmin 4

Question

I am triing to run a script in pgAdmin 4, but I get this error:

CREATE DATABASE cannot run inside a transaction block

And this is the script:

CREATE USER ky_auth WITH PASSWORD 'ky_auth';
COMMENT ON ROLE ky_auth IS 'KnowYourself Auth Database User';
CREATE DATABASE ky_auth WITH OWNER = ky_auth;
COMMENT ON DATABASE ky_auth IS 'KnowYourself Auth Database';

CREATE USER ky_pers WITH PASSWORD 'ky_pers';
COMMENT ON ROLE ky_pers IS 'KnowYourself Personal Database User';
CREATE DATABASE ky_pers WITH OWNER = ky_pers;
COMMENT ON DATABASE ky_pers IS 'KnowYourself Personal Database';

CREATE USER ky_oper WITH PASSWORD 'ky_oper';
COMMENT ON ROLE ky_oper IS 'KnowYourself Operational Database User';
CREATE DATABASE ky_oper WITH OWNER = ky_oper;
COMMENT ON DATABASE ky_oper IS 'KnowYourself Operational Database';

CREATE USER knowyourself_tests WITH PASSWORD 'ky_tests' CREATEDB;
COMMENT ON ROLE knowyourself_tests IS 'KnowYourself Integration Tests Database User';

So what I have to change?

Thank you

and if put this above the script:

SET AUTOCOMMIT = ON

Then I get this error:

 unrecognized configuration parameter "autocommit"

if I do this:

CREATE USER ky_auth WITH PASSWORD 'ky_auth';
COMMENT ON ROLE ky_auth IS 'KnowYourself Auth Database User';

then it succeeds. but then If I do this:


CREATE DATABASE ky_auth WITH OWNER = ky_auth;
COMMENT ON DATABASE ky_auth IS 'KnowYourself Auth Database';

I get this error:

ERROR:  CREATE DATABASE cannot run inside a transaction block

This is essentially the same thing: dba.stackexchange.com/questions/21463/… Need to run 'create' separately in pgAdmin or use commandline psql — Thomas Timbul
– Thomas Timbul, Commented Mar 9, 2020 at 14:46

Laurenz Albe · Accepted Answer · 2020-03-09 13:44:22Z

4

If there is no BEGIN; or START TRANSACTION; anywhere in your script, then pgAdmin must send your whole script to the database as a single “multi-statement”.

In that case you have to select and execute the CREATE DATABASE statements one by one.

Consider using psql to make this work more comfortably.

edited Mar 9, 2020 at 13:44

answered Mar 9, 2020 at 13:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Laurenz Albe Over a year ago

Ok, then maybe ma modified answer has the explanation.

anch2150 · Accepted Answer · 2021-02-26 19:28:32Z

1

AUTOCOMMIT is removed after 9.5. You need to run

\set autocommit on

Pay attention to the backslash \ in the beginning.

Afterwards your db creation query should work.

answered Feb 26, 2021 at 19:28

anch2150

916 bronze badges

Comments

Chris Bham UK · Accepted Answer · 2021-08-06 09:26:33Z

I might be slightly over simplifying things, but to work around this issue I simply highlight the individual statements and run them one at a time. That way pgadmin doesn't sent them in a transaction e.g.

first:

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'olddb' AND pid <> pg_backend_pid();

(highlight the above and execute it)

Then

CREATE DATABASE yournewdb
    WITH 
    OWNER = USER
    TEMPLATE = olddb
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

execute that

then finally (and somewhat needlessly)

COMMENT ON DATABASE yournewdb
    IS 'The development instance of the olddb database. Will be destroyed and recreated at short notice.';

Collectives™ on Stack Overflow

CREATE DATABASE cannot run inside a transaction block in pgadmin 4

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related