Re: Getting "could not read block" error when creating an index on a function.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Demitri Muna <postgresql(at)demitri(dot)com>
Cc: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>, pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Getting "could not read block" error when creating an index on a function.
Date: 2020-12-30 20:14:31
Message-ID: 2402314.1609359271@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> Am Wed, Dec 30, 2020 at 02:37:59PM -0500 schrieb Demitri Muna:
>> I want to index the results of these repeated, unchanging calculations to speed up other queries. Which mechanism would be best to do this? Create additional columns? Create another table?

> A materialized view ?

Yeah, or you might be able to do something with a before-insert-or-update
trigger that computes whatever desired value you want and fills it into a
derived column. Indexing that column then gives the same results as
indexing the derived expression; but it sidesteps the semantic problems
because the time of computation of the expression is well-defined, even
if it's not immutable.

You might try to avoid a handwritten trigger by defining a generated
column instead, but we insist that generation expressions be immutable
so it won't really work. (Of course, you could still lie about the
mutability of the expression, but I can't recommend that. Per Henry
Spencer's well-known dictum, "If you lie to the compiler, it will get its
revenge". He was speaking of C compilers, I suppose, but the principle
applies to database query optimizers too.)

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joao Miguel Ferreira 2020-12-31 12:20:52 plpgsql unit testing and code coverage
Previous Message Christophe Pettus 2020-12-30 19:49:32 Re: Getting "could not read block" error when creating an index on a function.