Re: Feature request: temporary schemas - Mailing list pgsql-general

On 15/09/2014 10:37 AM, Adrian Klaver wrote:
> From your second post:
>
> " 1. I'm already planning to run unit tests against a separate (but
>     identical) database than production, so there's no danger of wiping
>     out the production database.
>  2. I need to create a new temporary schema per test, and run 4-10 of
>     tests per second. I'm guessing this wouldn't perform well using
>     "pg_ctl".
> "
>
> I took that to mean you want to create the temporary schemas over an
> existing test database.

Sorry, I am having problems with the Postgresql terminology. H2
"database" = Postgresql "schema".

To answer your original question: ideally I would want each test to run
in its own database but in practice I don't think you can create/drop a
database through JDBC, can you? So instead, I'm thinking about manually
creating a test database and have unit tests create/drop schemas inside
of that. The test database wouldn't contain anything. It would just
isolate unit tests from the production database to make sure they don't
clobber each other.

If I could get each unit test to run inside its own database, that would
be even better.
>>> I would think a DROP DATABASE IF EXISTS, CREATE DATABASE at the
>>> beginning of the test would handle that.
>>
>> This would only clean up the next time tests are run. I'm looking for a
>> cleanup at the end of the tests, not the beginning.
>> As well, the fact that I have concurrent test execution means that I
>> don't know how many databases/schemas there are to drop. I guess I could
>> scan the database metadata for all test-related schemas but clearly this
>> isn't as clean/fun as having temporary schemas in the first place.
>
> I will admit to not being a testing expert, but from what I have done,
> I know test suites have setup and teardown sections.
>
> Would this not work?
>
> My previous suggestion was a fall through for the case you mentioned
> where a process is terminated outside the test.

Right, dropping databases in the teardown section is certainly doable.
It's just that the teardown section never runs if the unit tests are
terminated.

>
>> And lastly, remember that we want these tests to run as fast as
>> possible. TEMPORARY/UNLOGGED tables are ideal from that point of view
>> but I can't specify TEMPORARY/UNLOGGED because the unit tests and
>> production code must share the same SQL script.
>
> I can see that being possible. There will always be some difference
> though as unit tests are not the same as rolling out production
> scripts. The test code will need to include the actual test, unless I
> am missing something obvious. Entirely possible:)

Unit tests proceed as follows:

1. Run SQL script to populate the test schema (identical for all tests)
2. Run test code (different for each test)

I want unit tests and production to share #1 but have different #2.
Currently I am forced to produce nearly identical #1 for production and
unit tests. This feature request would enable me to have identical #1 :)

Gili