Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4

I've tried several times to upgrade a test database (with real data,
~500 GB) from 9.1 to 9.2 using pg_upgrade and every time it fails with
the same error. I've tried a few different options to pg_upgrade but
always the same result. Nothing really useful has turned up in
Google. Any thoughts? Complete output is below:

-bash-4.1$ time /usr/pgsql-9.2/bin/pg_upgrade -b /usr/pgsql-9.1/bin/
-B /usr/pgsql-9.2/bin/ -d /var/lib/pgsql/9.1/data/ -D
/var/lib/pgsql/9.2/data/ -P 50433 --link
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating catalog dump ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster ok
Removing support functions from new cluster ok
Adding ".old" suffix to old global/pg_control ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/pgsql/9.1/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Linking user relation files
/var/lib/pgsql/9.1/data/base/16406/3016054
Mismatch of relation OID in database "dbname": old OID 2938685, new OID 299721
Failure, exiting

real 12m31.600s
user 1m11.594s
sys 1m2.519s

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Evan D. Hoffman
> Sent: Wednesday, May 08, 2013 2:27 PM
> To: Postgresql Mailing List
> Subject: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9
> to 9.2.4
>
> I've tried several times to upgrade a test database (with real data,
> ~500 GB) from 9.1 to 9.2 using pg_upgrade and every time it fails with
> the same error. I've tried a few different options to pg_upgrade but
> always the same result. Nothing really useful has turned up in Google.
> Any thoughts? Complete output is below:
>
>
>
> Linking user relation files
> /var/lib/pgsql/9.1/data/base/16406/3016054
> Mismatch of relation OID in database "dbname": old OID 2938685, new OID
> 299721 Failure, exiting
>
>

Is it always the same file, same OIDs (old/new)?
If it's the same file, did you try to find out what relation it belongs to?

Igor Neyman

> -----Original Message-----
> From: Evan D. Hoffman [mailto:evandhoffman(at)gmail(dot)com]
> Sent: Wednesday, May 08, 2013 3:35 PM
> To: Igor Neyman
> Subject: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" -
> 9.1.9 to 9.2.4
>
> Looks like it IS the same OID every time, referencing an index. I
> already reindexed the entire DB in case it was some problem with a
> corrupt index. Here's the index info, if it's of any use.
>
>
> Interestingly, if I query which that relation's in, it's not the one
> that it complained about:
>
> db=# select pg_relation_filepath(2938685); pg_relation_filepath
> ----------------------
> base/16407/21446253
> (1 row)
>
> db=#
>
> (The file referenced in the error was
> /var/lib/pgsql/9.1/data/base/16406/3016054)
>
> On Wed, May 8, 2013 at 2:35 PM, Igor Neyman <ineyman(at)perceptron(dot)com>
> wrote:
> >
> >> -----Original Message-----
> >> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> >> owner(at)postgresql(dot)org] On Behalf Of Evan D. Hoffman
> >> Sent: Wednesday, May 08, 2013 2:27 PM
> >> To: Postgresql Mailing List
> >> Subject: [GENERAL] pg_upgrade fails, "mismatch of relation OID" -
> >> 9.1.9 to 9.2.4
> >>
> >>
> >> Linking user relation files
> >> /var/lib/pgsql/9.1/data/base/16406/3016054
> >> Mismatch of relation OID in database "dbname": old OID 2938685, new
> >> OID
> >> 299721 Failure, exiting
> >>
> >>
> >
> > Is it always the same file, same OIDs (old/new)?
> > If it's the same file, did you try to find out what relation it
> belongs to?
> >
> > Igor Neyman
> >

Is it the same file though?
And, if it is what do you get when you run:

Select relname from pg_class where relfilenode = 3016054::oid;

Please, reply to the list ("reply to all"), so that other people who may have better ideas/solutions for could see it.

Igor Neyman

Well, each time it fails it refers to the file
"/var/lib/pgsql/9.1/data/base/16406/3016054", but that's not the file
associated with OID 2938685.

Here's the output of that query:

db=# Select relname from pg_class where relfilenode = 3016054::oid;
relname
---------
(0 rows)

db=#

On Wed, May 8, 2013 at 4:12 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
>
>
>> -----Original Message-----
>> From: Evan D. Hoffman [mailto:evandhoffman(at)gmail(dot)com]
>> Sent: Wednesday, May 08, 2013 3:35 PM
>> To: Igor Neyman
>> Subject: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" -
>> 9.1.9 to 9.2.4
>>
>> Looks like it IS the same OID every time, referencing an index. I
>> already reindexed the entire DB in case it was some problem with a
>> corrupt index. Here's the index info, if it's of any use.
>>
>>
>> Interestingly, if I query which that relation's in, it's not the one
>> that it complained about:
>>
>> db=# select pg_relation_filepath(2938685); pg_relation_filepath
>> ----------------------
>> base/16407/21446253
>> (1 row)
>>
>> db=#
>>
>> (The file referenced in the error was
>> /var/lib/pgsql/9.1/data/base/16406/3016054)
>>
>> On Wed, May 8, 2013 at 2:35 PM, Igor Neyman <ineyman(at)perceptron(dot)com>
>> wrote:
>> >
>> >> -----Original Message-----
>> >> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
>> >> owner(at)postgresql(dot)org] On Behalf Of Evan D. Hoffman
>> >> Sent: Wednesday, May 08, 2013 2:27 PM
>> >> To: Postgresql Mailing List
>> >> Subject: [GENERAL] pg_upgrade fails, "mismatch of relation OID" -
>> >> 9.1.9 to 9.2.4
>> >>
>> >>
>> >> Linking user relation files
>> >> /var/lib/pgsql/9.1/data/base/16406/3016054
>> >> Mismatch of relation OID in database "dbname": old OID 2938685, new
>> >> OID
>> >> 299721 Failure, exiting
>> >>
>> >>
>> >
>> > Is it always the same file, same OIDs (old/new)?
>> > If it's the same file, did you try to find out what relation it
>> belongs to?
>> >
>> > Igor Neyman
>> >
>
> Is it the same file though?
> And, if it is what do you get when you run:
>
> Select relname from pg_class where relfilenode = 3016054::oid;
>
> Please, reply to the list ("reply to all"), so that other people who may have better ideas/solutions for could see it.
>
> Igor Neyman

"Evan D. Hoffman" <evandhoffman(at)gmail(dot)com> writes:
> (The file referenced in the error was
> /var/lib/pgsql/9.1/data/base/16406/3016054)

I'm not sure about how pg_upgrade manages its output, but it seems
entirely possible that that was the last file successfully transferred,
not the one the error occurred on.

> Looks like it IS the same OID every time, referencing an index.

What index exactly? Anything different about that index (or its
table) from others in the database?

regards, tom lane

> -----Original Message-----
> From: Evan D. Hoffman [mailto:evandhoffman(at)gmail(dot)com]
> Sent: Wednesday, May 08, 2013 4:22 PM
> To: Igor Neyman
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" -
> 9.1.9 to 9.2.4
>
> Well, each time it fails it refers to the file
> "/var/lib/pgsql/9.1/data/base/16406/3016054", but that's not the file
> associated with OID 2938685.
>
> Here's the output of that query:
>
> db=# Select relname from pg_class where relfilenode = 3016054::oid;
> relname
> ---------
> (0 rows)
>
> db=#
>
>

