One possible solution for postgres expression comparsion #1488
Replies: 2 comments 3 replies
-
It might also contains dll operation in a single migration, so the explain migth fail due to column missing. Also, this might not work between different versions of postgres. |
Beta Was this translation helpful? Give feedback.
-
Hi, Thanks an interesting idea, I had not though of using explain. Another alternative is to create a dummy table to create a dummy index against, since there does not seem to be a way of defining an index as "not valid", so that can't be created against a live table (since that could take a lot of time). An alternative to a dummy table is a an empty materialized view @zzzeek did you ever think of other alternatives? |
Beta Was this translation helpful? Give feedback.
-
Issue
In postgres, people may create an expression index or check constraint. However the expression used in database is not exactly same as user's input. This doesn't affect actual usage at all, but has significant impact on expresion comparsion. There are always auto type cast and sometime even some transformation. This makes index and check constraint(may be in the future) comparasion
almost impossible.
releated issues: #1390, #1462
For example, if I have a table as follows:
And take a look at the expressions:
index:
(extra -> 'user'::text) ->> 'info'::text
constraint:
type::text = ANY (ARRAY['a'::character varying, 'b'::character varying, 'c'::character varying]::text[])
For index, some regex to remove unwanted type cast and space migth work. As for constraint? I want to scream!
Well, It's definitely impossible to compare expression in python(hope I'm wrong). So is it possible to solve it in database?
Solution
Actually creating one in database and read from result did work, but requires permission for ddl(maybe fine for autogenerate?)
Another possible way is to use
explain
The result is:
Then filter unwanted part, it becomes:
(extra -> 'user'::text) ->> 'info'::text
It's exactly the same!
And for constraint:
And result:
Well, this one doesn't wrok. But if we use this expression to create constraint instead of the original one:
And the expression in database becomes:
type::text = ANY ('{a,b,c}'::text[])
Everyhing looks fine!
Summary
Use explain to do the cast in postgres, so the expression could be same to compare. This shold be done before create statment and comparsion.
Have a nice day.
Beta Was this translation helpful? Give feedback.
All reactions