Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 6 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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

share|improve this answer
    
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 '11 at 13:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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