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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers