The question:
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.
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
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