pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
ERROR: could not write block 196261 of temporary file: No space left 2+ messages / 2 participants [nested] [flat]
* ERROR: could not write block 196261 of temporary file: No space left @ 2006-09-20 22:00 TJ O'Donnell <tjo@acm.org> 0 siblings, 1 reply; 2+ messages in thread From: TJ O'Donnell @ 2006-09-20 22:00 UTC (permalink / raw) To: pgsql-sql I got the following error during a psql session of a big transaction filling a temporary table, then selecting distinct values from it for insertion into a permanent table. CREATE TABLE CREATE TABLE ERROR: could not write block 196261 of temporary file: No space left on device HINT: Perhaps out of disk space? UPDATE 0 I can't tell if this was because I ran out of real disk space, or because of some disk space limit set in pg config files. The log file contains no extra information. After this failure the real disk space was not full, of course because the transaction died and apparently any used disk space got cleaned up. I also can't tell if the error came from the Copy or the Insert. Can someone help me figure this out? Here is the SQL. Create Table fragment_table (smarts character varying, frequency integer); Create Temporary Table temp_table (smarts character varying); Copy temp_table (smarts) from stdin; (millions of strings here generated programatically) \. Insert into fragment_table (smarts) Select distinct smarts from temp_table where gnova.oe_valid(smarts) and gnova.matches(gnova.cansmiles(smarts),smarts); Update fragment_table set frequency = (select count(smiles) from input_set where smiles is not null and gnova.matches(smiles,smarts) group by smarts); ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ERROR: could not write block 196261 of temporary file: No space left @ 2006-09-20 22:31 Tom Lane <tgl@sss.pgh.pa.us> parent: TJ O'Donnell <tjo@acm.org> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2006-09-20 22:31 UTC (permalink / raw) To: TJ O'Donnell <tjo@acm.org>; +Cc: pgsql-sql "TJ O'Donnell" <tjo@acm.org> writes: > ERROR: could not write block 196261 of temporary file: No space left > on device > I can't tell if this was because I ran out of real disk space, > or because of some disk space limit set in pg config files. There is no "disk space limit in pg config files". Depending on your platform, that error could represent a per-user disk quota limit being enforced against the postmaster rather than genuine disk-full, but whatever the limit was it's at the OS level not Postgres. > I also can't tell if the error came from the Copy or the Insert. Since it says "temporary file", it's not talking about insertion of data into a real table (even a temporary one). It's talking about genuinely temporary intra-query workspace. I'd venture that the error comes from the SELECT DISTINCT step trying to write a sort temp file that there's not room for. (The fact that you see UPDATE 0 right after that is further evidence for this theory --- after the failure, there's nothing in fragment_table, so the UPDATE just falls through.) regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-09-20 22:31 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-09-20 22:00 ERROR: could not write block 196261 of temporary file: No space left TJ O'Donnell <tjo@acm.org> 2006-09-20 22:31 ` Re: ERROR: could not write block 196261 of temporary file: No space left Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox