关于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...

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