[ Jeff’s slides are available at: http://www.pgcon.org/2008/schedule/events/83.en.html ]
Last night’s meeting was Jeff Davis’ last presentation as a Portlander (for a while at least!). He is leaving his job at Laika to pursue streaming database nirvana at Truviso, a company whose database product is based on PostgreSQL. There’s a bunch of whip-smart people working there, and Jeff will fit right in. If you’ve got other questions about what Truviso does, feel free to ask Jeff.
Our “question of the day” was – What are your plans for the summer? We had some great answers — including that Len’s son Ari is hosting the morning NPR news show for the next two weeks! Go Ari! It sounds like James *might* be leaving us for Redmond — stay tuned for more news on that. Gab will be riding her bike, and other people are going to just enjoy the fact that it finally seems like it *is* actually summertime.
Mark Wong is working with Joshua Drake (and Gab?) to install our new Performance Lab downtown on Saturday. Look for updates and announcements about what those servers will be used for from Mark and JD next week. HP donated two of the machines, and I believe that one other came from Sun.
We also had cocktails served by Gabrielle. THANK YOU, GAB!
Jeff’s presentation started with using logic to help express in English basic SQL concepts. For example, JOIN can be expressed with AND, and UNION can be expressed with OR. Thinking about what questions you’ll be asking about the data, and also forming those questions and statements of fact in ways that allow the assignment of clear ‘true’ or ‘false’ values will help enormously in ensuring that your data structures and SQL queries work the way that you and your customers expect.
At the risk of butchering the next discussion, here’s my take on the second part: Jeff spent quite a bit of time talking about the merits of using statements of fact when describing states, and how focusing on the truth values helps you test your assumptions about results from queries. His closing words on this topic: “Use an iterative process to clean up data before migrating it to a new system: make assumptions, test the assumptions, revise the assumptions, and repeat.”
He used the example of “pending” versus “approved by purchasing but not yet received”. This sparked a lively debate about the terminology – is it accurate to say that “pending” is Context Sensitive, and “approved by purchasing…” is Context Insensitive? Most of us seemed to agree that the second example was more useful — although Len Shapiro made the case that “pending” wasn’t so bad. He made the case that we just needed a Data Dictionary to explain it in the application. James brought up that programmers and businesses are lazy, and are unlikely to keep a piece of documentation like that up-to-date. There was a lot more discussion about this.. but at that point, I think I asked Gab for another drink, and then got Len a second drink.
In the third segment, Jeff went over two ways that the SQL standard treats NULLs – as UNKNOWN or as NO VALUE. In particular he shared these two statements:
test=# select sum(column1) FROM (values (1), (NULL)) t;
test=# select (1 + NULL) as plus;
We chewed on that for a bit. In the first case, NULL is being treated as “no value”, and just ignored by SUM(). In the second case, NULL is being treated as UNKNOWN – as the NULL is not treated as the same data type (or domain) as ‘1’, and so the answer is NULL.
(oh boy, i hope i got that right, or there’s going to be a long thread to explain this :D)
So, then James brought up the 17 possible meanings for NULL, and how our puny human brains could not compute. Several people made exploding brain noises, we continued to talk about NULL and aggregate functions and how COUNT() seems to violate ALL the proper rules, and… whew.
It was almost 9pm, and we all decided it was time to retire to the Lucky Lab.
Len brought me up to speed on Tom’s latest GSOC work, and I said that I would help test out his graphical Planner analyzer tool. It’s something that helps you understand why it is that the Planner chose the plan it did. I’m sure that I’ll be able to speak more articulately about it after I play with it :)