CODEWITHSUNDEEP
pythondatabasepostgresqlpsycopg2temp-tables
Jonathan
Jonathan

Reputation: 1286

Do I need to recreate a temp table if a postgres connection refreshes?

So I want to upload a temporary table, populate it using a csv file and then run a bunch of other queries with the same connection. Currently I'm uploading a normal table, doing my queries and then dropping it. But I want to make it temporary to avoid confusion and to avoid large amounts of data being left in the db if the code stops for some reason (exception/debugging etc.) before it gets a chance to drop the table. I'm doing all this in python using psycopg2.

Firstly, I've assumed the temporary table will hang around as long as the connection is alive. Is this true? But more importantly, does a psycopg2 db connection ever automatically handle a momentary connection drop out by reestablishing a connection? The queries I'm running are very time consuming so I worry that this could happen. In which case is there some way of knowing when the connection refreshes so I can reupload the temporary table?

Upvotes: 1

Views: 1109

Answers (2)

jjanes
jjanes

Reputation: 44373

does a psycopg2 db connection ever automatically handle a momentary connection drop out by reestablishing a connection?

Do you mean does it get impatient, kill a live but "stalled" (e.g. network congestion) connection, and replace it with a new one? No. You could probably write code to do that if you wanted (but why would you?) but psycopg2 itself won't do that.

Upvotes: 1

Jim Jones
Jim Jones

Reputation: 19693

Firstly, I've assumed the temporary table will hang around as long as the connection is alive. Is this true?

A TEMPORARY TABLE is automatically dropped in the end of your session. So, no garbage is left out when the session ends.

But more importantly, does the db connection ever drop out and refresh?

Normally a session does not terminate automatically. If you're wondering if temporary tables could generate any conflict in other sessions, you don't have to worry: temporary tables only live inside the current session.

In which case is there some way of knowing when the connection refreshes so I can reupload the temporary table?

I'm not sure I follow your question here, but one thing is clear: if the session dies, the temporary table dies. If your client is configured to automatically retry to establish connection with the database it won't see the table again. If you want to avoid an error message when trying to create a table that already exists try:

CREATE TEMPORARY TABLE IF NOT EXISTS tb_tmp (...);

And to make sure it is empty:

TRUNCATE TABLE tb_tmp;

Upvotes: 4

Related Questions