Thread: When are largobject records TOASTed into pg_toast_2613?

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" which
isthe 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"
whichis 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?

The safest way would be to upgrade with pg_dumpall/psql.
That should get rid of that data corruption.

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

Thomas Boussekey <thomas.boussekey@gmail.com> writes:
> Le ven. 21 août 2020 à 15:10, Laurenz Albe <laurenz.albe@cybertec.at> a
> écrit :
>> 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?

No.  pg_dumpall does not do anything with system catalogs per se.

> Moreover, it will generate a large downtime

Yeah.  It's a tradeoff of safety versus downtime.  If that toast table
is physically empty (I would not bet on it without checking), then you
could manually hack up pg_class to remove the toast table, but there's
a nontrivial risk of permanently hosing your database by messing that
up.

            regards, tom lane

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

On Fri, Aug 21, 2020 at 03:10:30PM +0200, Laurenz Albe wrote:
> Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder
> how your "pg_largeobject" table could have grown one.

FWIW, src/include/catalog/toasting.h is giving me a list of 28 catalog
tables with a toast relation as of HEAD.
--
Michael

On Sat, 2020-08-22 at 10:47 +0900, Michael Paquier wrote:
> > Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder
> > how your "pg_largeobject" table could have grown one.
> 
> FWIW, src/include/catalog/toasting.h is giving me a list of 28 catalog
> tables with a toast relation as of HEAD.

Yes, I was behind the times.

Catalog tables *do* have TOAST tables, but not all of them, and "pg_largeobject" is one that doesn't.

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

On Fri, 2020-08-21 at 18:59 +0200, Thomas Boussekey wrote:
> I wrote this BASH script to remove the TOAST table, if it may help anyone:
> 
> [...]
>   toast_pages="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "select relpages from pg_class where
relname= 'pg_toast_2613';" )"
 
>   toast_tuples="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "select reltuples from pg_class where
relname= 'pg_toast_2613';" )"
 
> [...]

That are just the estimates.
You need to ascertain that the table is *really* empty.

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

On Sat, 2020-08-29 at 21:18 +0200, Thomas Boussekey wrote:
> You can find at the end of this email, a new version of the script that I use to remove the TOAST table on
pg_largobjectcatalog table.
 
> I fixed some typos and wrong synthaxes that I had typed too quickly in my first version.
> 
> Thanks to this script, I can migrate successfully the PostgreSQL instance.
> Yet, the `pg_largobject` table is still considered TOASTed.
> 
> I have the following behaviour:
> 
> [...]
> 
> --- But the pg_largeobject table is not accessible:
> SELECT * from pg_largeobject order by loid desc limit 5;
> ERROR:  could not open relation with OID 16619
> 
> --- Same error when using largeobject functions:
> SELECT lo_get(47232219);
> ERROR:  could not open relation with OID 16619
> 
> --- No TOAST reference into pg_depend for pg_largobject
> SELECT * from pg_depend where 2613 in (objsubid, refobjid);
>  classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype 
> ---------+-------+----------+------------+----------+-------------+---------
>        0 |     0 |        0 |       1259 |     2613 |           0 | p
> 
> --- As for OID 16619
> SELECT * from pg_depend where 16619 in (objsubid, refobjid);
>  classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype 
> ---------+-------+----------+------------+----------+-------------+---------
> ```
> 
> > Is there another catalog table where the TOAST reference can be located?

Yes, in the table itself.  It seems like some values in pg_largeobject
were stored in the TOAST table after all.

I told you it was dangerous...

I guess you'll have to migrate with dump/restore.

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