gpcheckcat fails during 'orphaned_toast_tables' with "invalid input syntax for integer"

Products

VMware Tanzu Data

Issue/Introduction

Symptom

When running the gpcheckcat utility, the process abruptly exits during the orphaned_toast_tables test.

Log Output (gpcheckcat log):

The log file shows no native errors and simply stops after initiating the check:

Performing test 'orphaned_toast_tables'
20260326:14:47:39:246017 gpcheckcat:gdm:gpadmin-[INFO]:------------------------------------
20260326:14:47:39:246017 gpcheckcat:gdm:gpadmin-[INFO]:-Performing check: checking for orphaned TOAST tables

Frontend Output (Terminal):

The console displays a Python stack trace ending with a PostgreSQL ProgrammingError indicating an invalid integer format involving a temporary TOAST table:

  File "/usr/local/gp6/greenplum-db-6.27.5/bin/gpcheckcat_modules/orphaned_toast_tables_check.py", line 121, in runCheck
    orphaned_toast_tables = db_connection.query(self.orphaned_toast_tables_query).dictresult()
pg.ProgrammingError: ERROR:  invalid input syntax for integer: "pg_toast_temp_123456.pg_toast_123456789"

Cause

Root Cause

This issue may caused by a conflict between a specific Greenplum Server Configuration Parameter (GUC) and the presence of active temporary TOAST tables.

The orphaned_toast_tables_check.py script executes a catalog query that uses the REGEXP_REPLACE function to extract the numeric OID from a temporary TOAST table's name:

REGEXP_REPLACE(tst.oid::regclass::text, 'pg_toast(_temp_\d+)?.pg_toast_', '')::int::regclass::oid = dbl.oid

If the database user running gpcheckcat (typically gpadmin) has the parameter standard_conforming_strings set to off, the database engine treats the backslash in the regular expression (\d) as an escape character instead of a digit identifier.

Because the regular expression fails to match the pattern, the string is not stripped. The query then attempts to cast the entire unedited string ("pg_toast_temp_123456.pg_toast_123456789") into an integer (::int), resulting in the syntax error and crashing the check.

Note: This failure only triggers if there is a temporary TOAST table actively present in the system catalogs at the exact moment the gpcheckcat query executes.

Resolution

Diagnostics

To confirm this is the issue, verify the setting of standard_conforming_strings for the user running the check.

Check the global GUC value:

gpconfig -s standard_conforming_strings

Check the session value as the admin user:

psql -U gpadmin -c "SHOW standard_conforming_strings;"

Test the failing regex query directly:
```
SELECT REGEXP_REPLACE('pg_toast_temp_123456.pg_toast_123456789', 'pg_toast(_temp_\d+)?.pg_toast_', '')::int::regclass::oid;
```
If the setting is off, this test query will return the exact invalid input syntax for integer error.

Resolution & Workarounds

You can resolve or mitigate this issue using one of the two following methods:

Option 1: Enable `standard_conforming_strings` for the Admin User (Recommended)

You can override the global GUC explicitly for the user running the utility (e.g., gpadmin). Setting it to on (the Greenplum default) allows the regex to parse correctly without disrupting other application users.

Connect to the database:
```
psql -U gpadmin -d template1
```

Alter the role:

ALTER ROLE gpadmin SET standard_conforming_strings = on;

Verify the fix by running the specific test to save time:
```
gpcheckcat -R orphaned_toast_tables -p 5432
```

Option 2: Ignore and Rerun (If GUC cannot be altered)

If strict requirements prevent changing this GUC even at the role level, you can simply rerun the utility.

Because the failure requires the precise timing of a temporary TOAST table existing during the check, it is an intermittent issue. If no temporary TOAST tables are present on subsequent runs, gpcheckcat will complete successfully.