And that is before running pg_upgrade, right?

Seems like some kind of pg_catalog corruption.
I guess, Bruce Momjian would know better, what's going on here.

Igor Neyman

On Wed, May 8, 2013 at 02:27:18PM -0400, Evan D. Hoffman wrote:
> If you want to start the old cluster, you will need to remove
> the ".old" suffix from /var/lib/pgsql/9.1/data/global/pg_control.old.
> Because "link" mode was used, the old cluster cannot be safely
> started once the new cluster has been started.
>
> Linking user relation files
> /var/lib/pgsql/9.1/data/base/16406/3016054
> Mismatch of relation OID in database "dbname": old OID 2938685, new OID 299721
> Failure, exiting

[ Moved to hackers ]

OK, that is odd. We preserve old/new OIDs, (not relfilenode, as someone
suggested in this thread); FYI:

* FYI, while pg_class.oid and pg_class.relfilenode are initially the same
* in a cluster, but they can diverge due to CLUSTER, REINDEX, or VACUUM
* FULL. The new cluster will have matching pg_class.oid and
* pg_class.relfilenode values and be based on the old oid value. This can
* cause the old and new pg_class.relfilenode values to differ. In summary,
* old and new pg_class.oid and new pg_class.relfilenode will have the
* same value, and old pg_class.relfilenode might differ.

The problem reported is that pg_dump was not able to preserve the
old/new oids between clusters. Can you get the answer for this query on
the old cluster:

SELECT relname from pg_class where oid = 2938685;

and on the new cluster, assuming you used 'copy' mode so you can start
the old/new clusters indepdendently:

SELECT relname from pg_class where oid = 299721;

I think we will find that there is something in pg_dump related to this
table that isn't preserving the oids.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

From the 9.1 cluster (port 5432):

db=# SELECT relname, relfilenode, relkind from pg_class where oid = 2938685;
relname | relfilenode | relkind
-----------------------+-------------+---------
substitutionlist_pkey | 21446253 | i
(1 row)

db=#

From the 9.2 cluster (port 5433):

db=# SELECT relname from pg_class where oid = 299721;
relname
---------
(0 rows)

db=#

Assuming the relfilenode would be the filename on disk, it exists in
the 9.1 DB but not in the 9.2:

[root(at)dev-db2 16407]# ls -lh /var/lib/pgsql/9.1/data/base/16407/21446253
-rw------- 1 postgres postgres 16K May 7 12:04
/var/lib/pgsql/9.1/data/base/16407/21446253
[root(at)dev-db2 16407]# ls -lh /var/lib/pgsql/9.2/data/base/16407/21446253
ls: cannot access /var/lib/pgsql/9.2/data/base/16407/21446253: No such
file or directory
[root(at)dev-db2 16407]#

On Wed, May 8, 2013 at 5:35 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Wed, May 8, 2013 at 02:27:18PM -0400, Evan D. Hoffman wrote:
>> If you want to start the old cluster, you will need to remove
>> the ".old" suffix from /var/lib/pgsql/9.1/data/global/pg_control.old.
>> Because "link" mode was used, the old cluster cannot be safely
>> started once the new cluster has been started.
>>
>> Linking user relation files
>> /var/lib/pgsql/9.1/data/base/16406/3016054
>> Mismatch of relation OID in database "dbname": old OID 2938685, new OID 299721
>> Failure, exiting
>
> [ Moved to hackers ]
>
> OK, that is odd. We preserve old/new OIDs, (not relfilenode, as someone
> suggested in this thread); FYI:
>
> * FYI, while pg_class.oid and pg_class.relfilenode are initially the same
> * in a cluster, but they can diverge due to CLUSTER, REINDEX, or VACUUM
> * FULL. The new cluster will have matching pg_class.oid and
> * pg_class.relfilenode values and be based on the old oid value. This can
> * cause the old and new pg_class.relfilenode values to differ. In summary,
> * old and new pg_class.oid and new pg_class.relfilenode will have the
> * same value, and old pg_class.relfilenode might differ.
>
> The problem reported is that pg_dump was not able to preserve the
> old/new oids between clusters. Can you get the answer for this query on
> the old cluster:
>
> SELECT relname from pg_class where oid = 2938685;
>
> and on the new cluster, assuming you used 'copy' mode so you can start
> the old/new clusters indepdendently:
>
> SELECT relname from pg_class where oid = 299721;
>
> I think we will find that there is something in pg_dump related to this
> table that isn't preserving the oids.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +

On Thu, May 9, 2013 at 10:20:12AM -0400, Evan D. Hoffman wrote:
> >From the 9.1 cluster (port 5432):
>
>
> db=# SELECT relname, relfilenode, relkind from pg_class where oid = 2938685;
> relname | relfilenode | relkind
> -----------------------+-------------+---------
> substitutionlist_pkey | 21446253 | i
> (1 row)
>
> db=#

OK, so it is an index, interesting.

> >From the 9.2 cluster (port 5433):
>
> db=# SELECT relname from pg_class where oid = 299721;
> relname
> ---------
> (0 rows)

Is it possible that you mis-copied the "new" OID from the error message?
It was at the end of the line. If so, could you get the right number?
The fact that old and new start with "29" but there are a different
number of digits in each number suggests it might be the wrong number.

> >> Linking user relation files
> >> /var/lib/pgsql/9.1/data/base/16406/3016054
> >> Mismatch of relation OID in database "dbname": old OID 2938685, new OID 299721
> >> Failure, exiting

If that is the right number, I am confused because pg_upgrade thinks
something has that oid in pg_class in your new cluster. It might help
for you to look for that number in the pg_upgrade logs, and you might
need to run a query from those logs to see where that number is coming
from.

> Assuming the relfilenode would be the filename on disk, it exists in
> the 9.1 DB but not in the 9.2:
>
> [root(at)dev-db2 16407]# ls -lh /var/lib/pgsql/9.1/data/base/16407/21446253
> -rw------- 1 postgres postgres 16K May 7 12:04
> /var/lib/pgsql/9.1/data/base/16407/21446253
> [root(at)dev-db2 16407]# ls -lh /var/lib/pgsql/9.2/data/base/16407/21446253
> ls: cannot access /var/lib/pgsql/9.2/data/base/16407/21446253: No such
> file or directory
> [root(at)dev-db2 16407]#

Relfilenodes are not preserved, so I would not be surprised to see no
match in the new cluster.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

I just did the whole process over from the beginning. here's the full
output:

-bash-4.1$ date ; time /usr/pgsql-9.2/bin/pg_upgrade -b /usr/pgsql-9.1/bin/
-B /usr/pgsql-9.2/bin/ -d /var/lib/pgsql/9.1/data/ -D
/var/lib/pgsql/9.2/data/ -p 50432 -P 50433 ; date
Thu May 9 14:31:07 EDT 2013
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating catalog dump ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster ok
Removing support functions from new cluster ok
Copying user relation files
/var/lib/pgsql/9.1/data/base/16406/3016054
Mismatch of relation OID in database "db": old OID 2938685, new OID 299749
Failure, exiting

