Horton
Menu

Thread

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

    Vincent Veyron <vv.lists@wanadoo.fr> — 2015-09-27T17:16:14Z

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


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

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

    Jim Nasby <jim.nasby@bluetreble.com> — 2015-09-27T19:31:35Z

    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

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2015-09-27T21:34:24Z

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

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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2015-09-27T22:32:01Z

    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

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

    Jim Nasby <jim.nasby@bluetreble.com> — 2015-09-28T01:13:17Z

    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

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

    Vincent Veyron <vv.lists@wanadoo.fr> — 2015-09-28T16:10:06Z

    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

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

    Vincent Veyron <vv.lists@wanadoo.fr> — 2015-09-28T16:15:55Z

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