Thread: pg_upgrade ?deficiency

pg_upgrade ?deficiency

From
Karsten Hilbert
Date:
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


Re: pg_upgrade ?deficiency

From
Bruce Momjian
Date:
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. +


Re: pg_upgrade ?deficiency

From
"Karsten Hilbert"
Date:
> 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


Re: pg_upgrade ?deficiency

From
Albe Laurenz
Date:
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

Re: pg_upgrade ?deficiency

From
"Karsten Hilbert"
Date:
> 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


Re: pg_upgrade ?deficiency

From
Bruce Momjian
Date:
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. +


Re: pg_upgrade ?deficiency

From
"Karsten Hilbert"
Date:
> 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


Re: pg_upgrade ?deficiency

From
Bruce Momjian
Date:
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. +


Re: pg_upgrade ?deficiency

From
Kevin Grittner
Date:
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


Re: pg_upgrade ?deficiency

From
Karsten Hilbert
Date:
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


Re: pg_upgrade ?deficiency

From
Bruce Momjian
Date:
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. +


Re: pg_upgrade ?deficiency

From
Tom Lane
Date:
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


Re: pg_upgrade ?deficiency

From
"Karsten Hilbert"
Date:
> 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


Re: pg_upgrade ?deficiency

From
Bruce Momjian
Date:
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. +