I want to create temp PostgreSQL table each time on client http request to my Rails 7.1 server or in per one background sidekiq job.
I have active record connection to my postgresql 15 database through pgbouncer (pool_mode = transaction).
I know that temp table are dropping when connection is closing.
It's safe if i first create temp table by using: MyModel.connection.execute 'create temp table tmp_test ...'
, then do some stuff (after 1 minute maybe?) and use second execute by MyModel.connection.execute 'insert into tmp_test values ...'
. My temp table will be still exists?
I'm little confusing that temp table are dropping if i took (another?) connection when i will be using second execute statement
What you plan to do is safe as long as you hold on to the connection you're issuing these subsequent queries in - in
pool_mode=transaction
you can do so by starting your work with temps by issuing abegin;
and ending with acommit;
. Make sure to create the temporary objects withon commit drop
unless your pool candiscard
them upon connection release. Pgbouncer does that by default.Temp objects are visible and accessible exclusively by the session/connection that created them, living in their private
pg_temp_NNN
schema. If you acquire a connection,create temp table
, release the connection and ask for another one, you're not guaranteed to get the same connection back, with that temp object in it. Without wrapping your work inbegin;...commit;
, pgbouncer will assume each statement can be run in its own transaction, so each time it's free to swap out your connection for a different one, same as if you were inpool_mode=statement
(autocommit behaviour).Depending on the pool, it may or may not be actually dropped, but you may lose sight of it immediately after creating because if your initial connection got released and then you got a new one, the temp is still in the old one. Concurrently, some other worker can accidentally acquire a connection from the pool, where that temp object persists.
If you considered
temp
objects because of performance, you can also considerunlogged
. If you considered them to re-use table names in concurrent workers without causing name collisions - which temps achieve by installing themselves in the session-specificpg_temp_NNN
schema - you can instead set up your own worker-specific schemas and use schema-qualified identifiers to refer to those tables (or prepend the schema name to yoursearch_path
which pgbouncer can track) to put them there:Regardless of whether these two statements end up being run in different connections and transactions, the resulting table persists and is visible to all future connections. To avoid leaking space, when you're done you must remove all worker-specific objects created this way by