meeting recap and the next meeting!

Hello!

Welcome to yet another edition of "what happened at PDXPUG".

Slides and SQL from the presentation are at: http://
pugs.postgresql.org/pdx/

I apologize to those of you who asked for an audio recording. I was
unable to produce a podcast of David's talk on DOMAINs. Everything
seemed to be ok, but at the crucial moment, the program I used seemed
to eat the audio. Maybe next time Randal will let us use his
recording setup with his iPod.

But onward!
--
Randal's brother Russ came to the meeting, so we were treated to the
story of their conceptions.

--
The EXPLAIN Operator Of The Month was: Index Scan. I ran through an
example of Index Scanning on a text column, showing the difference
between using a B-Tree or Hash index, and a situation (equality
tests) where the Hash Index performs quite a bit better. But, when
using LIKE, the B-Tree Index far outperformed the Hash Index. In
creating my vi 'presentation', I learned that you can have multiple
indexes on the same column (and the planner will chose the most
appropriate index for a query), and about functional and partial
indexes (which are really cool). David Wheeler mentioned creating
partial indexes on columns that tend to have lots of NULL values, and
also another example where indexing NULL values in a column is useful.

--
David's presentation on DOMAINs started with a common problem in
databases - a 'state' variable that has the same constraint, and
exists in multiple tables. After creating table after table with the
same CONSTRAINT, David implored us to stop the madness. Use a
DOMAIN! When constraints on a DOMAIN prevent an INSERT, the error
message is very similar.

After that example, David showed how DOMAINs really kick ass. This
more complex example validated UPC/EAN/GTIN values - GTIN is a
superset of UPC/EAN. DOMAINs can be validated by arbitrary
functions! David showed the code for this and demonstrated the use
of generate_series() in a fancy isa_gtin() function to verify the
checksum on GTINs.

DOMAINs pose problems with sorting. The GTINs, for example, is
fundamentally on a text field and may contain different numbers of
digits, which causes numeric sorts to fail on. To fix this, David
showed how to override operators (well, that's what I would call it
anyway). To produce indexes for the DOMAIN, you must create an
operator class - so he had examples for every operator needed to
create this. ORDER BY also uses the operators, as long as you specify
explicitly the operator you'd like to use. All the functions are in
the SQL file linked on the PDXPUG webpage.

We did talk about a datatype in contrib/ that does all/most of the
work for you in C - the isbn_issn extension is what you are looking for.

And that was it!

--
Randal agreed to give a presentation on removing inheritance from a
database. This will be on March 20th, 7pm. Gabrielle will be your
fearless leader for this meeting. I will be vacationing in Germany
for a couple of weeks in March!

--
After the meeting, we retired to the Lucky Lab where we drank a
couple pitchers and I asked some dumb questions about Object Oriented
Databases.

Thanks to everyone who attended.

-selena