postgresql - CREATE DATABASE 无法在 pgadmin 4 的事务 block 内运行

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 使这项工作更舒适。