Re: When are largobject records TOASTed into pg_toast_2613?

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_largobject catalog 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