gpcheckcat error: sycopg2.DataError: invalid input syntax for type bigint: "pg_toast_XXXXXX"

Products

• VMware Tanzu Data Suite

Issue/Introduction

The gpcheckcat utility fails with the back trace and error message below:

Performing test 'orphaned_toast_tables'
Traceback (most recent call last):
  File "/usr/local/greenplum-db-7.3.0/bin/gpcheckcat", line 3776, in <module>
    main()
  File "/usr/local/greenplum-db-7.3.0/bin/gpcheckcat", line 3763, in main
    runAllChecks(run_tests)
  File "/usr/local/greenplum-db-7.3.0/bin/gpcheckcat", line 2419, in runAllChecks
    runOneCheck(name)
  File "/usr/local/greenplum-db-7.3.0/bin/gpcheckcat", line 2399, in runOneCheck
    all_checks[name]["fn"]()
  File "/usr/local/greenplum-db-7.3.0/bin/gpcheckcat", line 1955, in checkOrphanedToastTables
    check_passed = checker.runCheck(conn)
  File "/usr/local/greenplum-db-7.3.0/bin/gpcheckcat_modules/orphaned_toast_tables_check.py", line 123, in runCheck
    curs.execute(self.orphaned_toast_tables_query)
  File "/usr/lib64/python3.6/site-packages/psycopg2/extras.py", line 144, in execute
    return super(DictCursor, self).execute(query, vars)
psycopg2.DataError: invalid input syntax for type bigint: "pg_toast_80205947"  (seg0 slice9 XX.XX.XX.XX:6000 pid=1595831)

Cause

This issue was found in Greenplum DB 7.3.0.

The 

Resolution

Workaround

Edit the file $GPHOME/bin/gpcheckcat_modules/orphaned_toast_tables_check.py with the follwoing steps

• Backup the file:
  cp $GPHOME/bin/gpcheckcat_modules/orphaned_toast_tables_check.py $GPHOME/bin/gpcheckcat_modules/orphaned_toast_tables_check.py.original
• Edit the file with vi or other text editor
• Change the line 98 from
  ON REGEXP_REPLACE(tst.oid::regclass::text, 'pg_toast(_temp_\d+)?.pg_toast_', '')::bigint::regclass::oid = dbl.oid
  to
  ON REGEXP_REPLACE(tst.oid::regclass::text, '(pg_toast_temp_\d+.|pg_toast.)?pg_toast_', '')::bigint::regclass::oid = dbl.oid
• Save the file

Fix

This is expected to be fixed in 7.4.0 and above.