real 16m17.924s
user 1m34.334s
sys 1m27.519s
Thu May 9 14:47:25 EDT 2013

Here's the query of that OID:

db=# SELECT relname, relfilenode, relkind from pg_class where oid = 299749;
relname | relfilenode | relkind
----------------+-------------+---------
pg_toast_17304 | 299749 | t
(1 row)

db=#

On Thu, May 9, 2013 at 10:45 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, May 9, 2013 at 10:20:12AM -0400, Evan D. Hoffman wrote:
> > >From the 9.1 cluster (port 5432):
> >
> >
> > db=# SELECT relname, relfilenode, relkind from pg_class where oid =
> 2938685;
> > relname | relfilenode | relkind
> > -----------------------+-------------+---------
> > substitutionlist_pkey | 21446253 | i
> > (1 row)
> >
> > db=#
>
> OK, so it is an index, interesting.
>
> > >From the 9.2 cluster (port 5433):
> >
> > db=# SELECT relname from pg_class where oid = 299721;
> > relname
> > ---------
> > (0 rows)
>
> Is it possible that you mis-copied the "new" OID from the error message?
> It was at the end of the line. If so, could you get the right number?
> The fact that old and new start with "29" but there are a different
> number of digits in each number suggests it might be the wrong number.
>
> > >> Linking user relation files
> > >> /var/lib/pgsql/9.1/data/base/16406/3016054
> > >> Mismatch of relation OID in database "dbname": old OID 2938685, new
> OID 299721
> > >> Failure, exiting
>
> If that is the right number, I am confused because pg_upgrade thinks
> something has that oid in pg_class in your new cluster. It might help
> for you to look for that number in the pg_upgrade logs, and you might
> need to run a query from those logs to see where that number is coming
> from.
>
> > Assuming the relfilenode would be the filename on disk, it exists in
> > the 9.1 DB but not in the 9.2:
> >
> > [root(at)dev-db2 16407]# ls -lh /var/lib/pgsql/9.1/data/base/16407/21446253
> > -rw------- 1 postgres postgres 16K May 7 12:04
> > /var/lib/pgsql/9.1/data/base/16407/21446253
> > [root(at)dev-db2 16407]# ls -lh /var/lib/pgsql/9.2/data/base/16407/21446253
> > ls: cannot access /var/lib/pgsql/9.2/data/base/16407/21446253: No such
> > file or directory
> > [root(at)dev-db2 16407]#
>
> Relfilenodes are not preserved, so I would not be surprised to see no
> match in the new cluster.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>

On Thu, May 9, 2013 at 03:23:20PM -0400, Evan D. Hoffman wrote:
> I just did the whole process over from the beginning. here's the full output:
>
> Copying user relation files
> /var/lib/pgsql/9.1/data/base/16406/3016054
> Mismatch of relation OID in database "db": old OID 2938685, new OID 299749
> Failure, exiting
>
> real 16m17.924s
> user 1m34.334s
> sys 1m27.519s
> Thu May 9 14:47:25 EDT 2013
>
> Here's the query of that OID:
>
> db=# SELECT relname, relfilenode, relkind from pg_class where oid = 299749;
> relname | relfilenode | relkind
> ----------------+-------------+---------
> pg_toast_17304 | 299749 | t
> (1 row)
>
> db=#

OK, so the old oid matches 'substitutionlist_pkey' and the new oid
matches 'pg_toast_17304'. Is that right? Does 'substitutionlist_pkey'
exist in the new cluster at all? You need to see if 2938685 exists in
the per-database dump file that should exist in the current directory,
and show me the lines matching and the DDL command below that. You can
email me the entire file privately if you want --- there is only DDL in
there, no data (please verify if you are concerned).

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

That's correct. Here's what substitutionlist_pkey looks like in the new
cluster. From this, it looks like it's actually correct (the oid for
substitutionlist_pkey is correct) but pg_upgrade thinks it's wrong and
dies. I'll look for the logs you requested and send them separately

db=# SELECT relname, relfilenode, relkind from pg_class where oid = 299749;
relname | relfilenode | relkind
----------------+-------------+---------
pg_toast_17304 | 299749 | t
(1 row)

db=# select oid, relname, relfilenode, pg_relation_filepath(oid), relkind
from pg_class where relname='substitutionlist_pkey';
oid | relname | relfilenode | pg_relation_filepath |
relkind
---------+-----------------------+-------------+----------------------+---------
2938685 | substitutionlist_pkey | 2938685 | base/16488/2938685 | i
(1 row)

db=# select version();
version

--------------------------------------------------------------------------------------------------------
------
PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-3), 6
4-bit
(1 row)

db=#

On Thu, May 9, 2013 at 3:29 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, May 9, 2013 at 03:23:20PM -0400, Evan D. Hoffman wrote:
> > I just did the whole process over from the beginning. here's the full
> output:
> >
> > Copying user relation files
> > /var/lib/pgsql/9.1/data/base/16406/3016054
> > Mismatch of relation OID in database "db": old OID 2938685, new OID
> 299749
> > Failure, exiting
> >
> > real 16m17.924s
> > user 1m34.334s
> > sys 1m27.519s
> > Thu May 9 14:47:25 EDT 2013
> >
> > Here's the query of that OID:
> >
> > db=# SELECT relname, relfilenode, relkind from pg_class where oid =
> 299749;
> > relname | relfilenode | relkind
> > ----------------+-------------+---------
> > pg_toast_17304 | 299749 | t
> > (1 row)
> >
> > db=#
>
> OK, so the old oid matches 'substitutionlist_pkey' and the new oid
> matches 'pg_toast_17304'. Is that right? Does 'substitutionlist_pkey'
> exist in the new cluster at all? You need to see if 2938685 exists in
> the per-database dump file that should exist in the current directory,
> and show me the lines matching and the DDL command below that. You can
> email me the entire file privately if you want --- there is only DDL in
> there, no data (please verify if you are concerned).
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>

On Thu, May 9, 2013 at 03:52:42PM -0400, Evan D. Hoffman wrote:
> That's correct. Here's what substitutionlist_pkey looks like in the new
> cluster. From this, it looks like it's actually correct (the oid for
> substitutionlist_pkey is correct) but pg_upgrade thinks it's wrong and dies.
> I'll look for the logs you requested and send them separately
>
> db=# SELECT relname, relfilenode, relkind from pg_class where oid = 299749;
> relname | relfilenode | relkind
> ----------------+-------------+---------
> pg_toast_17304 | 299749 | t
> (1 row)
>
> db=# select oid, relname, relfilenode, pg_relation_filepath(oid), relkind from
> pg_class where relname='substitutionlist_pkey';
> oid | relname | relfilenode | pg_relation_filepath | relkind
>
> ---------+-----------------------+-------------+----------------------+---------
> 2938685 | substitutionlist_pkey | 2938685 | base/16488/2938685 | i
> (1 row)
>
> db=# select version();
>>
> PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-3), 6
> 4-bit
> (1 row)

