Re: When are largobject records TOASTed into pg_toast_2613? - Mailing list pgsql-general
| From | Thomas Boussekey |
|---|---|
| Subject | Re: When are largobject records TOASTed into pg_toast_2613? |
| Date | |
| Msg-id | CALUeYmfXKXqGYXMPkHBz9RqkG9cY9AMnHS9MaDfo+Ev534XVfQ@mail.gmail.com Whole thread Raw |
| In response to | Re: When are largobject records TOASTed into pg_toast_2613? (Laurenz Albe <laurenz.albe@cybertec.at>) |
| List | pgsql-general |
Thanks Laurenz for your email
Le lun. 31 août 2020 à 09:42, Laurenz Albe <laurenz.albe@cybertec.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
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
>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
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
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
pgsql-general by date: