> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug
--
Sent via pdxpug mailing list (pdxpug@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pdxpug
No comments:
Post a Comment