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

On 9/27/15 12:16 PM, Vincent Veyron wrote:
> In English : ERROR: could not read block 0 from file « base/28903/29447 » : 0 bytes read out of 8192

What relation is that? (SELECT oid::regclass FROM pg_class WHERE
relfilenode=29447 in the appropriate database)

> "dossier_contrat" is a simple sql function (definition below) used in an index, whose creation always fails when restoring from a dump file (on different machines),
>
> 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.

> -------------------
> Function definition:
>
> \sf+ dossier_contrat
> CREATE OR REPLACE FUNCTION public.dossier_contrat(integer)
> RETURNS integer
> LANGUAGE sql
> IMMUTABLE
> 1 AS $function$
> 2 -- renvoie l'id du contrat couvrant un dossier statutaire
> 3 SELECT t1.id_contrat
> 4 FROM tblcontrat t1 INNER JOIN ( tblagent t2 INNER JOIN tbldossier t3 using (id_agent) ) ON t1.id_collectivite = t2.id_collectivite AND t1.id_caisse_retraite = substring(t2.id_affiliation FROM 6 FOR 3)
> 5 WHERE t3.date_origine BETWEEN t1.date_debut_garantie AND t1.date_fin_garantie AND t3.id_dossier = $1
> 6 $function$

--
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