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?
TDM Portal 126.96.36.199
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
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>));