Re: restrict global access to be readonly

>‍Jim Nasby writes:
> On 2/14/15 3:14 PM, Robert Haas wrote:
>> Although I like the idea, it's not clear to me how to implement it.

> Throw an error in AssignTransactionId/GetNewTransactionId?

>A whole lot depends on what you choose to mean by "read only". If it
>‍means the same thing as "all transactions are READ ONLY", that would be
>‍useful for some purposes, and it would have the advantage of having a
>‍visible relationship to a long-established feature with the same name.
>‍If you want it to mean "no writes to disk at all", that's something
>‍totally different, and possibly not all that useful (eg, do you really
>‍want to make sorts fail if they'd spill to disk? How about hint bit
>‍updates?). Jim's suggestion above would be somewhere in the middle,
>‍as it would successfully block use of temp tables but not eg. VACUUM.
>‍Another possibility that would be attractive for replication-related
>‍use-cases would be "nothing that generates WAL thank you very much".

>‍I'm inclined to think that we should agree on the desired semantics
>‍before jumping to implementation.

>‍regards, tom lane

The first choice Tom pointed makes sense to me: adding this as eqivalent to setting all subsequent transactions as read only. It is useful enough in the scenarios where disk limit for the instance is reached, we want to block all write access(this limit is typically soft limit and vacuum logs or sort spills could be permitted).

I previously thought of the choice of "not generating any WAL" semantics, but now doubt if thats practically useful. We are forced to restart the old master with recovery mode during switching roles of master-slave, which would make it into the state of not generating any WAL.

And for logical replication, seems setting transactions as readonly could do the job to avoid logs to be shipped to slave.

One other thing to consider is the user to be blocked. I expect this command to prevent write access even for the superusers, since there may be other internal apps that connect as superuser and do writes, they are expected to be blocked too. And sometime we may use this command to avoid any unexpected write operation.

Last thing is when the command returns. I expected it to return immediately and not waiting for existing active transactions to finish. This is to avoid existing long running transactions to block it and let the user to decide whether to wait or kill existing transactions.