Meeting recap - Logic and Databases with Jeff Davis

Hello!

Next meeting will be the PDXPUG PgDay conference on Sunday July 20,
2008. We'll be having talks all day at the Oregon Convention Center,
and then a Party at the Gotham Tavern -- 2240 N Interstate Ave # 160,
Portland, OR 97227. The tavern is on the Yellow line of the MAX, and
all are welcome to join us.

---

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;
sum
-----
1
(1 row)

test=# select (1 + NULL) as plus;
plus
------

(1 row)

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 :)

Thanks all for a great meeting!

--
Selena Deckelmann
United States PostgreSQL Association - http://www.postgresql.us
PDXPUG - http://pugs.postgresql.org/pdx
Me - http://www.chesnok.com/daily