Change the definition of an Index with Expression without dropping/recreating the index

All we need is an easy explanation of the problem, so here it is.

I’m in a peculiar situation:

I have multiple indexes with expressions that use a function. I want to switch them to a different function, where I can guarantee the function behaves the same (so the indexed values will not be invalid). Ideally, I don’t want to drop the existing index and re-index them, because this requires more development overhead.

I can see in the pg_index table, there is an indexprs column, which contains the "internal" definition of an index (the pg_indexes table is a pretty-print version of this). Specifically, I think I identified the relevant indexes to have a format along the lines of ({FUNCEXPR :funcid 870 :funcresulttype 25 :funcretset false

Backtracking that funcid 870 into the information_schema.routines, I was able to correlate it on the specific_name (which contains this funcid) and was able to determine that it is indeed the specific function that I want to change.

Would it – theoretically – be possible to replace this funcid with the new funcid and the indexes would then refer to the new function? How would I do so, since this column is of type pg_node_tree? If not, is there a way to achieve my goal in a non-hacky way?

addendum: I will probably still do this the "clean" way unless there’s a proven safe way to do this, but this has piqued my interest enough to ask a question about it.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If you do it right, that would work, but I also wouldn’t do it that way.

But that is not enough: PostgreSQL also keeps track of the dependency between the index and the function by entries in pg_depend, so you would have to modify the respective entry as well. Otherwise you’d get interesting error messages when you try to drop the index. If the oid 870 happens to get reused for a different function later on, you could also get other interesting ways of data corruption.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply