Last month I gave two presentations at the PostgreSQL Conference West. The first was a variation on Unit Test Your Database!, which I've now given about six times (and will at least two more times, including tomorrow night for Portland PLUG and in two weeks at the JPUG 10th Anniversary Conference). The second was a new talk, a 90-minute tutorial, called “pgTAP Best Practices.” And here it is, published on Slideshare. Enjoy.
Earlier this year, I was working on an administrative utility for Etsy that validates PostgreSQL database permissions. Of course, in order to verify that permissions were correct or needed updating, I had to have a way to examine PostgreSQL ACLs, which are arrays made of of strings that look like this:
I've been peripherally aware of the need for unicode normalization in my code for a while, but only got around to looking into it today. Although I use Encode to convert text inputs into Perl's internal form and UTF-8 or an appropriate encoding in all my outputs, it does nothing about normalization.
One of the cooler features of recent versions of PostgreSQL is support for CSV-formatted logging. I've never had a chance to use it, but after reading Josh's cool hack for determining sums of concurrent queries using windowing functions in PostgreSQL 8.4 to query a table generated from a CSV log, I just had to give it a try. But while there is decent documentation for loading up the contents of a CSV-formatted log file, there I couldn't find simple information on how to set it up. So I figured it out and record it here for posterity.
I'm pleased to announce that, after much thinking, committing, and not an insignificant amount of hair-pulling, pgTAP 0.22 has finally landed. Download it here. Many, many thanks to all who commented on my previous posts, made suggestions, and helped me on IRC to figure out how to get all this stuff to work. The crazy thing is that it does, quite well, all the way back to PostgreSQL 8.0.
While working on result set testing functions, I ran into a bit of weirdness when comparing rows between two cursors. I had code that looked more or less like this:
I've been following chromatic's new blog since it launched, and have read with particular interest his posts on the Perl 5 development and release process. The very long time between releases of stable versions of Perl has concerned me for a while, though I hadn't paid much attention until recently. There has been a fair amount of discussion about what it means for a release to be “stable,” from, among others, now-resigned Perl Pumpking Rafael Garcia-Suarez and Perl 5 contributor chromatic. Reading this commentary, I started to ponder what other major open-source projects might consider “stable,” and how they manage stability in their development and release processes. And it occurred to me that the Perl 5 code base is simultaneously treated too conservatively and -- more importantly -- not conservatively enough. What open-source projects treat their code highly conservatively?
Regular readers will know that I've been thinking a lot about testing SQL result sets and how to how to name result testing functions, and various implementation issues. I am very happy to say that I've now committed the first three such test functions to the Git repository. They've been tested on 8.4 and 8.3. Here's what I came up with.
I've been thinking more about testing SQL result sets and how to name functions that do such testing, and I've started to come to some conclusions. Some of the constraints I'm looking at:
I've been thinking more since I posted about testing SQL result sets, and I think I've settled on two sets of functions for pgTAP: one that tests two SQL queries (though you will be encouraged to use a prepared statement), and one to test two cursors. I'm thinking of naming them:
I've been continuing hacking on pgTAP in order to add a lot more schema-testing functionality and a few other niceties. But back when I started the project, I using it to write tests for CITEXT, which was great for my sanity as I developed it, but proved a bit controversial. In a pgsql-hackers post, Tom Lane wrote:
I just dropped a new version of pgTAP, following a few marathon hack sessions since my talk at PGCon (movie here, BTW). Actually, the new performs_ok() function came about as I wrote the presentation, but all the rest came on the flight home and in the few days since. Not sure when I'll hack on it this much again (it's getting a bit big at 5,758 lines of PL/pgSQL and SQL).
A few weeks ago, I twittered that, in my opinion, application developers should really learn how to use databases. And by that I mean SQL, really. I know that a lot of app developers like to use ORMs to access the database, so that you don't have to really think about it, but most ORMs are written by people who don't like databases, don't like SQL, haven't taken the time to learn it in any depth, and thus don't write very good SQL. And even if they do like SQL, that usually means they provide a way for you to execute SQL queries directly. The reason? Because the ORM does not really understand how building more and more complex queries can have negative performance issues, and that there is more than one way to do it. It's pretty common to have to go back to custom SQL to solve performance issues. So to solve those problems, you gotta understand SQL.
More thoughts on they whys and hows of database testing. I've been thinking about it quite a lot as I prepare for my PGCon presentation, and as I've corresponded with a couple of PostgreSQL core developers, and I wanted to share some of my thoughts here. I also want to know more about why one might think that database testing is pointless or useless Let me know!