Allowing DML RULEs that produce Read Only actions during RO xacts
I would like to allow RULEs ON INSERT, ON UPDATE and ON DELETE during
read only transactions iff they generate only SELECT statements that act
INSTEAD OF the actual event.
CREATE RULE foorah
AS ON INSERT TO foo
DO INSTEAD SELECT remote_insert(NEW.col1, NEW.col2, ...);
The above rule is currently disallowed during READ ONLY transactions,
even though the write action is re-written into a read-only action.
I have a small patch that allows this, attached here with test cases.
This would be a small, but useful additional feature for Hot Standby,
since it would allow INSERT, UPDATE, DELETE statements to be re-routed,
for various applications.
--
Simon Riggs www.2ndQuadrant.com
Attachments:
ro_dml_rules.v1.patchtext/x-patch; charset=UTF-8; name=ro_dml_rules.v1.patchDownload
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 1383123..0210d35 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -581,6 +581,14 @@ ExecCheckXactReadOnly(PlannedStmt *plannedstmt)
if (plannedstmt->intoClause != NULL)
goto fail;
+ /*
+ * If we're running a SELECT, allow it. This ensures that a
+ * write rule such as ON INSERT DO SELECT can be executed in
+ * a read-only session.
+ */
+ if (plannedstmt->commandType == CMD_SELECT)
+ return;
+
/* Fail if write permissions are requested on any non-temp table */
foreach(l, plannedstmt->rtable)
{
diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out
index fc98f01..f9eda6b 100644
--- a/src/test/regress/expected/transactions.out
+++ b/src/test/regress/expected/transactions.out
@@ -65,6 +65,24 @@ SELECT * FROM writetest, temptest; -- ok
CREATE TABLE test AS SELECT * FROM writetest; -- fail
ERROR: transaction is read-only
+BEGIN;
+SET transaction_read_only = false;
+CREATE RULE write2read_rule AS ON INSERT TO writetest DO INSTEAD SELECT new.a;
+COMMIT;
+BEGIN;
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ on
+(1 row)
+
+INSERT INTO writetest VALUES (1); -- ok
+ a
+---
+ 1
+(1 row)
+
+COMMIT;
START TRANSACTION READ WRITE;
DROP TABLE writetest; -- ok
COMMIT;
diff --git a/src/test/regress/sql/transactions.sql b/src/test/regress/sql/transactions.sql
index c670ae1..12a03fa 100644
--- a/src/test/regress/sql/transactions.sql
+++ b/src/test/regress/sql/transactions.sql
@@ -51,6 +51,15 @@ EXECUTE test; -- fail
SELECT * FROM writetest, temptest; -- ok
CREATE TABLE test AS SELECT * FROM writetest; -- fail
+BEGIN;
+SET transaction_read_only = false;
+CREATE RULE write2read_rule AS ON INSERT TO writetest DO INSTEAD SELECT new.a;
+COMMIT;
+BEGIN;
+SHOW transaction_read_only;
+INSERT INTO writetest VALUES (1); -- ok
+COMMIT;
+
START TRANSACTION READ WRITE;
DROP TABLE writetest; -- ok
COMMIT;
Simon Riggs <simon@2ndQuadrant.com> writes:
I would like to allow RULEs ON INSERT, ON UPDATE and ON DELETE during
read only transactions iff they generate only SELECT statements that act
INSTEAD OF the actual event.
I don't actually believe there is any use case for such a thing.
This would be a small, but useful additional feature for Hot Standby,
since it would allow INSERT, UPDATE, DELETE statements to be re-routed,
for various applications.
How would you "reroute" them without a non-read-only operation happening
somewhere along the line?
+ /* + * If we're running a SELECT, allow it. This ensures that a + * write rule such as ON INSERT DO SELECT can be executed in + * a read-only session. + */ + if (plannedstmt->commandType == CMD_SELECT) + return;
This will fail, very nastily, in writable-CTE cases.
regards, tom lane
On Sun, 2009-12-06 at 10:26 -0500, Tom Lane wrote:
Simon Riggs <simon@2ndQuadrant.com> writes:
I would like to allow RULEs ON INSERT, ON UPDATE and ON DELETE during
read only transactions iff they generate only SELECT statements that act
INSTEAD OF the actual event.I don't actually believe there is any use case for such a thing.
This would be a small, but useful additional feature for Hot Standby,
since it would allow INSERT, UPDATE, DELETE statements to be re-routed,
for various applications.How would you "reroute" them without a non-read-only operation happening
somewhere along the line?
I showed how in my example. The non-read-only operation happens on a
remote server.
If there are additional technical reasons why not, that's fine.
--
Simon Riggs www.2ndQuadrant.com
Simon Riggs <simon@2ndQuadrant.com> writes:
On Sun, 2009-12-06 at 10:26 -0500, Tom Lane wrote:
How would you "reroute" them without a non-read-only operation happening
somewhere along the line?
I showed how in my example. The non-read-only operation happens on a
remote server.
That seems awfully dubious from a transactional-safety point of view.
regards, tom lane
On Sun, 2009-12-06 at 10:26 -0500, Tom Lane wrote:
+ /* + * If we're running a SELECT, allow it. This ensures that a + * write rule such as ON INSERT DO SELECT can be executed in + * a read-only session. + */ + if (plannedstmt->commandType == CMD_SELECT) + return;This will fail, very nastily, in writable-CTE cases.
If the feature is not able to be added easily, then I'm not interested
either. It's a nice-to-have and I have no time for anything more, so if
it gives problems in other areas, I would not pursue further.
One question: would a writable-CTE be running in a read-only xact?
--
Simon Riggs www.2ndQuadrant.com