Using default_transaction_read_only is tricky with Citus on the worker nodes

[onderkalaci]

One use case for default_transaction_read_only is when a node's disk capacity gets below a certain threshold, the superuser can do the following to prevent any further modifications:

ALTER  SYSTEM set default_transaction_read_only TO true;
SELECT pg_reload_conf();

And, the user can still override the decision by:

SET default_transaction_read_only TO false;
-- any command that saves up space
DELETE FROM table;

When a worker node is set with default_transaction_read_only = true, it becomes tricky to modify the node again. The main problem is that the changes on default_transaction_read_only becomes valid after the current transaction finishes. It means, it is not possible to use default_transaction_read_only with SET LOCAL, which Citus knows how to propagate (e.g., Citus cannot propagate SET as there are various difficulties with it).

One alternative for users could be to set default_transaction_read_only on the coordinator whenever any node's disk space gets below a threshold. With that, users can still use the same pattern with Postgres, where they can override this rule by SET command:

SET default_transaction_read_only TO false;
-- any command that saves up space
DELETE FROM table;

[marcocitus]

We should at least propagate SET TRANSACTION along with other SET LOCAL commands.

[onderkalaci]

It turns out that some commands do not honor the GUC:

SET default_transaction_read_only TO true;

-- good ones
update test set a = 1;
ERROR:  cannot execute UPDATE in a read-only transaction

 alter table test add column x int;
ERROR:  cannot execute ALTER TABLE in a read-only transaction

insert into test select i from generate_series(0,1000)i                                              ;
ERROR:  cannot execute INSERT in a read-only transaction;
 insert into test VALUES (1);
ERROR:  cannot execute INSERT in a read-only transaction

-- COPY is allowed!
 copy test from STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1
>> \.
COPY 1
Time: 1190.674 ms (00:01.191)

[serprex]

I was hoping to get around the lack of SET TRANSACTION propagation with SET LOCAL transaction_read_only_mode TO off

citus=> set citus.propagate_set_commands TO 'local';
SET
citus=> begin;
BEGIN
citus=> set local transaction_read_only to off;
SET
citus=> select create_distributed_table('asdf','id');
ERROR:  transaction read-write mode must be set before any query
CONTEXT:  while executing command on private-w0.X0c0CpqozsSZZ56O.marlin-development.com:5432

Still, it'd be nice if at least BEGIN transaction mode was propagated:

citus=> begin read write;
BEGIN
citus=> select create_distributed_table('asdf','id');
ERROR:  cannot execute CREATE TABLE in a read-only transaction
CONTEXT:  while executing command on private-w0.X0c0CpqozsSZZ56O.marlin-development.com:5432

The suggestion to set read only on the coordinator only won't work in MX

[marcocitus]

We should probably merge https://github.com/citusdata/citus/pull/4945

[marcocitus]

Closing since https://github.com/citusdata/citus/pull/4945 was merged