Re: Meeting recap - Logic and Databases with Jeff Davis

On Jun 20, 2008, at 15:04, Selena Deckelmann wrote:

> 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.

Congrats on the new job, Jeff!

> We also had cocktails served by Gabrielle. THANK YOU, GAB!

I always miss cocktail nights. :-(

> 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

I think it's more useful, but if you're basing the schema on an
existing terminology used by your purchasing department, you can't go
too far wrong.

> -- 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.

Excellent points. I've never been anywhere where we had a data
dictionary. Still, "pending" isn't too bad -- especially if you've
ever looked at schemas in commercial products, where there can
sometimes seem to be a use of security through obscurity -- that is,
some vendors seem to try to protect their IP by giving their database
tables and columns completely meaningless names.

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

Boy, that sure seems like a bug. This should be legal, though:

try=# select sum(coalesce(column1, 0)) FROM (values (1), (NULL)) t;
sum
-----
1
(1 row)

Even if the above example works, I'd certainly recommend this version
(unless column1 is NOT NULL).

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

This I would expect.

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

Your explanation makes perfect sense. It's just that the first case
seems wrong (to me, at least).

> 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.

Yeah. COALESCE() is your friend. This is also why I try to make
columns NOT NULL as often as possible. NULLs are pretty evil.

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

Sounds interesting. Thanks for the summary, Selena, as usual.

Best,

David

>
>
> 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
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug