Postgresql regress test

最近看了下pg中的回归测试相关内容，现在将看到的内容记录下来。

1.先来一个例子

[postgres@gorilla1 regress]$ make check
make -C ../../../src/port all
make[1]: Entering directory `/tmp/postgresql-9.3.4/src/port'
make -C ../backend submake-errcodes
make[2]: Entering directory `/tmp/postgresql-9.3.4/src/backend'
make[2]: Nothing to be done for `submake-errcodes'.
make[2]: Leaving directory `/tmp/postgresql-9.3.4/src/backend'
make[1]: Leaving directory `/tmp/postgresql-9.3.4/src/port'
make -C ../../../src/common all
make[1]: Entering directory `/tmp/postgresql-9.3.4/src/common'
make -C ../backend submake-errcodes
make[2]: Entering directory `/tmp/postgresql-9.3.4/src/backend'
make[2]: Nothing to be done for `submake-errcodes'.
make[2]: Leaving directory `/tmp/postgresql-9.3.4/src/backend'
make[1]: Leaving directory `/tmp/postgresql-9.3.4/src/common'
rm -rf ./testtablespace
mkdir ./testtablespace
../../../src/test/regress/pg_regress --inputdir=. --temp-install=./tmp_check --top-builddir=../../..   --dlpath=.  --schedule=./parallel_schedule   
============== creating temporary installation        ==============
============== initializing database system           ==============
============== starting postmaster                    ==============
running on port 57536 with PID 2843
============== creating database "regression"         ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test tablespace               ... ok
parallel group (18 tests):  boolean char name varchar oid int2 int4 float4 text float8 int8 txid enum money bit uuid numeric rangetypes
     boolean                  ... ok
     char                     ... ok
     name                     ... ok
     varchar                  ... ok
     text                     ... ok
     int2                     ... ok
     int4                     ... ok
     int8                     ... ok
     oid                      ... ok
     float4                   ... ok
     float8                   ... ok
     bit                      ... ok
     numeric                  ... ok
     txid                     ... ok
     uuid                     ... ok
     enum                     ... ok
     money                    ... ok
     rangetypes               ... ok
test strings                  ... ok
test numerology               ... ok
parallel group (19 tests):  point lseg Box path circle abstime time polygon date timetz interval tinterval inet reltime macaddr comments tstypes timestamptz timestamp
     point                    ... ok
     lseg                     ... ok
     Box                      ... ok
     path                     ... ok
     polygon                  ... ok
     circle                   ... ok
     date                     ... ok
     time                     ... ok
     timetz                   ... ok
     timestamp                ... ok
     timestamptz              ... ok
     interval                 ... ok
     abstime                  ... ok
     reltime                  ... ok
     tinterval                ... ok
     inet                     ... ok
     macaddr                  ... ok
     tstypes                  ... ok
     comments                 ... ok
parallel group (6 tests):  geometry regex horology type_sanity oidjoins opr_sanity
     geometry                 ... ok
     horology                 ... ok
     regex                    ... ok
     oidjoins                 ... ok
     type_sanity              ... ok
     opr_sanity               ... ok
test insert                   ... ok
test create_function_1        ... ok
test create_type              ... ok
test create_table             ... ok
test create_function_2        ... ok
parallel group (2 tests):  copyselect copy
     copy                     ... ok
     copyselect               ... ok
parallel group (2 tests):  create_operator create_misc
     create_misc              ... ok
     create_operator          ... ok
parallel group (2 tests):  create_view create_index
     create_index             ... ok
     create_view              ... ok
parallel group (11 tests):  create_aggregate create_cast create_function_3 constraints triggers typed_table inherit drop_if_exists vacuum create_table_like updatable_views
     create_aggregate         ... ok
     create_function_3        ... ok
     create_cast              ... ok
     constraints              ... ok
     triggers                 ... ok
     inherit                  ... ok
     create_table_like        ... ok
     typed_table              ... ok
     vacuum                   ... ok
     drop_if_exists           ... ok
     updatable_views          ... ok
test sanity_check             ... ok
test errors                   ... ok
test select                   ... ok
parallel group (20 tests):  select_into select_distinct select_distinct_on select_implicit select_having subselect case aggregates transactions hash_index delete random portals arrays namespace update union btree_index join prepared_xacts
     select_into              ... ok
     select_distinct          ... ok
     select_distinct_on       ... ok
     select_implicit          ... ok
     select_having            ... ok
     subselect                ... ok
     union                    ... ok
     case                     ... ok
     join                     ... ok
     aggregates               ... ok
     transactions             ... ok
     random                   ... ok
     portals                  ... ok
     arrays                   ... ok
     btree_index              ... ok
     hash_index               ... ok
     update                   ... ok
     namespace                ... ok
     prepared_xacts           ... ok
     delete                   ... ok
