Indexing error when sending large payload content beyond Postgres Include design

[yyylly]

DB VERSION: PGSQL 13

index row size 2744 exceeds btree version 4 maximum 2704 for index "idx_received_Version_ExpiresAt_StatusName"

DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
at DotNetCore.CAP.PostgreSql.DbConnectionExtensions.ExecuteNonQueryAsync(DbConnection connection, String sql, DbTransaction transaction, Object[] sqlParams)
at DotNetCore.CAP.PostgreSql.DbConnectionExtensions.ExecuteNonQueryAsync(DbConnection connection, String sql, DbTransaction transaction, Object[] sqlParams)
at DotNetCore.CAP.PostgreSql.PostgreSqlDataStorage.StoreReceivedMessage(Object[] sqlParams)
at DotNetCore.CAP.PostgreSql.PostgreSqlDataStorage.StoreReceivedMessage(Object[] sqlParams)
at DotNetCore.CAP.PostgreSql.PostgreSqlDataStorage.StoreReceivedMessageAsync(String name, String group, Message message)
at DotNetCore.CAP.Internal.ConsumerRegister.<>c__DisplayClass22_0.<b__0>d.MoveNext()
Exception data:
Severity: ERROR
SqlState: 54000
MessageText: index row size 2744 exceeds btree version 4 maximum 2704 for index "idx_received_Version_ExpiresAt_StatusName"
Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
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.
SchemaName: rfr_cap
TableName: received
ConstraintName: idx_received_Version_ExpiresAt_StatusName
File: nbtutils.c
Line: 2677
Routine: _bt_check_third_page

received表大约有140w条数据

[yyylly]

CREATE INDEX IF NOT EXISTS "idx_received_Version_ExpiresAt_StatusName" ON "rfr_cap"."received" ("Version","ExpiresAt","StatusName") INCLUDE ("Id", "Content", "Retries", "Added")。

这是启动cap创建索引的语句，此时已经报错。

[yang-xiaodong]

see https://www.google.com/search?q=index+row+size+2744+exceeds+btree+version+4+maximum

[yyylly]

INCLUDE Content，把消息内容都构建到索引里面，虽然能有效提升查询，但content难以预料。或者索引创建参考这个？https://github.com/orgs/supabase/discussions/14898

[malcolby2333]

@yyylly @yang-xiaodong

We're also experiencing this issue, and it looks like it's caused by the Content being too long. I also checked the historical version of CAP this is the first time the index has been added, is it overly complicated? Is it possible to tweak the index to get around this issue.

[yang-xiaodong]

For your use case where the Content field needs to exceed the 2704-byte limit, I’m curious why you wouldn’t simply edit the index to remove the Content field from the INCLUDE clause. Leaving a comment here doesn’t solve the problem.

[malcolby2333]

@yang-xiaodong

Of course we can modify this index, I would say that since more than one person is experiencing the same problem as me, have you thought about tweaking the index, if there is a performance issue previous versions did not have any indexes why insist on adding such a complex index? Have you considered removing the Content field from the default index? As a CAP user, since the CAP document itself does not contain a length limit for Content, why not adjust the index?

[yyylly]

附议。建议直接移除，避免其他用户重复发生此类问题，然后还需手动处理。

[malcolby2333]

@yang-xiaodong What one are you not willing to adjust this stupid and harmful Index?

[hilken73]

Hi all
I have the same Problem. I simply deleted the indexes and all works. But you should really provide a solution without a need to do that. Why include a Content!! Row in an complex index?? Sorry guys but this will quick go over. Depending how much content i want deliver. If this is really needed, then you shoould also not allow to put more conent in the field and send already at sending a apropriate message. But then CAP get uslsess for bigger data packages. So please provide a fix.