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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers