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 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
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
Re: When are largobject records TOASTed into pg_toast_2613?
Hello all,
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:
```sql
---Using the pg_largeobject_loid_pn_index is OK:
SELECT loid from pg_largeobject order by loid desc limit 5;
loid
--
47232219
47232219
47232219
47232219
47232219
(5 rows)
--- according to pg_class, pg_largobject is not TOASTed anymore:
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 |47237561 |
0 | 8791 | 68 |727520 | r
2683 | pg_largeobject_loid_pn_index | 11 |47237567 |
0 | 1997 | 15 |727520 | i
2995 | pg_largeobject_metadata | 11 |2995 |
0 | 230 | 1 | 5071 | r
2996 | pg_largeobject_metadata_oid_index | 11 |2996 |
0 | 2320 | 18 | 5071 | i
(4 rows)
--- 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?
Thanks in advance for your help,
Have a nice Sunday,
Thomas
Latest version of the script:
```sql
#!/usr/bin/env bash
#
set -euo pipefail
database_name="xxx"
postgresql_conf_file="/yyy/postgresql.conf"
# Define log files
LOG_FOLDER="/zzz/log"
mkdir -p "${LOG_FOLDER}"
LOG_REMOVE="${LOG_FOLDER}/remove_operation.log"
# Step 1: check if table pg_toast_2613 exists
toast_count="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
-c "select count(*) from pg_class where relname = 'pg_toast_2613';")"
echo -e "TOAST exists ::${toast_count}" | tee -a "${LOG_REMOVE}"
if [[ "${toast_count}" == "1" ]]; then
# Step 2: Check if table pg_toast_2613 has rows and pages
toast_pages="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
-c "select relpages from pg_class where relname = 'pg_toast_2613';")"
toast_tuples="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select reltuples::int from pg_class where relname =
'pg_toast_2613';")"
echo -e "TOAST content pages ::${toast_pages}:: tuples ::${toast_tuples}"
| tee -a "${LOG_REMOVE}"
# Step 3 OPTIONAL: vacuum full pg_largeobject if needed
if [[ "${toast_tuples}" -gt "0" ]]; then
echo -e "Start of vacuum" | tee -a "${LOG_REMOVE}"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "VACUUM
FULL ANALYZE VERBOSE pg_largeobject;" 2>&1 | tee -a "${LOG_REMOVE}"
echo -e "End of vacuum" | tee -a "${LOG_REMOVE}"
## After VACUUM post-check
toast_pages="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select relpages from pg_class where relname =
'pg_toast_2613';")"
toast_tuples="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select reltuples::int from pg_class where relname =
'pg_toast_2613';")"
echo -e "TOAST content pages ::${toast_pages}:: tuples
::${toast_tuples}" | tee -a "${LOG_REMOVE}"
fi
# Step 4: Remove TOAST information for pg_largeobject into pg_class
echo -e "Remove TOAST on pg_largeobject" | tee -a "${LOG_REMOVE}"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "UPDATE
pg_class SET reltoastrelid = 0 WHERE oid = 2613;" | tee -a "${LOG_REMOVE}"
# Step 5: Drop pg_toast_2613% objects
echo -e "Change pg_toast_2613 type to relation" | tee -a "${LOG_REMOVE}"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "UPDATE
pg_class SET relkind = 'r' WHERE relname =
Re: When are largobject records TOASTed into pg_toast_2613?
On Fri, 2020-08-21 at 18:59 +0200, Thomas Boussekey wrote:
> I wrote this BASH script to remove the TOAST table, if it may help anyone:
>
> [...]
> toast_pages="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
> -c "select relpages from pg_class where relname = 'pg_toast_2613';" )"
> toast_tuples="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
> -c "select reltuples from pg_class where relname = 'pg_toast_2613';" )"
> [...]
That are just the estimates.
You need to ascertain that the table is *really* empty.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: When are largobject records TOASTed into pg_toast_2613?
On Sat, 2020-08-22 at 10:47 +0900, Michael Paquier wrote: > > Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder > > how your "pg_largeobject" table could have grown one. > > FWIW, src/include/catalog/toasting.h is giving me a list of 28 catalog > tables with a toast relation as of HEAD. Yes, I was behind the times. Catalog tables *do* have TOAST tables, but not all of them, and "pg_largeobject" is one that doesn't. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: When are largobject records TOASTed into pg_toast_2613?
On Fri, Aug 21, 2020 at 03:10:30PM +0200, Laurenz Albe wrote: > Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder > how your "pg_largeobject" table could have grown one. FWIW, src/include/catalog/toasting.h is giving me a list of 28 catalog tables with a toast relation as of HEAD. -- Michael signature.asc Description: PGP signature
Re: When are largobject records TOASTed into pg_toast_2613?
Le ven. 21 août 2020 à 16:45, Laurenz Albe a
écrit :
> On Fri, 2020-08-21 at 15:46 +0200, Thomas Boussekey wrote:
> > Le ven. 21 août 2020 à 15:10, Laurenz Albe a
> écrit :
> > > On Fri, 2020-08-21 at 14:00 +0200, Thomas Boussekey wrote:
> > > > Working on a PostgreSQL 9.5 to 12 upgrade, I encounter problems on a
> PostgreSQL instance when I have
> > > > an existing table `pg_toast_2613` into my application database.
> > > >
> > > > The upgrade process fails with the following error:
> > > >
> > > > ```
> > > > No match found in new cluster for old relation with OID 16619 in
> database "mirakl_db": "pg_toast.pg_toast_2613" which is the TOAST table for
> "pg_catalog.pg_largeobject"
> > > > No match found in new cluster for old relation with OID 16621 in
> database "mirakl_db": "pg_toast.pg_toast_2613_index" which is an index on
> "pg_toast.pg_toast_2613" which is the TOAST table for
> > > > "pg_catalog.pg_largeobject"
> > > > ```
> > > >
> > > > The `pg_upgrade` command fails when I have the table `pg_toast_2613`
> that exists, even if it is empty.
> > > > I read the PostgreSQL documentation, and I didn't find when the
> pg_largeobject table needs to be toasted.
> > > > I thought it might be linked with records' size, but my queries
> below don't correlate that!
> > >
> > > Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder
> > > how your "pg_largeobject" table could have grown one.
> > >
> > > Did you do any strange catalog modifications?
> >
> > Several years before I arrived in this company, the `pg_largeobject`
> table had been moved to a dedicated tablespace located on a low-IOPS
> mechanical disk.
> > One of my first projects when I started working in the company was to
> move the `pg_largeobject` table back to the default system tablespace.
> > This might be a side-effect of the migration.
>
> I just tried that on v12, and it didn't create a TOAST table.
>
> But obviously there is/was a bug somewhere.
>
> > > The safest way would be to upgrade with pg_dumpall/psql.
> >
> > The `pg_dumpall` command will also copy the content and the existence of
> the `pg_toast_2613` table, isn't it?
> > It might generate errors at the execution on the new instance?
> > Moreover, it will generate a large downtime
>
> No, pg_dumpall will not duplicate that strange TOAST table.
> It would be the only safe way to upgrade.
>
> If you can ascertain that the TOAST table is empty and you
> like to live dangerous, you can try:
>
> UPDATE pg_class SET reltoastrelid = 0 WHERE oid = 2613;
> UPDATE pg_class SET relkind = 'r' WHERE relname = 'pg_toast_2613';
> DELETE FROM pg_depend WHERE classid = 1259 AND refclassid = 1259 AND
> refobjid = 2613 AND objid = 'pg_toast.pg_toast_2613'::regclass;
> DROP TABLE pg_toast.pg_toast_2613;
>
Thanks Laurenz & Tom for your precious information.
I wrote this BASH script to remove the TOAST table, if it may help anyone:
```sh
#!/usr/bin/env bash
#
set -euo pipefail
database_name="xxx"
postgresql_conf_file="/xxx/postgresql.conf"
# Step 1: check if table pg_toast_2613 exists
toast_count="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
-c "select count(*) from pg_class where relname = 'pg_toast_2613';")"
echo -e "TOAST exists ::${toast_count}"
if [[ "${toast_count}" == "1" ]]; then
# Step 2: Check if table pg_toast_2613 has rows and pages
toast_pages="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
-c "select relpages from pg_class where relname = 'pg_toast_2613';" )"
toast_tuples="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select reltuples from pg_class where relname =
'pg_toast_2613';" )"
echo -e "TOAST content pages ::${toast_pages}:: tuples ::${toast_tuples}"
# Step 3 OPTIONAL: vacuum full pg_largobject if needed
if [[ "${toast_tuples}" -gt "0" ]]; then
echo -e "Start of vacuum"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "VACUUM
FULL VERBOSE pg_largobject;"
echo -e "End of vacuum"
## After VACUUM post-check
toast_pages="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select relpages from pg_class where relname =
'pg_toast_2613';" )"
toast_tuples="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select reltuples from pg_class where relname =
'pg_toast_2613';" )"
echo -e "TOAST content pages ::${toast_pages}:: tuples
::${toast_tuples}"
fi
# Step 4: Remove TOAST information for pg_largobject into pg_class
echo -e "Remove TOAST on pg_largobject"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "UPDATE
pg_class SET reltoastrelid = 0 WHERE oid = 2613;"
# Step 5: Drop pg_toast_2613% objects
echo -e "Change pg_toast_2613 type to relation"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "UPDATE
pg_class SET relkind = 'r' WHERE relname = 'pg_toast_2613';"
echo -e "Delete pg_depend for pg_toast_2613"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "DELETE
FROM pg_depend
Re: When are largobject records TOASTed into pg_toast_2613?
On Fri, 2020-08-21 at 15:46 +0200, Thomas Boussekey wrote: > Le ven. 21 août 2020 à 15:10, Laurenz Albe a écrit > : > > On Fri, 2020-08-21 at 14:00 +0200, Thomas Boussekey wrote: > > > Working on a PostgreSQL 9.5 to 12 upgrade, I encounter problems on a > > > PostgreSQL instance when I have > > > an existing table `pg_toast_2613` into my application database. > > > > > > The upgrade process fails with the following error: > > > > > > ``` > > > No match found in new cluster for old relation with OID 16619 in database > > > "mirakl_db": "pg_toast.pg_toast_2613" which is the TOAST table for > > > "pg_catalog.pg_largeobject" > > > No match found in new cluster for old relation with OID 16621 in database > > > "mirakl_db": "pg_toast.pg_toast_2613_index" which is an index on > > > "pg_toast.pg_toast_2613" which is the TOAST table for > > > "pg_catalog.pg_largeobject" > > > ``` > > > > > > The `pg_upgrade` command fails when I have the table `pg_toast_2613` that > > > exists, even if it is empty. > > > I read the PostgreSQL documentation, and I didn't find when the > > > pg_largeobject table needs to be toasted. > > > I thought it might be linked with records' size, but my queries below > > > don't correlate that! > > > > Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder > > how your "pg_largeobject" table could have grown one. > > > > Did you do any strange catalog modifications? > > Several years before I arrived in this company, the `pg_largeobject` table > had been moved to a dedicated tablespace located on a low-IOPS mechanical > disk. > One of my first projects when I started working in the company was to move > the `pg_largeobject` table back to the default system tablespace. > This might be a side-effect of the migration. I just tried that on v12, and it didn't create a TOAST table. But obviously there is/was a bug somewhere. > > The safest way would be to upgrade with pg_dumpall/psql. > > The `pg_dumpall` command will also copy the content and the existence of the > `pg_toast_2613` table, isn't it? > It might generate errors at the execution on the new instance? > Moreover, it will generate a large downtime No, pg_dumpall will not duplicate that strange TOAST table. It would be the only safe way to upgrade. If you can ascertain that the TOAST table is empty and you like to live dangerous, you can try: UPDATE pg_class SET reltoastrelid = 0 WHERE oid = 2613; UPDATE pg_class SET relkind = 'r' WHERE relname = 'pg_toast_2613'; DELETE FROM pg_depend WHERE classid = 1259 AND refclassid = 1259 AND refobjid = 2613 AND objid = 'pg_toast.pg_toast_2613'::regclass; DROP TABLE pg_toast.pg_toast_2613; But I won't guarantee that that won't break your database. In particular, it is a no-go unless the TOAST table is empty. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Fwd: When are largobject records TOASTed into pg_toast_2613?
Thomas Boussekey writes: > Le ven. 21 août 2020 à 15:10, Laurenz Albe a > écrit : >> The safest way would be to upgrade with pg_dumpall/psql. > The `pg_dumpall` command will also copy the content and the existence of > the `pg_toast_2613` table, isn't it? No. pg_dumpall does not do anything with system catalogs per se. > Moreover, it will generate a large downtime Yeah. It's a tradeoff of safety versus downtime. If that toast table is physically empty (I would not bet on it without checking), then you could manually hack up pg_class to remove the toast table, but there's a nontrivial risk of permanently hosing your database by messing that up. regards, tom lane
Fwd: When are largobject records TOASTed into pg_toast_2613?
-- Forwarded message - De : Thomas Boussekey Date: ven. 21 août 2020 à 15:37 Subject: Re: When are largobject records TOASTed into pg_toast_2613? To: Laurenz Albe Le ven. 21 août 2020 à 15:10, Laurenz Albe a écrit : > On Fri, 2020-08-21 at 14:00 +0200, Thomas Boussekey wrote: > > Working on a PostgreSQL 9.5 to 12 upgrade, I encounter problems on a > PostgreSQL instance when I have > > an existing table `pg_toast_2613` into my application database. > > > > The upgrade process fails with the following error: > > > > ``` > > No match found in new cluster for old relation with OID 16619 in > database "mirakl_db": "pg_toast.pg_toast_2613" which is the TOAST table for > "pg_catalog.pg_largeobject" > > No match found in new cluster for old relation with OID 16621 in > database "mirakl_db": "pg_toast.pg_toast_2613_index" which is an index on > "pg_toast.pg_toast_2613" which is the TOAST table for > > "pg_catalog.pg_largeobject" > > ``` > > > > The `pg_upgrade` command fails when I have the table `pg_toast_2613` > that exists, even if it is empty. > > I read the PostgreSQL documentation, and I didn't find when the > pg_largeobject table needs to be toasted. > > I thought it might be linked with records' size, but my queries below > don't correlate that! > > Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder > how your "pg_largeobject" table could have grown one. > Several years before I arrived in this company, the `pg_largeobject` table had been moved to a dedicated tablespace located on a low-IOPS mechanical disk. One of my first projects when I started working in the company was to move the `pg_largeobject` table back to the default system tablespace. This might be a side-effect of the migration. > > Did you do any strange catalog modifications? > > The safest way would be to upgrade with pg_dumpall/psql. > The `pg_dumpall` command will also copy the content and the existence of the `pg_toast_2613` table, isn't it? It might generate errors at the execution on the new instance? Moreover, it will generate a large downtime > That should get rid of that data corruption. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
Re: When are largobject records TOASTed into pg_toast_2613?
On Fri, 2020-08-21 at 14:00 +0200, Thomas Boussekey wrote: > Working on a PostgreSQL 9.5 to 12 upgrade, I encounter problems on a > PostgreSQL instance when I have > an existing table `pg_toast_2613` into my application database. > > The upgrade process fails with the following error: > > ``` > No match found in new cluster for old relation with OID 16619 in database > "mirakl_db": "pg_toast.pg_toast_2613" which is the TOAST table for > "pg_catalog.pg_largeobject" > No match found in new cluster for old relation with OID 16621 in database > "mirakl_db": "pg_toast.pg_toast_2613_index" which is an index on > "pg_toast.pg_toast_2613" which is the TOAST table for > "pg_catalog.pg_largeobject" > ``` > > The `pg_upgrade` command fails when I have the table `pg_toast_2613` that > exists, even if it is empty. > I read the PostgreSQL documentation, and I didn't find when the > pg_largeobject table needs to be toasted. > I thought it might be linked with records' size, but my queries below don't > correlate that! Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder how your "pg_largeobject" table could have grown one. Did you do any strange catalog modifications? The safest way would be to upgrade with pg_dumpall/psql. That should get rid of that data corruption. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: When are largobject records TOASTed into pg_toast_2613?
Le ven. 21 août 2020 à 14:00, Thomas Boussekey a écrit : > Hello all, > > Working on a PostgreSQL 9.5 to 12 upgrade, I encounter problems on a > PostgreSQL instance when I have an existing table `pg_toast_2613` into my > application database. > > The upgrade process fails with the following error: > > ``` > No match found in new cluster for old relation with OID 16619 in database > "mirakl_db": "pg_toast.pg_toast_2613" which is the TOAST table for > "pg_catalog.pg_largeobject" > No match found in new cluster for old relation with OID 16621 in database > "mirakl_db": "pg_toast.pg_toast_2613_index" which is an index on > "pg_toast.pg_toast_2613" which is the TOAST table for > "pg_catalog.pg_largeobject" > ``` > > The `pg_upgrade` command fails when I have the table `pg_toast_2613` that > exists, even if it is empty. > I read the PostgreSQL documentation, and I didn't find when the > pg_largeobject table needs to be toasted. I thought it might be linked with > records' size, but my queries below don't correlate that! > > I tried to dig into the data and found the following elements: > * a records exists ONLY into one table (either the pg_largobject table or > the pg_toast_2613, but not BOTH) > * The `chunk_id` present into the `pg_toast_2613` table doesn't represent > real large objects (impossible to query their size) > * The `chunk_id` present into the `pg_toast_2613` table are not linked to > existing documents into our applicative tables. > > I had a look on my 200+ production & test environments: > * on half of these instances, the `pg_toast_2613` table doesn't exist > * on 10% of them, the `pg_toast_2613` table exists and is empty > > Here are the points, I want to clarify: > - What is the aim of the `pg_toast_2613` table? > - Does it contain REAL large objects or other useful data? > - Is there a workaround to make the `pg_upgrade` successful? > > Thanks in advance for your help, > Thomas > > > # Appendix > > ```sql > -- Getting the 30 first items of BOTH tables > # SELECT loid, count(*) from pg_largeobject group by loid order by 1 limit > 30; > loid | count > +--- > 24567 | 1 > 24588 | 1 > 24608 | 1 > 24635 | 1 > 24648 | 1 > 24699 | 1 > 27505 | 1 > 84454 |32 > 89483 | 1 > 109676 |34 > 109753 |34 > 109821 |34 > 109855 | 2 > 137150 | 6 > 141236 |29 > 141265 | 1 > 156978 |29 > 157036 |29 > 157065 | 2 > 161835 |29 > 161864 | 1 > 166275 |29 > 166333 |29 > 166404 |29 > 166439 | 2 > 171487 |29 > 171516 | 1 > 175825 |29 > 175854 | 1 > 180171 |29 > (30 rows) > > # SELECT chunk_id, count(*) from pg_toast.pg_toast_2613 group by chunk_id > order by 1 limit 30; > chunk_id | count > --+--- > 84455 | 2 > 84456 | 2 > 84457 | 2 > 84458 | 2 > 84459 | 2 > 84460 | 2 > 84461 | 2 > 84462 | 2 > 84463 | 2 > 84464 | 2 > 84465 | 2 > 84466 | 2 > 84467 | 2 > 84468 | 2 > 84469 | 2 > 84470 | 2 > 84471 | 2 > 84472 | 2 > 84473 | 2 > 84474 | 2 > 84475 | 2 > 84476 | 2 > 84477 | 2 > 84478 | 2 > 84479 | 2 > 84480 | 2 > 84481 | 2 > 84482 | 2 > 84483 | 2 > 84484 | 2 > (30 rows) > > -- Searching IDs 84454, 84455 into applicative table > # SELECT * from mirakl_lob where blob in (84454, 84455); > mirakl_document_id | blob > +--- >2859 | 84454 > > SELECT length(lo_get (84455)); > ERROR: large object 84455 does not exist > > SELECT length(lo_get (84454)); > length > > 64080 > > ``` > Additional information, I restored a basebackup for an instance containing the `pg_toast_2613` table. At first glimpse, the TOAST table is 30 times the size of pg_largobject (see relpages in the first query below). I tried to VACUUM FULL the `pg_largobject` table, and the rows into the `pg_toast_2613` table vanished! Can it be a suitable workaround to apply the following logic in my migration process? * If `pg_toast_2613` table exists - Perform `VACUUM FULL VERBOSE pg_largeobject` - If `SELECT COUNT(*) FROM pg_toast_2613;` = 0 - unTOAST the `pg_largobject` table (if a procedure exists) ```sql # SELECT oid, relname, relnamespace, relfilenode, reltoastrelid, relpages, reltuples >from pg_class >where relname like 'pg_toast_2613%' >or relname like 'pg_largeobject%' order by relname; oid | relname | relnamespace | relfilenode | reltoastrelid | relpages | reltuples ---+---+--+-+---+--+--- 2613 | pg_largeobject| 11 | 5349225 | 16637 | 263 | 5662 2683 | pg_largeobject_loid_pn_index | 11 |
When are largobject records TOASTed into pg_toast_2613?
Hello all, Working on a PostgreSQL 9.5 to 12 upgrade, I encounter problems on a PostgreSQL instance when I have an existing table `pg_toast_2613` into my application database. The upgrade process fails with the following error: ``` No match found in new cluster for old relation with OID 16619 in database "mirakl_db": "pg_toast.pg_toast_2613" which is the TOAST table for "pg_catalog.pg_largeobject" No match found in new cluster for old relation with OID 16621 in database "mirakl_db": "pg_toast.pg_toast_2613_index" which is an index on "pg_toast.pg_toast_2613" which is the TOAST table for "pg_catalog.pg_largeobject" ``` The `pg_upgrade` command fails when I have the table `pg_toast_2613` that exists, even if it is empty. I read the PostgreSQL documentation, and I didn't find when the pg_largeobject table needs to be toasted. I thought it might be linked with records' size, but my queries below don't correlate that! I tried to dig into the data and found the following elements: * a records exists ONLY into one table (either the pg_largobject table or the pg_toast_2613, but not BOTH) * The `chunk_id` present into the `pg_toast_2613` table doesn't represent real large objects (impossible to query their size) * The `chunk_id` present into the `pg_toast_2613` table are not linked to existing documents into our applicative tables. I had a look on my 200+ production & test environments: * on half of these instances, the `pg_toast_2613` table doesn't exist * on 10% of them, the `pg_toast_2613` table exists and is empty Here are the points, I want to clarify: - What is the aim of the `pg_toast_2613` table? - Does it contain REAL large objects or other useful data? - Is there a workaround to make the `pg_upgrade` successful? Thanks in advance for your help, Thomas # Appendix ```sql -- Getting the 30 first items of BOTH tables # SELECT loid, count(*) from pg_largeobject group by loid order by 1 limit 30; loid | count +--- 24567 | 1 24588 | 1 24608 | 1 24635 | 1 24648 | 1 24699 | 1 27505 | 1 84454 |32 89483 | 1 109676 |34 109753 |34 109821 |34 109855 | 2 137150 | 6 141236 |29 141265 | 1 156978 |29 157036 |29 157065 | 2 161835 |29 161864 | 1 166275 |29 166333 |29 166404 |29 166439 | 2 171487 |29 171516 | 1 175825 |29 175854 | 1 180171 |29 (30 rows) # SELECT chunk_id, count(*) from pg_toast.pg_toast_2613 group by chunk_id order by 1 limit 30; chunk_id | count --+--- 84455 | 2 84456 | 2 84457 | 2 84458 | 2 84459 | 2 84460 | 2 84461 | 2 84462 | 2 84463 | 2 84464 | 2 84465 | 2 84466 | 2 84467 | 2 84468 | 2 84469 | 2 84470 | 2 84471 | 2 84472 | 2 84473 | 2 84474 | 2 84475 | 2 84476 | 2 84477 | 2 84478 | 2 84479 | 2 84480 | 2 84481 | 2 84482 | 2 84483 | 2 84484 | 2 (30 rows) -- Searching IDs 84454, 84455 into applicative table # SELECT * from mirakl_lob where blob in (84454, 84455); mirakl_document_id | blob +--- 2859 | 84454 SELECT length(lo_get (84455)); ERROR: large object 84455 does not exist SELECT length(lo_get (84454)); length 64080 ```
