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,

  1. 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?
  2. 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.
  3. 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.

4 Answers 4


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;
> create table test();
> \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.

  • But pg_temp is a single schema right? So when I run concurrent unit tests, won't they clobber each other's tables/data?
    – Gili
    Sep 16, 2014 at 20:53
  • 1
    No. It's an alias for the current session's temporary schema. I'll update the answer with an example.
    – hbn
    Sep 16, 2014 at 21:27
  • Bear in mind if you just close and open a connection, you may well end up with the same temporary schema, but it will have been emptied. Open 2 concurrently to see different ones being allocated. You can't see another session's temporary schema unless you are a superuser.
    – hbn
    Sep 16, 2014 at 21:35
  • Sure I saw a comment from you asking about when to set this. Anyway - it's set per-session if you just do a SET search_path; use SET LOCAL search_path to set per subtransaction, or if you wish, you can set at user level with ALTER USER mytestuser SET search_path = 'pg_temp', or at database level with ALTER DATABASE mytestdb SET search_path = 'pg_temp'
    – hbn
    Sep 17, 2014 at 5:40
  • Out of curiosity, is there any way to make this work for functions without an explicit schema reference? Or is this impossible for the pg_temp schema?
    – Gili
    Sep 17, 2014 at 6:13

You can get the name of the current temporary schema (after creating the first temp table) like laid out in the link you added:

SELECT nspname
FROM   pg_namespace
WHERE  oid = pg_my_temp_schema();

But your current plan still wouldn't make a lot of sense. To create tables in the current temporary schema, just create temporary tables. That's all. By default, the search_path is defined so that temporary tables are visible first. One never needs to schema-qualify temp tables. You shouldn't ever have to address the current temporary schema directly in any way - that's an implementation detail.

  • Agreed it's a hack, but it may be significantly more straightforward than parameterising creation code to allow temporary tables to be created.
    – hbn
    Sep 14, 2014 at 19:37
  • Good point, except as @hbn mentioned I want unit tests and production code to run the same SQL script. The former should to run against a temporary schema while the latter should not.
    – Gili
    Sep 14, 2014 at 19:58
  • @hbn, out of curiosity what would parameterized creation code look like? I am using flywaydb.org and it just executes plain SQL files (no variables). I probably don't want to go down this road. I'm just curious what's involved.
    – Gili
    Sep 14, 2014 at 20:11
  • I've never used flywaydb. At a very basic level, you could use some text-templating language (e.g. Jinja2 in Python) to preprocess your creation scripts, optionally adding a "temporary" when you create a table. If you're explicitly creating functions, the get-the-temporary-schema hack is probably unavoidable as (as far as I know), you can't directly create a temporary function.
    – hbn
    Sep 14, 2014 at 20:16
  • @hbn, If you're explicitly sequences ...: I think your last comment contained a typo. What did you mean to say between explicitly and sequences?
    – Gili
    Sep 14, 2014 at 20:19

Do your tests involve transactions? DDL is transactional in PostgreSQL, so if you create your schema and tables, then run your tests, all within a single transaction that is then rolled back, the schema is never actually committed and visible to other sessions.

You'd still need to use a probably-unique name for your schema (maybe include hostname and PID), as CREATE SCHEMA will fail immediately if an identically-named schema already exists, and will block if another session has created an identically-named schema in an uncommitted transaction.

An alternative would possibly just be to use temporary tables, if you're able to modify your database creation scripts to do that.

  • Nice trick, but it wouldn't work in my case because a single test operates across multiple transactions. Each test method is a web client that fires multiple server-side transactions. For example, it creates, queries and deletes a user. Each call is a separate HTTP request, and runs in its own transaction.
    – Gili
    Sep 14, 2014 at 20:03
  • Fair enough, my approach was very limited.
    – hbn
    Sep 14, 2014 at 20:23
  • @Gili: Note that this technique of never committing CREATE SCHEMA is the only one that can guarantee to have them disappear when the unit test gets killed. Sep 15, 2014 at 15:46

I just got an idea.

Postgresql guarantees that a session can't see another's temporary tables. I'm guessing this means that when you create a temporary table, it creates a temporary schema. So perhaps I could do the following:

  1. Create a (dummy) temporary table and look up its schema.
  2. Use this schema for the test (create the tables, run the test).
  3. When the connection is closed, Postgresql will drop the schema.

I don't like relying on implementation details, but in this case this seems pretty safe.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.