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

On Fri, 2020-08-21 at 15:46 +0200, Thomas Boussekey wrote:
> Le ven. 21 août 2020 à 15:10, Laurenz Albe <laurenz.albe@cybertec.at> a écrit :
> > On Fri, 2020-08-21 at 14:00 +0200, Thomas Boussekey wrote:
> > > 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"
whichis 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!
> > 
> > Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder
> > how your "pg_largeobject" table could have grown one.
> >
> > Did you do any strange catalog modifications?
> 
> Several years before I arrived in this company, the `pg_largeobject` table had been moved to a dedicated tablespace
locatedon a low-IOPS mechanical disk.
 
> One of my first projects when I started working in the company was to move the `pg_largeobject` table back to the
defaultsystem tablespace.
 
> This might be a side-effect of the migration.

I just tried that on v12, and it didn't create a TOAST table.

But obviously there is/was a bug somewhere.

> > The safest way would be to upgrade with pg_dumpall/psql.
> 
> The `pg_dumpall` command will also copy the content and the existence of the `pg_toast_2613` table, isn't it?
> It might generate errors at the execution on the new instance?
> Moreover, it will generate a large downtime

No, pg_dumpall will not duplicate that strange TOAST table.
It would be the only safe way to upgrade.

If you can ascertain that the TOAST table is empty and you
like to live dangerous, you can try:

UPDATE pg_class SET reltoastrelid = 0 WHERE oid = 2613;
UPDATE pg_class SET relkind = 'r' WHERE relname = 'pg_toast_2613';
DELETE FROM pg_depend WHERE classid = 1259 AND refclassid = 1259 AND refobjid = 2613 AND objid =
'pg_toast.pg_toast_2613'::regclass;
DROP TABLE pg_toast.pg_toast_2613;

But I won't guarantee that that won't break your database.

In particular, it is a no-go unless the TOAST table is empty.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com