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 = '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
```

Le ven. 21 août 2020 à 18:59, Thomas Boussekey <thomas(dot)boussekey(at)gmail(dot)com>
a écrit :

>
>
> Le ven. 21 août 2020 à 16:45, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> a
> écrit :
>
>> On Fri, 2020-08-21 at 15:46 +0200, Thomas Boussekey wrote:
>> > Le ven. 21 août 2020 à 15:10, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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;
>>
> 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 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
> ```
> My PostgreSQL instance is OK, and the migration to PostgreSQL12 is
> sucessful.
> I continue testing the instance
>
> Have a nice week-end,
> Thomas
>
>>
>> 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
>>
>>