Question 113688 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Create index error "could not read block 0"

So I got a super weird problem here. I spun up a new server to restore a backup (tried taking a new snapshot and restoring a backup few times since original server is doing well).

create index on companies ((linkedin_name(company url)));

this works on the original server (pgsql 14). on the new server (pgsql 15) I get this error

could not execute query: ERROR:  could not read block 0 in file "base/16387/8646581": read only 0 of 8192 bytes

this is where it gets weird. if I do this, it works fine

create table companies2 as select * from companies;
create index on companies2 ((linkedin_name(company url)));

this works just fine… THEN… and this is where it gets REALLY weird.

drop table companies;
alter table companies2 rename to companies;

this shows the index but if I try to reindex I now get the same error!!

… I don’t even know where to begin to debug this. Thoughts?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So, I figured it out. The function referenced the table I wanted to create the index on and apparently that's a bad thing.


  2. 0

    The cause is that you are lying to the database. You declared the function linkedin_name() as IMMUTABLE, when clearly it isn’t. If you use that function in an index definition, you will end up with data corruption, as evidenced by your problem.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search