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 a begin; and ending with a commit;. Make sure to create the temporary objects with on commit drop unless your pool can discard 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 in begin;...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 in pool_mode=statement (autocommit behaviour).
temp table are dropping if i took (another?) connection when i will be using second execute statement
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 consider unlogged. 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-specific pg_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 your search_path which pgbouncer can track) to put them there:
create schema my_worker_123_schema;
create unlogged table my_worker_123_schema.my_unlogged_table;
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
drop schema my_worker_123_schema cascade;