Обсуждение: error on CREATE INDEX when restoring from dump file: could not read block 0

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?


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


--
                    Bien à vous, Vincent Veyron

https://legalcase.libremen.com/
Legal case, contract and insurance claim management software

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

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

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

Jim Nasby <Jim.Nasby@BlueTreble.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.fsf@news-spur.riddles.org.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

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

On Sun, 27 Sep 2015 14:34:24 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>

Hi Adrian

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

Combinations of all these, always the same result.


> Hmm, just had a thought. Wonder if it is a dependency issue with what
> dossier_contrat expects to see loaded prior to its creation?

Yes, apparently due to a change since 9.3, explained by Tom here :


http://www.postgresql.org/message-id/flat/87tx0dc80x.fsf@news-spur.riddles.org.uk#87tx0dc80x.fsf@news-spur.riddles.org.uk


--
                    Bien à vous, Vincent Veyron

https://marica.fr/
Gestion des contentieux, des dossiers de sinistres assurance et des contrats pour le service juridique

On Sun, 27 Sep 2015 20:13:17 -0500
Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:

Hi Jim, Tom

>
> 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 have checks in the application logic to prevent changes in those tables that would invalidate the index.

Tom explained my problem here:
http://www.postgresql.org/message-id/flat/87tx0dc80x.fsf@news-spur.riddles.org.uk

I guess I'll have to live without it for now; the speed increase in queries is nice, but not humanly noticeable as the
tablesare not huge. 

I can resort to a denormalized field holding the value of id_contrat. I was trying to avoid that, hence the calculated
index,but it appears not to be much better. 



--
                    Bien à vous, Vincent Veyron

https://legalcase.libremen.com/
Legal case, contract and insurance claim management software