Thread: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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
andbe 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.relfilenodemight 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. +
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
"Evan D. Hoffman"
Date:
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. +
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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. +
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
"Evan D. Hoffman"
Date:
<div dir="ltr">I just did the whole process over from the beginning. here's the full output:<br /><br /><font face="couriernew, monospace">-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<br /> Thu May 9 14:31:07EDT 2013<br />Performing Consistency Checks<br />-----------------------------<br />Checking current, bin, and datadirectories ok<br />Checking cluster versions ok<br /> Checking databaseuser is a superuser ok<br />Checking for prepared transactions ok<br/>Checking for reg* system OID user data types ok<br />Checking for contrib/isn with bigint-passingmismatch ok<br /> Creating catalog dump ok<br />Checking for presenceof required libraries ok<br />Checking database user is a superuser ok<br />Checkingfor prepared transactions ok<br /><br />If pg_upgrade fails after this point, you mustre-initdb the<br />new cluster before continuing.<br /><br />Performing Upgrade<br />------------------<br />Analyzingall rows in the new cluster ok<br />Freezing all rows on the new cluster ok<br /> Deleting files from new pg_clog ok<br />Copying old pg_clog to new server ok<br />Setting next transaction ID for new cluster ok<br />Resetting WAL archives ok<br /> Setting frozenxid counters in new cluster ok<br />Creatingdatabases in the new cluster ok<br />Adding support functions to new cluster ok<br />Restoring database schema to new cluster ok<br /> Removing support functions from newcluster ok<br />Copying user relation files<br /> /var/lib/pgsql/9.1/data/base/16406/3016054 <br /><font color="#cc0000">Mismatch of relation OID in database "db": old OID 2938685, new OID 299749<br /></font>Failure,exiting<br /><br />real 16m17.924s<br />user 1m34.334s<br />sys 1m27.519s<br />Thu May 9 14:47:25 EDT 2013<br/><br /></font>Here's the query of that OID:<br /><br /><font face="courier new, monospace">db=# SELECT relname, relfilenode,relkind from pg_class where oid = 299749;<br /> relname | relfilenode | relkind <br />----------------+-------------+---------<br/> pg_toast_17304 | 299749 | t<br />(1 row)<br /><br />db=# </font><br/></div><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Thu, May 9, 2013 at 10:45 AM, Bruce Momjian<span dir="ltr"><<a href="mailto:bruce@momjian.us" target="_blank">bruce@momjian.us</a>></span> wrote:<br /><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">OnThu, May 9, 2013 at 10:20:12AM -0400, Evan D. Hoffman wrote:<br /> > >From the 9.1 cluster (port 5432):<br/> ><br /> ><br /> > db=# SELECT relname, relfilenode, relkind from pg_class where oid = 2938685;<br />> relname | relfilenode | relkind<br /> > -----------------------+-------------+---------<br /> > substitutionlist_pkey | 21446253 | i<br /> > (1 row)<br /> ><br /> > db=#<br /><br /></div>OK, so it isan index, interesting.<br /><div class="im"><br /> > >From the 9.2 cluster (port 5433):<br /> ><br /> > db=#SELECT relname from pg_class where oid = 299721;<br /> > relname<br /> > ---------<br /> > (0 rows)<br /><br/></div>Is it possible that you mis-copied the "new" OID from the error message?<br /> It was at the end of the line. If so, could you get the right number?<br /> The fact that old and new start with "29" but there are a different<br/> number of digits in each number suggests it might be the wrong number.<br /><div class="im"><br /> > >>Linking user relation files<br /> > >> /var/lib/pgsql/9.1/data/base/16406/3016054<br /> > >>Mismatch of relation OID in database "dbname": old OID 2938685, new OID 299721<br /> > >> Failure, exiting<br/><br /></div>If that is the right number, I am confused because pg_upgrade thinks<br /> something has that oidin pg_class in your new cluster. It might help<br /> for you to look for that number in the pg_upgrade logs, and youmight<br /> need to run a query from those logs to see where that number is coming<br /> from.<br /><div class="im"><br/> > Assuming the relfilenode would be the filename on disk, it exists in<br /> > the 9.1 DB but notin the 9.2:<br /> ><br /> > [root@dev-db2 16407]# ls -lh /var/lib/pgsql/9.1/data/base/16407/21446253<br /> >-rw------- 1 postgres postgres 16K May 7 12:04<br /> > /var/lib/pgsql/9.1/data/base/16407/21446253<br /> > [root@dev-db216407]# ls -lh /var/lib/pgsql/9.2/data/base/16407/21446253<br /> > ls: cannot access /var/lib/pgsql/9.2/data/base/16407/21446253:No such<br /> > file or directory<br /> > [root@dev-db2 16407]#<br /><br/></div>Relfilenodes are not preserved, so I would not be surprised to see no<br /> match in the new cluster.<br /><divclass="HOEnZb"><div class="h5"><br /> --<br /> Bruce Momjian <<a href="mailto:bruce@momjian.us">bruce@momjian.us</a>> <a href="http://momjian.us" target="_blank">http://momjian.us</a><br/> EnterpriseDB <a href="http://enterprisedb.com" target="_blank">http://enterprisedb.com</a><br/><br /> + It's impossible for everything to be true. +<br /></div></div></blockquote></div><br/></div>
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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. +
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
"Evan D. Hoffman"
Date:
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: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 filesOK, so the old oid matches 'substitutionlist_pkey' and the new oid
> /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=#
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. +
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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_classWHERE 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_2012http://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. +
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
"Evan D. Hoffman"
Date:
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:
SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
WHERE oid =
"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
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
"Evan D. Hoffman"
Date:
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:
"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
Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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. +
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
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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 002c105a0706bd1c1e939fe0f47ecdceeae6c52dAuthor: 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/newrelations and use the same array index value for both. This should speed up pg_upgrade for databases with manyrelations. 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. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
"Evan D. Hoffman"
Date:
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'mworking on was created as a streaming replica, then I broke the replication to make it a standalone master specificallyfor 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. +
On Thu, May 9, 2013 at 10:11 PM, Tom Lane <tgl@sss.pgh.pa.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
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Alvaro Herrera
Date:
Greg Stark escribió: > On Thu, May 9, 2013 at 10:11 PM, Tom Lane <tgl@sss.pgh.pa.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
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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@sss.pgh.pa.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 relfilenodeis 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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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 CHARACTERvarying(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), x14DATE, 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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
"Evan D. Hoffman"
Date:
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@momjian.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@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Andres Freund
Date:
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
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
"Evan D. Hoffman"
Date:
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@2ndquadrant.com> wrote:
I don't think disregarding the new clusters ideas about the requirementOn 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.
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.True.
> I don't think we know enough about the cause of this pg_upgrade failure
> to know if this is necessary.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
"Evan D. Hoffman"
Date:
<div dir="ltr">Adding & dropping a column resolved the problem. Currently vacuuming the new cluster. Thanks for yourhelp everybody!</div><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Sat, May 11, 2013 at 4:58 PM, BruceMomjian <span dir="ltr"><<a href="mailto:bruce@momjian.us" target="_blank">bruce@momjian.us</a>></span> wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">OnFri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:<br /> > On Fri, May 10, 2013 at 12:36:21PM -0400,Evan D. Hoffman wrote:<br /> > > "........pg.dropped.16........" INTEGER /* dummy */,<br /> > > "........pg.dropped.17........" INTEGER /* dummy */,<br /> > > "........pg.dropped.18........" INTEGER /* dummy*/,<br /> > > "........pg.dropped.19........" INTEGER /* dummy */,<br /> > > "........pg.dropped.20........"INTEGER /* dummy */,<br /> > > "........pg.dropped.21........" INTEGER /* dummy*/,<br /> > > "........pg.dropped.22........" INTEGER /* dummy */,<br /> > > "........pg.dropped.23........"INTEGER /* dummy */,<br /> > > "........pg.dropped.24........" INTEGER /* dummy*/,<br /> > > "........pg.dropped.25........" INTEGER /* dummy */,<br /> > > "........pg.dropped.26........"INTEGER /* dummy */,<br /> > > ha27 character varying(10) DEFAULT 'UNKNOWN'::charactervarying,<br /> > > "........pg.dropped.28........" INTEGER /* dummy */,<br /> > > dr29 character varying(10)<br /> ><br /> > OK, this verifies that the table had a lot of DDL churn. I have no idea<br/> > how to pursue this further because I am unsure how we are going to<br /> > replicate the operations performedon this table in the past, as you<br /> > mentioned much of this was before your time on the job.<br /> ><br/> > Evan, I suggest you force a toast table on the table by doing:<br /> ><br /> > ALTER TABLE bpm.setupinfoADD COLUMN dummy TEXT;<br /> ><br /> > Then drop the column. That will create a toast table and willallow<br /> > pg_upgrade to succeed.<br /><br /></div>FYI, I did test adding a TEXT column and altering a column toTEXT on<br /> Postgres 9.1, and both created a toast table. I am still have no clues<br /> about what would have causedthe missing toast table.<br /><div class="HOEnZb"><div class="h5"><br /> --<br /> Bruce Momjian <<a href="mailto:bruce@momjian.us">bruce@momjian.us</a>> <a href="http://momjian.us" target="_blank">http://momjian.us</a><br/> EnterpriseDB <a href="http://enterprisedb.com" target="_blank">http://enterprisedb.com</a><br/><br /> + It's impossible for everything to be true. +<br /></div></div></blockquote></div><br/></div>
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Steve Singer
Date:
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
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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@momjian.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@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > > -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Bruce Momjian
Date:
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
From
Jerry Sievers
Date:
Bruce Momjian <bruce@momjian.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@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 > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800