Hi All,
I've come upon some very strange behaviour with an UPDATE query which
causes Postgres to consume all the disk space on the server for no
apparent reason.
Basically, I'm trying to run an UPDATE involving three medium-sized
tables (~60k rows each), which generates a query plan with three Hash
Joins. But when I try to execute it, Postgres appears to go into some
kind of loop, gradually filling up the disk partition. After a long wait
it responds with "ERROR: could not write to hash-join temporary file: No
space left on device; SQL state: 53100"; the partition in question has
9.5GB free at the beginning of the query - that's a lot of hash file!
If I run "ANALYZE temp_fares_mappings;" - the table which is being
Updated, and is the outermost in the query plan - the problem goes away
*even though the Query Plan hasn't changed*.
I'm not entirely sure how to simplify the query and still reproduce the
issue, but I've produced an anonymized data sample and SQL query at
http://rwec.co.uk/x/break_postgres.zip In this case, "Analyze" *does*
change the query plan (as well as fixing the issue), but I can
consistently reproduce the disk-filling behaviour using this sample on
Postgres 9.0 and 9.2.
Note that it doesn't appear to matter if one or all of the tables are
permanent, as I have been using various combinations for debugging, and
always get the same behaviour. Trying to write a corresponding SELECT
statement doesn't seem to generate the same issue, at least with the
queries I've tried.
- The plan for the real query is here: http://explain.depesz.com/s/WTP
- Sample data and SQL query: http://rwec.co.uk/x/break_postgres.zip
- Query plan for sample data, without running Analyze, is here:
http://explain.depesz.com/s/qsH
- Postgres version strings: "PostgreSQL 9.0.5 on x86_64-pc-linux-gnu,
compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.5.2 p1.1, pie-0.4.5)
4.5.2, 64-bit" and "PostgreSQL 9.2.1 on x86_64-pc-linux-gnu, compiled by
x86_64-pc-linux-gnu-gcc (Gentoo Hardened 4.5.4 p1.0, pie-0.4.7) 4.5.4,
64-bit"
Does anyone have any idea what's going on here, and whether it is in
fact a bug? It certainly feels like one...
Thanks for your time.
--
<div style="color: black; font-family: 'Helvetica neue', Helvetica,
Arial, san-serif;font-size: 12px;">
<p style="font-size: 16px; font-weight: bold; margin: 2px 0;">
Rowan Collins
</p>
<p style="margin: 2px 0;">
Senior Developer <span style="color: #C0C0C0;">
|</span> CWT Digital
</p>
<p style="margin: 2px 0; line-height: 38px;">
<span style="color: #ff6600; font-weight: bold; font-size:
14px;">t.</span> 0845 456 0070
<span style="color: #ff6600; font-weight: bold; font-size: 14px;
margin-left: 5px;">w.</span> <a
href="http://www.cwtdigital.com/?utm_source=E-mail%2BSignature&utm_medium=E-mail&utm_campaign=E-mail%2BSignature">cwtdigital.com</a>
</p>
</div>