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.kla...@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.kla...@aklaver.com >
