Thread: ERROR: could not write block 196261 of temporary file: No space left

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);

"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