Re: Subselect left join / not exists()

[ 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