Is there a way to access temporary tables of other sessions in postgres?

Question

I'm working with a Windows application that uses a (local) postgres Database and stores some information in a temporary table. I'd like to have a look at the temporary table, but pgadmin and dbVis tell me: ERROR: cannot access temporary tables of other sessions when trying to query the data. I tried changing the permissions of the schema and table, but this didn't seem to help, even though I'm accessing the database with the same user as the program itself (at least in dbVis). Is there a setting I can change in my database that allows me to have "root" access to all session in my database?

Answer 1

The lack of access to temporary tables in other sessions is not a matter of permissions, it's a technical limitation of the design. A PostgreSQL backend can't access temporary tables of another backend because none of the usual housekeeping to allow concurrent access is done for temporary tables.

In 9.2 you will want to use an UNLOGGED table instead; this can be visible from other sessions, but retains most of the performance benefits of a temporary table.

Answer 2

The short answer is "No". Temporary tables in other sessions are invisible by design. It makes no difference if two sessions have the same user. Even:

The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables

Answer 3

I don't know whether this can help you, but you can give a try.

Following system catalog table query should able to list all temporary tables created in other sessions in the database:

select pn.nspname, pc.relname from pg_class pc, pg_namespace pn where pc.relnamespace = pn.oid and pc.relname ilike 'your_temp_table_name';

Per PostgreSQL doc, Temporary tables exist in a special schema and are usually created with name as pg_temp_xxx. So, using schemaname.relationname from above query, you should able to query your temporary table. As you can see here, temporary table is referenced with schema-qualified name.

Example: select * from pg_temp_20.your_temp_table_name