parallel group (4 tests):  security_label privileges matview collate
     privileges               ... ok
     security_label           ... ok
     collate                  ... ok
     matview                  ... ok
parallel group (3 tests):  psql alter_generic misc
     alter_generic            ... ok
     misc                     ... ok
     psql                     ... ok
test rules                    ... ok
test event_trigger            ... ok
parallel group (16 tests):  select_views portals_p2 dependency foreign_key guc tsearch advisory_lock cluster bitmapops combocid functional_deps xmlmap tsdicts foreign_data json window
     select_views             ... ok
     portals_p2               ... ok
     foreign_key              ... ok
     cluster                  ... ok
     dependency               ... ok
     guc                      ... ok
     bitmapops                ... ok
     combocid                 ... ok
     tsearch                  ... ok
     tsdicts                  ... ok
     foreign_data             ... ok
     window                   ... ok
     xmlmap                   ... ok
     functional_deps          ... ok
     advisory_lock            ... ok
     json                     ... ok
parallel group (19 tests):  limit copy2 prepare truncate xml domain returning without_oid rowtypes with largeobject alter_table rangefuncs polymorphism temp plancache sequence conversion plpgsql
     plancache                ... ok
     limit                    ... ok
     plpgsql                  ... ok
     copy2                    ... ok
     temp                     ... ok
     domain                   ... ok
     rangefuncs               ... ok
     prepare                  ... ok
     without_oid              ... ok
     conversion               ... ok
     truncate                 ... ok
     alter_table              ... ok
     sequence                 ... ok
     polymorphism             ... ok
     rowtypes                 ... ok
     returning                ... ok
     largeobject              ... ok
     with                     ... ok
     xml                      ... ok
test stats                    ... ok
============== shutting down postmaster               ==============

=======================
 All 136 tests passed. 
=======================

通过上面的例子可以看出来，regress test的测试用例使用起来是相当的简单。下面我们来详细介绍下regress test。

2.启动regress test

通过上面的例子，我们看出启动regress test是很容易的，只要在src/test/regress目录下执行make check即可。

3.测试模式

例子中的执行语句如下：

../../../src/test/regress/pg_regress--inputdir=. --temp-install=./tmp_check --top-builddir=../../.. --dlpath=.--schedule=./parallel_schedule

这个--schedule=./parallel_schedule参数是什么意思呢？这是因为在PG/PGXC的regress test中，有两种测试模式，分别为：基于临时安装的数据库测试和基于已安装的数据库进行测试。两者的区别在于，临时安装的测试在测试开始之前会自行安装数据库集群，测试完成之后会将数据库集群停掉，并删除临时产物。反之则是基于已安装的集群。这就是临时安装的log：

../../../src/test/regress/pg_regress --inputdir=. --temp-install=./tmp_check --top-builddir=../../..   --dlpath=.  --schedule=./parallel_schedule   
============== creating temporary installation        ==============
============== initializing database system           ==============
============== starting postmaster                    ==============
running on port 57536 with PID 2843

