Re: Subselect left join / not exists() - Mailing list pgsql-sql

From Desmond Coertzen
Subject Re: Subselect left join / not exists()
Date
Msg-id CALQ6=2Bs6K9Qe7NzOCUGGVjVSVszjy5L+wGPGA1a-COn0-8GhQ@mail.gmail.com
Whole thread Raw
In response to Re: Subselect left join / not exists()  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Subselect left join / not exists()
List pgsql-sql
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@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:

http://www.postgresql.org/message-id/flat/87tx0dc80x.fsf@news-spur.riddles.org.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

pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Subselect left join / not exists()
Next
From: Tom Lane
Date:
Subject: Re: Subselect left join / not exists()