postgresql 错误：无法在只读事务中执行 CREATE TABLE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31186414/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR: cannot execute CREATE TABLE in a read-only transaction
提问by ltrainpr
I'm trying to setup the pgexercisesdata in my local machine. When I run: psql -U <username> -f clubdata.sql -d postgres -xI get the error: psql:clubdata.sql:6: ERROR: cannot execute CREATE SCHEMA in a read-only transaction.
我正在尝试在我的本地机器上设置pgexercises数据。当我运行时：psql -U <username> -f clubdata.sql -d postgres -x我收到错误：psql:clubdata.sql:6: ERROR: cannot execute CREATE SCHEMA in a read-only transaction。
Why did it create a read-only database on my local machine? Can I change this?
为什么它在我的本地机器上创建了一个只读数据库？我可以改变这个吗？
采纳答案by ltrainpr
Reached out to pgexercises.com and they were able to help me.
联系 pgexercises.com，他们能够帮助我。
I ran these commands(separately):
我运行了这些命令（单独）：
psql -U <username> -d postgres
begin;
set transaction read write;
alter database exercises set default_transaction_read_only = off;
commit;
\q
Then I dropped the database from the terminal dropdb exercisesand ran script again psql -U <username> -f clubdata.sql -d postgres -x -q
然后我从终端删除数据库dropdb exercises并再次运行脚本psql -U <username> -f clubdata.sql -d postgres -x -q
回答by Daniel Vérité
Normally the most plausible reasons for this kind of error are :
通常，这种错误最合理的原因是：
trying create statements on a read-only replica (the entire instance is read-only).
<username>hasdefault_transaction_read_onlyset to ONthe database has
default_transaction_read_onlyset to ON
在只读副本上尝试创建语句（整个实例都是只读的）。
<username>已default_transaction_read_only设置为 ON数据库已
default_transaction_read_only设置为 ON
The script mentioned has in its first lines:
提到的脚本在其第一行中有：
CREATE DATABASE exercises;
\c exercises
CREATE SCHEMA cd;
and you report that the error happens with CREATE SCHEMAat line 6, not before.
并且您报告错误发生CREATE SCHEMA在第 6 行，而不是之前。
That means that the CREATE DATABASEdoes work, when run by <username>.
And it wouldn't work if any of the reasons above was directly applicable.
这意味着CREATE DATABASE当由<username>. 如果上述任何原因直接适用，它将不起作用。
One possibility that would technically explain this would be that default_transaction_read_onlywould be ONin the postgresql.conffile, and set to OFFfor the database postgres, the one that the invocation of psql connects to, through an ALTER DATABASEstatement that supersedes the configuration file.
从技术上解释这一点的一种可能性是，default_transaction_read_only它将ON在postgresql.conf文件中，并通过取代配置文件的语句设置OFF为数据库postgres，即 psql 的调用连接到的那个数据库ALTER DATABASE。
That would be why CREATE DATABASEworks, but then as soon as it connects to a different database with \c, the default_transaction_read_onlysetting of the session would flip to ON.
这就是为什么CREATE DATABASE有效，但是一旦它连接到不同的数据库\c，default_transaction_read_only会话的设置就会翻转到ON.
But of course that would be a pretty weird and unusual configuration.
但当然，这将是一个非常奇怪和不寻常的配置。
回答by Brad Mathews
I was having getting cannot execute CREATE TABLE in a read-only transaction, cannot execute DELETE TABLE in a read-only transactionand others.
我有得到cannot execute CREATE TABLE in a read-only transaction，cannot execute DELETE TABLE in a read-only transaction和其他人。
They all followed a cannot execute INSERT in a read-only transaction. It was like the connection had switched itself over to read-only in the middle of my batch processing.
他们都跟着一个cannot execute INSERT in a read-only transaction。就好像在我的批处理过程中连接已经切换到只读状态。
Turns out, I was running out of storage! Write access was disabled when the database could no longer write anything. I am using Postgres on Azure. I don't know if the same effect would happen if I was on a dedicated server.
原来，我的存储空间用完了！当数据库无法再写入任何内容时，写入访问被禁用。我在 Azure 上使用 Postgres。我不知道如果我在专用服务器上是否会发生同样的效果。
回答by Kevin Parker
This occurred when I was restoring a production database locally, the database is still doing online recovery from the WAL records.
这发生在我在本地恢复生产数据库时，该数据库仍在从 WAL 记录进行在线恢复。
A little bit unexpected as I assumed pgbackgrest was creating instantly recoverable restores, perhaps not.
有点出乎我的意料，因为我认为 pgbackgrest 正在创建可立即恢复的恢复，也许不是。
91902 postgres 20 0 1445256 14804 13180 D 4.3 0.3 0:28.06 postgres: startup recovering 000000010000001E000000A5
回答by ps2goat
This doesn't quite answer the original question, but I received the same error and found this page, which ultimately led to a fix.
这并不能完全回答最初的问题，但我收到了同样的错误并找到了这个页面，最终导致了修复。
My issue was trying to run a function with temp tables being created and dropped. The function was created with SECURITY DEFINERprivileges, and the user had access locally.
我的问题是尝试运行一个创建和删除临时表的函数。该函数是使用SECURITY DEFINER特权创建的，用户可以在本地访问。
In a different environment, I received the cannot execute DROP TABLE in a read-only transactionerror message. This environment was AWS Aurora, and by default, non-admin developers were given read-only privileges. Their server connections were thus set up to use the read-only node of Aurora (-ro-is in the connection url), which must put the connection in the read-only state. Running the same function with the same user against the write node worked.
在不同的环境中，我收到了cannot execute DROP TABLE in a read-only transaction错误消息。这个环境是 AWS Aurora，默认情况下，非管理员开发人员被授予只读权限。因此，他们的服务器连接被设置为使用 Aurora 的只读节点（-ro-位于连接 url 中），该节点必须将连接置于只读状态。使用相同的用户对写入节点运行相同的功能有效。
Seems like a good use case for table variables like SQL Server has! Or, at least, AWS should modify their flow to allow temp tables to be created and dropped on read nodes.
对于像 SQL Server 这样的表变量来说，这似乎是一个很好的用例！或者，至少，AWS 应该修改其流程以允许在读取节点上创建和删除临时表。