[postgres@gorilla1 regress]$ gmake installcheck
gmake -C ../../../src/port all
gmake[1]: Entering directory `/tmp/postgresql-9.3.4/src/port'
gmake -C ../backend submake-errcodes
gmake[2]: Entering directory `/tmp/postgresql-9.3.4/src/backend'
gmake[2]: Nothing to be done for `submake-errcodes'.
gmake[2]: Leaving directory `/tmp/postgresql-9.3.4/src/backend'
gmake[1]: Leaving directory `/tmp/postgresql-9.3.4/src/port'
gmake -C ../../../src/common all
gmake[1]: Entering directory `/tmp/postgresql-9.3.4/src/common'
gmake -C ../backend submake-errcodes
gmake[2]: Entering directory `/tmp/postgresql-9.3.4/src/backend'
gmake[2]: Nothing to be done for `submake-errcodes'.
gmake[2]: Leaving directory `/tmp/postgresql-9.3.4/src/backend'
gmake[1]: Leaving directory `/tmp/postgresql-9.3.4/src/common'
rm -rf ./testtablespace
mkdir ./testtablespace
../../../src/test/regress/pg_regress --inputdir=. --psqldir='/opt/pgsql/bin'   --dlpath=.  --schedule=./serial_schedule 
(using postmaster on Unix socket,default port)
============== dropping database "regression"         ==============
NOTICE:  database "regression" does not exist,skipping
DROP DATABASE
============== creating database "regression"         ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test tablespace               ... ok
test boolean                  ... ok
test char                     ... ok
test name                     ... ok
test varchar                  ... ok
test text                     ... ok
test int2                     ... ok
test int4                     ... ok
test int8                     ... ok
test oid                      ... ok
test float4                   ... ok
test float8                   ... ok
test bit                      ... ok
test numeric                  ... ok
test txid                     ... ok
test uuid                     ... ok
test enum                     ... ok
test money                    ... ok
test rangetypes               ... ok
test strings                  ... ok
test numerology               ... ok
test point                    ... ok
test lseg                     ... ok
test Box                      ... ok
test path                     ... ok
test polygon                  ... ok
test circle                   ... ok
test date                     ... ok
test time                     ... ok
test timetz                   ... ok
test timestamp                ... ok
test timestamptz              ... ok
test interval                 ... ok
test abstime                  ... ok
test reltime                  ... ok
test tinterval                ... ok
test inet                     ... ok
test macaddr                  ... ok
test tstypes                  ... ok
test comments                 ... ok
test geometry                 ... ok
test horology                 ... ok
test regex                    ... ok
test oidjoins                 ... ok
test type_sanity              ... ok
test opr_sanity               ... ok
test insert                   ... ok
test create_function_1        ... ok
test create_type              ... ok
test create_table             ... ok
test create_function_2        ... ok
test copy                     ... ok
test copyselect               ... ok
test create_misc              ... ok
test create_operator          ... ok
test create_index             ... ok
test create_view              ... ok
test create_aggregate         ... ok
test create_function_3        ... ok
test create_cast              ... ok
test constraints              ... ok
test triggers                 ... ok
test inherit                  ... ok
test create_table_like        ... ok
test typed_table              ... ok
test vacuum                   ... ok
test drop_if_exists           ... ok
test updatable_views          ... ok
test sanity_check             ... ok
test errors                   ... ok
test select                   ... ok
test select_into              ... ok
test select_distinct          ... ok
test select_distinct_on       ... ok
test select_implicit          ... ok
test select_having            ... ok
test subselect                ... ok
test union                    ... ok
test case                     ... ok
test join                     ... ok
test aggregates               ... ok
test transactions             ... ok
test random                   ... ok
test portals                  ... ok
test arrays                   ... ok
test btree_index              ... ok
test hash_index               ... ok
test update                   ... ok
test delete                   ... ok
test namespace                ... ok
test prepared_xacts           ... ok
test privileges               ... ok
test security_label           ... ok
test collate                  ... ok
test matview                  ... ok
test alter_generic            ... ok
test misc                     ... ok
test psql                     ... ok
test rules                    ... ok
test event_trigger            ... ok
test select_views             ... ok
test portals_p2               ... ok
test foreign_key              ... ok
test cluster                  ... ok
test dependency               ... ok
test guc                      ... ok
test bitmapops                ... ok
test combocid                 ... ok
test tsearch                  ... ok
test tsdicts                  ... ok
test foreign_data             ... ok
test window                   ... ok
test xmlmap                   ... ok
test functional_deps          ... ok
test advisory_lock            ... ok
test json                     ... ok
test plancache                ... ok
test limit                    ... ok
test plpgsql                  ... ok
test copy2                    ... ok
test temp                     ... ok
test domain                   ... ok
test rangefuncs               ... ok
test prepare                  ... ok
test without_oid              ... ok
test conversion               ... ok
test truncate                 ... ok
test alter_table              ... ok
test sequence                 ... ok
test polymorphism             ... ok
test rowtypes                 ... ok
test returning                ... ok
test largeobject              ... ok
test with                     ... ok
test xml                      ... ok
test stats                    ... ok

=======================
 All 136 tests passed. 
=======================

4.结果判断

PG的regress test的流程为：逐个执行sql/目录下的sql脚本，将执行的结果重定向到results/目录下。而expected/目录下则是预期的执行结果。将results目录下的文件逐个与expected中文件进行diff。若文件不一致，则判定为对应的sql脚本执行结果为失败，反之为成功。

5.自做成test case的方法

根据4中介绍的regress test的执行流程可知，自做成test case的方法相当简单，可分为如下3步：

• 在sql/目录下加入自做成的test case的sql脚本。
• 在expected/目录下加入自做成test case的sql脚本的正确结果。需要注意的是，由于判断是使用diff命令，因此即使空格符也会导致diff的结果失败。所以，做成expected目录下的结果文件需要十分注意。
• 在parallel_schedule/ serial_schedule文件中添加调用sql脚本。

6.注意事项：

在使用regress test时，有如下几点需要注意：

• 如果使用临时安装的测试方式，pg的端口都是hard code的，使用前需要却用这些端口的占用情况。
• 如果使用已安装的测试方式，测连接数据库时，会使用coord的默认端口5432。所以必须使用默认安装的数据库集群才能使用已安装方法的回归测试。