关于postgresql:源码分析-PostgreSQL-回归测试详解

本文首发于 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 脚本。

参考

1. http://www.postgresql.org/doc…
2. http://www.postgresql.org/doc…

各种 Makefile

src/Makefile.global  
src/Makefile  
src/makefiles/pgxs.mk   
contrib/contrib-global.mk   
contrib/xx/Makefile  
......  

本文转自：https://github.com/digoal/blo…

欢送关注我的微信公众号【数据库内核】：分享支流开源数据库和存储引擎相干技术。