Re: restrict global access to be readonly

On Tue, Feb 17, 2015 at 4:40 AM, happy times <guangzhouzhang(at)qq(dot)com> wrote:
> 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.

The use cases I can think of are:

- Opening a possibly-damaged database strictly read-only so you can
inspect it without risking further damage; or forcing a damaged
database that is already up and running to go read-only to prevent
further damage. In this case, you'd want to prohibit all writes to
the data files, even hint bit changes, but the use of temporary files
for sorts or hashes would be fine.

- Forcing a master into a read-only state in preparation for a
controlled switchover. If you can completely stop WAL generation on
the master, replay all WAL generated on the master prior to the
switchover on the standby, and then switch over, you could make the
old master a slave of the new master. I think the requirements here
are similar to the previous case. I'm not 100% sure that we need to
prevent hint bits getting set in this case, but it probably wouldn't
hurt. Temp file writes are, again, OK.

- Taking a copy of the database for backup purposes. Same thing again.

- Accessing a cluster stored on a read-only medium, like a CD or DVD.
In this case, even temporary file writes are no good.

- Your proposed use case of preventing the disk from being filled up
is a little different. There's no real problem if the data files fill
up the disk; at a certain point, users will get errors, but forcing
the database read only is going to do that anyway (and sooner).
There's a big problem if the xlog partition fills up, though. You
could want a few different things here depending on the details of
your use case, but preventing all WAL generation is one of them.

Based on the above, I'm inclined to think that making the system read
only should (1) prevent all WAL generation and (2) prevent all data
file modifications, but (3) still allow the use of temporary files.

--

It's also worth taking a look at what other systems support. SQL
server support something like this feature using this (ugly) syntax:

ALTER DATABASE [whatever] SET READ_ONLY WITH NO_WAIT

I'm not sure what the semantics are, exactly.

In Oracle, you can open a database read-only:

ALTER DATABASE whatever OPEN READ ONLY;

It's not clear to me whether you can use this to force an
already-read-write database back to read only mode. Oracle also lets
you make a tablespace read-only if there are no open transactions,
running hot backups, etc anywhere in the system. That syntax is just:

ALTER TABLESPACE whatever READ ONLY;

That forbids all future data file modifications.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company