CREATE DATABASE不能在pgadmin 4中的事务块内运行

Question

我尝试在pgAdmin 4中运行一个脚本，但我得到了这个错误：

CREATE DATABASE cannot run inside a transaction block

这是脚本：

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

那么我需要改变什么呢？

谢谢

如果把这个放在脚本上面：

SET AUTOCOMMIT = ON

然后我得到这个错误：

unrecognized configuration parameter "autocommit"

如果我这样做：

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

我得到这个错误：

ERROR:  CREATE DATABASE cannot run inside a transaction block

Answer 1

如果脚本中没有BEGIN;或START TRANSACTION;，则pgAdmin必须将整个脚本作为单个“多语句"；。

在这种情况下，您必须逐个选择并执行CREATE DATABASE语句。

考虑使用psql使此工作更加舒适。

Answer 2

AUTOCOMMIT在9.5之后被删除。你需要运行

set autocommit on

注意开头的反斜杠。

之后，您的数据库创建查询应该可以工作了。

Answer 3

我可能有点过于简化了，但为了解决这个问题，我只需突出显示各个语句，然后一次运行它们。这样一来，pgadmin就不会在交易中发送它们，例如

第一个：

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

(突出显示以上内容并执行(

然后

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

执行

最后(有些不必要(

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