Create index error "could not read block 0"-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.