On Thu, Nov 21, 2013 at 06:22:50AM -0800, Kevin Grittner wrote: > > 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. OK, this is good information to hear. > > I believe a pg_dumpall restore might fail in the same way. > > Then it should also be fixed. Yes, that is easy to do. > > 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. Not sure about backpatching. default_transaction_read_only has been around since 7.4. Setting it to true would cause pg_dump to fail unless you changed the database setting, and pg_dumpall would fail completely as there is no way to turn off the database setting. The problem is that I don't remember any report of this failing in pg_dump, pg_dumpall, or pg_upgrade, so saying it is a major issue is hard to accept. However, looking forward, I think we should add it to pg_dump (and hence pg_dumpall), and we should fix pg_upgrade so it is more reliable. I am thinking we should either document in pg_upgrade the use of PGOPTIONS to avoid this issue, or have pg_upgrade append to PGOPTIONS in its environment to use some of pg_dump's resets, and that will be passed to libpq connections, psql, and all the utilities pg_upgrade calls. -- Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
