Postgres 10 temp tablespace question

Am 09.03.19 um 02:05 schrieb Joseph Dunleavy:

I am building a multi-tenant deployment with multiple database - 1
tenant per database.

I would like to be able to dedicate specific temp tablespace to
a�specific database or user/schemas.

I understand how to define temp_tablespace in postgresql.conf.

I can't find anything in the documentation on how to dedicate a
specific temp_tablespaces to a specific database, user or schema.

I also thought maybe I could create a trigger on logon to set a
specific temp tablespace per user, but I can't do that either.

Is it possible in Postgresql to dedicate a specific temp tablespace to
a specific database or user/schema?

yes:

test=*# create tablespace tmp_tbsp1 location '/tmp/tbsp1';
FEHLER:� CREATE TABLESPACE kann nicht in einem Transaktionsblock laufen
test=*# commit;
COMMIT
test=# create tablespace tmp_tbsp1 location '/tmp/tbsp1';
CREATE TABLESPACE
test=# create tablespace tmp_tbsp2 location '/tmp/tbsp2';
CREATE TABLESPACE
test=# create tablespace tmp_tbsp3 location '/tmp/tbsp3';
CREATE TABLESPACE
test=# create user usr1;
CREATE ROLE
test=*# create user usr2;
CREATE ROLE
test=*# create user usr3;
CREATE ROLE
test=*# alter user usr1 set temp_tablespaces = 'tmp_tbsp1';
ALTER ROLE
test=*# alter user usr2 set temp_tablespaces = 'tmp_tbsp2';
ALTER ROLE
test=*# alter user usr3 set temp_tablespaces = 'tmp_tbsp3';
ALTER ROLE
test=*#

test=*# show temp_tablespaces;
�temp_tablespaces
------------------

(1 row)

test=*# commit;
COMMIT
test=# \c - usr2;
psql (11.1 (Ubuntu 11.1-3.pgdg16.04+1), server 9.5.15)
You are now connected to database "test" as user "usr2".
test=> show temp_tablespaces;
�temp_tablespaces
------------------
�tmp_tbsp2
(1 row)

test=*>

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com