pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.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

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

-----Original Message-----
From: Evan D. Hoffman [mailto:evandhoffman@gmail.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@perceptron.com>
wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.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

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

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@perceptron.com> wrote:

-----Original Message-----
From: Evan D. Hoffman [mailto:evandhoffman@gmail.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@perceptron.com>
wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.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

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

"Evan D. Hoffman" <evandhoffman@gmail.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

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

-----Original Message-----
From: Evan D. Hoffman [mailto:evandhoffman@gmail.com]
Sent: Wednesday, May 08, 2013 4:22 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.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

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

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

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@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@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@dev-db2 16407]#

On Wed, May 8, 2013 at 5:35 PM, Bruce Momjian <bruce@momjian.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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

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@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@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@dev-db2 16407]#

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

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

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@momjian.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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.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@momjian.us> wrote:

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

Looks like your guess was correct:

[ehoffman@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@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@momjian.us> wrote:

"Evan D. Hoffman" <evandhoffman@gmail.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@postgresql.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@sss.pgh.pa.us> wrote:

On Thu, May 9, 2013 at 04:21:05PM -0400, Evan D. Hoffman wrote:

Looks like your guess was correct:

[ehoffman@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@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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

Bruce Momjian <bruce@momjian.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@postgresql.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@momjian.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@momjian.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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.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@momjian.us> wrote:

On Thu, May 9, 2013 at 05:11:43PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.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@momjian.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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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