Thanks Laurenz for your email Le lun. 31 août 2020 à 09:42, Laurenz Albe <laurenz.a...@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 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