agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Desmond Coertzen <patrolliekaptein@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Subselect left join / not exists()
Date: Sun, 13 Mar 2016 19:13:52 -0400
Message-ID: <10339.1457910832@sss.pgh.pa.us> (raw)
In-Reply-To: <CALQ6=2Cu_pROq6AAw5HZ8Mkon1B6oNPRsap6khpNS2AUAxV7wQ@mail.gmail.com>
References: <CALQ6=2BRu5P5=u5RE8su_JQhJBj+1b-oSMbuq97d40M4C6iwgQ@mail.gmail.com>
<32307.1456498805@sss.pgh.pa.us>
<CALQ6=2AkuawZW=6BvGwj3tJn=rHdtuvgv0WA6LDAU5BRp0ssng@mail.gmail.com>
<CALQ6=2Cu_pROq6AAw5HZ8Mkon1B6oNPRsap6khpNS2AUAxV7wQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
[ 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (10+ messages) latest in thread
Message-ID: <10339.1457910832@sss.pgh.pa.us>
Permalink: ../10339.1457910832@sss.pgh.pa.us/
Also on: postgresql.org/message-id/10339.1457910832@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, patrolliekaptein@gmail.com
Subject: Re: Subselect left join / not exists()
In-Reply-To: <10339.1457910832@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox