Bruce Momjian <bruce@xxxxxxxxxx> wrote: > On Wed, Nov 20, 2013 at 02:36:08PM +0100, Karsten Hilbert wrote: >>> Karsten Hilbert wrote: >>>> Let me try to rephrase: >>>> >>>> Fact: pg_upgrade can NOT properly upgrade clusters which >>>> contain databases that are set to >>>> "default_transaction_read_only on" >>>> Question: Is this intended ? >>> >>> I am pretty sure that this is an oversight and hence a bug. >> >> oversight, yes ... I thought as much and was therefore a bit >> cautious of calling it a bug, chose to name it "?deficiency" ;-) > > Well, pg_upgrade can't handle every possible configuration. How > do we even restore into such a database? You marked the database > as read-only, and pg_upgrade is going to honor that and not > modify it. That interpretation makes no sense to me. I know of users who have databases where 90% of their transactions don't modify data, so they set the *default* for transactions to read only, and override that for transactions that are read write. The default is not, and never has been, a restriction on what is allowed -- it is a default that is quite easy to override. If we have tools that don't handle that correctly, I consider that a bug. > I believe a pg_dumpall restore might fail in the same way. Then it should also be fixed. > You need to change the default on the old cluster before > upgrading. It is overly cumbersome to set the > default_transaction_read_only for every database connection Why is this any different from other settings we cover at the front of pg_dump output?: | SET statement_timeout = 0; | SET lock_timeout = 0; | SET client_encoding = 'UTF8'; | SET standard_conforming_strings = on; | SET check_function_bodies = false; | SET client_min_messages = warning; > and there are many other settings that might also cause failures. You mean, like the above? > What you might be able to do is to set PGOPTIONS to "-c > default_transaction_read_only=false" and run pg_upgrade. If more > people report this problem, I could document this work-around. This is most likely to bite those using serializable transactions for data integrity, because declaring transactions read only makes a huge difference in performance in those cases. That is where I have seen people set the default for read only to on; they want to explicitly set it off only when needed. I would be happy to supply a patch to treat default_transaction_read_only the same as statement_timeout or standard_conforming_strings in pg_dump and related utilities. Since it causes backup/restore failure on perfectly valid databases I even think this is a bug which merits back-patching. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general