Topic: PostgreSQL for Pythoneers
Speaker: Jason Kirtland
Date: Thursday, 5/15/08
Location: FreeGeek, 1731 SE 10th Ave

Come join us for an evening of Pythoneering with Jason Kirtland, a key
force behind the Portland Python Interest Group -
http://wiki.python.org/moin/PortlandPythonUserGroup. They are having
Lightning Talks tomorrow night at Cubespace.

Jason hasn't provided too many details at this point, but I imagine it
will be a tour of a popular Python ORM and a discussion of using it
with PostgreSQL.

"A tour of SQLAlchemy [1], an open source database toolkit and object relational mapper in Python. The tour will be SQL heavy and Python-light, and I'm planning on focusing on the object relational mapping part of the tool.

If you have a schema that you'd like to see mapped, send it my way and I'll do my best to work it in to the presentation.

[1] http://www.sqlalchemy.org/"

Also, Jason has offered to map RT - a continuation of our refactored schema meeting for RT.

Refreshments afterward at the Lucky Lab!

This past weekend, Selena, Mark & I loaded up Mark's car with flyers, mugs & t-shirts & headed up to Bellingham, WA for Linuxfest NW (website: http://linuxfestnorthwest.org/).

Several LUGs in the area host this annual conference. This was my first Linuxfest & I was really impressed - it was very well-organized, well-attended and FUN.

Mark gave his talk about ptop/pgtop first thing Saturday morning. We managed to see some other talks too - Selena checked out some Drupal talks & I went to Eric Hopper's IPv6 discussion on Sunday.

We all had a great time running the booth, despite the lack of JD's entertaining presence. He was replaced by surprise guest boothster, Chris Travers! Chris showed up to give a LedgerSMB talk and we conned him into boothbeasting with us. Thanks, Chris! Even though it was crowded behind our table, I think four staffers was the right (minimum!) number to have for handling questions & greeting people.

The conference was really packed. I think I talked at length to somewhere around 12-15 people. We had a HUGE range of questions, all the way from people wondering about replication strategies to "What's a database, anyway?" We also ran through the inevitable "how do you pronounce it?" Selena gave a long demo of pgadmin to someone who is currently using SQL Server. A lot of people are very interested in a MySQL -> PostgreSQL migration tool.

We ran out of almost all printed materials we'd brought on the first day, necessitating phone calls to Josh B to acquire more flyers and a local copy shop to print them. Selena had the brilliant idea to get some elephant logo stickers printed as well; they were very popular.

Saturday night, Silicon Mechanics hosted the afterparty at the American Museum of Radio & Electricity, a truly excellent site for a geek party! We got to dink around with static electricity machines & toys from past centuries. Selena stepped up to the theramin while Mark & I checked out the old music boxes. The best part of the evening for me was when the docents set up a small tesla coil. >:) We had a great little indoor lightning show & Selena became part of a multi-person chain that lit up a fluorescent tube.

Back home now, attempting to recover from Conference Brain.

Thanks everyone who came out for the meeting! Next meeting on our schedule will be about Python and PostgreSQL, courtesy of Jason Kirtland.

We had a terrific turnout, and four new people. One of the FreeGeek folks came and talked with me after the meeting about how he had changed the database that FreeGeek uses from MySQL to PostgreSQL a while back.

We had two people from InnoTech attend that had also seen my 5-minute schpeale. One had already planned on coming (Hi Mike!), but I take credit for getting Craig interested! The other new person, Ed, works with Mike on Rails application development.

ANNOUNCEMENTS:

PDXPUG PgDay before OSCON is happening July 20, 2008! We have some volunteers to help organize it, I'm setting up a mailing list. Those who volunteered, expect some email this weekend about kicking things off.

PostgreSQL Conference East was a total success! ~100 people
Check out details at http://www.postgresqlconference.org/

MEETING DETAILS:

David's presentation was both an introduction to Rails development, and a tutorial on getting Rails to work with PostgreSQL. Some of his examples came directly from his work on I Want Sandy (www.iwantsandy.com), a web-based product that uses PostgreSQL.

There was intense discussion of drivers with thank-yous to Jeff Davis for adopting the Ruby PostgreSQL driver. There were useful, detailed examples of how to create data migrations and develop queries for Rails. There was a lot of monkey patching.