OK, that is very helpful. I am now wondering if the problem is that 9.2
has created a toast table for a 9.1 table that didn't have one. Can you
run this query on 9.2:

SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
WHERE reltoastrelid = 299749;

(I think its oid will be 17304 based on the toast name.) Then, in the
9.1 cluster, using the 'oid' mentioned above, show me:

SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
WHERE oid = 'oid_from_above';

If its 'reltoastrelid' is zero, that means 9.2 has a toast table while
9.1 did not have one, and we then need to find out why. I would need to
see the schema of that table. For TOAST details, see:

http://momjian.us/main/blogs/pgblog/2012.html#January_17_2012
http://momjian.us/main/blogs/pgblog/2012.html#January_19_2012

This is the first time I am seeing this failure so I am having to ask
lots of questions.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Looks like your guess was correct:

[ehoffman(at)dev-db2 ~]$ psql -Upostgres db -p 5433
psql (9.2.4)
Type "help" for help.

db=# SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
db-# WHERE reltoastrelid = 299749;
oid | relname | reltoastrelid | reltoastidxid
-------+-----------+---------------+---------------
17304 | setupinfo | 299749 | 0
(1 row)

db=# \q
[ehoffman(at)dev-db2 ~]$ psql -Upostgres db -p 5432
psql (9.2.4, server 9.1.9)
WARNING: psql version 9.2, server version 9.1.
Some psql features might not work.
Type "help" for help.

db=# SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
db-# WHERE oid = 17304;
oid | relname | reltoastrelid | reltoastidxid
-------+-----------+---------------+---------------
17304 | setupinfo | 0 | 0
(1 row)

db=#

On Thu, May 9, 2013 at 4:15 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
> WHERE oid =
>

"Evan D. Hoffman" <evandhoffman(at)gmail(dot)com> writes:
> Looks like your guess was correct:

Could we see the full schema (eg psql \d+) for setupinfo?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Here it is with the interesting field names mangled for paranoia reasons:

db=# \d+ bpm.setupinfo;
Table
"bpm.setupinfo"
Column | Type |
Modifiers | Storage | Stats target | Description
-----------------------------+------------------------+--------------------------------------+----------+--------------+-------------
id | bigint | not null
| plain | |
clientid | bigint | not null
| plain | |
rxxxxxxxxxxx | character varying(40) |
| extended | |
ryyyyyyyyyyyyy | character varying(40) |
| extended | |
rzzzzzzzzzzzzzzzz | character varying(40) |
| extended | |
fxxxxxxxx | character varying(40) |
| extended | |
fyyyyyyy | character varying(40) |
| extended | |
fzzzzzzzzzz | character varying(40) |
| extended | |
sxxxxxxx | boolean | default false
| plain | |
onholdxxxx | character varying(20) |
| extended | |
wxxxxxxxxxxxxxxxxxxxxxxx | boolean | default false
| plain | |
encryxxxxxxxxxxxxxxxxxxxxx | character varying(100) |
| extended | |
encrypyyyyyyyyyyyyyy | character varying(100) |
| extended | |
cxxxxxxxxxxxxxxx | date |
| plain | |
projxxxxxxxxxxxxxxx | date |
| plain | |
has_existing_dxxxxxxxxxxxx | character varying(10) | default
'UNKNOWN'::character varying | extended | |
dropped_has_existingxxxxxxx | character varying(10) |
| extended | |
Indexes:
"setupinfo_pkey" PRIMARY KEY, btree (id)
"setupinfo_clientid_key" UNIQUE CONSTRAINT, btree (clientid)
Foreign-key constraints:
"setupinfo_clientid_fkey" FOREIGN KEY (clientid) REFERENCES
control.client(id)
Has OIDs: no

db=#

On Thu, May 9, 2013 at 4:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Evan D. Hoffman" <evandhoffman(at)gmail(dot)com> writes:
> > Looks like your guess was correct:
>
> Could we see the full schema (eg psql \d+) for setupinfo?
>
> regards, tom lane
>

On Thu, May 9, 2013 at 04:21:05PM -0400, Evan D. Hoffman wrote:
> Looks like your guess was correct:
>
> [ehoffman(at)dev-db2 ~]$ psql -Upostgres db -p 5433
> psql (9.2.4)
> Type "help" for help.
>
> db=# SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
> db-# WHERE reltoastrelid = 299749;
> oid | relname | reltoastrelid | reltoastidxid
> -------+-----------+---------------+---------------
> 17304 | setupinfo | 299749 | 0
> (1 row)
>
> db=# \q
> [ehoffman(at)dev-db2 ~]$ psql -Upostgres db -p 5432
> psql (9.2.4, server 9.1.9)
> WARNING: psql version 9.2, server version 9.1.
> Some psql features might not work.
> Type "help" for help.
>
> db=# SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
> db-# WHERE oid = 17304;
> oid | relname | reltoastrelid | reltoastidxid
> -------+-----------+---------------+---------------
> 17304 | setupinfo | 0 | 0
> (1 row)

OK, that's progress. Having received the table schema privately via
email, I see several 'character varying(40)' fields in the schema. So
the question is how was this table able to get away without a TOAST
table in 9.1, while 9.2 created one for an empty table? Ideas?

Evan, is there anything unusual about this table or its history?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> OK, that's progress. Having received the table schema privately via
> email, I see several 'character varying(40)' fields in the schema. So
> the question is how was this table able to get away without a TOAST
> table in 9.1, while 9.2 created one for an empty table? Ideas?

AFAICT the needs_toast_table() logic is identical between 9.1 and 9.2,
so it seems like it must have something to do with an odd ALTER TABLE
history in the source database. It's hard to think what, however.

In any case, it seems like pg_upgrade ought to have a strategy for
dealing with tables acquiring toast tables like this, since if we
ever do tweak the needs_toast_table() logic, or for instance do
something like deciding to support 6-byte UTF8 codes, we're going
to face such cases. I dunno exactly how we might deal with it though...

BTW, Evan, which encoding is in use in this DB?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, May 9, 2013 at 05:11:43PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > OK, that's progress. Having received the table schema privately via
> > email, I see several 'character varying(40)' fields in the schema. So
> > the question is how was this table able to get away without a TOAST
> > table in 9.1, while 9.2 created one for an empty table? Ideas?
>
> AFAICT the needs_toast_table() logic is identical between 9.1 and 9.2,
> so it seems like it must have something to do with an odd ALTER TABLE
> history in the source database. It's hard to think what, however.
>
> In any case, it seems like pg_upgrade ought to have a strategy for
> dealing with tables acquiring toast tables like this, since if we
> ever do tweak the needs_toast_table() logic, or for instance do
> something like deciding to support 6-byte UTF8 codes, we're going
> to face such cases. I dunno exactly how we might deal with it though...

