From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
Cc: | Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>, pgsql-general <pgsql-general(at)postgresql(dot)org>, "Hilbert, Sebastian" <Sebastian(dot)Hilbert(at)gmx(dot)net> |
Subject: | Re: pg_upgrade ?deficiency |
Date: | 2013-11-22 18:32:30 |
Message-ID: | 20131122183230.GA23961@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2013-11-22 18:35:21 | Re: [GENERAL] pg_upgrade ?deficiency |
Previous Message | mrprice22 | 2013-11-22 18:25:04 | Solution for Synonyms |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2013-11-22 18:35:21 | Re: [GENERAL] pg_upgrade ?deficiency |
Previous Message | Bruce Momjian | 2013-11-22 18:19:55 | Re: Suggestion: Issue warning when calling SET TRANSACTION outside transaction block |