Postgres Pro
Downloads
Home   >   mailing lists

Thread: Could not read block of temporary files

Could not read block of temporary files

From
Rebecca Clarke
Date:
Hi all,

I'm creating a table from a select query. During the execution it errors with:

ERROR:  could not read block 13 of temporary file: Success

I am running Postgresql 9.1 on a Debian/Linux server.

Does anyone have any suggestions on what could be causing this?

I checked diskspace and permissions for the tablespace directory and pgsql_tmp directory and they are fine.

Many thanks

R Clarke

Re: Could not read block of temporary files

From
Adrian Klaver
Date:
On 03/25/2015 03:50 AM, Rebecca Clarke wrote:
> Hi all,
>
> I'm creating a table from a select query. During the execution it errors
> with:
>
> ERROR:  could not read block 13 of temporary file: Success

That is a mixed message.

Did the create table succeed?

Does it always happen?

Could you show the command in question?

>
> I am running Postgresql 9.1 on a Debian/Linux server.

What is the exact version, 9.1.x ?

>
> Does anyone have any suggestions on what could be causing this?

Not at this time.

>
> I checked diskspace and permissions for the tablespace directory and
> pgsql_tmp directory and they are fine.
>
> Many thanks
>
> R Clarke


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Could not read block of temporary files

From
R Clarke
Date:
I'm using Postgresql 9.1.15

It didn't succeed, which is why the message is confusing. The table was not created. It runs for about 10 minutes and then this error comes up.

I tried reindexing the tables but that didn't help.

This is the query:

create table lu_addresses as
SELECT
   distnct(l.id), 
   l.start_number,
   s.street,
   s.locality,
   b.postcode,
   case when o.organisation != '' then o.organisation || ', ' else '' end as organisation,
   b.the_geom,
   b.status,
FROM
   buildings AS b,
   streets AS s, 
   land AS l full outer join orgnisation AS o on (l.id = o.id)
WHERE b.id = l.id
AND l.id = s.id

I ended up cutting the query right down to basics and found the culprit to be the the_geom field which is a geometry datatype.

I had 8GB of memory and my postgres config set to:

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB

After running pgtune, specifying I had 7GB memory, I changed the config to:

shared_buffers = 1920MB
effective_cache_size = 5632MB 
work_mem = 48MB
maintenance_work_mem = 480MB 

I've rerun the query and now I no longer get the error.



On Wed, Mar 25, 2015 at 1:51 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 03/25/2015 03:50 AM, Rebecca Clarke wrote:
Hi all,

I'm creating a table from a select query. During the execution it errors
with:

ERROR:  could not read block 13 of temporary file: Success

 That is a mixed message.

Did the create table succeed?

Does it always happen?

Could you show the command in question?


I am running Postgresql 9.1 on a Debian/Linux server.

 What is the exact version, 9.1.x ?


Does anyone have any suggestions on what could be causing this?

 Not at this time.



I checked diskspace and permissions for the tablespace directory and
pgsql_tmp directory and they are fine.

Many thanks

R Clarke


--
Adrian Klaver
adrian.klaver@aklaver.com