Well, pg_upgrade operates in super-paranoid mode, so if we relax this,
it could potentially allow silent upgrade failures. I realize
eventually we will need to deal with this, but I would prefer to delay
that.

Also, I added code in PG 9.1 to allow the old/new clusters to have
identical OID layouts, so this would certainly complicate the code; see
info.c::gen_db_file_maps() for the check that is failing, and you can
see the 1:1 relationship. It was done in this commit:

commit 002c105a0706bd1c1e939fe0f47ecdceeae6c52d
Author: Bruce Momjian <bruce(at)momjian(dot)us>
Date: Sat Jan 8 13:44:44 2011 -0500

In pg_upgrade, remove functions that did sequential array scans looking
up relations, but rather order old/new relations and use the same array
index value for both. This should speed up pg_upgrade for databases
with many relations.

FYI, historically we have fixed TOAST table creation issues in pg_dump.

Evan, is the 9.1 cluster loaded into 9.1 or did you use pg_upgrade
previously to upgrade it _to_ 9.1?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I believe the history of this cluster is that it started on 9.0 and was upgraded to 9.1 via pg_upgrade. The instance I'm working on was created as a streaming replica, then I broke the replication to make it a standalone master specifically for testing pg_upgrade to 9.2.

On May 9, 2013, at 5:29 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, May 9, 2013 at 05:11:43PM -0400, Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>> OK, that's progress. Having received the table schema privately via
>>> email, I see several 'character varying(40)' fields in the schema. So
>>> the question is how was this table able to get away without a TOAST
>>> table in 9.1, while 9.2 created one for an empty table? Ideas?
>>
>> AFAICT the needs_toast_table() logic is identical between 9.1 and 9.2,
>> so it seems like it must have something to do with an odd ALTER TABLE
>> history in the source database. It's hard to think what, however.
>>
>> In any case, it seems like pg_upgrade ought to have a strategy for
>> dealing with tables acquiring toast tables like this, since if we
>> ever do tweak the needs_toast_table() logic, or for instance do
>> something like deciding to support 6-byte UTF8 codes, we're going
>> to face such cases. I dunno exactly how we might deal with it though...
>
> Well, pg_upgrade operates in super-paranoid mode, so if we relax this,
> it could potentially allow silent upgrade failures. I realize
> eventually we will need to deal with this, but I would prefer to delay
> that.
>
> Also, I added code in PG 9.1 to allow the old/new clusters to have
> identical OID layouts, so this would certainly complicate the code; see
> info.c::gen_db_file_maps() for the check that is failing, and you can
> see the 1:1 relationship. It was done in this commit:
>
> commit 002c105a0706bd1c1e939fe0f47ecdceeae6c52d
> Author: Bruce Momjian <bruce(at)momjian(dot)us>
> Date: Sat Jan 8 13:44:44 2011 -0500
>
> In pg_upgrade, remove functions that did sequential array scans looking
> up relations, but rather order old/new relations and use the same array
> index value for both. This should speed up pg_upgrade for databases
> with many relations.
>
> FYI, historically we have fixed TOAST table creation issues in pg_dump.
>
> Evan, is the 9.1 cluster loaded into 9.1 or did you use pg_upgrade
> previously to upgrade it _to_ 9.1?
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, May 9, 2013 at 10:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> In any case, it seems like pg_upgrade ought to have a strategy for
> dealing with tables acquiring toast tables like this,

Acquiring toast tables seems pretty trivial to deal with. *Losing* a
toast table might be a bit more involved...

Neither seems intractable though.

--
greg

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Greg Stark escribió:
> On Thu, May 9, 2013 at 10:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > In any case, it seems like pg_upgrade ought to have a strategy for
> > dealing with tables acquiring toast tables like this,
>
> Acquiring toast tables seems pretty trivial to deal with. *Losing* a
> toast table might be a bit more involved...

pg_upgrade already deals with the new code deciding not to create a
toast table (by forcing it to do so anyway in binary upgrade mode).
It's only the other case that's problematic -- but then AFAICS fixing
that is just a SMOP.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, May 9, 2013 at 06:05:14PM -0400, Alvaro Herrera wrote:
> Greg Stark escribió:
> > On Thu, May 9, 2013 at 10:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > In any case, it seems like pg_upgrade ought to have a strategy for
> > > dealing with tables acquiring toast tables like this,
> >
> > Acquiring toast tables seems pretty trivial to deal with. *Losing* a
> > toast table might be a bit more involved...
>
> pg_upgrade already deals with the new code deciding not to create a
> toast table (by forcing it to do so anyway in binary upgrade mode).

Yes, a good point I had forgotten. postgres --binary-upgrade mode can
force the toast table to be created to match the old cluster; see
toasting.c::create_toast_table():

/*
* Check to see whether the table actually needs a TOAST table.
*
* If an update-in-place toast relfilenode is specified, force toast file
* creation even if it seems not to need one.
*/
if (!needs_toast_table(rel) &&
(!IsBinaryUpgrade ||
!OidIsValid(binary_upgrade_next_toast_pg_class_oid)))
return false;

> It's only the other case that's problematic -- but then AFAICS fixing
> that is just a SMOP.

Yes, it is this opposite case where the _new_ cluster wants a TOAST
table that the old cluster doesn't have, which is what Evan is
reporting.

Evan, have you adjusted the TOAST storage parameters for this table at
all, via ALTER TABLE SET STORAGE?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, May 9, 2013 at 05:41:39PM -0400, Evan D. Hoffman wrote:
> I believe the history of this cluster is that it started on 9.0 and
> was upgraded to 9.1 via pg_upgrade. The instance I'm working on was
> created as a streaming replica, then I broke the replication to make
> it a standalone master specifically for testing pg_upgrade to 9.2.

OK, I did some research on this, using a stripped-down copy of his table
schema:

CREATE TABLE test(
x1 bigint,
x2 bigint,
x3 CHARACTER varying(40),
x4 CHARACTER varying(40),
x5 CHARACTER varying(40),
x6 CHARACTER varying(40),
x7 CHARACTER varying(40),
x8 CHARACTER varying(40),
x9 boolean,
x10 CHARACTER varying(40),
x11 boolean,
x12 CHARACTER varying(100),
x13 CHARACTER varying(100),
x14 DATE,
x15 DATE,
x16 CHARACTER varying(10),
x17 CHARACTER varying(10)
);

Using my default UTF8 encoding, I see a TOAST table created for all
versions of Postgres, 9.0 through 9.3. However, if I create a database
with C locale/Latin1 encoding:

CREATE DATABASE test3 ENCODING 'LATIN1' LC_COLLATE 'C'
LC_CTYPE 'C' TEMPLATE template0;

I then get a table with no TOAST table. Could the encoding of this
database have changed? I know pg_upgrade has always tests to see
old/new clusters had matching "LC_COLLATE" and "LC_CTYPE" match. It is
possible that the encodings were changed, and pg_upgrade didn't test
that? I don't think so because pg_dumpall is the one who creates the
new databases with matching encodings.

