Restore fails with error - Invalid created PGSQL_TABLE_TYPE table

Environment

• YugabyteDB - YSQL

Fixed in below versions

• > 2.20.8.0
• > 2024.1.4.0
• > 2024.2.0.0
• > 2.25.0.0

Issue

The restore task fails with the following error: Invalid created PGSQL_TABLE_TYPE table. This error can be found in the yb-controller logs.

Sample Error:

I1107 09:38:49.990936  1146 rpc_endpoint.h:60] Invalid created PGSQL_TABLE_TYPE table 'test_idx' in namespace id 00004500000030008000000000000000: schema={Schema [
    0:test_id[int64 NOT NULL HASH],
    1:flow[string NOT NULL RANGE_ASC_NULL_LAST],
    2:ybidxbasectid[binary NOT NULL RANGE_ASC_NULL_FIRST]
]

Additional Information:

This error occurs when column names in a table are renamed. If you alter a column on the main table, columns on the index are not automatically updated to reflect changes in the main table. This behaviour is consistent with PostgreSQL.

Resolution

Step 1- Identify all affected indexes:

• Use the following query to list indexes affected by column renaming. The output will display the index name, affected column, and column definition.

SELECT a.attrelid::regclass AS indexname, 
       a.attname AS column,
       pg_catalog.pg_get_indexdef(i.indexrelid, a.attnum, TRUE) AS definition
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_index i ON a.attrelid = i.indexrelid
WHERE a.attnum > 0
AND NOT a.attisdropped
AND a.attname <> pg_catalog.pg_get_indexdef(i.indexrelid, a.attnum, TRUE)
ORDER BY a.attnum;

Sample Output:

indexname         | column |    definition
------------------+--------+-------------------
t1_id_idx         | id     | uuid
demo_upper_code   | upper  | upper(code::text)

• In the output above, the index t1_id_idx is affected because the column id was renamed to uuid. Although demo_upper_code appears in the results, it is not actually affected, as the upper column remains consistent in both the main table and the index. It is just that the column name and index definition are actually different.

Step 2- Generate CREATE INDEX and DROP INDEX statements for affected indexes:

• Enable expanded mode in ysqlsh (\x) and execute the following query to retrieve the CREATE INDEX and DROP INDEX statements for the affected indexes.

SELECT indexrelid::regclass AS indexname,
       'DROP INDEX ' || indexrelid::regclass || ';' AS drop_index_statement
       pg_catalog.pg_get_indexdef(indexrelid) || ';' AS create_index_statement
FROM pg_catalog.pg_index
WHERE indexrelid IN ('t1_id_idx'::regclass); -- Add affected index names here

Sample Output:

-[ RECORD 1 ]----------+-----------------------------------------------------------
indexname              | t1_id_idx
drop_index_statement   | DROP INDEX t1_id_idx;
create_index_statement | CREATE INDEX t1_id_idx ON public.t1 USING lsm (uuid HASH);\

Step 3- Drop and recreate the affected indexes:

Use the DROP INDEX and CREATE INDEX statements provided by the query output to remove and then recreate the affected indexes.