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

On 09/15/2014 08:05 AM, cowwoc wrote:
> 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.

Well DROP DATABASE is a SQL command so it could be used in a query:

http://www.postgresql.org/docs/9.3/interactive/sql-dropdatabase.html

The caveat is you cannot DROP the database you are connected to. You
would need to connect one of the system databases, postgres for
instance, and then run the DROP DATABASE command there.

>
> If I could get each unit test to run inside its own database, that would
> be even better.

That would be possible, though some thought would need to be given per
your desire to run concurrent tests. Would need to think carefully about
naming issues.


>>>> 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.

Just a food for thought item. This is something I think you would have
to deal with any case. I think it is plausible that even in the
TEMPORARY SCHEMA case a unit test could terminate and leave a
connection(session) open and therefore the schema still active.
Subsequent runs would create new temp schemas, but you could have old
carcasses lingering. My guess is that might end up impacting your test
results.

>
>>
>>> 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 :)

I see the logic. The thing is this is a time issue. What you want would
be considered a feature change. Feature changes only happen in new major
versions. Currently work has started on the next major version, 9.5. So
you would need to convince developers to make the change in the near
future in order to get it out with 9.5. Even then you are probably
looking at around a year before it hits production. In the meantime you
will need a Plan B. Doing the DROP/CREATE DATABASE adds just a little
difference to the script in #1. After that everything else would be the
same.

>
> Gili


--
Adrian Klaver
adrian.klaver@aklaver.com