DML rules don't work during read only transactions. Now that is a real
shame because it would allow some important capabilities when running in
Hot Standby mode.
postgres=# create or replace function foo () returns void
language plpgsql as
$$ begin null; end; $$;
CREATE FUNCTION
postgres=# create or replace rule test1 as on insert to accounts do instead select foo();
CREATE RULE
postgres=# begin transaction read only;
BEGIN
postgres=# insert into accounts values (1,1,1,'a');
ERROR: transaction is read-only
It seems that the command is refused because it requires a write
permission, rather than because it actually does any kind of write
command. (Obviously we need permission to perform the action, that has
already been checked and I'm not suggesting relaxing security).
We run ExecCheckXactReadOnly() during ExecutorStart(). At that point we
already know whether the operation is CMD_SELECT or a writable command,
so we could alternatively check for that rather than the write
permission and it looks like it would be a faster check also. Any
CMD_SELECT that called a function that contained writable commands would
fail by the same route when the executor is called recursively.
Any objection to allowing RULEs like the above example to work correctly
during read only transactions?
-- Simon Riggs www.2ndQuadrant.com
