search

JasperFx / weasel

Database Development Made Easy for .Net
MIT License
65 stars 19 forks source link

IndexDefinition doesn't represent index_ops expression correctly and has a wrong representation of "Mask" and "Columns" #28

Closed rwasef1830 closed 2 years ago

rwasef1830 commented 2 years ago

Hello, IndexDefinition.Parse doesn't handle jsonb_path_ops keyword correctly and consumes it as part of the column name. This causes Marten to think the jsonb_path_ops Index changed and makes it try to drop/recreate Index every time the storage is "ensured". This furthermore triggers https://github.com/JasperFx/marten/issues/960 when this drop statement is generated in the middle of a transaction.

rwasef1830 commented 2 years ago

Digging deeper, there's a bigger issue. The current parser makes a wrong assumption that the "Mask" is per index definition when in reality it is per-column in the expression.

This is a valid definition:

CREATE INDEX idx_1 ON public.mt_doc_user USING gin (data jsonb_path_ops, data2 jsonb_ops);

It actually can't parse multi-column indices properly. Fixing this properly will lead to breaking change in the API (change of the Columns and Mask properties).

rwasef1830 commented 2 years ago

Actually, it can work due to a happy coincidence that having the column as "data jsonb_path_ops" gives the same DDL as "data" with mask "? jsonb_path_ops". So as a workaround users can specify the index ops right after the column name when defining the index in code and it should work until this bug is fixed. Properly fixing this bug is a breaking change.

rwasef1830 commented 2 years ago

My issue is actually due to bug #30