Hi,

While running 2 sessions in different terminals for the same user, what
happens in the second session here looks a bit weird:

=========
<session 1>
=========
db=# create temp table t_test (id int);
CREATE TABLE

db=# select table_schema from information_schema.tables
where table_name = 't_test';
table_schema
--------------
pg_temp_2
(1 row)

db=# select has_schema_privilege('pg_temp_2', 'usage');
has_schema_privilege
----------------------
t
(1 row)

db=# select has_table_privilege('pg_temp_2.t_test', 'insert');
has_table_privilege
---------------------
t
(1 row)

db=# select has_table_privilege('t_test', 'insert');
has_table_privilege
---------------------
t
(1 row)

db=# insert into t_test values (1);
INSERT 0 1

=========
<session 2>
=========

db=# select table_schema from information_schema.tables
where table_name = 't_test';
table_schema
--------------
pg_temp_2
(1 row)

db=# select has_schema_privilege('pg_temp_2', 'usage');
has_schema_privilege
----------------------
t
(1 row)

db=# select has_table_privilege('pg_temp_2.t_test', 'insert');
has_table_privilege
---------------------
t
(1 row)

db=# select has_table_privilege('t_test', 'insert');
ERROR: relation "t_test" does not exist

db=# insert into t_test values (1);
ERROR: relation "t_test" does not exist



db=# select version();
version
------------------------------------------------------------------------
PostgreSQL 8.1.3 on i586-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)



In this light, is there a possibility to find out what schema will be used for
temporary tables created during the current session? I need to find out
whether a specific temporary table exists and is accessible for the current
user in the current session.



--
Best,




Frank.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Frank van Vugt <ftm.van.vugt (AT) foxi (DOT) nl> writes:
In the first place, you are evidently running as superuser, which means
that has_foo_privilege will ALWAYS say 't' (except possibly if the
target object doesn't exist, in which case I think you get an error).
In the second place, trying to access another session's temp table is
unsupported.

After you've created at least one temp table, you can look at the result
of "current_schemas(true)". There's no guarantee that the schema even
exists before you've created something...


regression=# select current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row)

regression=# create temp table t(f1 int);
CREATE TABLE
regression=# select current_schemas(true);
current_schemas
-------------------------------
{pg_temp_1,pg_catalog,public}
(1 row)

regression=# select (current_schemas(true))[1];
current_schemas
-----------------
pg_temp_1
(1 row)

regression=#

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi,

Ok, seems reasonable

Yep, one does.

I understand, it's more the opposite, I was fixing a bug in a plpgsql function
that would fail when the user has created a certain (temporary) table in a
second session, because the code only checked the existence of the table_name
without taking into account the proper schema.

Got that, looks like an acceptable workaround in this case, though.

Is there a guaranteed order of the resulting array, i.e. is this guaranteed to
return the temp schema, given there is one:

'select (current_schemas(true))[1]'.....?

(obviously, regexp's will also do the trick, I'm just asking)



--
Best,




Frank.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Frank van Vugt <ftm.van.vugt (AT) foxi (DOT) nl> writes:
Yes, in the current implementation and for the foreseeable future (else
temp tables would fail to mask permanent tables).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq