21

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?

Improve this question

3 Answers 3

Reset to default
18

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.

Improve this answer
13

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

Improve this answer
2
  • When temp tables are dropped, do they need a vacuum to mop up any memory or disk resources? I'm trying to resolve this problem and not sure if this fact that autovacuum can't see temp tables is the reason for the memory leak. Do I need to manually vacuum?
    – poshest
    Apr 21, 2016 at 10:31
  • "When temp tables are dropped, do they need a vacuum" no, see here. Same is true for any tables, not just temp tables.
    – Jack Douglas
    Apr 21, 2016 at 21:39
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

Improve this answer
1
  • 1
    Thanks for the tip. I was able to find out the name and schema for the temporary table using dbvis (which probably uses the first query you mentioned), but the problem was that I couldn't access them from a session other than the one they were created in.
    – newenglander
    Sep 7, 2011 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.