Archive for March, 2006

Postgresql and XML

I’ve known about some work coordinating Postgresql and XML for years, looking at that post and it’s predecessor, it’s been in the works for at least 3 or 4 years. While I wasn’t watching, it made it into the Postgres standard distribution in the contrib section.

The general idea is that you can store an xml blob in the database, and then use it as semi-structured information for queries and the like. It’s not relational, normalized, nor is it a replacement for actually putting things into rows and columns like a database should be, but I actually have a use for it. (It can also be used to do XSLT transforms for output, but that’s not my problem now).

For a while, I’ve been caching complete results in an xml format in my database after parsing out the bits that I need — stuff that I’m not immediately interested in that might have fields added to it as time goes on, but that’s really not worth pulling out into it’s own relational model right now. Occasionally, it’s nice to be able to do some ad hoc mining of those blobs to see how well things have been performing, and that’s where some XML parsing in the database really beats eyeball or regex parsing.

So now that I’ve just been saving these chunks, I can issue a query like:

select id, amount, score 
   from xpath_table('id', 'xml_string', 'results', 
      'amount/value|amount/score', '1=1')
   as results (id int, amount text, score text)

and see two fields from deep into the xml pulled out for me to filter and join against.

This is one of those cases where there is an impedance mismatch between relatively dynamic data and static sql modeling — similar to the impedance mismatches that make object relational mapping so much fun. This sort of thing has been described in terms of type safety and compile time type checking. Often times you just need a way to defer parsing or understanding chunks of data until later. That article mentions blobs of XML in interfaces, this one is in data storage.

No comments

More random

Imagemagick is really wonderful, except when it’s not.

~$ convert -density 200x200 -units PixelsPerInch test.png test.tif
~$ convert -units PixelsPerInch -density 200x200 test.png test.tif

These two commands do different things, the first gives 200 pixels per centimeter, the second gives 200 pixels per inch. (This with 6.1.7, I’ve seen a bug report for 6.2.x as well.)

No comments


  • The microwave really kills the wifi performance of the mp3 player. Constant skipping, for 4 minutes at least.
  • Painting bathrooms is a pain. Prep is half the battle, the other half is cutting in all the random corners, edges, and behind the toilet. It’s about 1 hour of hand painting to 10 minutes of rolling.
  • Purdy roller covers work better than the cheap ones from Home Depot.
  • Low/No VOC paint is worth every penny. I like Safecoat from the Environmental Home Center
  • I have honey cream highlights in my hair now. From painting myself in a closet.
  • Orange peel texture hides so many surface imperfections it’s not even funny. Now I know why it’s used in inexpensive new construction.
  • It’s unbelievable how much I can get done when there’s not a toddler in the house.
No comments



No comments


boy, on the ferry

No comments


komodo dragon, eye to eye

No comments


  1. firefox extensions
  2. non-framework
  3. java ajax templating
  4. ajaz sortable lists

Apple has a safari update that disables cross domain http redirects. I wonder if that means that it’s going to break as much as I think it might break. Guess we’ll see after a reboot.

Posted because my tabs in safari aren’t going to survive a reboot.

No comments