Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Are temporary tables in postgresql visible over all client sessions?

I want to create a temp table so as to be able to join it to a few tables because joining those tables with the content of the proposed temporary table takes a lot of time (fetching the content of the temporary table is time consuming.Repeating it over and over takes more and more time). I am dropping the temporary table when my needs are accomplished. I want to know if these temporary tables would be visible over other client session(my requirement is to make them visible only for current client session). I am using postgresql. It would be great if you could suggest better alternatives to the solution I am thinking of.

like image 838
sudheer singh Avatar asked Dec 16 '16 06:12

sudheer singh


People also ask

Are temp tables session-specific?

Creation of Global Temporary Tables The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.

Where are temporary tables stored in Postgres?

Temporary tables get put into a schema called "pg_temp_NNN", where "NNN" indicates which server backend you're connected to. This is implicitly added to your search path in the session that creates them.

How does temp table work in PostgreSQL?

A temporary table, as the name implies, is a short-lived table that exists for the duration of a database session. PostgreSQL automatically drops the temporary tables at the end of a session or a transaction. Syntax: CREATE TEMPORARY TABLE temp_table( ... ); or, CREATE TEMP TABLE temp_table( ... );

Can temporary table data be accessed in another schema in the same session?

This means that within the same Schema, you can have both Temporary and Non-temporary Tables with the same name. However, keep in mind that the Temporary Table takes precedence over any other table, that is, with the same name in the same schema during the session.


Video Answer


2 Answers

PostgreSQL then is the database for you. Temporary tables done better than the standard. From the docs,

Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect is not the same. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.

like image 76
NO WAR WITH RUSSIA Avatar answered Oct 19 '22 13:10

NO WAR WITH RUSSIA


Pleas read the documentation.

Temporary tables are only visible in the current session and are automatically dropped when the database session ends.

If you specify ON COMMIT, the temporary table will automatically be dropped at the end of the current transaction.

If you need good table statistics on a temporary table, you have to call ANALYZE explicitly, as these statistics are not collected automatically.

like image 41
Laurenz Albe Avatar answered Oct 19 '22 12:10

Laurenz Albe