Re: Temporary schemas

Hello,

I have created a temporary table using

create temporary table foo
(
id integer
);

and noticed this was created in a schema called "pg_temp_2"

My question is:

is this always "pg_temp_2"?
Or will the name of the "temp schema" change?

If it isn't always the same, is there a way I can retrieve the schema name for temporary tables?

Regards
Thomas

On 1 November 2010 10:46, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:

> Hello,
>
> I have created a temporary table using
>
> create temporary table foo
> (
> id integer
> );
>
> and noticed this was created in a schema called "pg_temp_2"
>
> My question is:
>
> is this always "pg_temp_2"?
> Or will the name of the "temp schema" change?
>
> If it isn't always the same, is there a way I can retrieve the schema name
> for temporary tables?
>
> Regards
> Thomas
>
>
You can use:

SELECT nspname
FROM pg_namespace
WHERE oid = pg_my_temp_schema();

to get the name of the current temporary schema for your session.

And it's always pg_temp_[nnn] as far as I'm aware, with a corresponding
pg_toast_temp_[nnn] schema.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom Brown wrote on 01.11.2010 12:33:
> You can use:
>
> SELECT nspname
> FROM pg_namespace
> WHERE oid = pg_my_temp_schema();
>
> to get the name of the current temporary schema for your session.

Thanks that's what I was looking for.

Regards
Thomas

On Mon, Nov 1, 2010 at 6:46 AM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> Hello,
>
> I have created a temporary table using
>
> create temporary table foo
> (
>  id integer
> );
>
> and noticed this was created in a schema called "pg_temp_2"
>
> My question is:
>
> is this always "pg_temp_2"?
> Or will the name of the "temp schema" change?
>
> If it isn't always the same, is there a way I can retrieve the schema name
> for temporary tables?

Curious why you'd want to do this -- temporary magic schemas are an
implementation artifact, and there shouldn't ever be a reason to
directly reference them.

merlin

Merlin Moncure wrote on 01.11.2010 21:13:
> On Mon, Nov 1, 2010 at 6:46 AM, Thomas Kellerer<spam_eater(at)gmx(dot)net> wrote:
>> Hello,
>>
>> I have created a temporary table using
>>
>> create temporary table foo
>> (
>> id integer
>> );
>>
>> and noticed this was created in a schema called "pg_temp_2"
>>
>> My question is:
>>
>> is this always "pg_temp_2"?
>> Or will the name of the "temp schema" change?
>>
>> If it isn't always the same, is there a way I can retrieve the schema name
>> for temporary tables?
>
> Curious why you'd want to do this -- temporary magic schemas are an
> implementation artifact, and there shouldn't ever be a reason to
> directly reference them.
>
Yes and no ;)

The problem is, that the JDBC driver only returns information about the temp tables, if I specify that schema directly.

Thomas

On Mon, Nov 1, 2010 at 4:27 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> The problem is, that the JDBC driver only returns information about the temp
> tables, if I specify that schema directly.

Have you filed a bug report to jdbc yet? :-D.

merlin

On 11/01/2010 04:13 PM, Merlin Moncure wrote:
> On Mon, Nov 1, 2010 at 4:27 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
>> The problem is, that the JDBC driver only returns information about the temp
>> tables, if I specify that schema directly.
>
> Have you filed a bug report to jdbc yet? :-D.
>
> merlin
>

But can you consider temp tables as part of the schema when they last
only the duration of the session?

Merlin Moncure wrote on 01.11.2010 23:13:
> On Mon, Nov 1, 2010 at 4:27 PM, Thomas Kellerer<spam_eater(at)gmx(dot)net> wrote:
>> The problem is, that the JDBC driver only returns information about the temp
>> tables, if I specify that schema directly.
>
> Have you filed a bug report to jdbc yet? :-D.

I thought about it initially, but then realized that it works as documented by the JDBC API.

When requesting the table information without specifying a schema, it is returned.
But in my application I use the current schema to request information about non-qualified tables which obviously fails as the current schema is usually public or another user schema but never pg_temp_xxx. So even though a select from a temp table (whithout a schema) works fine from within JDBC, retrieving metadata only works when either specifying no schema, or the correct one - which is a bit confusing but absolutely according to the JDBC specs.

Regards
Thomas

Thanks! very useful for me!

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-schemas-tp3244865p5742352.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.