Re: When are largobject records TOASTed into pg_toast_2613?

[Thomas Boussekey <thomas.boussekey@xxxxxxxxx>]

Thanks Laurenz for your email

Le lun. 31 août 2020 à 09:42, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> a écrit :

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 can empty the TOAST without altering the access to the data (through vacuum full).

```

-- Check before VACUUM

WITH last_loid(loid) AS (SELECT distinct loid FROM pg_largeobject order by loid desc limit 5)
select loid, length(lo_get(loid)) from last_loid;
  loid  | length
--------+--------
 361314 |    672
 361294 |  40672
 359321 |    672
 359301 |  40672
 355170 |    672

-- VACUUM the pg_largeobject table:
VACUUM FULL ANALYZE VERBOSE pg_largeobject;
INFO:  vacuuming "pg_catalog.pg_largeobject"
INFO:  "pg_largeobject": found 0 removable, 12393 nonremovable row versions in 120 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.10s/0.29u sec elapsed 0.61 sec.
INFO:  analyzing "pg_catalog.pg_largeobject"
INFO:  "pg_largeobject": scanned 114 of 114 pages, containing 12393 live rows and 0 dead rows; 12393 rows in sample, 12393 estimated total rows
VACUUM
Time: 675.114 ms

-- TOAST is now empty (0 tuples)
SELECT oid, relname, relnamespace, relfilenode, reltoastrelid, relpages, (relpages*8/1024)::int as mb_size, reltuples::int, relkind
from pg_class
where relname like 'pg_toast_2613%'
or relname like 'pg_largeobject%' order by relname;

  oid  |              relname              | relnamespace | relfilenode | reltoastrelid | relpages | mb_size | reltuples | relkind
-------+-----------------------------------+--------------+-------------+---------------+----------+---------+-----------+---------
  2613 | pg_largeobject                    |           11 |      369726 |         18172 |      114 |       0 |     12393 | r
  2683 | pg_largeobject_loid_pn_index      |           11 |      369732 |             0 |       36 |       0 |     12393 | i
  2995 | pg_largeobject_metadata           |           11 |        2995 |             0 |        2 |       0 |       181 | r
  2996 | pg_largeobject_metadata_oid_index |           11 |        2996 |             0 |        2 |       0 |       181 | i
 18172 | pg_toast_2613                     |           99 |      369729 |             0 |        0 |       0 |         0 | t
 18174 | pg_toast_2613_index               |           99 |      369731 |             0 |        1 |       0 |         0 | i
(6 rows)

-- The 5 last largeObjects are still available

WITH last_loid(loid) AS (SELECT distinct loid FROM pg_largeobject order by loid desc limit 5)
>select loid, length(lo_get(loid)) from last_loid;
  loid  | length
--------+--------
 361314 |    672
 361294 |  40672
 359321 |    672
 359301 |  40672
 355170 |    672

```

None of the LOID in the pg_largeobject table have a corresponding chunk_id into the pg_toast table.

```

-- ID existing in the 2 tables (HEAP & TOAST)
WITH
  plo(id,count_rows) AS (SELECT loid, count(*) FROM pg_largeobject GROUP BY loid),
  pt2(id,count_rows) AS (SELECT chunk_id, count(*) FROM pg_toast.pg_toast_2613 GROUP BY chunk_id)
SELECT count(*)
FROM plo
  INNER JOIN pt2
    ON plo.id = pt2.id;
 count
-------
     0

```

I have no record of the TOAST link in the `pg_depend` table:

```

 # DELETE FROM pg_depend WHERE classid = "" AND refclassid = 1259 AND refobjid = 2613 AND objid = 'pg_toast.pg_toast_2613'::regclass;
 DELETE 0

```

I told you it was dangerous...

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

It seems the only possible option, I would have liked that another way could be possible

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

Thomas