Thread: temp table problem due to thread concurrency ( ERROR: could not open file "base/ ) ?
temp table problem due to thread concurrency ( ERROR: could not open file "base/ ) ?
From
"Jan-Peter Seifert"
Date:
<div style="font-family: Verdana;font-size: 12.0px;"><div class="signature">Hello,</div><div class="signature"> </div><divclass="signature">a 'bulk loading' routine using temporary tables suddenly failed during testsfor no obvious reasons. At some point one of the updates on the temporary table fails with the error message "ERROR: could not open file "base/...".</div><div class="signature"> </div><div class="signature">-Execute is used for executingthe statements.</div><div class="signature">-Autocommit mode is used because of possible large load size.</div><divclass="signature">-At the beginning of a load script another script drops all temporary tables - using a selecton pg_tables for getting the table names.</div><div class="signature">-Then 'normal' temporary tables (with no 'ONCOMMIT DROP') with a fixed name are created and filled via insert first - followed by several updates. One of those updatesfails.</div><div class="signature"> </div><div class="signature">An strace revealed that another process dropped thetemporary table in question before the failing update - most likely the drop script. This also happens when in the sametransaction. Additionally we used ON COMMIT DROP and DISCARD TEMP and DISCARD PLANS instead of dropping all temporarytables. However, DISCARD TEMP obviously blocks the INSERT when within a transaction.</div><div class="signature"> </div><divclass="signature">Removing the drop script and dropping the temporary tables created at thebeginning of the load at the end seems to help or am I mistaken?</div><div class="signature"> </div><div class="signature">Sois every 'JDBC' execute using its own thread/process? If so is there a way to force them to execute insuccession and not in parallel/concurrently?</div><div class="signature"> </div><div class="signature">Could you tell me,please?</div><div class="signature"> </div><div class="signature">Thank you very much!</div><div class="signature"> </div><divclass="signature">Best wishes,</div><div class="signature"> </div><div class="signature">Peter</div><divclass="signature"> </div><div class="signature"> </div><div class="signature"> </div><divclass="signature"> </div><div class="signature"> </div></div>
Re: temp table problem due to thread concurrency ( ERROR: could not open file "base/ ) ?
From
David G Johnston
Date:
Why cannot you just: Open Connection Begin; Create TEMP Tables Do stuff with TEMP Tables Commit; Close Connection (auto-drops TEMP tables) Also, figuring out if you code is broken is much easier if we can actually see the code; describing it only in plain English doesn't usually result in people providing useful answers. Both code and description are usually needed. Temporary tables are session-local and so as long as you do not actively share a connection dropping temporary tables and these kinds of concurrency errors should be impossible. Since you are seeing them, and you feel the need to drop temporary tables, either you are doing things wrong or at minimum doing unnecessary work. Server processes (PostgreSQL is process, not thread, based) are connection (and thus session) oriented. Client threads/processes are whatever you tell your code to do; connections themselves are not inherently thread-safe so if you are multi-threading and sharing connections you are doing something highly discouraged and, given the errors, are doing so incorrectly. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/temp-table-problem-due-to-thread-concurrency-ERROR-could-not-open-file-base-tp5802963p5803003.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.