Temporary schema per connection?

I am trying to migrate my unit tests from H2 to Postgresql.

Currently, H2 gives me an in-memory schema such that each connection maps to a unique schema, creates the tables, runs the test, and drops the schema. The schema creation and destruction is handled automatically by H2.

The unit tests run concurrently.

What is the best way to do this in Postgresql? Specifically,

• How do I get a unique schema per connection?

• Should the test framework generate unique names or is there a built-in mechanism for doing this?

• How do I ensure that the schema is dropped when the connection is dropped?

• I don't want to end up with dangling schemas when unit tests get killed.

• What approach will yield the highest performance?

• I need to create/drop tens of schemas per second.

UPDATE: I found a related answer here but it fails to drop schemas in case the process running the unit tests gets killed.

pg_temp is an alias for the current session's temporary schema.

If you do a SET search_path TO pg_temp before running your tests it should all just work (as long as nothing is referencing a schema explicitly).

If you don't want to change your script at all, then set the search_path on the user that the tests log in as:

> ALTER ROLE testuser SET search_path = pg_temp;

Then everything that user creates will be in pg_temp unless explicitly specified.

Here's an example from psql, showing the actual schema (for this connection) that the alias resolves to:

> SET search_path TO pg_temp;
SET
> create table test();
CREATE TABLE
> \dt test
          List of relations
  Schema   | Name | Type  |  Owner
-----------+------+-------+----------
 pg_temp_4 | test | table | postgres
(1 row)

And, as you'd expect, that schema is different for every concurrent connection, and is gone after the connection is closed.

Note that this also works for functions, though you will have to explicitly reference the pg_temp schema when calling them.

StackExchange Database Administrators Q#76494, answer score: 16