On 09/27/2015 10:16 AM, Vincent Veyron wrote:
> Hi,
>
> I'm getting the following error when restoring my database from a dump file in postgresql 9.4
>
> --------------
> psql:tmp/mydb.out:159861: ERREUR: n'a pas pu lire le bloc 0 du fichier « base/28903/29447 » : a lu seulement 0
octets
> sur 8192
> CONTEXTE : fonction SQL « dossier_contrat » lors du lancement
> --------------
>
> In English : ERROR: could not read block 0 from file « base/28903/29447 » : 0 bytes read out of 8192
>
> "dossier_contrat" is a simple sql function (definition below) used in an index, whose creation always fails when
restoringfrom 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?
What are your pg_dump/pg_restore commands?
Are you using the 9.4 version of pg_dump to dump the 9.1 server or the
9.1 version?
Hmm, just had a thought. Wonder if it is a dependency issue with what
dossier_contrat expects to see loaded prior to its creation?
If it is plain text dump you could see by searching through the file. If
it is the custom format then pg_restore -l will list the contents in order.
>
>
> -------------------
> 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$
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com