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,
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@xxxxxxxxxxx> 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@xxxxxxxxxxx
