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

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)