TDM Portal - PostgreSQL masking job failed with SQLSTATE: 54000, Error code: 0

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

While attempting to mask a PostgreSQL table using TDM Portal and we are seeing one of our masking jobs fail with the following error:

SQLSTATE: 54000,  Error code: 0,  Message: ERROR: index row size 2768 exceeds btree version 4 maximum 2704 for index <table_name_idx>
  Detail: Index row references tuple (1708,20) in relation <table_name>.
  Hint: Values larger than 1/3 of a buffer page cannot be indexed.
  Consider a function index of an MD5 hash of the value, or use full text indexing.

Could someone please help us understand what we should do to correct this issue?

Environment

TDM Portal 4.10.221.0
FDM 4.10.535.0
PostgreSQL-42.5.0 driver

Cause

There were changes made to indexing in PostgreSQL v12.

"In new btree indexes, the maximum index entry length is reduced by eight bytes, to improve handling of duplicate entries (Peter Geoghegan). This means that a REINDEX operation on an index pg_upgrade'd from a previous release could potentially fail." For more information, see the PostgreSQLv12 documentation

 

Resolution

We recommend working with your Postgres DBA to investigate the index in question and recreate the index using MD5, as the PostgreSQL drive recommends.

Have your DBA  review the index to understand why the index is failing. Is it indexing a large number of columns, or is it indexing a column that has a large number of data for each row?

The Postgres driver is providing some hints to create an index based on the hash value of the data rather than the data itself to reduce the likelihood of running out of range

For example: CREATE INDEX <your_idx> ON <your_table> (MD5(<your_column>));