Size of temp tables created in a particular session

Question

I created a temp table using below query

Drop table if exists tmp_a;
Create temp table tmp_a
(
    id int
);

Insert into tmp_a select generate_series(1,10000);

When I queried pg_stat_activity, it is showing as "IDLE" in current_query column for the above session.

I will get the size of all temp table from pg_class table using this query. But I want the list of temp tables created for a particular session and the size of those temp tables i.e if I created two temp tables from two different sessions then the result should be like below

procpid | temp table name | size | username    
12345   | tmp_a           | 20   | gpadmin    
12346   | tmp_b           | 30   | gpadmin 

Please share the query if anyone has it

Answer 1

It's actually simpler than you think --
The temporary schema namesapce is the same as the session id --

So...

SELECT
  a.procpid as ProcessID,
  a.sess_id as SessionID,
    n.nspname as SchemaName,
    c.relname as RelationName,
    CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
    WHEN 'i' THEN 'index'
    WHEN 'S' THEN 'sequence'
    WHEN 's' THEN 'special'
    END as RelationType,
    pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner,
    pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM 
  pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_stat_activity a ON 'pg_temp_' || a.sess_id::varchar = n.nspname
WHERE  c.relkind IN ('r','s') 
AND  (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC;

And you get --

 processid | sessionid | schemaname | relationname | relationtype | relationowner | relationsize
-----------+-----------+------------+--------------+--------------+---------------+--------------
      5006 |         9 | pg_temp_9  | tmp_a        | table        | gpadmin       | 384 kB
      5006 |         9 | pg_temp_9  | tmp_b        | table        | gpadmin       | 384 kB
(2 rows)

Let's put that process to sleep -- and startup another....

gpadmin=#
[1]+  Stopped                 psql

[gpadmin@gpdb-sandbox ~]$ psql
psql (8.2.15)
Type "help" for help.

gpadmin=# SELECT nspname
FROM   pg_namespace
WHERE  oid = pg_my_temp_schema();

 nspname
---------
(0 rows)

gpadmin=# Create temp table tmp_a( id int );
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE

gpadmin=# SELECT nspname
FROM   pg_namespace
WHERE  oid = pg_my_temp_schema();

 nspname
---------
 pg_temp_10
(1 row)

... run the same query ...

 processid | sessionid | schemaname | relationname | relationtype | relationowner | relationsize
-----------+-----------+------------+--------------+--------------+---------------+--------------
      5006 |         9 | pg_temp_9  | tmp_a        | table        | gpadmin       | 384 kB
      5006 |         9 | pg_temp_9  | tmp_b        | table        | gpadmin       | 384 kB
     27365 |        10 | pg_temp_10 | tmp_a        | table        | gpadmin       | 384 kB
(3 rows)