How to solve PostgreSQL index width problem-CodePudding

I have a docker container which contains a PostgreSQL database. An application then connects to the database. In the database I have a table defined as:

CREATE TABLE IF NOT EXISTS configuration (
    id SERIAL PRIMARY KEY,
    board BIGINT NOT NULL REFERENCES boards ( id ),
    date_time decimal(20,0) NOT NULL,
    version INTEGER NOT NULL,
    data TEXT NOT NULL,
    UNIQUE(board, date_time, version, data)
);

I am not explicitly creating any indices or any of the other meta-objects associated with this table.

The application used to be able to write to this table without problem, but now I am getting the following error:

Failure during 'insert_configuration_record': ERROR:  index row size 5992 exceeds btree version 4 maximum 2704 for index "configuration_board_date_time_version_data_key"
DETAIL:  Index row references tuple (2,12) in relation "configuration".
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.

It is possible that the PostgreSQL version changed at some point when I rebuilt the docker container, but I have not seen any messages refusing to load the database from the persistent storage or asking me to upgrade it. The current database version is (PostgreSQL) 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1).

It is possible that previously the data I was writing was short enough to not hit the limit.

How do I use "a function index of an MD5 hash of the value" to avoid this problem?

CodePudding user response：

If you had that unique constraint before with the same data, then you must have built PostgreSQL with a block size greater than the default 8kB.

Anyway, you should do that the hint tells you, and instead of the unique constraint create a unique index:

CREATE UNIQUE INDEX ON configuration (
   board,
   date_time,
   version,
   md5(data)
);

You cannot turn this index into a unique constraint, because such a constraint can only be defined on plain columns, not on expressions. However, the behavior will be just the same as a constraint.