Postgres DB index row size error on search_index_item description column index

[roytan883]

Describe the bug

QueryFailedError: index row size 3208 exceeds btree version 4 maximum 2704 for index "IDX_9a5a6a556f75c4ac7bfdd03410"


it is: "IDX_9a5a6a556f75c4ac7bfdd03410" ON search_index_item (description);

To Reproduce Steps to reproduce the behavior:

1. add a long text on product description
2. Click on admin ui re-build index
3. fail
4. See error

Expected behavior A clear and concise description of what you expected to happen.

Environment (please complete the following information):

• @vendure/core version: 1.6..4
• Database (mysql/postgres etc): postgres

[michaelbromley]

add a long text on product description

Can you give an example of a description string which causes this error?

[roytan883]

like this description is for one product, it just contains some Images. After adding about 10 product, the error come.

<p>
<img src="https://img12.xxxxxx.com/imgzone/jfs/t1/83178/40/20452/528509/62bbe1b5Eb6a1c188/4cda1f3ef9960476.jpg" alt="" title="">
<img src="https://img13.xxxxxx.com/imgzone/jfs/t1/186613/28/25713/511778/62bbe1b8E40fdc88d/79acc0472abc8810.jpg" alt="" title="">
<img src="https://img14.xxxxxx.com/imgzone/jfs/t1/199204/35/24628/509435/62bbe1b9E8a7ed9d2/bbc238aa216af621.jpg" alt="" title="">
<img src="https://img30.xxxxxx.com/imgzone/jfs/t1/50667/6/19532/511156/62bbe1bcE2a60dfde/0db446db46df01c6.jpg" alt="" title="">
<img src="https://img12.xxxxxx.com/imgzone/jfs/t1/86308/35/29780/747322/62bbe1bfE05938bd8/b5857689c0b7e1c4.jpg" alt="" title="">
<img src="https://img11.xxxxxx.com/imgzone/jfs/t1/126418/39/26001/482153/62bbe1c1E0ce32f96/36161b289fbd9ab5.jpg" alt="" title="">
<img src="https://img20.xxxxxx.com/imgzone/jfs/t1/86728/39/28009/866675/62bbe1c4Eba254e55/88f5f4ce8869a15f.jpg" alt="" title="">
<img src="https://img13.xxxxxx.com/imgzone/jfs/t1/94080/17/30719/839711/62bbe1c8Ee0963b8b/4577de65e2e9bb3c.jpg" alt="" title="">
<img src="https://img11.xxxxxx.com/imgzone/jfs/t1/186202/24/25776/87033/62bbe1c8E35c2a595/71bf171fbc662f56.jpg" alt="" title="">
</p>

[michaelbromley]

Why are you storing HTML in the product description? The description is intended to store the name of the product, like "T-shirt".

[roytan883]

When create a production on admin-ui. The description components is a rich text editor. It can insert images.

BTW, i think description should not be indexed.

[roytan883]

I don't write HTML to description, the rich text editor auto did that.

[michaelbromley]

Oh sorry, I'm mistaken, I was thinking of the name property.

[roytan883]

I think "Product name" should be short. But "description" can be very long, even it is not HTML, just text only, it can be very long.

[roytan883]

After i delete some products long "description", the reindex works. So this bug is confirm, i think.

[michaelbromley]

Yes, you are correct. What version of Postgres? And is it running on some cloud hosted Postgres instance or just a regular Postgres instance?

[roytan883]

just a regular Postgres: PostgreSQL (ver. 14.4 (Ubuntu 14.4-1.pgdg18.04+1))

[roytan883]

I think "description" can be index by another tsvector column, it self should not be indexed.

[roytan883]

Btw, i modified node_modules/@vendure/core/dist/plugin/default-search-plugin/search-strategy/postgres-search-strategy.js And remove this line : ts_rank_cd(to_tsvector(${minIfGrouped('si.description')}), to_tsquery(:term)) * 1) In my case, most of "description" are Images. search description is no sense. And create index for "description" of Images HTML also is no sense.

[roytan883]

Normal btree index are used for equal compare, so create normal btree index on "description" i think is no sense.

The user input search key fully match "description" is so rare. If the product is real, its "description" should not just one or two words,very few words on "description" only happened when testing. So in real world shop case, the match rate is 0.00001 %

[michaelbromley]

I've spent a bit of time looking into this today.

First of all, I cannot reproduce this locally, and I'm not sure why.

I added a very long description to a product (71,330 bytes long) and had no issue updating the search index and searching. I'm not sure whether this is related to the postgres config?

I looked further into the topic of full-text searching and indexing in postgres and came across this very good article: Indexing for full text search in PostgreSQL. My take-away is that there are better ways to index the data for postgres - namely creating explicit ts vector indexes by pre-computing the tsvectors rather than converting them at search-time as the current implementation does.

However, there are a couple of issues here:

1. I don't want to introduce a breaking change to the behaviour of the search, unless this is a big problem for lots of people. So far I've not heard any other complaints about this.
2. The DefaultSearchPlugin is intentionally basic, and also needs to be generic enough to work across all supported databases. So postgres-specific special indexes are tough to fit into that constraint.
3. If you want remove the index from your project, you can now use the EntityMetadataModifier API to do so in a safe manner.

For these reasons I'm going to leave this issue for now, and re-consider it at a later point or if further reports come in.

[roytan883]

@michaelbromley Thanks for your reply. Right now I manually create vector indexes and using trigger to update them, and it works good. For this bug, i just manually remove the "description" index and use my vector indexes instead. Later i will try to use EntityMetadataModifier API