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

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

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

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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to