Archive for September, 2011

27 September, 2011

Tuesday Tidbit – JOIN on multiple fields

by gorthx

I use this so rarely I have a hard time remembering the syntax; I always try to use a comma instead of AND. Which, of course, throws an error.

Looks like this:
SELECT [stuff]
FROM table1 t1
JOIN table2 t2 ON
(t1.field1 = t2.field1 AND t1.field2 = t2.field2);

Tags: ,
20 September, 2011

PgOpen recap

by gorthx

Another excellent conference from Selena!

I went to almost all of the talks I intended; there wasn’t a dud in the bunch. There were others I wanted to see as well (in particular Josh William’s “Monitor the Heck Out of Your Database”) but I don’t have the ability to be in two places at once yet. Fortunately, there is video feed; unfortunately, it requires silverlight, so I’ll have to wait until the video’s ported to its final location. Meanwhile, slide decks are available for most talks on the PostgreSQL Wiki.

My top three recommendations are:
– Vanessa Hurst’s Honey I Shrunk the Database, in which she discussed copying, slicing, and mutating your data. Which sounds a lot like what I used to do with DNA (which is, after all, also data.) I also learned that you can pass multiple tables to pg_dump -t (sometimes, the best bits are those little side tips you pick up in people’s talks.)
– Stephen Frost’s Finding Slow Queries and Fixing Them. Top tip for me from this talk: if you have slow DELETEs, try indexing FKs that depend on that table – otherwise you’re doing sequential scans on the tables affected by any CASCADE options.
– Bruce Momjian’s Unlocking the Lock Manager, although you should probably have already seen his “MVCC Unmasked” talk, which I haven’t.

Check them out when they come to a conference near you.

I also learned a lot in the pub track, like I finally understand why TRUNCATE is so much faster than DELETE FROM table [1], and of course came up with silly ideas, like implementing query hints in the form of “warmer…warmer…colder…warmer…” and installing Pg on a router. Which, oddly, came up in two completely unrelated conversations. Must be a sign.

What’s more, I had a fine time in Chicago to boot – even got to do a little bike tour along the lake before my flight out on Saturday. I have next year’s dates on my calendar and will definitely be attending.

Something I’d like to see at future conferences: an “Ask the Expert” or “Pg Helpdesk” group discussion/hackfest/learning lab where people can get help with problems. I’ll be thinking about how to make that work, and suggest it for next year.

1 – DELETE FROM table deletes the individual rows; TRUNCATE removes the file from disk and just re-creates the table, and is therefore faster. Thanks, Aaron!

11 September, 2011

My picks for PgOpen

by gorthx

Postgres Open is coming up this week! Here are the talks I want to check out:

I’m heading to Fast Read Scaling with repmgr first. While I’m not personally using replication at the moment, it never hurts to be prepared.

“How to migrate from Oracle to Postgres” has come up a lot lately at PDXPUG, so I’m interested in the “pinch of sarcasm” talk on Why You Should Not Move Away From Oracle.

I’ve heard Honey I Shrunk the Database is an excellent talk, and I’m curious to see how somebody else has solved the problem of creating appropriate test data.

In the immediately-after-lunch slot, I can’t decide between Billing System for a Telco and Identifying Slow Queries and Fixing Them.

…after that is my own logging talk, which I should probably attend, then a short break and I’ll finish out the day with PostgreSQL 9.1 Grand Tour.

Even though I could probably see Get Your Preferred Feature Developed in Portland, Wheeler is one of my favorite speakers. I’m going anyway.

I keep missing MVCC Unmasked, a mistake I don’t intend to repeat with Unlocking the Postgres Lock Manager.

Measuring Scalability and Performance with TCP looks intriguing.

Rob Treat always has good war stories, so Managing Databases in a DevOps Environment is next on the list.

I am super-excited about PL/R – I didn’t even know R was available as a procedural lang for Pg!

Last up, PostgreSQL at Urban Airship.

Other things I hope to squeeze in: an errand for my sister at Lyon & Healy, a stop at Fishman’s, and hopefully some kayaking and biking along the waterfront.

2 September, 2011

Updating multiple rows using a FROM clause

by gorthx

Last week I discovered a new-to-me Postgres feature, which has been around for quite a while: you can use a FROM clause to UPDATE multiple rows based on values in another table. (See docs here and here. The second link is older but has a more illustrative example.)

After some recent large-scale changes to our network infrastructure, we had to update our inventory database to reflect which equipment had been upgraded. In the past, I’ve accomplished this by using perl to generate a series of individual statements like
UPDATE equipment
SET hostname = 'shiny', model = 'FancyCiscoRouter'
WHERE hostname = 'crufty';

into a file, and just running that with i. The not-so-new way is faster and easier.

I had my main table of equipment names:
foo=# SELECT * from equipment;
ip | hostname | model
----------+--------------------+------- | old-and-in-the-way | 12000 | crusty | 7300 | decrepit | 7300

And a new table with the new equipment names & models:
foo=# SELECT * from new_equipment ;
ip | hostname | model
----------+----------+------- | unicorn | ASR | rainbow | 2900 | glitter | 2900

Here’s how you’d update the main table from the information in the new table:
UPDATE equipment
SET hostname=new_equipment.hostname, model = new_equipment.model
FROM new_equipment
WHERE equipment.ip = new_equipment.ip;

foo=# SELECT * FROM equipment ;
ip | hostname | model
----------+----------+------- | unicorn | ASR | rainbow | 2900 | glitter | 2900

…don’t forget to COMMIT. :)

Is anybody besides me super-annoyed that Cisco gave a router platform the same number series as the old Catalyst switches? Talk about confusing. “2900s? I thought we replaced those?”

Tags: ,