Demonstrate the globality of metadata, and the privacy of use, of temporary objects

These tests will show that temporary object metadata is visible across different sessions but that you can use a temporary object only in the session that created it.

Demonstrate the globality of metadata

Create and save this SQL script as "prepare-qry.sql".

prepare qry as
with c(name, is_my_temp, schema, owner) as (
  select
    c.relname,
    c.relnamespace = pg_my_temp_schema(),
    n.nspname,
    r.rolname
  from
    pg_class as c
    inner join
    pg_namespace as n
    on c.relnamespace = n.oid
    inner join
    pg_roles as r
    on c.relowner = r.oid
  where relkind = 'r')
select name, is_my_temp::text, schema
from c
where owner = current_role
order by (replace(schema::text, 'pg_temp_', ''))::int;

And create and save this SQL script as "t.sql".

-- For example, connect as the role "d0$u0" to the database "d0".
\c d0 d0$u0
create table pg_temp.t(k int, v int) on commit delete rows;

\ir prepare-qry.sql

start transaction;
insert into pg_temp.t(k, v) values(1, 42);
select * from pg_temp.t;
execute qry;

Start a new session as an ordinary role that has all privileges on the current database. Call this "Session 0". Make sure that there are no other sessions using this database. Then do this:

-- For example, connect as the role "d0$u0" to the database "d0".
\c d0 d0$u0
\i prepare-qry.sql
execute qry;

At this stage, it will produce no rows. Then, in a new terminal window, start a new session as the same test user connecting to the same database. Call this "Session 1". Execute the script:

\i t.sql

Repeat this for a reasonable number of newly started sessions. Three is enough. But the more you start, the more convincing the demo will be of the rule for the behavior. When you've started as many sessions as you intend to, repeat execute qry in each session.

With "Session 0" through "Session 3", you'll see (something like) this in "Session 0":

 name | is_my_temp |  schema   
------+------------+-----------
 t    | false      | pg_temp_3
 t    | false      | pg_temp_4
 t    | false      | pg_temp_5

The actual numbers appended to pg_temp_ are unpredictable. Notice that pg_my_temp_schema() returned false for each of the three temporary schemas. The results show that each session that executed this:

create table pg_temp.t

using a schema-qualified identifier for the temporary table that starts with the alias pg_temp, has created a differently-named temporary schema. This, of course, must be the case because a schema-object is uniquely identified by its name and the name of the schema where it lives—and the table always has the same name, t. You'll then see this in "Session 2":

 name | is_my_temp |  schema   
------+------------+-----------
 t    | true       | pg_temp_3
 t    | false      | pg_temp_4
 t    | false      | pg_temp_5

Notice that that pg_my_temp_schema() returned true for pg_temp_3 and_false_ for the other two temporary schemas. The same pattern continues: pg_temp_4 is the temporary schema for "Session 2"; and pg_temp_5 is the temporary schema for "Session 3".

Now exit each of "Session 3", "Session 2", and "Session 1", in turn, and after exiting each repeat execute qry in "Session 0". You'll see that when each session exits, its temporary schema vanishes.

Demonstrate the privacy of use of temporary objects

First, create a new version of "prepare-qry.sql" thus:

prepare qry as
with c(name, kind, is_my_temp, schema, owner) as (
  select
    c.relname,
    'table',
    c.relnamespace = pg_my_temp_schema(),
    n.nspname,
    r.rolname
  from
    pg_class as c
    inner join
    pg_namespace as n
    on c.relnamespace = n.oid
    inner join
    pg_roles as r
    on c.relowner = r.oid
  where relkind = 'r'

  union all

  select
    p.proname,
    'function',
    p.pronamespace = pg_my_temp_schema(),
    n.nspname,
    r.rolname
  from
    pg_proc as p
    inner join
    pg_namespace as n
    on p.pronamespace = n.oid
    inner join
    pg_roles as r
    on p.proowner = r.oid
  where prokind = 'f')
select name, kind, is_my_temp::text, schema
from c
where owner = current_role
order by name desc;

Now exit and re-start "Session 0" as, so far, the only session and create and populate a temporary table and create a temporary function to display its contents.

\c d0 d0$u0
create table pg_temp.t(k int);
insert into pg_temp.t(k) values (17), (42), (57);
create function pg_temp.f(i out int)
  returns setof int
  language sql
  set search_path = pg_catalog, pg_temp
as $body$
  select k from pg_temp.t order by k;
$body$;

select i from pg_temp.f();

This is the result:

 i  
----
 17
 42
 57

Now, still in the same session, do this:

\ir prepare-qry.sql
execute qry;

This is the result:

 name |   kind   | is_my_temp |  schema   
------+----------+------------+-----------
 t    | table    | true       | pg_temp_2
 f    | function | true       | pg_temp_2

To prepare for the second part of the test, demonstrate that it is possible to identify the actual temporary schema that the current session uses rather than do this with the pg_temp alias:

select k from pg_temp_2.t order by k;

and

select i from pg_temp_2.f(); 

Now start a second session and do this:

\c d0 d0$u0
\ir prepare-qry.sql
execute qry;

Just as the tests in the section Demonstrate the globality of metadata led to expect, you'll see this:

 name |   kind   | is_my_temp |  schema   
------+----------+------------+-----------
 t    | table    | false      | pg_temp_2
 f    | function | false      | pg_temp_2

In other words, you see the same facts about the same temporary schema-objects with the difference that the temporary schema where they live does not belong to the current session. Now try to use the temporary schema-objects using the same explicit identifiers that worked in the session to which the pg_temp_2 belongs:

select k from pg_temp_2.t order by k;

and

select i from pg_temp_2.f(); 

Each of these attempts fails with the same error:

permission denied for schema pg_temp_2

Arguably, the error could have been better worded. After all, the owner of pg_temp_2 is the role that implicitly created it. And this same role has authorized both sessions. Nevertheless, the meaning is clear: only the session that created a temporary schema-object can use it.