Index row size exceeds btree version for postgresql

Getting advice
1

I’ve changed value column type in user_attribute table to varchar (without size), because I need to store larger values. But now I received error from postgresql:

ERROR: index row size 2744 exceeds btree version 4 maximum 2704 for index "idx_user_attribute_name"

The definition of this index is:

CREATE INDEX idx_user_attribute_name ON public.user_attribute USING btree (name, value)

Can I do something with it?
I was looking into change index to use md5, but then I need to change all queries inside Keycloak. I found only one - in UserEntity.java:

 @NamedQuery(name="getRealmUsersByAttributeNameAndValue", query="select u from UserEntity u join u.attributes attr " +
                "where u.realmId = :realmId and attr.name = :name and attr.value = :value")

Any ideas?

2

Yes! You should change back the value column type to a varchar that has a fixed value that fits inside the index. This is the only way you can fix the behavior. And if you really need to put things there which exceed the value use a different table! You can customize your keycloak to keep in the value a reference to the actual value and then add the very large piece of data as a decorating step.

It is not straightforward but really I do not think you have a different option. Probably is not a good idea to drop that index, which would also fix your problem

1 Like