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

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 = 'pg_toast_2613';" | tee -a "${LOG_REMOVE}"

  echo -e "Delete pg_depend link between pg_toast_2613 and pg_largeobject" | tee -a "${LOG_REMOVE}"
  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;" | tee -a "${LOG_REMOVE}"

  echo -e "Delete pg_depend link between pg_toast_2613 and ---MISSING OBJECT---" | tee -a "${LOG_REMOVE}"
  psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "DELETE FROM pg_depend WHERE classid = 1259 AND refclassid = 1259 AND objid = 'pg_toast.pg_toast_2613'::regclass AND refobjsubid not in (select oid from pg_class);" | tee -a "${LOG_REMOVE}"

  echo "allow_system_table_mods=on" >>"${postgresql_conf_file}"
  systemctl restart postgresql-9.5.service

  echo -e "Drop relation pg_toast_2613" | tee -a "${LOG_REMOVE}"
  psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "DROP TABLE pg_toast.pg_toast_2613;" | tee -a "${LOG_REMOVE}"

  sed -i '/^allow_system_table_mods=on/d' ${postgresql_conf_file}
  systemctl restart postgresql-9.5.service

  # Refresh value of variable toast_count
  toast_count="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "select count(*) from pg_class where relname = 'pg_toast_2613';")"
fi

if [[ "${toast_count}" == "0" ]]; then
  echo -e "No TOAST table pg_toast_2613" | tee -a "${LOG_REMOVE}"
fi
```