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

Accepted Answer

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.

Answer 2

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.

Answer 3

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.';

Create Database Cannot Run Inside a Transaction Block in Pgadmin 4

3 Answers

Must Read

Your Answer

David Miller

Create Database Cannot Run Inside a Transaction Block in Pgadmin 4

3 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

David Miller