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

Demitri Muna <postgresql@demitri.com> writes:

I’m getting stuck on a problem I was hoping I could get some help with. I’m trying to create an index for the results of a function that touches two tables like this and get the following error:

CREATE INDEX my_idx ON mytable (first_time(id));
ERROR: could not read block 0 in file "base/16386/42868": read only 0 of 8192 bytes
CONTEXT: SQL function "first_time" during startup

What's apparently happening is that some query in the function is trying
to examine the newly-created index before it's been populated.

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.

regards, tom lane

Hi Tom,

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

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 ?

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On Dec 30, 2020, at 11:37, Demitri Muna <postgresql@demitri.com> wrote:
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?

This might be a good use for a generated column.

https://www.postgresql.org/docs/current/ddl-generated-columns.html
--
-- Christophe Pettus
xof@thebuild.com

On Dec 30, 2020, at 11:48, Christophe Pettus <xof@thebuild.com> wrote:

This might be a good use for a generated column.

https://www.postgresql.org/docs/current/ddl-generated-columns.html

I take that back; the generation formula has to be immutable as well. Perhaps a column populated by a trigger?
--
-- Christophe Pettus
xof@thebuild.com

Karsten Hilbert <Karsten.Hilbert@gmx.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

Thank you for the responses! I was going to go with a materialized view, but then realized that since the dataset is static it’s really no different from just creating a new table and indexing that. The suggestions provide useful advice for the future though.

Cheers,
Demitri