Re: Subselect left join / not exists()

I get it.

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> [ sorry for slow response ]
>
> Desmond Coertzen <patrolliekaptein(at)gmail(dot)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:
>
>
> http://www.postgresql.org/message-id/flat/87tx0dc80x(dot)fsf(at)news-spur(dot)riddles(dot)org(dot)uk
>
> 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.
>
> regards, tom lane
>