I was under the impression that postgres will not allow me to declare a function immutable if it falls outside of the bounds of the definition. In example function sp_payment_iscash, the result would have been the same for every row with the same arguments, although that is because I willed it like that by design and not because it could not change through user action by manipulating the other tables references by the function.
I have other indexed immutable functions that references the table in the index and one more table. I will be taking a hard look at those as well.
The ability of postgres to index a function of this nature will be very much kick-ass if it could be supported in the future.
Thanks for your valuable input.
Regards.
On Mon, Mar 14, 2016 at 1:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
[ sorry for slow response ] Desmond Coertzen <patrolliekaptein@gmail.com> writes: > I cannot create this index on 9.3.11. I tried to recreate the index on > 9.3.11 after my restore of my live setup from 8.4.22.
> New detail in the output this time: > ERROR: could not read block 0 in file "base/28654/39611": read only 0 of > 8192 bytes
I think you are running into the same issue discussed in this thread:
namely that you are trying to create an index on an allegedly immutable function which, far from being immutable, actually attempts to consult the table that the index is on. That's never been considered supported, which is why not a lot of enthusiasm has been mustered for suppressing this weird error message. The error message is indeed annoying and confusing, but it's not like such an index could be expected to work usefully if we prevented the error during index build. In the example you've got here, not only is the function consulting the underlying table, but four other tables as well. Updates on any one of those could invalidate the result, but there's no mechanism to cause the index entries to be recomputed when some other table changes.
So in short, you really need to reconsider trying to use an index this way.