本文首发于 2016-03-30 15:29:35
回归测试是 PostgreSQL 的测试方法之一。
回归测试,需要事先定义好测试脚本(通常是 SQL 脚本,放在 sql 目录中),同时定义好调用执行测试脚本的预期正确输出文件(通常放在 expected 目录中)。
测试使用 make check
或 make installcheck
进行,它会通过 pg_regress
程序调用 sql 目录中的 SQL,并收集输出结果(通常放到 results 目录中),最后 pg_regress 会对 expected 目录和 results 目录中的文件使用 diff 进行一一比较。
如果比较发现文件内容不一致,会将不一致的结果输出到 regression.diffs
文件中,并返回这个 TEST CASE failed。
但是这种测试方法实际上有一些需要注意的地方,例如我们使用不同的本地化设置,时区可能得到的结果和期望的结果就不一样。另外有些不可预知的结果,例如随机值,数据的顺序,执行计划和优化器相关参数有关。这些因素都可能导致测试结果和预期不一致,那么我们就需要人为去修复这种 failed。
PostgreSQL 的主代码测试文件在 src/test/regress
目录中。
这个目录的结构如下:
postgres@digoal-> ll -rt total 1.2M -rw-r--r-- 1 postgres postgres 579 Jun 10 03:29 standby_schedule # 测试standby的调度配置, 其实就是调度sql里的文件名 -rw-r--r-- 1 postgres postgres 2.3K Jun 10 03:29 serial_schedule # 串行测试的调度配置 -rw-r--r-- 1 postgres postgres 937 Jun 10 03:29 resultmap # 不同的测试平台的结果映射文件,因为不同平台某些测试结果可能不相同,所以一个expected文件不能支持所有的平台。例如浮点数测试。 -rwxr-xr-x 1 postgres postgres 4.4K Jun 10 03:29 regressplans.sh -rw-r--r-- 1 postgres postgres 20K Jun 10 03:29 regress.c -rw-r--r-- 1 postgres postgres 159 Jun 10 03:29 README -rw-r--r-- 1 postgres postgres 2.7K Jun 10 03:29 pg_regress_main.c -rw-r--r-- 1 postgres postgres 1.6K Jun 10 03:29 pg_regress.h -rw-r--r-- 1 postgres postgres 69K Jun 10 03:29 pg_regress.c -rw-r--r-- 1 postgres postgres 3.6K Jun 10 03:29 parallel_schedule # 并行测试的调度配置 -rw-r--r-- 1 postgres postgres 624 Jun 10 03:29 Makefile -rw-r--r-- 1 postgres postgres 5.6K Jun 10 03:29 GNUmakefile drwxrwxrwx 2 postgres postgres 4.0K Jun 10 03:38 output drwxrwxrwx 2 postgres postgres 4.0K Jun 10 03:38 input drwxrwxrwx 2 postgres postgres 4.0K Jun 10 03:38 data 一些测试数据 drwxrwxrwx 2 postgres postgres 4.0K Sep 7 14:51 sql # 测试用到的SQL drwxrwxr-x 2 postgres postgres 4.0K Sep 7 14:52 results # 通过pg_regress调用sql目录中的脚本,得到的结果 drwxrwxrwx 2 postgres postgres 4.0K Sep 7 14:51 expected # 执行sql目录中的文件对应的正确返回结果
上层目录结构如下,其中包含了一些其他的测试目标,例如隔离级别的测试,本地化测试,性能测试,线程安全测试。等。
postgres@digoal-> cd /opt/soft_bak/postgresql-9.4.4/src/test postgres@digoal-> ll total 36K drwxrwxrwx 2 postgres postgres 4.0K Jun 10 03:38 examples drwxrwxrwx 4 postgres postgres 4.0K Jun 10 03:41 isolation drwxrwxrwx 6 postgres postgres 4.0K Jun 10 03:38 locale -rw-r--r-- 1 postgres postgres 389 Jun 10 03:29 Makefile drwxrwxrwx 4 postgres postgres 4.0K Jun 10 03:38 mb drwxrwxrwx 4 postgres postgres 4.0K Jun 10 03:38 performance drwxrwxrwx 2 postgres postgres 4.0K Jun 10 03:38 perl drwxrwxrwx 10 postgres postgres 4.0K Sep 7 19:17 regress drwxrwxrwx 2 postgres postgres 4.0K Jun 10 03:38 thread
接下来我们看看 PostgreSQL 的回归测试程序 pg_regress 的用法,它不会安装到 PGHOME/bin 中,只在 src/test/regress 中存在。
$ cd src/test/regress $ src/test/regress/pg_regress --help PostgreSQL regression test driver Usage: pg_regress [OPTION]... [EXTRA-TEST]... Options: --config-auth=DATADIR update authentication settings for DATADIR --create-role=ROLE create the specified role before testing --dbname=DB use database DB (default "regression") --debug turn on debug mode in programs that are run --dlpath=DIR look for dynamic libraries in DIR --encoding=ENCODING use ENCODING as the encoding --inputdir=DIR take input files from DIR (default ".") --launcher=CMD use CMD as launcher of psql --load-extension=EXT load the named extension before running the tests; can appear multiple times --load-language=LANG load the named language before running the tests; can appear multiple times --max-connections=N maximum number of concurrent connections (default is 0, meaning unlimited) --outputdir=DIR place output files in DIR (default ".") --schedule=FILE use test ordering schedule from FILE (can be used multiple times to concatenate) --temp-install=DIR create a temporary installation in DIR --use-existing use an existing installation Options for "temp-install" mode: --extra-install=DIR additional directory to install (e.g., contrib) --no-locale use C locale --port=PORT start postmaster on PORT --temp-config=FILE append contents of FILE to temporary config --top-builddir=DIR (relative) path to top level build directory Options for using an existing installation: --host=HOST use postmaster running on HOST --port=PORT use postmaster running at PORT --user=USER connect as USER --psqldir=DIR use psql in DIR (default: configured bindir) The exit status is 0 if all tests passed, 1 if some tests failed, and 2 if the tests could not be run for some reason. Report bugs to <pgsql-bugs@postgresql.org>.
在 PostgreSQL 源码根目录,或者源码的 regress 目录中执行如下:
make check # 测试时需要初始化数据库集群 make installcheck # 使用以及启动的数据库集群测试,不需要初始化数据库集群
以下同时测试主代码以及 contrib 的代码:
make check-world make installcheck-world
如果要使用自定义的 diff 参数,可以设置一个环境变量,例如:make check PG_REGRESS_DIFF_OPTS='-u'
。
同时我们还可以使用不同的 LOCALE 进行测试。例如:
make check LANG=de_DE.utf8 make check NO_LOCALE=1 make check LANG=C ENCODING=EUC_JP
当我们要测试调度中不包含的测试 SQL 时,可以使用 EXTRA_TESTS
参数,至于这些脚本为什么默认不包含在调度中,可能是因为这些 SQL 脚本可能对平台的依赖比较严重,所以没有放到默认的测试中。例如:
make check EXTRA_TESTS=collate.linux.utf8 LANG=en_US.utf8 make check EXTRA_TESTS=numeric_big
接下来我们看看调度文件以及 sql 脚本目录:
postgres@digoal-> pwd /opt/soft_bak/postgresql-9.4.4/src/test/regress postgres@digoal-> less serial_schedule # src/test/regress/serial_schedule # This should probably be in an order similar to parallel_schedule. test: tablespace test: boolean test: char test: name test: varchar test: text test: int2 test: int4 test: int8 ......
并行调度:
postgres@digoal-> less parallel_schedule # ---------- # src/test/regress/parallel_schedule # # By convention, we put no more than twenty tests in any one parallel group; # this limits the number of connections needed to run the tests. # ---------- # run tablespace by itself, and first, because it forces a checkpoint; # we'd prefer not to have checkpoints later in the tests because that # interferes with crash-recovery testing. test: tablespace # ---------- # The first group of parallel tests # ---------- test: boolean char name varchar text int2 int4 int8 oid float4 float8 bit numeric txid uuid enum money rangetypes pg_lsn regproc ......
调度文件的 test: 后面跟的就是sql目录下的文件名(不含 .sql 后缀)。
postgres@digoal-> less sql/ total 1940 drwxrwxrwx 2 postgres postgres 4096 Sep 7 14:51 ./ drwxrwxrwx 10 postgres postgres 4096 Sep 7 22:34 ../ -rw-r--r-- 1 postgres postgres 2237 Jun 10 03:29 abstime.sql -rw-r--r-- 1 postgres postgres 4097 Jun 10 03:29 advisory_lock.sql -rw-r--r-- 1 postgres postgres 20295 Jun 10 03:29 aggregates.sql -rw-r--r-- 1 postgres postgres 24882 Jun 10 03:29 alter_generic.sql -rw-r--r-- 1 postgres postgres 54461 Jun 10 03:29 alter_table.sql -rw-r--r-- 1 postgres postgres 17244 Jun 10 03:29 arrays.sql -rw-r--r-- 1 postgres postgres 594 Jun 10 03:29 async.sql -rw-r--r-- 1 postgres postgres 1365 Jun 10 03:29 bitmapops.sql -rw-r--r-- 1 postgres postgres 6406 Jun 10 03:29 bit.sql -rw-r--r-- 1 postgres postgres 4164 Jun 10 03:29 boolean.sql ......
所以前面提到的 EXTRA_TESTS 实际上也是 sql 目录中的文件名(不带 .sql 后缀)。
make check EXTRA_TESTS=collate.linux.utf8 LANG=en_US.utf8 make check EXTRA_TESTS=numeric_big
来实际的试一下吧:
postgres@digoal-> pwd /opt/soft_bak/postgresql-9.4.4/src/test/regress postgres@digoal-> make installcheck-parallel //并行测试,使用已经开启的现有的数据库集群 make -C ../../../src/port all ...... ../../../src/test/regress/pg_regress --inputdir=. --psqldir='/opt/pgsql9.4.4/bin' --dlpath=. --schedule=./parallel_schedule (using postmaster on /data01/pg_root_1921, port 1921) ============== dropping database "regression" ============== DROP DATABASE ============== creating database "regression" ============== CREATE DATABASE ALTER DATABASE ============== running regression test queries ============== test tablespace ... ok ...... parallel group (19 tests): limit conversion sequence returning without_oid polymorphism copy2 xml prepare plancache rowtypes temp domain with truncate largeobject rangefuncs alter_table plpgsql plancache ... ok limit ... ok plpgsql ... ok copy2 ... ok temp ... ok domain ... ok rangefuncs ... FAILED prepare ... ok without_oid ... ok conversion ... ok truncate ... ok alter_table ... ok sequence ... ok polymorphism ... FAILED rowtypes ... ok returning ... ok largeobject ... ok with ... FAILED xml ... ok test stats ... ok ...... ========================= 22 of 145 tests failed. ========================= The differences that caused some tests to fail can be viewed in the file "/opt/soft_bak/postgresql-9.4.4/src/test/regress/regression.diffs". A copy of the test summary that you see above is saved in the file "/opt/soft_bak/postgresql-9.4.4/src/test/regress/regression.out". make: *** [installcheck-parallel] Error 1
有些测试失败了,diff 文件已经输出到 /opt/soft_bak/postgresql-9.4.4/src/test/regress/regression.diffs
,我们可以查看一下看看为什么测试结果和预期结果不一致。
postgres@digoal-> less regression.diffs *** /opt/soft_bak/postgresql-9.4.4/src/test/regress/expected/pg_lsn.out 2015-06-10 03:29:38.000000000 +0800 --- /opt/soft_bak/postgresql-9.4.4/src/test/regress/results/pg_lsn.out 2015-09-07 22:45:04.413922536 +0800 *************** *** 72,92 **** generate_series(1, 5) k WHERE i <= 10 AND j > 0 AND j <= 10 ORDER BY f; ! QUERY PLAN ! -------------------------------------------------------------------------- ! Sort ! Sort Key: (((((i.i)::text || '/'::text) || (j.j)::text))::pg_lsn) ! -> HashAggregate ! Group Key: ((((i.i)::text || '/'::text) || (j.j)::text))::pg_lsn -> Nested Loop -> Function Scan on generate_series k ! -> Materialize ! -> Nested Loop ! -> Function Scan on generate_series j ! Filter: ((j > 0) AND (j <= 10)) ! -> Function Scan on generate_series i ! Filter: (i <= 10) ! (12 rows) SELECT DISTINCT (i || '/' || j)::pg_lsn f FROM generate_series(1, 10) i, --- 72,90 ---- generate_series(1, 5) k WHERE i <= 10 AND j > 0 AND j <= 10 ORDER BY f; ......
对于主代码,如果我们需要自定义测试 SQL,我们可以修改 regress/sql
目录下的文件,或者新增文件。同时修改 regress/expected
目录下的对应期望文件,或者现在期望文件。
如果是新增文件的情况,我们还需要修改调度文件 regress/serial_schedule和regress/parallel_schedule
,把测试加入调度。
最后,再以 ltree
插件为例,看看如何配置一个外加插件的回归测试。
ltree 的源码目录:
postgres@digoal-> cd contrib/ postgres@digoal-> cd ltree/ postgres@digoal-> ll -rt total 1.1M -rw-r--r-- 1 postgres postgres 517 Jun 10 03:29 Makefile -rw-r--r-- 1 postgres postgres 2.4K Jun 10 03:29 ltxtquery_op.c -rw-r--r-- 1 postgres postgres 11K Jun 10 03:29 ltxtquery_io.c -rw-r--r-- 1 postgres postgres 7.9K Jun 10 03:29 ltree--unpackaged--1.0.sql -rw-r--r-- 1 postgres postgres 994 Jun 10 03:29 ltreetest.sql -rw-r--r-- 1 postgres postgres 13K Jun 10 03:29 ltree_op.c -rw-r--r-- 1 postgres postgres 6.9K Jun 10 03:29 _ltree_op.c -rw-r--r-- 1 postgres postgres 14K Jun 10 03:29 ltree_io.c -rw-r--r-- 1 postgres postgres 7.3K Jun 10 03:29 ltree.h -rw-r--r-- 1 postgres postgres 16K Jun 10 03:29 ltree_gist.c -rw-r--r-- 1 postgres postgres 13K Jun 10 03:29 _ltree_gist.c -rw-r--r-- 1 postgres postgres 155 Jun 10 03:29 ltree.control -rw-r--r-- 1 postgres postgres 18K Jun 10 03:29 ltree--1.0.sql -rw-r--r-- 1 postgres postgres 7.1K Jun 10 03:29 lquery_op.c -rw-r--r-- 1 postgres postgres 263 Jun 10 03:29 crc32.h -rw-r--r-- 1 postgres postgres 4.1K Jun 10 03:29 crc32.c drwxrwxrwx 2 postgres postgres 4.0K Jun 10 03:38 sql drwxrwxrwx 2 postgres postgres 4.0K Jun 10 03:38 expected drwxrwxrwx 2 postgres postgres 4.0K Jun 10 03:38 data
contrib/ltree 的 Makefile 如下(在这里配置回归测试的调度,用到变量 REGRESS,对应 sql 目录中的脚本文件名):
# contrib/ltree/Makefile MODULE_big = ltree OBJS = ltree_io.o ltree_op.o lquery_op.o _ltree_op.o crc32.o \ ltxtquery_io.o ltxtquery_op.o ltree_gist.o _ltree_gist.o PG_CPPFLAGS = -DLOWER_NODE EXTENSION = ltree DATA = ltree--1.0.sql ltree--unpackaged--1.0.sql REGRESS = ltree ifdef USE_PGXS PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) else subdir = contrib/ltree top_builddir = ../.. include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif
其中:
include $(PGXS)
或:
include $(top_builddir)/src/Makefile.global
都指向了:
src/makefiles/pgxs.mk
这个 makefile 中会用到回归测试相关的两个变量:
# REGRESS -- list of regression test cases (without suffix) # REGRESS_OPTS -- additional switches to pass to pg_regress
引用 src/makefiles/pgxs.mk
的部分内容如下:
ifdef REGRESS # Select database to use for running the tests ifneq ($(USE_MODULE_DB),) REGRESS_OPTS += --dbname=$(CONTRIB_TESTDB_MODULE) else REGRESS_OPTS += --dbname=$(CONTRIB_TESTDB) endif # where to find psql for running the tests PSQLDIR = $(bindir) # When doing a VPATH build, must copy over the data files so that the # driver script can find them. We have to use an absolute path for # the targets, because otherwise make will try to locate the missing # files using VPATH, and will find them in $(srcdir), but the point # here is that we want to copy them from $(srcdir) to the build # directory. ifdef VPATH abs_builddir := $(shell pwd) test_files_src := $(wildcard $(srcdir)/data/*.data) test_files_build := $(patsubst $(srcdir)/%, $(abs_builddir)/%, $(test_files_src)) all: $(test_files_build) $(test_files_build): $(abs_builddir)/%: $(srcdir)/% $(MKDIR_P) $(dir $@) ln -s $< $@ endif # VPATH .PHONY: submake submake: ifndef PGXS $(MAKE) -C $(top_builddir)/src/test/regress pg_regress$(X) endif # against installed postmaster installcheck: submake $(REGRESS_PREP) $(pg_regress_installcheck) $(REGRESS_OPTS) $(REGRESS) ifdef PGXS check: @echo '"$(MAKE) check" is not supported.' @echo 'Do "$(MAKE) install", then "$(MAKE) installcheck" instead.' else check: all submake $(REGRESS_PREP) $(pg_regress_check) --extra-install=$(subdir) $(REGRESS_OPTS) $(REGRESS) endif endif # REGRESS
这里用到了 ltree 中 Makefile 中定义的 subdir 和 REGRESS 变量,如下:
top_builddir = ../.. subdir = contrib/ltree REGRESS = ltree
所以我们在 contrib/ltree 中执行 make check 会执行:(指PGXS未定义时)
$(pg_regress_check) --extra-install=$(subdir) $(REGRESS_OPTS) $(REGRESS)
pg_regress_check 这个变量在 src/Makefile.global
中定义了,其实就是 pg_regress 命令的调用:
src/Makefile.global src/Makefile.global:srcdir = . pg_regress_locale_flags = $(if $(ENCODING),--encoding=$(ENCODING)) $(NOLOCALE) pg_regress_check = $(top_builddir)/src/test/regress/pg_regress --inputdir=$(srcdir) --temp-install=./tmp_check --top-builddir=$(top_builddir) $(pg_regress_locale_flags) $(EXTRA_REGRESS_OPTS)
在 contrib/ltree 中执行 make check 最终执行的是(没有定义的变量直接忽略):
../../src/test/regress/pg_regress --inputdir=. --temp-install=./tmp_check --top-builddir=../.. --extra-install=contrib/ltree ltree
我们可以直接到 ltree 的源码目录测试这条命令:
[root@digoal ~]# chown -R postgres:postgres /opt/soft_bak/postgresql-9.4.4 [root@digoal ~]# su - postgres postgres@digoal-> cd /opt/soft_bak/postgresql-9.4.4/contrib/ltree/ postgres@digoal-> ../../src/test/regress/pg_regress --inputdir=. --temp-install=./tmp_check --top-builddir=../.. --extra-install=contrib/ltree ltree ============== removing existing temp installation ============== ============== creating temporary installation ============== ============== initializing database system ============== ============== starting postmaster ============== running on port 57636 with PID 27852 ============== creating database "regression" ============== CREATE DATABASE ALTER DATABASE ============== running regression test queries ============== test ltree ... ok ============== shutting down postmaster ============== ============== removing temporary installation ============== ===================== All 1 tests passed. =====================
另外一种测试时 installcheck,和 check 不同的是,installcheck 不需要初始化数据库,是在我们开启了数据库集群的情况下的测试。
同样的方法,我们可以发现它调用的是:
$(pg_regress_installcheck) $(REGRESS_OPTS) $(REGRESS)
通过 src/Makefile.global 的定义:
pg_regress_installcheck = $(top_builddir)/src/test/regress/pg_regress --inputdir=$(srcdir) --psqldir='$(PSQLDIR)' $(pg_regress_locale_flags) $(EXTRA_REGRESS_OPTS) bindir := $(shell $(PG_CONFIG) --bindir)
以及 src/makefiles/pgxs.mk
PSQLDIR = $(bindir)
最终转换为:
../../src/test/regress/pg_regress --inputdir=. --psqldir='/opt/pgsql/bin' ltree
启动数据库后,就可以进行测试了。同样需要注意(PGPORT PGHOST PGDATABASE PGUSER 等)环境变量。
postgres@digoal-> pg_ctl start postgres@digoal-> pwd /opt/soft_bak/postgresql-9.4.4/contrib/ltree postgres@digoal-> ../../src/test/regress/pg_regress --inputdir=. --psqldir='/opt/pgsql/bin' ltree (using postmaster on /data01/pg_root_1921, port 1921) ============== dropping database "regression" ============== DROP DATABASE ============== creating database "regression" ============== CREATE DATABASE ALTER DATABASE ============== running regression test queries ============== test ltree ... ok ===================== All 1 tests passed. =====================
所以插件的回归测试配置也很简单,同样需要 sql, expected 目录,以及通过配置 Makefile 来指定需要回归测试的 sql 脚本。
各种Makefile
src/Makefile.global src/Makefile src/makefiles/pgxs.mk contrib/contrib-global.mk contrib/xx/Makefile ......
本文转自:https://github.com/digoal/blog/blob/master/201509/20150907_04.md
欢迎关注我的微信公众号【MySQL数据库技术】。
标题 | 网址 |
---|---|
GitHub | https://dbkernel.github.io |
知乎 | https://www.zhihu.com/people/dbkernel/posts |
思否(SegmentFault) | https://segmentfault.com/u/dbkernel |
掘金 | https://juejin.im/user/5e9d3ed251882538083fed1f/posts |
开源中国(oschina) | https://my.oschina.net/dbkernel |
博客园(cnblogs) | https://www.cnblogs.com/dbkernel |