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

[Demitri Muna <postgresql@xxxxxxxxxxx>]

Hi Tom,

> On Dec 30, 2020, at 11:50 AM, Tom Lane <tgl@xxxxxxxxxxxxx> 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