Hello all,
Working on a PostgreSQL 9.5 to 12 upgrade, I encounter problems on a
PostgreSQL instance when I have an existing table `pg_toast_2613` into my
application database.
The upgrade process fails with the following error:
```
No match found in new cluster for old relation with OID 16619 in database
"mirakl_db": "pg_toast.pg_toast_2613" which is the TOAST table for
"pg_catalog.pg_largeobject"
No match found in new cluster for old relation with OID 16621 in database
"mirakl_db": "pg_toast.pg_toast_2613_index" which is an index on
"pg_toast.pg_toast_2613" which is the TOAST table for
"pg_catalog.pg_largeobject"
```
The `pg_upgrade` command fails when I have the table `pg_toast_2613` that
exists, even if it is empty.
I read the PostgreSQL documentation, and I didn't find when the
pg_largeobject table needs to be toasted. I thought it might be linked with
records' size, but my queries below don't correlate that!
I tried to dig into the data and found the following elements:
* a records exists ONLY into one table (either the pg_largobject table or
the pg_toast_2613, but not BOTH)
* The `chunk_id` present into the `pg_toast_2613` table doesn't represent
real large objects (impossible to query their size)
* The `chunk_id` present into the `pg_toast_2613` table are not linked to
existing documents into our applicative tables.
I had a look on my 200+ production & test environments:
* on half of these instances, the `pg_toast_2613` table doesn't exist
* on 10% of them, the `pg_toast_2613` table exists and is empty
Here are the points, I want to clarify:
- What is the aim of the `pg_toast_2613` table?
- Does it contain REAL large objects or other useful data?
- Is there a workaround to make the `pg_upgrade` successful?
Thanks in advance for your help,
Thomas
# Appendix
```sql
-- Getting the 30 first items of BOTH tables
# SELECT loid, count(*) from pg_largeobject group by loid order by 1 limit
30;
loid | count
--------+-------
24567 | 1
24588 | 1
24608 | 1
24635 | 1
24648 | 1
24699 | 1
27505 | 1
84454 | 32
89483 | 1
109676 | 34
109753 | 34
109821 | 34
109855 | 2
137150 | 6
141236 | 29
141265 | 1
156978 | 29
157036 | 29
157065 | 2
161835 | 29
161864 | 1
166275 | 29
166333 | 29
166404 | 29
166439 | 2
171487 | 29
171516 | 1
175825 | 29
175854 | 1
180171 | 29
(30 rows)
# SELECT chunk_id, count(*) from pg_toast.pg_toast_2613 group by chunk_id
order by 1 limit 30;
chunk_id | count
----------+-------
84455 | 2
84456 | 2
84457 | 2
84458 | 2
84459 | 2
84460 | 2
84461 | 2
84462 | 2
84463 | 2
84464 | 2
84465 | 2
84466 | 2
84467 | 2
84468 | 2
84469 | 2
84470 | 2
84471 | 2
84472 | 2
84473 | 2
84474 | 2
84475 | 2
84476 | 2
84477 | 2
84478 | 2
84479 | 2
84480 | 2
84481 | 2
84482 | 2
84483 | 2
84484 | 2
(30 rows)
-- Searching IDs 84454, 84455 into applicative table
# SELECT * from mirakl_lob where blob in (84454, 84455);
mirakl_document_id | blob
--------------------+-------
2859 | 84454
SELECT length(lo_get (84455));
ERROR: large object 84455 does not exist
SELECT length(lo_get (84454));
length
--------
64080
```
