2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Craig's PlanetPostgreSQL3 / Using the PostgreSQL TAP framework in extensions
craig.ringer

Using the PostgreSQL TAP framework in extensions

April 10, 2017/3 Comments/in Craig's PlanetPostgreSQL /by craig.ringer

Support for using the TAP protocol to run extended regression tests was added to PostgreSQL back in 9.4 with the adoption of Perl’s prove tool and Test::More to test initdb, pg_basebackup, etc.

Since then the TAP-based tests have been greatly expanded, particularly with the advent of the src/test/recovery tests and the PostgresNode module in PostgreSQL 9.6. PostgreSQL now comes with a built-in test harness for easily starting up postgres instances, creating and restoring backups for replication, setting up streaming, and lots more.

You can now use this to test your extensions.

pg_regress and its limitations

Extensions have long supported pg_regress based tests. Just drop the test scripts in sql/. Put the expected results in expected/. List the test names (sans directory and file extension) in your Makefile‘s REGRESS target. Run make check.

This is great as far as it goes, but it falls short when:

  • You need to test with multiple different server configurations that require a server restart;
  • You need to test replication, failover, or run tests on a read-replica;
  • You need to run client programs like pg_recvlogical, pg_basebackup, pg_dump etc in your tests;
  • You need to test behaviour that isn’t always exactly the same each time you run it;
  • You can’t avoid outputting things like dates, transaction IDs, or other data that varies for each test run;
  • Different postgres versions require tests with quite different inputs and/or outputs.

Even when it’s posible to use pg_regress, a TAP based test may be more convenient. It can be too much hassle to use conditionals in the makefile and multiple pg_regress. Using alternate output files is confusing and error-prone, and adds maintenance burden.

TAP helps you write integration tests. It’s great. Use it.

How to use TAP and Test::More in extensions

Make sure your PostgreSQL install was configured --enable-tap-tests when it was compiled. This is not (yet) the default.

Add the following to your Makefile, remembering to indent with tabs:

prove_installcheck: install
        rm -rf $(CURDIR)/tmp_check/log
        cd $(srcdir) && TESTDIR='$(CURDIR)' PATH="$(bindir):$$PATH" PGPORT='6$(DEF_PGPORT)' PG_REGRESS='$(top_builddir)/src/test/regress/pg_regress' $(PROVE) $(PG_PROVE_FLAGS) $(PROVE_FLAGS) $(if $(PROVE_TESTS),$(PROVE_TESTS),t/*.pl)
 
installcheck: prove_installcheck

Now create a directory with the name t/ in the same directory as your Makefile.

There, add Perl scripts to exercise your module’s functionality. Here’s a trivial example taken from src/test/recovery/t/001_stream_rep.pl in the PostgreSQL source tree and trimmed:

#!/usr/bin/env perl
use strict;
use warnings;
use Cwd;
use Config;
use TestLib;
use Test::More tests => 1;
# Initialize master node
my $node_master = get_new_node('master');
$node_master->init(allows_streaming => 1);
$node_master->start;
my $backup_name = 'my_backup';

# Take backup
$node_master->backup($backup_name);

# Create streaming standby linking to master
my $node_standby_1 = get_new_node('standby_1');
$node_standby_1->init_from_backup($node_master, $backup_name,
        has_streaming => 1);
$node_standby_1->append_conf('postgresql.conf', qq[
hot_standby_feedback = on
]);
$node_standby_1->start;

is(
  $node_standby_1->safe_psql('postgres', 'SELECT pg_is_in_recovery()'),
  't',
  'replica is in recovery'
);

Run it with

make -s prove_installcheck

It will print a results summary:

$ make -s prove_installcheck
t/test.pl .. 
1..1
ok 1 - replica is in recovery
ok
All tests successful.

Details – server logs, a more detailed test log, etc – will be output in tmp_check/.

If you got

TAP tests not enabled

instead, recompile PostgreSQL --enable-tap-tests, install, and try again.

The test isn’t that exciting, but it shows the sorts of things you can do that simply aren’t possible with pg_regress. You can find much more interesting examples in PostgreSQL’s recovery test suite and elsewhere in the source tree.

Extension authors can also write modules to use to share functionality across tests, which is well worth doing for bigger test suites. Ahaha

The TAP framework has made developing for PostgreSQL massively easier and it’s now aiding development of complex extensions like BDR and pglogical too. I strongly recommend getting a handle on it.

The main downside right now is that PGXS doesn’t know how to make a proper temp-install within an extension’s sub-directory. You have to install the extension to test it. This is no different to PGXS‘s support for make installcheck, though.

But, perl?

PostgreSQL’s TAP tests use Perl. But because it’s the “test anything protocol”, you don’t have to use Perl to write your extension’s tests. You’re free to use any tools and languages you want … but if you don’t use Perl, you won’t get the goodies the test framework bundles for Perl like PostgresNode, so Perl is usually the path of least resistance.

In-core tests are required to run on Perl 5.8 using only core modules + the IPC::Run module. No such restrictions apply to out-of-core modules, so you can at lest make your Perl code nicer to write and test.

Honestly, once you get used to it writing tests in Perl isn’t so bad. Make friends with Devel::Trace, use the test log files, the diag, note and explain Test::More commands, and make sure to use Carp. You’ll forget you’re writing Perl in no time.

If you really can’t stand the idea, you can always write some Perl glue to set up your required server environment then run your test scripts with the tools and languages of your choice. So long as they can produce TAP-compatible output, all will be well.

Future improvements

I’d quite like to backport the new PostgresNode.pm stuff to PostgreSQL 9.4, where the TAP support first appeared. This would make extension testing a lot easier.

PGXS could use true support for running the prove tests like it has for pg_regress with REGRESS=.

Thanks!

Huge credit for making this possible goes to Peter Eisentraut, Michael Paquier and Álvaro Herrera plus numerous reviewers and contributors.

Thanks to Tom Lane for applying the patches to install PostgresNode.pm etc, so we can start using this in extensions.

Tags: integration testing, perl, pg_regress, postgres, PostgreSQL, prove, regression testing, tap, testing
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
3 replies
  1. Aleksander Alekseev
    Aleksander Alekseev says:
    April 10, 2017 at 10:33 am

    For those who for some reason doesn’t like Perl I would recommend “Learning Perl” and “Intermediate Perl”. With these books you will discover that Perl is in fact very simple language, maybe even simpler then Python and Ruby. Unfortunately many people don’t like to read books 🙁

    Reply
  2. Arseny Sher
    Arseny Sher says:
    August 16, 2018 at 10:57 am

    Another option for people trying to avoid Perl is to have a look at testgres, which is basically the same ideas expressed in Python:
    https://postgrespro.github.io/testgres/

    Reply
    • craig.ringer
      craig.ringer says:
      September 2, 2018 at 2:09 pm

      Huh, that’s handy. I can’t justify picking it up so long as Pg core uses Perl and bundles TAP, PostgresNode.pm etc, though, since that’s what I have to use for anything submittable.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Traceable commit for PostgreSQL 10 When autovacuum does not vacuum
Scroll to top
×