Re: error on CREATE INDEX when restoring from dump file: could not read block 0

Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
>> I can create the index in psql with :
>> CREATE INDEX tbldossier_id_contrat_idx ON tbldossier USING btree (dossier_contrat(id_dossier));
>> but subsequent dumps/restores will always fail on it (only in 9.4, it works fine in 9.1).
>> What should I be looking for to find the cause of the error?

> This will never work well. You're taking a function that is only STABLE
> and falsely marking it as IMMUTABLE. There may be some other underlying
> issue causing the read error though.

Yeah. I think this is a variant of the symptom discussed in
http://www.postgresql.org/message-id/flat/87tx0dc80x(dot)fsf(at)news-spur(dot)riddles(dot)org(dot)uk

namely that planning for the function's internal access to tbldossier
tries to access the not-quite-valid-yet index.

I would be more excited about fixing this if the cases that had come up
didn't involve index definitions that were broken on their face. In this
example the index entries would depend on entries in not just one but
*three* tables, for none of which could the index possibly get updated
correctly when rows other than the row that PG thinks the index entry is
for get updated.

As an example, even if we stopped this error from occurring, there would
be no guarantee that a restore from pg_dump would populate the index
usefully, since pg_dump could have no idea that the other two tables need
to be populated before building this index.

regards, tom lane