PGCon2010 - Final Release III
PGCon 2010
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Norman Yamada |
| Schedule | |
|---|---|
| Day | Talks - 2 - 2010-05-21 |
| Room | DMS 1160 |
| Start time | 15:00 |
| Duration | 01:00 |
| Info | |
| ID | 244 |
| Event type | Lecture |
| Track | Case Studies |
| Language used for presentation | English |
Reconciling and comparing databases redux
Deploying and testing triggers and functions in multiple databases
The Millburn Corporation is a hedge fund which uses complex data-driven trading models based on the daily prices of various commodities, currencies and other inputs. As part of our application development process, we use independent staging and development instances of our production database to let us have a smoother and less mistake-prone deployment of new models and price streams.
Last year, I presented a talk at PgCon 2009 that examined in broad detail how we make heavy use of different schemas to compare and reconcile data between our different environments. In this talk, I'll examine in detail our attempt to solve a problem we face in our database environment: how to test complex triggers and functions before they're deployed; and how to reconcile and track changes in these functions as they move from our development to our staging and production environments. Specifically, I'll describe how we make use of subversion, pg_dump and pgTAP to roll trigger and function changes to our development environment nightly; and how we test the integrity of the functions and trigger functions on our staging and production environments nightly using pgTAP.
Last year's talk covered how we use simple cross-schema queries and DBI-Link (for cross-database queries) to reconcile data between different databases. In more complex cases, we also use inherited tables with non-overlapping sequences and custom accessor functions to access different data streams in different schemas.
Function comparison and trigger comparisons across schemas and databases are more complex. I will examine how we use MD5 hashes to compare function bodies; walk through most of the common system catalog queries we use to verify function arguments and return values -- stealing ideas from Greg Sabino Mullane's check_postgres script; and then show the testing and deployment framework we've developed at Millburn to test triggers and functions using temporary schemas and fixture files (canned data) to regression test our functions.
Difficulties to be discussed: how to keep the same trigger code in different environments, even if the trigger must use different search paths in different environments; pitfalls of function caching; many other issues tk...