Re: When are largobject records TOASTed into pg_toast_2613? - Mailing list pgsql-general

On Fri, 2020-08-21 at 15:46 +0200, Thomas Boussekey wrote:
> Le ven. 21 août 2020 à 15:10, Laurenz Albe <laurenz.albe@cybertec.at> 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;

#!/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 WHERE classid = 1259 AND refclassid = 1259 AND refobjid = 2613 AND objid = 'pg_toast.pg_toast_2613'::regclass;"

  echo "allow_system_table_mods=on" >> "${postgresql_conf_file}"
  systemctl restart postgresql-9.5.service
 
  echo -e "Drop relation pg_toast_2613"
  psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "DROP TABLE pg_toast.pg_toast_2613;"

  sed -i '/^postgres_enable_version:/d' ${postgresql_conf_file}
  systemctl restart postgresql-9.5.service
fi

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