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

[Demitri Muna]

Hi Tom,

> On Dec 30, 2020, at 11:50 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> 
> I would call this a bug if it were a supported case, but really you are
> doing something you are not allowed to.  Functions in indexed expressions
> are required to be immutable, and a function that looks at the contents of
> a table --- particularly the very table that the index is on --- is simply
> not going to be that.  Marking such a function immutable to try to end-run
> around the restriction is unsafe.

Thank you, that makes perfect sense. In my mind it was immutable since the 
database is read-only, but I can see to PG it’s not. Can you suggest an 
alternate for what I’m trying to do? Given this schema (a “person” has a number 
of “events”):

CREATE TABLE person (
    id SERIAL,
    ...
);

CREATE TABLE event (
    id SERIAL,
    patient_id INTEGER
    event_timestamp TIMESTAMP,
    …
);

I have a function (the one I was trying to index) that returns the earliest 
event for a person. I’m scanning another table with ~10B rows several times 
using a few of these “constant” values:

* first_event_timestamp(person_id) + ‘1 month’
* first_event_timestamp(person_id) + ‘13 months’
* etc.

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?

Thanks again,
Demitri