Re: When are largobject records TOASTed into pg_toast_2613?

Le ven. 21 août 2020 à 14:00, Thomas Boussekey <thomas(dot)boussekey(at)gmail(dot)com>
a écrit :

> 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
>
> ```
>

Additional information,

I restored a basebackup for an instance containing the `pg_toast_2613`
table.

At first glimpse, the TOAST table is 30 times the size of pg_largobject
(see relpages in the first query below).
I tried to VACUUM FULL the `pg_largobject` table, and the rows into the
`pg_toast_2613` table vanished!

Can it be a suitable workaround to apply the following logic in my
migration process?

* If `pg_toast_2613` table exists
- Perform `VACUUM FULL VERBOSE pg_largeobject`
- If `SELECT COUNT(*) FROM pg_toast_2613;` = 0
- unTOAST the `pg_largobject` table (if a procedure exists)

```sql
# SELECT oid, relname, relnamespace, relfilenode, reltoastrelid, relpages,
reltuples
>from pg_class
>where relname like 'pg_toast_2613%'
>or relname like 'pg_largeobject%' order by relname;
oid | relname | relnamespace | relfilenode |
reltoastrelid | relpages | reltuples
-------+-----------------------------------+--------------+-------------+---------------+----------+-----------
2613 | pg_largeobject | 11 | 5349225 |
16637 | 263 | 5662
2683 | pg_largeobject_loid_pn_index | 11 | 5348991 |
0 | 90 | 5662
2995 | pg_largeobject_metadata | 11 | 2995 |
0 | 307 | 179
2996 | pg_largeobject_metadata_oid_index | 11 | 27619 |
0 | 259 | 179
16637 | pg_toast_2613 | 99 | 5349226 |
0 | 6120 | 16027
16639 | pg_toast_2613_index | 99 | 5349227 |
0 | 251 | 4678
(6 rows)

# VACUUM FULL VERBOSE pg_largeobject;
INFO: vacuuming "pg_catalog.pg_largeobject"
INFO: "pg_largeobject": found 8 removable, 5770 nonremovable row versions
in 263 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.04s/0.11u sec elapsed 0.22 sec.
VACUUM
Time: 258.031 ms

# SELECT oid, relname, relnamespace, relfilenode, reltoastrelid, relpages,
reltuples
from pg_class
where relname like 'pg_toast_2613%'
or relname like 'pg_largeobject%' order by relname;
oid | relname | relnamespace | relfilenode |
reltoastrelid | relpages | reltuples
-------+-----------------------------------+--------------+-------------+---------------+----------+-----------
2613 | pg_largeobject | 11 | 7819455 |
16637 | 67 | 5770
2683 | pg_largeobject_loid_pn_index | 11 | 7819461 |
0 | 18 | 5770
2995 | pg_largeobject_metadata | 11 | 2995 |
0 | 307 | 179
2996 | pg_largeobject_metadata_oid_index | 11 | 27619 |
0 | 259 | 179
16637 | pg_toast_2613 | 99 | 7819458 |
0 | 0 | 0
16639 | pg_toast_2613_index | 99 | 7819460 |
0 | 1 | 0
(6 rows)

Time: 0.950 ms
```