Thread: [HACKERS] possibility to specify template database for pg_regress

On Wed, Feb 8, 2017 at 12:43 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Is possible to specify template database for pg_regress?
> I have to run tests on database with thousands database objects. Using
> template is much faster than import these objects.

Not directly, all the databases created by pg_regress are enforced
with template0.. Having a switch sounds useful though without seeing
in details your use case.
-- 
Michael

Michael Paquier <michael.paquier@gmail.com> writes:
> On Wed, Feb 8, 2017 at 12:43 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> Is possible to specify template database for pg_regress?
>> I have to run tests on database with thousands database objects. Using
>> template is much faster than import these objects.

> Not directly, all the databases created by pg_regress are enforced
> with template0.. Having a switch sounds useful though without seeing
> in details your use case.

I ran into a use-case just today: I wanted to run one particular
regression test script under CLOBBER_CACHE_ALWAYS, but it needed
stuff created by earlier scripts, and I didn't especially want to
run all of those scripts under CCA.  With a way to select a template,
I could've run the earlier scripts in a normal build, renamed the
ending-state regression database to something else, and then installed
a CCA-enabled executable and run a test with just the script of
interest.  The way I actually got it done was considerably hackier :-(
        regards, tom lane

On 2017-02-07 19:23:45 -0500, Tom Lane wrote:
> Michael Paquier <michael.paquier@gmail.com> writes:
> > On Wed, Feb 8, 2017 at 12:43 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> >> Is possible to specify template database for pg_regress?
> >> I have to run tests on database with thousands database objects. Using
> >> template is much faster than import these objects.
> 
> > Not directly, all the databases created by pg_regress are enforced
> > with template0.. Having a switch sounds useful though without seeing
> > in details your use case.
> 
> I ran into a use-case just today: I wanted to run one particular
> regression test script under CLOBBER_CACHE_ALWAYS, but it needed
> stuff created by earlier scripts, and I didn't especially want to
> run all of those scripts under CCA.  With a way to select a template,
> I could've run the earlier scripts in a normal build, renamed the
> ending-state regression database to something else, and then installed
> a CCA-enabled executable and run a test with just the script of
> interest.  The way I actually got it done was considerably hackier :-(

Can't you do that with --use-existing?  I'm pretty sure I used it for
very similar issues before.  And yes, the --help text for that is
misleading.

- Andres

On Wed, Feb 8, 2017 at 9:23 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I ran into a use-case just today: I wanted to run one particular
> regression test script under CLOBBER_CACHE_ALWAYS, but it needed
> stuff created by earlier scripts, and I didn't especially want to
> run all of those scripts under CCA.  With a way to select a template,
> I could've run the earlier scripts in a normal build, renamed the
> ending-state regression database to something else, and then installed
> a CCA-enabled executable and run a test with just the script of
> interest.  The way I actually got it done was considerably hackier :-(

Looking at the code, --dbname can actually accept a list of databases.
Perhaps we could just have the equivalent for templates? I think that
we just need to be sure that the template list matches the length of
the database list if the template list is longer than one.
-- 
Michael

On Wed, Feb 8, 2017 at 4:24 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> What is sense for list of databases?

ECPG uses it for example, see 0992259.

> Some option --template can be great - with backpatch if it is possible.

That's not really complicated to patch... That could be a nice task
for a starter.
-- 
Michael

On Thu, Feb 9, 2017 at 5:13 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> here is a patch

Thanks.

-       for (sl = dblist; sl; sl = sl->next)
-           create_database(sl->str);
+       if (templatelist != NULL)
+       {
+           _stringlist *tl;
+
+           for (sl = dblist, tl = templatelist; sl; sl = sl->next, tl
= tl->next)
+           {
+               if (tl != NULL)
+                   create_database(sl->str, tl->str);
+               else
+               {
+                   fprintf(stderr, _("%s: the template list is
shorter than database list\n"),
+                           progname);
+                   exit(2);
+               }
+           }
+       }
+       else
+           for (sl = dblist; sl; sl = sl->next)
+               create_database(sl->str, "template0");
There is one problem here: if the length of the template list is
shorter than the database list, databases get halfly created, then
pg_regress complains, letting the instance in a half-way state. I
think that you had better do any sanity checks before creating or even
dropping existing databases.
-- 
Michael

On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> here is new update - check is done before any creating

It may be better to do any checks before dropping existing databases
as well... It would be as well just simpler to complain with a single
error message like "database and template list lengths do not match".
-- 
Michael

On 2017-02-13 20:59:43 +0100, Pavel Stehule wrote:
> Hi
> 
> 2017-02-13 6:46 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:
> 
> > On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule <pavel.stehule@gmail.com>
> > wrote:
> > > here is new update - check is done before any creating
> >
> > It may be better to do any checks before dropping existing databases
> > as well... It would be as well just simpler to complain with a single
> > error message like "database and template list lengths do not match".
> >
> 
> next step

I still fail to see why --use-existing as suggested in
https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvqqq7@alap3.anarazel.de
isn't sufficient.

- Andres

On Tue, Feb 14, 2017 at 11:36 AM, Andres Freund <andres@anarazel.de> wrote:
> On 2017-02-13 20:59:43 +0100, Pavel Stehule wrote:
>> Hi
>>
>> 2017-02-13 6:46 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:
>>
>> > On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule <pavel.stehule@gmail.com>
>> > wrote:
>> > > here is new update - check is done before any creating
>> >
>> > It may be better to do any checks before dropping existing databases
>> > as well... It would be as well just simpler to complain with a single
>> > error message like "database and template list lengths do not match".
>> >
>>
>> next step

This looks fine to me.

> I still fail to see why --use-existing as suggested in
> https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvqqq7@alap3.anarazel.de
> isn't sufficient.

Some tests create objects without removing them, meaning that
continuous runs would fail with only --use-existing. This patch brings
value in such cases.
-- 
Michael

On 2017-02-14 11:46:52 +0900, Michael Paquier wrote:
> > I still fail to see why --use-existing as suggested in
> > https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvqqq7@alap3.anarazel.de
> > isn't sufficient.
> 
> Some tests create objects without removing them, meaning that
> continuous runs would fail with only --use-existing. This patch brings
> value in such cases.

You can trivially script the CREATE/DROP DB outside with
--use-existing. Which seems a lot more flexible than adding more and
more options to pg_regress.

On 2/13/17 8:50 PM, Andres Freund wrote:
> On 2017-02-14 11:46:52 +0900, Michael Paquier wrote:
>>> I still fail to see why --use-existing as suggested in
>>> https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvqqq7@alap3.anarazel.de
>>> isn't sufficient.
>>
>> Some tests create objects without removing them, meaning that
>> continuous runs would fail with only --use-existing. This patch brings
>> value in such cases.
>
> You can trivially script the CREATE/DROP DB outside with
> --use-existing. Which seems a lot more flexible than adding more and
> more options to pg_regress.

AFAIK if you're doing make check (as opposed to installcheck) it's 
significantly more complicated than that since you'd have to create a 
temp cluster/install yourself.

As an extension author, I'd *love* to have the cluster management stuff 
in pg_regress broken out: it's the only reason I use pg_regress, and 
pg_regress's idea of what a test failure is just gets in my way. But 
breaking that out is far more invasive than allowing a template database.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

On 2017-02-14 12:33:35 -0600, Jim Nasby wrote:
> On 2/13/17 8:50 PM, Andres Freund wrote:
> > On 2017-02-14 11:46:52 +0900, Michael Paquier wrote:
> > > > I still fail to see why --use-existing as suggested in
> > > > https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvqqq7@alap3.anarazel.de
> > > > isn't sufficient.
> > > 
> > > Some tests create objects without removing them, meaning that
> > > continuous runs would fail with only --use-existing. This patch brings
> > > value in such cases.
> > 
> > You can trivially script the CREATE/DROP DB outside with
> > --use-existing. Which seems a lot more flexible than adding more and
> > more options to pg_regress.
> 
> AFAIK if you're doing make check (as opposed to installcheck) it's
> significantly more complicated than that since you'd have to create a temp
> cluster/install yourself.

But in that case you can't have useful templates in the regression test
either, so the whole discussion is moot?

On 2/14/17 1:59 PM, Andres Freund wrote:
>> AFAIK if you're doing make check (as opposed to installcheck) it's
>> significantly more complicated than that since you'd have to create a temp
>> cluster/install yourself.>
> But in that case you can't have useful templates in the regression test
> either, so the whole discussion is moot?

At that point it depends on what you're trying to do. Presumably 
separating cluster control would make it much easier to script 
createdb/dropdb as you suggested. Tom's use case might be more easily 
served by specifying a template database. I don't think Pavel ever 
posted his use case.

Speaking for myself, my normal pattern is to have a number of separate 
pg_regress suites, each of which ends up loading the extension under 
test. Loading a large extension can end up being very time consuming; 
enough so that I'd expect it to be much faster to create the temp 
cluster, load all the prereq's once in some template database, and then 
use that template for most/all of the tests. In that scenario separating 
cluster create/drop would certainly be useful, but the template option 
would probably be helpful as well (though since pg_regress' diff-based 
methodology just gets in my way I'd likely use some other harness to 
actually run the tests).
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

On 2017-02-14 14:29:56 -0600, Jim Nasby wrote:
> On 2/14/17 1:59 PM, Andres Freund wrote:
> > > AFAIK if you're doing make check (as opposed to installcheck) it's
> > > significantly more complicated than that since you'd have to create a temp
> > > cluster/install yourself.
> >
> > But in that case you can't have useful templates in the regression test
> > either, so the whole discussion is moot?
> 
> At that point it depends on what you're trying to do. Presumably separating
> cluster control would make it much easier to script createdb/dropdb as you
> suggested.

That's not what I responded to...


> Tom's use case might be more easily served by specifying a
> template database. I don't think Pavel ever posted his use case.

Wait, that's precisely what Pavel asked?

On 2017-02-07 16:43:47 +0100, Pavel Stehule wrote:
> Is possible to specify template database for pg_regress?
> 
> I have to run tests on database with thousands database objects. Using
> template is much faster than import these objects.

Obviously that only makes sense with installcheck.


> Speaking for myself, my normal pattern is to have a number of separate
> pg_regress suites, each of which ends up loading the extension under test.
> Loading a large extension can end up being very time consuming; enough so
> that I'd expect it to be much faster to create the temp cluster, load all
> the prereq's once in some template database, and then use that template for
> most/all of the tests.

I seriously doubt that. CREATE DATABASE is ridiculously expensive,
copies everything on the file-level and requires checkpoints.  If your
extension is more expensive than that, I'd say you're likely doing
something wrong.

- Andres

On 2/14/17 2:49 PM, Pavel Stehule wrote:
>     > Tom's use case might be more easily served by specifying a
>     > template database. I don't think Pavel ever posted his use case.
>
>     Wait, that's precisely what Pavel asked?
>
>
> I would to use regress test environment in my current case. 99% code in
> plpgsql, but there is pretty complex schema. About 300 tables. 1k views.
> 2k functions. Import schema is slow. Database clonning is much faster.

FWIW, for actual production environments (which I assume this is), I 
find pg_regress to be completely useless. Some simple shell scripts to 
build the database (possibly using sqitch) and then a script around 
pg_prove is what I normally use. https://github.com/BlueTreble/db_tools 
gives you the general idea.

>> Speaking for myself, my normal pattern is to have a number of separate
>> pg_regress suites, each of which ends up loading the extension under test.
>> Loading a large extension can end up being very time consuming; enough so
>> that I'd expect it to be much faster to create the temp cluster, load all
>> the prereq's once in some template database, and then use that template for
>> most/all of the tests.
>
> I seriously doubt that. CREATE DATABASE is ridiculously expensive,
> copies everything on the file-level and requires checkpoints.  If your
> extension is more expensive than that, I'd say you're likely doing
> something wrong.

That depends on the extension. pgTap for example contains over 900 
functions. A quick test on my laptop shows it's faster to create a 
database from a template containing the extension than it is to create 
the extension itself.

decibel@decina:[12:33]~$time createdb t

real    0m0.433s
user    0m0.004s
sys    0m0.009s
decibel@decina:[12:34]~$time psql -c 'create extension pgtap' t
CREATE EXTENSION

real    0m0.559s
user    0m0.002s
sys    0m0.007s
decibel@decina:[12:34]~$time createdb -T t t2

real    0m0.441s
user    0m0.002s
sys    0m0.005s
decibel@decina:[12:34]~$time psql -c 'drop extension pgtap' t
DROP EXTENSION

real    0m0.197s
user    0m0.002s
sys    0m0.006s
decibel@decina:[12:34]~$time dropdb t

real    0m0.189s
user    0m0.003s
sys    0m0.007s
decibel@decina:[12:34]~$time dropdb t2

real    0m0.154s
user    0m0.002s
sys    0m0.005s
decibel@decina:[12:34]~$

Interestingly, CREATE EXTENSION is 2x faster than simply running the file:

decibel@decina:[12:38]~$time psql -qtf 
$PGDATA/../share/extension/pgtap--0.97.0.sql t

real    0m1.225s
user    0m0.044s
sys    0m0.028s
decibel@decina:[12:39]~$

decibel@decina:[12:41]~$(echo 'begin;'; echo "\i 
$PGDATA/../share/extension/pgtap--0.97.0.sql"; echo 'commit;') | time 
psql -qt t        1.12 real         0.04 user         0.02 sys
decibel@decina:[12:41]~$
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)