werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly

Hi,

OK, first, I know the reason of this error "index row size 3040 exceeds btree maximum, 2712" and know that we cannot create index on certain columns with size larger than 1/3 buffer page size.

The question is, no matter if I deleted records that caused the problem or all records of the table, the error still occurred and would disappear after a while randomly, like 1 or 2 minutes or so.

Therefore I suspect if this is a bug or any postgresql internal mechanism I was not aware would lead to this problem?

See my test as below:

pgdb=# drop table test;
DROP TABLE
pgdb=# create table test as select * from tbl_weekly;
SELECT

pgdb=# create index idx_test on test USING btree (term) WITH (fillfactor=100) WHERE catid = 1;
ERROR: index row size 3040 exceeds btree maximum, 2712
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.

--because this is a TOAST table, so the size should be determined by pg_column_size(), not octet_length()

pgdb=# select length(term), pg_column_size(term),octet_length(term),catid from test where length(term)>=2000 order by 1;
length | pg_column_size | octet_length | catid
--------+----------------+--------------+-------
2088 | 1430 | 2088 | 80
2088 | 1430 | 2088 | 125
2088 | 1430 | 2088 | 1
2190 | 1450 | 2190 | 50
2190 | 1450 | 2190 | 1
2190 | 1450 | 2190 | 30
2205 | 1184 | 2205 | 80
2205 | 1184 | 2205 | 1
2205 | 1184 | 2205 | 100
2586 | 1894 | 2586 | 100
2586 | 1894 | 2586 | 80
2586 | 1894 | 2586 | 320
2586 | 1894 | 2586 | 1
5179 | 3028 | 5179 | 1
5179 | 3028 | 5179 | 125
5179 | 3028 | 5179 | 80
(16 rows)

--so the fix is to delete records with pg_column_size>2700, in this case, to delete records with pg_column_size=3028 (length=5179) and catid=1.
pgdb=# delete from test where length(term) =5179 and catid=1;
DELETE 1

pgdb=# select length(term), pg_column_size(term),octet_length(term),catid from test where length(term)>=2000 order by 1;
length | pg_column_size | octet_length | catid
--------+----------------+--------------+-------
2088 | 1430 | 2088 | 80
2088 | 1430 | 2088 | 1
2088 | 1430 | 2088 | 125
2190 | 1450 | 2190 | 1
2190 | 1450 | 2190 | 30
2190 | 1450 | 2190 | 50
2205 | 1184 | 2205 | 80
2205 | 1184 | 2205 | 1
2205 | 1184 | 2205 | 100
2586 | 1894 | 2586 | 80
2586 | 1894 | 2586 | 320
2586 | 1894 | 2586 | 100
2586 | 1894 | 2586 | 1
5179 | 3028 | 5179 | 125
5179 | 3028 | 5179 | 80
(15 rows)

--even deleted the record that cause the problem, the index creation SQL fail again

pgdb=# create index idx_test on test USING btree (term) WITH (fillfactor=100) WHERE catid = 1;
ERROR: index row size 3040 exceeds btree maximum, 2712
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.
pgdb=# vacuum full analyze test;
VACUUM
pgdb=# create index idx_test on test USING btree (term) WITH (fillfactor=100) WHERE catid = 1;
ERROR: index row size 3040 exceeds btree maximum, 2712
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.

-- After a while, run index creation sql again, it succeeded.
pgdb=# create index idx_test on test USING btree (term) WITH (fillfactor=100) WHERE catid = 1;
CREATE INDEX