Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1afFD0-00087k-Bg for pgsql-sql@arkaria.postgresql.org; Sun, 13 Mar 2016 23:14:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1afFCz-0002IV-UX for pgsql-sql@arkaria.postgresql.org; Sun, 13 Mar 2016 23:14:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1afFCy-0002Gz-2y for pgsql-sql@postgresql.org; Sun, 13 Mar 2016 23:14:00 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1afFCu-0000iF-9y for pgsql-sql@postgresql.org; Sun, 13 Mar 2016 23:13:59 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id u2DNDqoa010340; Sun, 13 Mar 2016 19:13:52 -0400 From: Tom Lane To: Desmond Coertzen cc: pgsql-sql@postgresql.org Subject: Re: Subselect left join / not exists() In-reply-to: References: <32307.1456498805@sss.pgh.pa.us> Comments: In-reply-to Desmond Coertzen message dated "Tue, 01 Mar 2016 12:24:49 +0200" Date: Sun, 13 Mar 2016 19:13:52 -0400 Message-ID: <10339.1457910832@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org [ sorry for slow response ] Desmond Coertzen 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