On Tue, Feb 17, 2015 at 4:40 AM, happy times <guangzhouzh...@qq.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers