but I still expect Postgres to save us from such data inconsistencies issues by using early binding for functional Indexes.
Well, if the functions you are writing are "black boxes" to PostgreSQL this expectation seems unreasonable. As of v14 at least you have the option to write a SQL standard function definition which is whose parsed expression and dependencies are saved instead of a black box of text.
However still there are cases where functional Indexes are created on extension functions (For Ex - cube extension) which are present in different schemas and then those cube functions are defined as invoker security type with nested functions calls without any schema qualification.
Right, because if you try doing that in a security definer context the lack of a schema qualification will provoke a function not found error due to the sanitized search_path (IIRC, if it doesn't then the two cases should behave identically...)
One way would be to define search_path for such nested functions.
Which the user is well advised to do, or, better, just schema-qualify all object references. This can get a bit annoying for operators, in which case an explicit, localized, search_path becomes more appealing.
The tools are available for one to protect themself. I suspect the historical baggage we carry prevents the server from being more aggressive in enforcing the use of these tools since not all cases where they are not used are problematic and there is lots of legacy code working just fine. The security lockdown for this dynamic has already happened by basically admitting that the idea of a "public" schema at the front of the default search_path was a poor decision. And while I see that there is possibly room for improvement here if desired, it is, for me, acceptable for the project to put the responsibility of not executing problematic code in the hands of the DBA.
I'm curious how "EXECUTE" command and dynamic SQL fit into your POV here (specifically in function bodies). Right now, with "black box inside of black box" mechanics it isn't really an issue but if you want to not keep function bodies as black boxes now dynamic SQL becomes the top-most late binding point.
