Re: Could not read block of temporary files

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>