Could someone have manually changed the encoding of the 9.1 database in
the system tables? If so, that would cause this problem.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hmm... the database itself predates me, so I can't say for sure what
encoding it was created with, but when I did a "pg_dumpall -s" it
showed every database in the cluster uses "SET client_encoding =
'UTF8';"

On Thu, May 9, 2013 at 7:25 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Thu, May 9, 2013 at 05:41:39PM -0400, Evan D. Hoffman wrote:
>> I believe the history of this cluster is that it started on 9.0 and
>> was upgraded to 9.1 via pg_upgrade. The instance I'm working on was
>> created as a streaming replica, then I broke the replication to make
>> it a standalone master specifically for testing pg_upgrade to 9.2.
>
> OK, I did some research on this, using a stripped-down copy of his table
> schema:
>
> CREATE TABLE test(
> x1 bigint,
> x2 bigint,
> x3 CHARACTER varying(40),
> x4 CHARACTER varying(40),
> x5 CHARACTER varying(40),
> x6 CHARACTER varying(40),
> x7 CHARACTER varying(40),
> x8 CHARACTER varying(40),
> x9 boolean,
> x10 CHARACTER varying(40),
> x11 boolean,
> x12 CHARACTER varying(100),
> x13 CHARACTER varying(100),
> x14 DATE,
> x15 DATE,
> x16 CHARACTER varying(10),
> x17 CHARACTER varying(10)
> );
>
> Using my default UTF8 encoding, I see a TOAST table created for all
> versions of Postgres, 9.0 through 9.3. However, if I create a database
> with C locale/Latin1 encoding:
>
> CREATE DATABASE test3 ENCODING 'LATIN1' LC_COLLATE 'C'
> LC_CTYPE 'C' TEMPLATE template0;
>
> I then get a table with no TOAST table. Could the encoding of this
> database have changed? I know pg_upgrade has always tests to see
> old/new clusters had matching "LC_COLLATE" and "LC_CTYPE" match. It is
> possible that the encodings were changed, and pg_upgrade didn't test
> that? I don't think so because pg_dumpall is the one who creates the
> new databases with matching encodings.
>
> Could someone have manually changed the encoding of the 9.1 database in
> the system tables? If so, that would cause this problem.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, May 9, 2013 at 09:22:55PM -0400, Evan D. Hoffman wrote:
> Hmm... the database itself predates me, so I can't say for sure what
> encoding it was created with, but when I did a "pg_dumpall -s" it
> showed every database in the cluster uses "SET client_encoding =
> 'UTF8';"

OK, that's good to know.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, May 9, 2013 at 06:19:31PM -0400, Bruce Momjian wrote:
> > pg_upgrade already deals with the new code deciding not to create a
> > toast table (by forcing it to do so anyway in binary upgrade mode).
>
> Yes, a good point I had forgotten. postgres --binary-upgrade mode can
> force the toast table to be created to match the old cluster; see
> toasting.c::create_toast_table():
>
> /*
> * Check to see whether the table actually needs a TOAST table.
> *
> * If an update-in-place toast relfilenode is specified, force toast file
> * creation even if it seems not to need one.
> */
> if (!needs_toast_table(rel) &&
> (!IsBinaryUpgrade ||
> !OidIsValid(binary_upgrade_next_toast_pg_class_oid)))
> return false;
>
> > It's only the other case that's problematic -- but then AFAICS fixing
> > that is just a SMOP.
>
> Yes, it is this opposite case where the _new_ cluster wants a TOAST
> table that the old cluster doesn't have, which is what Evan is
> reporting.

So, if we eventually agree we need to be able to _suppress_ creation of
the TOAST table on the new cluster, I propose we do it in a similar way
to how we force TOAST creation, by having pg_dump set a backend variable
that is then tested in the backend to suppress TOAST table creation.

I don't think we know enough about the cause of this pg_upgrade failure
to know if this is necessary.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-05-10 07:25:35 -0400, Bruce Momjian wrote:
> On Thu, May 9, 2013 at 06:19:31PM -0400, Bruce Momjian wrote:
> > > pg_upgrade already deals with the new code deciding not to create a
> > > toast table (by forcing it to do so anyway in binary upgrade mode).
> >
> > Yes, a good point I had forgotten. postgres --binary-upgrade mode can
> > force the toast table to be created to match the old cluster; see
> > toasting.c::create_toast_table():
> >
> > /*
> > * Check to see whether the table actually needs a TOAST table.
> > *
> > * If an update-in-place toast relfilenode is specified, force toast file
> > * creation even if it seems not to need one.
> > */
> > if (!needs_toast_table(rel) &&
> > (!IsBinaryUpgrade ||
> > !OidIsValid(binary_upgrade_next_toast_pg_class_oid)))
> > return false;
> >
> > > It's only the other case that's problematic -- but then AFAICS fixing
> > > that is just a SMOP.
> >
> > Yes, it is this opposite case where the _new_ cluster wants a TOAST
> > table that the old cluster doesn't have, which is what Evan is
> > reporting.
>
> So, if we eventually agree we need to be able to _suppress_ creation of
> the TOAST table on the new cluster, I propose we do it in a similar way
> to how we force TOAST creation, by having pg_dump set a backend variable
> that is then tested in the backend to suppress TOAST table creation.

I don't think disregarding the new clusters ideas about the requirement
of a toast table is a good idea; far too likely to cause problems in the
future.
So if there is a valid case where this can happen - which I am far from
sure from what I skimmed so far - we need a) a way to get a toast oid
that doesn't conflict with any of the oids in the old cluster b)
pg_upgrade then needs to accept that the new cluster might have more
toast rels than the old version.

> I don't think we know enough about the cause of this pg_upgrade failure
> to know if this is necessary.

True.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

If it's of any value, here's the create statements for the table from the
pg_upgrade logs:

--
-- Name: setupinfo; Type: TABLE; Schema: bpm; Owner: postgres; Tablespace:
--