I enjoyed this talk so much. You can find audio of David's presentation of this talk at PostgreSQL Conference West here: http://www.postgresqlconference.org/fall2007_audio/RoR_Essentials.mp3

David is going to pass the updated slides on to me, and I'll post them on http://pugs.postgresq.org/pdx

Mark also brought the T2000 that was donated to the new Performance Lab that Mark is setting up. He's working with Joshua Drake on getting the machine racked and set up to run tests! Gabrielle used her serial-fu to help him get the machine booted and running.

Many of us retired to the Lucky Lab for refreshments.

Meeting in one week!

Date: Thursday, April 17, 2008
Time: 7:00pm (that's 1900 hours.)
Place: FreeGeek

Our own David Wheeler will give a repeat performance of his talk from PostgreSQL Conference West 2007:

Ruby on Rails Essentials for PostgreSQL Enthusiasts

Has all the hype got you jazzed to develop Ruby on Rails applications on PostgreSQL? Is virtually everything you find about Rails MySQL- specific? Come to this talk to learn all you need to know to make Rails and PostgreSQL work together harmoniously. Topics will likely include:

* The ins and outs of Rails migrations
* How to add support for foreign key constraints
* Managing partial indexes and other PostgreSQL-specific objects
* Working with views
* Monkey patching for fun and profit
* Supporting multi-column primary keys
* The joys and pains of Rails collections
* Working with time zones
* The antiquity of the Ruby PostgreSQL driver (NOT)
* Enforcing constraints in Rails *and* in the database
* Getting Rails to execute *your* queries instead of its own
* Saved queries for "fat models"

---
See you there!

Just quickly uploading some slides from the meeting. Covered all the crazy conferences coming up soon, and included a plug for Summer of Code and PostgreSQL -- if you're a student, its $4500 for the summer and great experience! Check it out!!

Also, a few slides about USPA and what we're up to!

Check out Joshua's recap of our ice-breaker over on his blog.

Ed Sawicki gave an overview of spam supression techniques he's using and gave us a few tips on tools that were useful to him. We recommended he have a look at some CIDR address types available on pgfoundry.org to help with some performance issues he found.

Save The Date: OSCON PostgreSQL Day

PostgreSQL community,

The O'Reilly Open Source Conference 2008 schedule will soon be revealed, and
some of you -- especially those speaking -- will be making your travel
arrangements to visit Portland. But, wait! Before you buy your plane
tickets, you'd better know about the PostgreSQL Day hosted by PDXPUG!

What: All-day PostgreSQL one-track mini-conference
When: Sunday, July 20th, 9am to 5pm, possible party afterwards
Who: PDXPUG will select speakers
Where: Portland Convention Center, Portland, Oregon

Of course, we are also looking for speakers for the PostgreSQL Day. PDXPUG
wants four hour-long sessions and one 2.5-hour workshop. Please
e-mail selena -at- postgresql.org with your offer to speak and speakers will be selected in mid-April.

Thanks to Josh Berkus, Joshua Drake, the OSCON conference and PDXPUG
for helping make this happen!

TOPIC: Managing internet services: using the right tool for the job
SPEAKER: Ed Sawicki
WHEN: March 20, 2008, 7pm
WHERE: FreeGeek, 1731 SE 10th Ave, Portland, OR 97214

Also: What the heck is the United States PostgreSQL Association?, Selena Deckelmann

AND: ELEPHANT BUTTONS! courtesy of David Fetter.

Here's what Ed had to say about his presentation:

"I'll probably end up doing live benchmarks, showing code
snippets, and explaining my rationale for why I chose
to do things the way I do."

"My current projects involve managing a variety
of Internet services, such as spam suppression and the
associated per-user black/white list management, DNS record
management, PKI key management, and a variety of others. Some
of these services require that I deal with the storage and
retrieval of both discrete IP addresses and CIDR blocks in
real time.

I've had to decide on data stores that include plain text
files, SQL using SQLite and Postgres, and constant databases
using tinycdb. At every stage of development I've had to
decide which of these was best based on tools available and
performance testing. For example, Postgres has IP and CIDR
data types and adequate facilities to search for IP addresses
within a CIDR block but performance pales in comparison to
simpler tools that use plain text files. As a result, my
applications use various data stores instead of just one."

Refreshment afterward at the Lucky Lab!

[cross posted w/ photos at selena's blog]
[Update: SQL for database schema courtesy of David Wheeler now included at the bottom of the page.]

Last night, PDXPUG and Code-n-Splode got together to refactor the database for RT. David Wheeler, Jeff Davis and Mark Wong led the discussion.

Through the course of the meeting, the group chose to pick out a few key features that would be better served if the database schema was more normalized, and offered a few new constraints we thought would help manage the data. Toward the end of the meeting, Igal suggested having a Perl workshop to refactor the code related to one or more of the database changes. The idea here was to be helpful, rather than just poking holes in the schema.

David started the discussion off with an introduction to RT. RT was created about 10 years ago by Jesse Vincent of Best Practical, and the database schema has been augmented over the years to accommodate new functionality. They support both MySQL and PostgreSQL as backends, and tend to dislike using database-specific technology.

The killer feature in RT is its email-to-ticket functionality. Managing tickets directly from email is convenient and reduces the maintenance overhead for users and developers. RT has some great workflow controls - allowing specific actions to be taken when certain types of changes occur in a ticket.

David reviewed a few key tables, and focused in on tickets, transactions and attachments. These tables are ones he was most familiar with from integrating RT in with I Want Sandy. A few key observations were:

• No foreign key constraints
• "Polymorphic relations" (tables storing multiple types of information)
• Default values in columns could be nicer

Addressing these three issues could fix potential data integrity problems, improve searchability and the make SQL queries of the data simpler and easier to maintain. We were also interested in improving the quality of the data stored in each row through constraints.

After this overview, we dove into a couple problems.

First, we weren't sure in the database how to group workflow actions together. Based on the database, each transaction is independent, and "scrip" actions appear to be based on a row insert. The symptom of this is that if you create a workflow control, you can't group together two "transactions" and yield a single email response. You'll get an email for each transaction recorded in the database.

We didn't dig into the associated Perl modules, but from a database perspective, a straight-forward normalization exercise solves the problem. We renamed transactions to ticket_events, and added a secondary table containing ticket_data. This new structure allows for multiple ticket_data rows to be linked to a single ticket_event. A simple view on these two tables could present nearly the same information as is available in transactions today, but now the system could group multiple ticket_data together.

We also tackled the schema representing email messages and their relationship with tickets. The existing database requires a JOIN between transactions and tickets to find. David and Jeff laid out a message table, split out interesting fields in email to make search and comparison easier. They also a foreign key to link email directly to both tickets and ticket_events.

Toward the end of the meeting, Mark wrote out the foreign key relationships for the new relational schema. We really needed a schema->diagram system so that we could view the schema in real-time. Some suggestions were made to use Autodoc with GraphViz to get real-time visualization of the entire schema. Next time!

The discussion also ranged into synthetic vs. natural keys, appropriate use and definition of NULLs, the difference between Entity relationship diagrams and relational schema diagrams, and the utility of workshops like this one to see how other people think about schema refactoring.

Before the meeting, David spent about an hour digging into the schema and talking with RT developers to get a feel for what types of changes they'd be open to. We're not sure that the suggestions from our little workshop will be incorporated, mostly because the codebase for RT is large and complex. David's plan was to send the suggestions on to Best Practical.

Topic: Extreme Database Makeover: RT
Speakers: Jeff Davis, Mark Wong, David Wheeler
When: Tuesday, February 26, 2008, 6:30pm
Where: Cubespace, 622 SE Grand Avenue, Portland, OR 97214

(In association with Code-n-Splode http://pdx.codensplode.org/)

Come join us for a little fun with RT. Jeff Davis recently went through an implementation of RT and had a few suggestions for their database schema. David Wheeler and Mark Wong promised to come help "refactor".

There is a .dot representation of their schema out on the web, and I figured I/we could print this out, and maybe bring in a few sheets of paper to document our changes.

If anyone has a printer and a few moments to volunteer, I'd appreciate help with the printing.

At the end of the meeting, we'd like to pass on some of our suggestions to the good folks at Best Practical.

Afterward, we'll seek refreshments at the Side Door Pub!