Обсуждение: temp table problem due to thread concurrency ( ERROR: could not open file "base/ ) ?

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

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.