-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('17306'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT binary_upgrade.set_next_array_pg_type_oid('17305'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_class oids
SELECT binary_upgrade.set_next_heap_pg_class_oid('17304'::pg_catalog.oid);

CREATE TABLE setupinfo (
id1 bigint NOT NULL,
cl2 bigint NOT NULL,
re3 character varying(40),
re4 character varying(40),
re5 character varying(40),
ft6 character varying(40),
ft7 character varying(40),
ft8 character varying(40),
sf9 boolean DEFAULT false,
on10 character varying(20),
we11 boolean DEFAULT false,
en12 character varying(100),
en13 character varying(100),
cs14 date,
pr15 date,
"........pg.dropped.16........" INTEGER /* dummy */,
"........pg.dropped.17........" INTEGER /* dummy */,
"........pg.dropped.18........" INTEGER /* dummy */,
"........pg.dropped.19........" INTEGER /* dummy */,
"........pg.dropped.20........" INTEGER /* dummy */,
"........pg.dropped.21........" INTEGER /* dummy */,
"........pg.dropped.22........" INTEGER /* dummy */,
"........pg.dropped.23........" INTEGER /* dummy */,
"........pg.dropped.24........" INTEGER /* dummy */,
"........pg.dropped.25........" INTEGER /* dummy */,
"........pg.dropped.26........" INTEGER /* dummy */,
ha27 character varying(10) DEFAULT 'UNKNOWN'::character varying,
"........pg.dropped.28........" INTEGER /* dummy */,
dr29 character varying(10)
);

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = 1, attalign = 'c', attbyval = false
WHERE attname = '........pg.dropped.16........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.16........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.17........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.17........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.18........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.18........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.19........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.19........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.20........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.20........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.21........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.21........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.22........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.22........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.23........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.23........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.24........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.24........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.25........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.25........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.26........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.26........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = 1, attalign = 'c', attbyval = false
WHERE attname = '........pg.dropped.28........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.28........";

-- For binary upgrade, set heap's relfrozenxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '318630027'
WHERE oid = 'setupinfo'::pg_catalog.regclass;

ALTER TABLE bpm.setupinfo OWNER TO postgres;

SET search_path = analytics, pg_catalog;

--

On Fri, May 10, 2013 at 7:30 AM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:

> On 2013-05-10 07:25:35 -0400, Bruce Momjian wrote:
> > On Thu, May 9, 2013 at 06:19:31PM -0400, Bruce Momjian wrote:
> > > > pg_upgrade already deals with the new code deciding not to create a
> > > > toast table (by forcing it to do so anyway in binary upgrade mode).
> > >
> > > Yes, a good point I had forgotten. postgres --binary-upgrade mode can
> > > force the toast table to be created to match the old cluster; see
> > > toasting.c::create_toast_table():
> > >
> > > /*
> > > * Check to see whether the table actually needs a TOAST table.
> > > *
> > > * If an update-in-place toast relfilenode is specified, force
> toast file
> > > * creation even if it seems not to need one.
> > > */
> > > if (!needs_toast_table(rel) &&
> > > (!IsBinaryUpgrade ||
> > > !OidIsValid(binary_upgrade_next_toast_pg_class_oid)))
> > > return false;
> > >
> > > > It's only the other case that's problematic -- but then AFAICS fixing
> > > > that is just a SMOP.
> > >
> > > Yes, it is this opposite case where the _new_ cluster wants a TOAST
> > > table that the old cluster doesn't have, which is what Evan is
> > > reporting.
> >
> > So, if we eventually agree we need to be able to _suppress_ creation of
> > the TOAST table on the new cluster, I propose we do it in a similar way
> > to how we force TOAST creation, by having pg_dump set a backend variable
> > that is then tested in the backend to suppress TOAST table creation.
>
> I don't think disregarding the new clusters ideas about the requirement
> of a toast table is a good idea; far too likely to cause problems in the
> future.
> So if there is a valid case where this can happen - which I am far from
> sure from what I skimmed so far - we need a) a way to get a toast oid
> that doesn't conflict with any of the oids in the old cluster b)
> pg_upgrade then needs to accept that the new cluster might have more
> toast rels than the old version.
>
> > I don't think we know enough about the cause of this pg_upgrade failure
> > to know if this is necessary.
>
> True.
>
> Greetings,
>
> Andres Freund
>
> --
> Andres Freund http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

On Fri, May 10, 2013 at 12:36:21PM -0400, Evan D. Hoffman wrote:
> "........pg.dropped.16........" INTEGER /* dummy */,
> "........pg.dropped.17........" INTEGER /* dummy */,
> "........pg.dropped.18........" INTEGER /* dummy */,
> "........pg.dropped.19........" INTEGER /* dummy */,
> "........pg.dropped.20........" INTEGER /* dummy */,
> "........pg.dropped.21........" INTEGER /* dummy */,
> "........pg.dropped.22........" INTEGER /* dummy */,
> "........pg.dropped.23........" INTEGER /* dummy */,
> "........pg.dropped.24........" INTEGER /* dummy */,
> "........pg.dropped.25........" INTEGER /* dummy */,
> "........pg.dropped.26........" INTEGER /* dummy */,
> ha27 character varying(10) DEFAULT 'UNKNOWN'::character varying,
> "........pg.dropped.28........" INTEGER /* dummy */,
> dr29 character varying(10)

OK, this verifies that the table had a lot of DDL churn. I have no idea
how to pursue this further because I am unsure how we are going to
replicate the operations performed on this table in the past, as you
mentioned much of this was before your time on the job.

Evan, I suggest you force a toast table on the table by doing:

ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;

Then drop the column. That will create a toast table and will allow
pg_upgrade to succeed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Fri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:
> On Fri, May 10, 2013 at 12:36:21PM -0400, Evan D. Hoffman wrote:
> > "........pg.dropped.16........" INTEGER /* dummy */,
> > "........pg.dropped.17........" INTEGER /* dummy */,
> > "........pg.dropped.18........" INTEGER /* dummy */,
> > "........pg.dropped.19........" INTEGER /* dummy */,
> > "........pg.dropped.20........" INTEGER /* dummy */,
> > "........pg.dropped.21........" INTEGER /* dummy */,
> > "........pg.dropped.22........" INTEGER /* dummy */,
> > "........pg.dropped.23........" INTEGER /* dummy */,
> > "........pg.dropped.24........" INTEGER /* dummy */,
> > "........pg.dropped.25........" INTEGER /* dummy */,
> > "........pg.dropped.26........" INTEGER /* dummy */,
> > ha27 character varying(10) DEFAULT 'UNKNOWN'::character varying,
> > "........pg.dropped.28........" INTEGER /* dummy */,
> > dr29 character varying(10)
>
> OK, this verifies that the table had a lot of DDL churn. I have no idea
> how to pursue this further because I am unsure how we are going to
> replicate the operations performed on this table in the past, as you
> mentioned much of this was before your time on the job.
>
> Evan, I suggest you force a toast table on the table by doing:
>
> ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;
>
> Then drop the column. That will create a toast table and will allow
> pg_upgrade to succeed.

FYI, I did test adding a TEXT column and altering a column to TEXT on
Postgres 9.1, and both created a toast table. I am still have no clues
about what would have caused the missing toast table.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Adding & dropping a column resolved the problem. Currently vacuuming the
new cluster. Thanks for your help everybody!

On Sat, May 11, 2013 at 4:58 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Fri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:
> > On Fri, May 10, 2013 at 12:36:21PM -0400, Evan D. Hoffman wrote:
> > > "........pg.dropped.16........" INTEGER /* dummy */,
> > > "........pg.dropped.17........" INTEGER /* dummy */,
> > > "........pg.dropped.18........" INTEGER /* dummy */,
> > > "........pg.dropped.19........" INTEGER /* dummy */,
> > > "........pg.dropped.20........" INTEGER /* dummy */,
> > > "........pg.dropped.21........" INTEGER /* dummy */,
> > > "........pg.dropped.22........" INTEGER /* dummy */,
> > > "........pg.dropped.23........" INTEGER /* dummy */,
> > > "........pg.dropped.24........" INTEGER /* dummy */,
> > > "........pg.dropped.25........" INTEGER /* dummy */,
> > > "........pg.dropped.26........" INTEGER /* dummy */,
> > > ha27 character varying(10) DEFAULT 'UNKNOWN'::character varying,
> > > "........pg.dropped.28........" INTEGER /* dummy */,
> > > dr29 character varying(10)
> >
> > OK, this verifies that the table had a lot of DDL churn. I have no idea
> > how to pursue this further because I am unsure how we are going to
> > replicate the operations performed on this table in the past, as you
> > mentioned much of this was before your time on the job.
> >
> > Evan, I suggest you force a toast table on the table by doing:
> >
> > ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;
> >
> > Then drop the column. That will create a toast table and will allow
> > pg_upgrade to succeed.
>
> FYI, I did test adding a TEXT column and altering a column to TEXT on
> Postgres 9.1, and both created a toast table. I am still have no clues
> about what would have caused the missing toast table.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>

On 05/11/2013 04:58 PM, Bruce Momjian wrote:
> On Fri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:
>> OK, this verifies that the table had a lot of DDL churn. I have no idea
>> how to pursue this further because I am unsure how we are going to
>> replicate the operations performed on this table in the past, as you
>> mentioned much of this was before your time on the job.
>>
>> Evan, I suggest you force a toast table on the table by doing:
>>
>> ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;
>>
>> Then drop the column. That will create a toast table and will allow
>> pg_upgrade to succeed.
> FYI, I did test adding a TEXT column and altering a column to TEXT on
> Postgres 9.1, and both created a toast table. I am still have no clues
> about what would have caused the missing toast table.
>

I once saw a case where a varchar(x) column was changed to something
larger by manually updating the catalog with an UPDATE statement on
pg_attribute.atttypmod. Everything was fine until they tried pg_upgrade
which failed because the DDL to create the table from pg_dump with the
larger column creates a table that had a toast table but the original
table in the 8.3 cluster did not have a toast table.

Steve

On Sun, May 12, 2013 at 10:43:41PM -0400, Evan D. Hoffman wrote:
> Adding & dropping a column resolved the problem. Currently vacuuming the new
> cluster. Thanks for your help everybody!

Great! Someday we will figure out what caused it.

---------------------------------------------------------------------------

>
>
> On Sat, May 11, 2013 at 4:58 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Fri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:
> > On Fri, May 10, 2013 at 12:36:21PM -0400, Evan D. Hoffman wrote:
> > > "........pg.dropped.16........" INTEGER /* dummy */,
> > > "........pg.dropped.17........" INTEGER /* dummy */,
> > > "........pg.dropped.18........" INTEGER /* dummy */,
> > > "........pg.dropped.19........" INTEGER /* dummy */,
> > > "........pg.dropped.20........" INTEGER /* dummy */,
> > > "........pg.dropped.21........" INTEGER /* dummy */,
> > > "........pg.dropped.22........" INTEGER /* dummy */,
> > > "........pg.dropped.23........" INTEGER /* dummy */,
> > > "........pg.dropped.24........" INTEGER /* dummy */,
> > > "........pg.dropped.25........" INTEGER /* dummy */,
> > > "........pg.dropped.26........" INTEGER /* dummy */,
> > > ha27 character varying(10) DEFAULT 'UNKNOWN'::character varying,
> > > "........pg.dropped.28........" INTEGER /* dummy */,
> > > dr29 character varying(10)
> >
> > OK, this verifies that the table had a lot of DDL churn. I have no idea
> > how to pursue this further because I am unsure how we are going to
> > replicate the operations performed on this table in the past, as you
> > mentioned much of this was before your time on the job.
> >
> > Evan, I suggest you force a toast table on the table by doing:
> >
> > ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;
> >
> > Then drop the column. That will create a toast table and will allow
> > pg_upgrade to succeed.
>
> FYI, I did test adding a TEXT column and altering a column to TEXT on
> Postgres 9.1, and both created a toast table. I am still have no clues
> about what would have caused the missing toast table.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>
>

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, May 13, 2013 at 09:36:51AM -0400, Steve Singer wrote:
> On 05/11/2013 04:58 PM, Bruce Momjian wrote:
> >On Fri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:
> >>OK, this verifies that the table had a lot of DDL churn. I have no idea
> >>how to pursue this further because I am unsure how we are going to
> >>replicate the operations performed on this table in the past, as you
> >>mentioned much of this was before your time on the job.
> >>
> >>Evan, I suggest you force a toast table on the table by doing:
> >>
> >> ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;
> >>
> >>Then drop the column. That will create a toast table and will allow
> >>pg_upgrade to succeed.
> >FYI, I did test adding a TEXT column and altering a column to TEXT on
> >Postgres 9.1, and both created a toast table. I am still have no clues
> >about what would have caused the missing toast table.
> >
>
> I once saw a case where a varchar(x) column was changed to something
> larger by manually updating the catalog with an UPDATE statement on
> pg_attribute.atttypmod. Everything was fine until they tried
> pg_upgrade which failed because the DDL to create the table from
> pg_dump with the larger column creates a table that had a toast
> table but the original table in the 8.3 cluster did not have a toast
> table.

That is a good point. We used to tell users they could manually update
pg_attribute to increase the length of a column --- that obviously will
not work anymore with pg_upgrade, and now that we have an ALTER TABLE
that can handle it without a table rewrite since PG 9.1, there is no
reason for users to need to adjust pg_attribute.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:

> On Fri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:
>
>> On Fri, May 10, 2013 at 12:36:21PM -0400, Evan D. Hoffman wrote:
>> > "........pg.dropped.16........" INTEGER /* dummy */,
>> > "........pg.dropped.17........" INTEGER /* dummy */,
>> > "........pg.dropped.18........" INTEGER /* dummy */,
>> > "........pg.dropped.19........" INTEGER /* dummy */,
>> > "........pg.dropped.20........" INTEGER /* dummy */,
>> > "........pg.dropped.21........" INTEGER /* dummy */,
>> > "........pg.dropped.22........" INTEGER /* dummy */,
>> > "........pg.dropped.23........" INTEGER /* dummy */,
>> > "........pg.dropped.24........" INTEGER /* dummy */,
>> > "........pg.dropped.25........" INTEGER /* dummy */,
>> > "........pg.dropped.26........" INTEGER /* dummy */,
>> > ha27 character varying(10) DEFAULT 'UNKNOWN'::character varying,
>> > "........pg.dropped.28........" INTEGER /* dummy */,
>> > dr29 character varying(10)
>>
>> OK, this verifies that the table had a lot of DDL churn. I have no idea
>> how to pursue this further because I am unsure how we are going to
>> replicate the operations performed on this table in the past, as you
>> mentioned much of this was before your time on the job.
>>
>> Evan, I suggest you force a toast table on the table by doing:
>>
>> ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;
>>
>> Then drop the column. That will create a toast table and will allow
>> pg_upgrade to succeed.
>
> FYI, I did test adding a TEXT column and altering a column to TEXT on
> Postgres 9.1, and both created a toast table. I am still have no clues
> about what would have caused the missing toast table.

Possibly manual catalog updates to change a varchar(N) to text and
whoopsie! That may be one explanation.

> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800