Re: Feature request: temporary schemas

Hi Adrian,

Replies below.

On 14/09/2014 8:34 PM, Adrian Klaver wrote:
> On 09/14/2014 02:01 PM, cowwoc wrote:
>> See http://dba.stackexchange.com/q/76494/4719 for a related discussion.
> So from the above link and the discussion here so far I gather you want:
>
> 1) A CREATE TEMPORARY SCHEMA that behaves like CREATE TEMPORARY TABLE,
> where it lasts only for a session and masks any existing schema and
> contained objects with the same name for the duration.

Yes. Tests should not be able to see each other's tables, data (and
ideally not even each other's schema).

> 2) You want to run this on a test database as so you can have 'masked'
> tests over the permanent schema. Presumably because the tests can be
> destructive and you do not want change the underlying structure and/or
> data in your test database.

Not exactly. Each test is responsible for populating its own schema
(creating tables, inserting data). The main purpose of using temporary
schemas is to ensure that each test runs in isolation so that data from
other tests cannot influence the outcome of the test. This ensures test
execution/results are 100% reproducible.

> 3) This needs to happen on the order of 4-10 times a second and it
> needs to be tied to a session, as multiple transactions need to be run
> in each test.
>
> As to whether this is the appropriate list, I would say yes at least
> for sounding out the idea. The list to address for dealing with the
> folks that actually would implement the idea I would suggest --hackers.
>
> In the meantime, the only way I can see doing this is creating and
> dropping databases. Not sure that would meet your 4-10 times a second
> requirement though.

One of the requirements is that if someone kills the process running the
unit tests, it can't leave behind any dangling schemas. I expect all
test data to get dropped automatically when the connection is closed
unexpectedly, so DROP DATABASE won't do.

Gili