There are a lot of things that are understandably forbidden in a
read-only transaction, but one would not expect SELECT to be among
them. And yet, one can get the system to complain about precisely
that:
rhaas=# create table rules_src(f1 int, f2 int);
ERROR: relation "rules_src" already exists
rhaas=# create table rules_log(f1 int, f2 int, tag text);
ERROR: relation "rules_log" already exists
rhaas=# insert into rules_src values(1,2), (11,12);
INSERT 0 2
rhaas=# create rule r2 as on update to rules_src do also
rhaas-# values(old.*, 'old'), (new.*, 'new');
ERROR: rule "r2" for relation "rules_src" already exists
rhaas=# begin transaction read only;
BEGIN
rhaas=# update rules_src set f2 = f2 / 10;
ERROR: cannot execute SELECT in a read-only transaction
It sees fair for this to fail; I am after all attempting an update
inside of a read-only transaction. But it is mighty strange to
complain about SELECT, since (1) the example contains exactly 0
instances of the keyword SELECT and (2) SELECT is a read-only
operation. Changing "do also" to "do instead" produces the same
failure. This seems to be the result of this code in
ExecCheckXactReadOnly:
if ((rte->requiredPerms & (~ACL_SELECT)) == 0) continue;
...
PreventCommandIfReadOnly(CreateCommandTag((Node *) plannedstmt));
There's nothing obviously stupid about that, but the results in this
case don't make much sense.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company