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

On 9/27/15 5:32 PM, Tom Lane wrote:
> 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.

Not to mention the issue of what happens when someone updates tblcontrat
or tblagent. (It'd be cool if we had cross-table indexes, but this
certainly isn't how to do it...)

I am wondering if there's a practical way to restrict what relations can
be referenced by a query/transaction/subtrans. That would allow for
generating a better error here. It'd also make it possible to ignore
certain transactions in HeapTupleSatisfiesVacuum if such a restriction
was published. There's probably some other uses as well.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com