Обсуждение: 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/ ) ?
От
"Jan-Peter Seifert"
Дата:
Hello,
a 'bulk loading' routine using temporary tables suddenly failed during tests for 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/...".
-Execute is used for executing the statements.
-Autocommit mode is used because of possible large load size.
-At the beginning of a load script another script drops all temporary tables - using a select on pg_tables for getting the table names.
-Then 'normal' temporary tables (with no 'ON COMMIT DROP') with a fixed name are created and filled via insert first - followed by several updates. One of those updates fails.
An strace revealed that another process dropped the temporary table in question before the failing update - most likely the drop script. This also happens when in the same transaction. Additionally we used ON COMMIT DROP and DISCARD TEMP and DISCARD PLANS instead of dropping all temporary tables. However, DISCARD TEMP obviously blocks the INSERT when within a transaction.
Removing the drop script and dropping the temporary tables created at the beginning of the load at the end seems to help or am I mistaken?
So is every 'JDBC' execute using its own thread/process? If so is there a way to force them to execute in succession and not in parallel/concurrently?
Could you tell me, please?
Thank you very much!
Best wishes,
Peter
Re: temp table problem due to thread concurrency ( ERROR: could not open file "base/ ) ?
От
David G Johnston
Дата:
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.