Re: 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(at)decina:[12:33]~$time createdb t

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

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

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

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

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

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

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

decibel(at)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(at)decina:[12:39]~$

decibel(at)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(at)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)