Re: When are largobject records TOASTed into pg_toast_2613? - Mailing list pgsql-general

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

```