Postgres Pro
Downloads
Home   >   mailing lists

Re: Could not read block of temporary files - Mailing list pgsql-general

From R Clarke
Subject Re: Could not read block of temporary files
Date
Msg-id CAMChtdd041Hm0Bs78R4xTEhJ5oosFaKVLhV+FZ4ntgUuk=uHWg@mail.gmail.com
Whole thread Raw
In response to Re: Could not read block of temporary files  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Jacobo Vazquez
Date:
Subject: SSPI authentication ASC_REQ_REPLAY_DETECT flag
Next
From: "David G. Johnston"
Date:
Subject: Re: Populating missing dates in postgresql data