Thread: pg_upgrade ?deficiency
Hello all,
I am upgrading a 8.4 cluster to 9.1 and am seeing the following:
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS
SELECT c.oid
FROM
pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
LEFT OUTER JOIN pg_catalog.pg_index i ON c.oid = i.indexrelid
WHERE
relkind IN ('r', 'm', 'i', 'S')
AND
i.indisvalid IS DISTINCT FROM false
AND
i.indisready IS DISTINCT FROM false
AND
((n.nspname !~ '^pg_temp_'
AND
n.nspname !~ '^ pg_toast_temp_'
AND
n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast')
AND
c.oid >= 16384
)
OR
(n.nspname = 'pg_catalog'
AND
relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index')
));
ERROR: transaction is read-only
Now, this is quite understandable since one of the databases
is set to
ALTER DATABASE ... SET DEFAULT_TRANSACTION_READ_ONLY TO ON;
However, since the above setting is something which can
be expected every so often in any odd PostgreSQL cluster
(and not some weird coincidence no one really knows how
they got into in the first place) I would think pg_upgrade
really should be able to handle.
Technically that's pretty easy - make sure transactions are
set to readwrite for the pg_upgrade run by any number of
means:
- ALTER DATABASE before/after pg_upgrade
- ALTER USER running the pg_upgrade
- SET TRANSACTION READ WRITE at the appropriate times
- ...
Or at least this limitation of pg_upgrade (requiring
DB write access) should get a mention in the docs and/or
man page.
What is the informed opinion on this ?
Thanks,
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Tue, Nov 19, 2013 at 11:22:47AM +0100, Karsten Hilbert wrote: > ERROR: transaction is read-only > > Now, this is quite understandable since one of the databases > is set to > > ALTER DATABASE ... SET DEFAULT_TRANSACTION_READ_ONLY TO ON; > > However, since the above setting is something which can > be expected every so often in any odd PostgreSQL cluster > (and not some weird coincidence no one really knows how > they got into in the first place) I would think pg_upgrade > really should be able to handle. > > Technically that's pretty easy - make sure transactions are > set to readwrite for the pg_upgrade run by any number of > means: > > - ALTER DATABASE before/after pg_upgrade > - ALTER USER running the pg_upgrade > - SET TRANSACTION READ WRITE at the appropriate times > - ... > > Or at least this limitation of pg_upgrade (requiring > DB write access) should get a mention in the docs and/or > man page. > > What is the informed opinion on this ? I think pg_upgrade did the right thing here by throwing an error. There is no clean way to handle these cases without possibly causing more problems. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
> On Tue, Nov 19, 2013 at 11:22:47AM +0100, Karsten Hilbert wrote:
> > ERROR: transaction is read-only
> >
> > Now, this is quite understandable since one of the databases
> > is set to
> >
> > ALTER DATABASE ... SET DEFAULT_TRANSACTION_READ_ONLY TO ON;
> >
> > However, since the above setting is something which can
> > be expected every so often in any odd PostgreSQL cluster
> > (and not some weird coincidence no one really knows how
> > they got into in the first place) I would think pg_upgrade
> > really should be able to handle.
> >
> > Technically that's pretty easy - make sure transactions are
> > set to readwrite for the pg_upgrade run by any number of
> > means:
> >
> > - ALTER DATABASE before/after pg_upgrade
> > - ALTER USER running the pg_upgrade
> > - SET TRANSACTION READ WRITE at the appropriate times
> > - ...
> >
> > Or at least this limitation of pg_upgrade (requiring
> > DB write access) should get a mention in the docs and/or
> > man page.
> >
> > What is the informed opinion on this ?
>
> I think pg_upgrade did the right thing here by throwing an error. There
> is no clean way to handle these cases without possibly causing more
> problems.
I am not sure this is the ideal way of looking at the problem (for one
thing it wasn't pg_upgrade throwing the error). Maybe I have not clearly
expressed myself.
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 ?
Thanks,
Karsten
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. Yours, Laurenz Albe
> 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" ;-) Karsten
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. I believe a pg_dumpall restore might fail in the same way. 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, and there are many other settings that might also cause failures. If it was a silent failure, I would be more concerned. 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. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
> 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. > > Well, pg_upgrade can't handle every possible configuration. Agreed. That would be a design decision: "no, pg_upgrade will not support upgrading some of your databases, for example those which are set to default_transaction_ready_only=on". If I don't like that, fine, I can go and use other tools or else submit a patch and hope for inclusion or apply a workaround. That's why I tacitly suggested a hint in the docs might help to become aware of the above limitation. Of course, I should submit a patch to the docs just as well. > How do we even restore into such a database? We read the state, remember the state, change the state, restore the data, set the initial state. But you knew that, I assume. > You marked the database as read-only, and pg_upgrade > is going to honor that and not modify it. Oh, I am extremely happy for pg_upgrade to NOT modify ANY of my databases ! All I am wondering is whether it is by design decision (and if so, why) that it cannot transfer some databases from one PG version to another one. I am more than happy if it doesn't modify the databases in the process ;-) > I believe a pg_dumpall restore might fail in the same way. pg_dumpall works but a full pg_restore/psql from that dump likely will not. I haven't tested that yet, though, and I deliberately did not want to raise *that* question just yet... > You need to change the default on the old cluster before upgrading. I know. That wasn't my question though. > It is overly cumbersome to set the default_transaction_read_only for every > database connection, There is no need for that (see above). > and there are many other settings that might also cause failures. If so they warrant documentation as well as they become known. > If it was a silent failure, I would be more concerned. Absolutely, full agreement. > What you might be able to do is to set PGOPTIONS to "-c > default_transaction_read_only=false" and run pg_upgrade. That is a good idea. It might have occurred to me earlier had the pg_upgrade limitation been documented ;-) Thanks for your work on PostgreSQL, Karsten
On Wed, Nov 20, 2013 at 04:07:59PM +0100, Karsten Hilbert 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. > > > > Well, pg_upgrade can't handle every possible configuration. > > Agreed. That would be a design decision: "no, pg_upgrade will > not support upgrading some of your databases, for example those > which are set to default_transaction_ready_only=on". > > If I don't like that, fine, I can go and use other tools or > else submit a patch and hope for inclusion or apply a workaround. > > That's why I tacitly suggested a hint in the docs might > help to become aware of the above limitation. > > Of course, I should submit a patch to the docs just as well. I think the big question is whether a generic mention that there are some database settings, like read-only, that can prevent updates, and you might need to use PGOPTIONS to avoid that. However, you are the first case to report this, so I am hesistant. > > How do we even restore into such a database? > > We read the state, remember the state, change the state, > restore the data, set the initial state. But you knew that, > I assume. Yep. > > You marked the database as read-only, and pg_upgrade > > is going to honor that and not modify it. > > Oh, I am extremely happy for pg_upgrade to NOT modify > ANY of my databases ! All I am wondering is whether > it is by design decision (and if so, why) that it cannot > transfer some databases from one PG version to another > one. I am more than happy if it doesn't modify the > databases in the process ;-) Yes, messing with status can often be problematic. > > What you might be able to do is to set PGOPTIONS to "-c > > default_transaction_read_only=false" and run pg_upgrade. > > That is a good idea. It might have occurred to me earlier > had the pg_upgrade limitation been documented ;-) True. Does anyone else see value in documenting this? I can do the docs. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian <bruce@momjian.us> 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
On Thu, Nov 21, 2013 at 06:22:50AM -0800, Kevin Grittner wrote:
> 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
... (and pg_upgrade failures -- which may internally
just be dump/restore cycles ?) ...
> on perfectly valid databases I even think this is
> a bug which merits back-patching.
Thanks so much, Kevin, for offering to work
on that part. Maybe it's a small thing but
it'll make PostgreSQL once again feel
professionally consistent.
I would have needed to become proficient in C
and get acqainted with the PG source in order
to produce a patch myself.
Thanks,
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian <bruce@momjian.us> writes:
> 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.
No, neither pg_dump nor pg_dumpall would fail. What would fail is
restoring into a database that has this option already set. It's possible
that users of this option haven't noticed it because they never attempted
a restore in such a context.
> 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.
Yeah, it's a minor issue at best, but perhaps worth fixing since
the solution is so easy.
The bigger picture here is that there are lots of ways to break
pg_upgrade via not-sane settings, and there always will be.
I don't think we should try to promise that there won't be.
regards, tom lane
> Bruce Momjian <bruce@momjian.us> writes: > > 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. > > No, neither pg_dump nor pg_dumpall would fail. What would fail is > restoring into a database that has this option already set. It's possible > that users of this option haven't noticed it because they never attempted > a restore in such a context. I was the original poster on -users who raised this issue. Maybe I can clarify somewhat: I have been attempting to upgrade an 8.4 cluster to 9.1 by means of the 9.1 pg_upgrade command. That failed due to one of the databases in the 8.4 cluster being "ALTER DATABASE ... SET DEFAULT_TRANSACTION_READ_ONLY TO ON". Hence my question on that list whether that was to be considered a bug, a deficiency, or an oversight. I knew workarounds quite well but wondered whether that pg_upgrade behaviour was intended to stay that way. I suggested that if it is intended to stay it might benefit from a hint in the documentation. > Yeah, it's a minor issue at best, but perhaps worth fixing since > the solution is so easy. That would be really helpful. > The bigger picture here is that there are lots of ways to break > pg_upgrade via not-sane settings, and there always will be. Would setting default_transaction_read_only to on be considered non-sane ? If so, why ? > I don't think we should try to promise that there won't be. That last assertion is what everyone should certainly be able to agree with ;-) Thanks, Karsten
On Fri, Nov 22, 2013 at 03:13:33PM -0500, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > 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. > > No, neither pg_dump nor pg_dumpall would fail. What would fail is > restoring into a database that has this option already set. It's possible > that users of this option haven't noticed it because they never attempted > a restore in such a context. Well, pg_dumpall is going to restore that setting before putting any data in the database, so it will fail. I have tested that, and also tested that PGOPTIONS fixes it. > > 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. > > Yeah, it's a minor issue at best, but perhaps worth fixing since > the solution is so easy. Yes. > The bigger picture here is that there are lots of ways to break > pg_upgrade via not-sane settings, and there always will be. > I don't think we should try to promise that there won't be. So document PGOPTIONS in pg_upgrade? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +