Thread: RULEs and Read Only transactions

RULEs and Read Only transactions

From
Simon Riggs
Date:
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