PostGIS Garden Test

The PostGIS Garden Test is a suite of SQL statements designed to stress test the PostGIS library within the PostgreSQL environment. It is a test generated from the PostGIS documentation reference.xml, RT_reference.xml (PostGIS and Raster Reference section of manual) that tries to test every documented function against every kind of geometry/geography/raster supported by PostGIS as well as common issues like handling of nulls, empty geometries etc. The main objective of the tests is to try to catch bugs that will crash the server. It is also useful for monitoring odd behavior as well as a regression test against prior versions of the software. To test against prior versions, build a test from the prior version reference.xml and test against both the old and the new version on the same server and then do a diff between the two (in newer version of the tests, you can diff the outputs in the *log_output table by logid. With postgis-1.4, postgis-1.5, and postgis-2.0 this is much easier to do as you can run both versions on the same server in different databases.

To build the garden test you need an xsltproc (the same that is used to build the docs or some other .xsl processor). On windows you can download precompiled binaries from ​http://www.zlatkovic.com/pub/libxml/ and its part of the libxslt package. You may also need to download the iconv and zlib packages at same site and extract bin/.dlls in same folder as your xsltproc.exe.

The (reference.xml or postgis.xml) and (postgis_gardentest.sql.xsl, raster_gardentest.sql.xsl (introduced in PostGIS 2.0) located in the doc and doc/xsl folders are both needed to build the tests.

NOTE: These tests are equivalent to a monkey testing the software so a lot of tests fail. It's good in a sense as a monkey will stuff a geometry, geography or raster in any function that takes that and as such will test the system as human monkeys/applications are bound to do and will also manage to crash the server in places where the software is not kiddie proofed. As of PostGIS 2.0, there are currently about 80,000 some odd tests for the geometry/geography side and about 1,600 tests for raster generated and run (these are growing). The PostGIS 1.5 has about 40,000 some odd tests for geometry/geography. The main reason for the increase in tests is the number of new geometry types/empty types and functions added in PostGIS 2.0.

Version PostGIS 1.5 0 2.0+ (the geometry/geography logging has been backported to PostGIS 1.5.3+ (currently in branch))

The latest version and branch version of PostGIS 1.5 includes logic that will during the tests - create two tables in the test database and populate them for later inspection: (for the 1.5 version the table names are defaulted to postgis_garden15_log, postgis_garden15_log_output - NOTE these can be changed by editing the var_logtable variable in the docs/xsl/postgis_gardentest.sql.xsl file)

• postgis_garden_log: This contains a record for each test and logs both the start, end times and the sql statement that was run. columns are:
  • logid - an autonumber it should end up being the same numbers for the same tests if the same generated script file is used to run against each postgresql box
  • log_label - a short description of the test
  • spatial_class - (will contain geometry or geography)
  • func - name of function being tested
  • g1 - type of geometry of first geometry
  • g2 - type of geometry of second or null if a single geometry function
  • log_start - start time (timestamp)
  • log_end - end time (timestamp)
  • log_sql - the SQL statement that was run.

If the test crashes before completion, the record with the max logid will tell you the query that crashed the server. I usually just run a query like this to figure out the crashing query:

SELECT * 
  FROM postgis_garden_log ORDER BY logid DESC limit 1;

• postgis_garden_log_output -- this outputs the query results for queries that return something in xml format. It uses the built-in query_to_xml function that has existed since PostgreSQL 8.3 to do that. columns:
  • logid - you can join this with the postgis_garden_log table to get the descriptive detail and SQL for the test.
  • log_output - an xml field that contains the query_to_xml output of the query.

There is also a companion for raster testing: raster_gardentest.sql.xsl. The structure follows the same pattern as postgis_garden_log except that g1/g2 fields may refer to a pixeltype or geometry type and spatial_class is raster.

-- To Build the script There is an unfortunate dependency on postgis_agg_mm.xml which is really not needed just create a dummy blank file called postgis_agg_mm.xml in the trunk/doc to get around this Then:

xsltproc -o geo_torturetest.sql trunk/doc/xsl/postgis_gardentest.sql.xsl trunk/doc/postgis.xml
xsltproc -o rast_torturetest.sql trunk/doc/xsl/raster_gardentest.sql.xsl trunk/doc/postgis.xml

-- run them

psql -p 5432 -U postgres -d postgres -c "DROP DATABASE testpostgis;"
psql -p 5432 -U postgres -d postgres -c "CREATE DATABASE testpostgis;" 
psql -p 5432 -U postgres -d testpostgis -c "CREATE LANGUAGE plpgsql;" 
psql -p 5432 -U postgres -d testpostgis -f postgis.sql 
psql -p 5432 -U postgres -d testpostgis -f spatial_ref_sys.sql
psql -p 5432 -U postgres -d testpostgis -f rtpostgis.sql 
psql -p 5432 -U postgres -d testpostgis -f geo_torturetest.sql > geo_torturetest_results.txt 
psql -p 5432 -U postgres -d testpostgis -f rast_torturetest.sql > rast_torturetest_results.txt   

There are a lot of nice benefits about logging the query and the output to a table:

• It's easier to inspect
• You can do a join between two log output tables by logid (as long as the logs were generated from the same test script) to compare results from different versions of postgis)

Here is an example query I use to figure out which tests don't complete that used to complete in PostGIS 1.5. Note for this -- I ran the PostGIS 1.5. batter of tests against a 1.5.2 install and a PostGIS 2.0 install and rename the log tables to be a bit more descriptive. The below will spit out all the tests that used to complete in postgis 1.5 install that now throw errors in postgis 2.0 install

SELECT p2.logid, p2.func,  p2.g1, p2.g2, p2.spatial_class, p2.log_sql
FROM postgis_garden15on20_log As p2 INNER JOIN
    postgis_garden15on152_log As p1 ON p2.logid = p1.logid
WHERE p1.log_end is NOT NULL and p2.log_end is NULL;

• You can also rerun a subset of the queries for closer inspection by just writing an sql statement something like below -- which will retest all tests for the ST_3DDistance function that completed successfully

SELECT logid, log_label, g1, g2, query_to_xml(log_sql, false,false,'') As result, log_sql
 FROM postgis_garden_log 
WHERE func = 'ST_3DDistance' AND log_end IS NOT NULL ;

-- basic stats getting timings in milliseconds of raster garden test just for ST_Intersects function for all pixel types --

SELECT logid, log_label, g1, g2, date_part('epoch',age(log_end,log_start))*1000 As dur_millisec
 FROM raster_garden_log 
WHERE func = 'ST_Intersects'  ;

• You can do even more cool things like creating an army of monkeys to try to crash your server using ​pgbench to simulate monkey users with a transaction script that

looks something like the below script that will run 10 random spatial queries on functions that start with ST_ that have succeeded in the past.

BEGIN;
 SELECT query_to_xml(log_sql) FROM postgis_garden_log  WHERE func LIKE 'ST_%' and log_end IS NOT NULL ORDER BY random() LIMIT 10;
END;

• You can even run stats with the outputs as we are showing in our PL/R series ​Quick Intro to R and PL/R part 1

Version PostGIS 1.5+

There is an unfortunate dependency on postgis_agg_mm.xml which is really not needed just create a dummy blank file called postgis_agg_mm.xml in the trunk/doc to get around this Then:

xsltproc -o torturetest.sql branches/1.5/doc/xsl/postgis_gardentest.sql.xsl branches/1.5/doc/postgis.xml

Below is a basic script to test tests and run them (if you are running from dos, change the slashes or just run everything from same folder:

psql -p 5432 -U postgres -d postgres -c "DROP DATABASE testpostgis;"
psql -p 5432 -U postgres -d postgres -c "CREATE DATABASE testpostgis;" 
psql -p 5432 -U postgres -d testpostgis -c "CREATE LANGUAGE plpgsql;" 
psql -p 5432 -U postgres -d testpostgis -f postgis.sql 
psql -p 5432 -U postgres -d testpostgis -f spatial_ref_sys.sql
psql -p 5432 -U postgres -d testpostgis -f torturetest.sql > torturetest_results.txt  

If you want it to output the actual query that is being tested, then use the -a command like so

psql -p 5432 -U postgres -d testpostgis -f torturetest.sql -a > torturetest_results.txt

If you want to include timing information - you can edit the generated torturetest.sql and add a

\timing 

Version PostGIS 1.4

xsltproc -o torturetest.sql branches/1.4/doc/xsl/postgis_gardentest.sql.xsl branches/1.4/doc/reference.xml

Testing subset of functions

A companion to the full postgis_gardentest.sql.xsl is the postgis_gardentest_subset.sql.xsl. This version skips the table creation battery of tests and allows you to specify a subset of functions to test.

This test is useful to test out new functions introduced or just test functions that have changed from prior versions to make sure they are behaving as expected. It makes the output file much shorter and easier to scan.

It will only test functions whose ids are contained in the inputfninclude parameter. NOTE this currently causes a bit of overtesting since ST_MakeLine and ST_M will match ST_MakeLine. Below is an example use.

xsltproc --param inputfninclude '"ST_Collect ST_Distance ST_DWithin ST_GeomFromGML ST_Length"' -o torturetest_subset.sql trunk/doc/xsl/postgis_gardentest_subset.sql.xsl trunk/doc/reference.xml
psql -p 5432 -U postgres -d postgres -c "CREATE DATABASE testpostgis;" 
psql -p 5432 -U postgres -d testpostgis -f postgis.sql 
psql -p 5432 -U postgres -d testpostgis -f spatial_ref_sys.sql
psql -p 5432 -U postgres -d testpostgis -f torturetest_subset.sql > torturetest_results.txt 
psql -p 5432 -U postgres -d postgres -c "DROP DATABASE testpostgis;"