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

1

3 Answers

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.

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.

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy