Re: When are largobject records TOASTed into pg_toast_2613?

Thanks Laurenz for your email

Le lun. 31 août 2020 à 09:42, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> 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 = 1259 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