from __future__ import *

The OSCON 2005 PostgreSQL Presentations are up, but a bunch of them are in OpenOffice format. I went through the pain of installing OpenOffice to convert them to PDF. For completeness, I have also included the presentations that were already available as PDF.

Chris Browne:

• Replicating PostgreSQL Databases Using Slony-I
• Event Propagation in Slony-I
• Sample Replication RT/3
• Upcoming Slony-I Enhancements

Joe Conway:

• Terrabytes of Business Intelligence

Lance Obermeyer:

• Running PostgreSQL On Windows

Bruce Momjian:

• State of PostgreSQL 8.1
• Porting PostgreSQL To Windows

Aaron Thul:

• PostgreSQL Built Your Car

Robert Treat:

• Explaining Explain

We're currently experimenting with various optimization techniques for our databases using PostgreSQL's features, and we've become quite interested in the Table Partitioning in PostgreSQL 8.1. Bizgres has this feature now (amongst others that will likely benefit our app) in its 0.7 release, so I decided to give it a whirl.

Installing Bizgres 0.7 on Mac OS X is easy enough, but it loses a few points (no big deal, it's beta):

• The installer doesn't use Apple's installer, it uses some InstallAnywhere crap that I don't really trust
• The installer is probably one of the ugliest things I have ever seen on Mac OS X outside of perversiontracker
• It throws a readline library in a Fink-owned location if it's not already there (install_name_tool, anyone?!)
• It doesn't install any sort of preference pane, StartupItem, or GUI tools at all
• The demos are there, but they're sitting in tar files... wtf?

Installing on FreeBSD er.. loses even more points. It's not a supported platform yet, so that's to be expected. The build process depends on a suite of bash scripts to set some environment variables, and ant to do the build. Autoconf happens somewhere too, but that's not invoked directly. The problems here are:

• Bash isn't the default shell in FreeBSD, and isn't even normally installed (unless you did it yourself)

• The bash scripts assume /bin/bash (where it's /usr/local/bin/bash on FreeBSD)

• The Ant build script, common.xml:

  • Needs OS-specific incantations
  • Assumes make is GNU make
  • Assumes bash is at /bin/bash (even though it checks for its presence anywhere in $PATH!)

• Oh, and it needs Java for Ant:

  Java Is A Royal Pain In The Ass On FreeBSD (and installing it is beyond the scope of this post).

So anyway, I fixed those things and made a patch. Here's how you could build and install with it:

• Download the 0.7 source tarball

• Do this:

  cd bizgres
  curl -s http://redivi.com/~bob/bizgres-0_7_4-FreeBSD-1.patch | patch -u -p0
  su
  bash
  source source-before-build.sh
  ant -Ddebug=false -DinstallRoot=/usr/local/bizgres -DdevoBuild=true install
  exit
  exit
  

This will install bizgres to /usr/local/bizgres-0_7_4. It's not going to make any rc scripts for you, but they can be trivially adapted from the PostgreSQL port.

Note that:

• This is the first time I've ever attempted to port something to FreeBSD (though I do have extensive Mac OS X experience)
• Bizgres 0.7 is beta software
• This is largely untested
• FreeBSD is not a supported platform (by them, or me)

If this causes you to lose all of your data, first, make sure that you weren't using MySQL. If you were indeed using Bizgres on FreeBSD, then blame yourself for not listening to the above.

UPDATE:

Changed the URL of the patch to one that compiles with OpenSSL support by default. To add OpenSSL support to an already patched source (or to JUST add OpenSSL support to unpatched source), use the following:

http://redivi.com/~bob/bizgres-0_7_4-OpenSSL.patch

/usr/ports/databases/postgresql-contrib comes with the very useful (until 8.1, anyway) pg_autovacuum daemon, but no rc script. Here's one I whipped up:

/usr/local/etc/rc.d/019.pg_autovacuum.sh

#!/bin/sh
#
# PROVIDE: pg_autovacuum
# REQUIRE: postgresql
# KEYWORD: FreeBSD shutdown

# Add the following line to /etc/rc.conf to enable `pg_autovacuum':
#
# pg_autovacuum_enable="YES"
#
#

. /etc/rc.subr


pg_autovacuum_enable=${pg_autovacuum_enable:-"NO"}
pg_autovacuum_flags=${pg_autovacuum_flags:-"-v 300 -V 0.5"}

name=pg_autovacuum
rcvar=`set_rcvar`
command=/usr/local/bin/pg_autovacuum
load_rc_config ${name}
command_args="-D ${pg_autovacuum_flags}"
pg_autovacuum_user=pgsql

run_rc_command "$1"

I did submit a PR with this by way of send-pr, but it doesn't seem to have ended up in the PR repository. I'm not sure what to do about that, as it's the recommended way to submit PRs!

It seems that most of my gripes about MySQL boil down to three things:

1. It sucks if your recent previous experience is with more capable SQL databases. I actually learned SQL using MySQL back in 1997 or whatever, but it was using hardcopy literature written for the ANSI standard... so I'd write SQL with views, subselects, and whatnot, and curse MySQL for not having the features I thought I wanted. Once I moved on to the PostgreSQL/MSSQL/Oracle stuff I knew that I actually did want those features.

   I think the last project I used MySQL on was in 1998 or 1999. Everything since has been other databases, but I decided to try MySQL this time around. I decided to give it another shot because I know some people running high volume stuff on MySQL, I had met a couple respectable people working for MySQL over the years, I thought it would be fast (it's not -- what we really need is materialized views, and I had to write a daemon to poll the database to implement them), and I knew I wouldn't have to spend much time setting it up. I think this was a premature optimization, and I'm pretty sure that something else would've been faster anyway now that I have a feel for what MySQL is and isn't good at (read: our stuff).

   I know that a lot of this is being fixed in MySQL 5, but damned if I'm going to use a beta database that has hundreds of new features that haven't ever been in the wild. I also have a feeling that it's probably full of legacy brain damage like PHP 5, even though it tries to suck significantly less.

2. It broke in two different ways in one week, after having used it for only about two months. Granted, we're putting a lot of stress on it, but you expect a database that's been around this long to at least gracefully degrade into getting slow, rather than throwing data away. The first two months were nice, but I have a feeling that other people are experiencing this crap later on in the game, and are simply just screwed because they have too much of an investment in their MySQL-based code. It's painful to migrate a couple million rows no matter how you slice it, but I have better things to do than babysit a database.

   We have our own flat file transaction log now, so I'm not so worried about MySQL dying again during the transition :)

3. The MySQL GUI tools are really weird and buggy and/or out of date on OS X. They're almost good enough, but tend to crash or have weird UI quirks. Who the hell decided to remove the select all option from the edit menus? I put them back in with Interface Builder, but I feel sorry for people who don't have Cocoa experience :) This is only a somewhat valid complaint, because most of the other RDBMS don't really have GUI tools for OS X. Also, the performance/health monitoring graphs in the administrator are just totally worthless, because they adjust the scale once a second, and most of the time it's not even done correctly so the peaks get clipped. It looks cool, but man is that graph useless.

So long, and thanks for all the marketing buzz.

We've been using MySQL for MochiBot for a while. It worked OK, until this week.

MySQL replication is unreliable. We had two MySQL servers running, one as a master, and one as a slave; the simplest of all replication scenearios. The slave was used only for redundancy and load balancing. It receives only SELECT queries and never mutates data (none of the connections connecting to it even have permission to mutate!). What happened? Replication stopped -- but only on one table, with no errors. The binlogs are all synchronized, it just decided not to update that table anymore. The workaround for this was easy, we stopped using replication. Granted, I consulted a few people who are running really high profile and high traffic sites on MySQL and they've never seen this before, but I can no longer trust MySQL to replicate our data.

The final blow was MySQL's MyISAM tables are fast, but dangerous. What happens when you try and put a lot of data in them? They explode. There's an arcane MAX_TABLE_ROWS limit to a MyISAM table. If you hit this limit, it won't let you insert any more rows. It doesn't grow for you. Why not? Beats me. I should've known this, but frankly, I've never trusted MySQL to store an 8GB table before, so I never ran up against it. Never again!

We're through, and are in the process of migrating to PostgreSQL ASAP. The bonus is that we can use all the nice features of SQL that you lose when making the MySQL choice: subselects, views, triggers, stored procedures, etc. I really should've listened to myself and avoided MySQL in the first place.

MochiKit 0.80 has been released! This release includes a new example, a bunch of new features, and some bug fixes.

The major new features are HSV color model support for MochiKit.Visual (for all of you Photoshop users out there), Python-like strip functions in MochiKit.Format and the updateNodeAttributes, appendChildNodes, replaceChildNodes trio in MochiKit.DOM.

The new view-source example uses the excellent dp.SyntaxHighlighter and extends it to provide XMLHttpRequest-based syntax highlighting. It simply reads the file name from location.hash, makes an educated guess about how to highlight it, and displays the content. It's quite cool, and now all of the examples have links to view their source files using it!

The mailing list is really taking off too, so you should sign up!

2005-08-12 v0.80

• Source highlighting in all examples, moved to a view-source example
• Added some experimental syntax highlighting for the Rounded Corners example, via the LGPL dp.SyntaxHighlighter 1.2.0 now included in examples/common/lib
• Use an indirect binding for the logger conveniences, so that the global logger could be replaced by setting MochiKit.Logger.logger to something else (though an observer is probably a better choice).
• Allow MochiKit.DOM.getElementsByTagAndClassName to take a string for parent, which will be looked up with getElement
• Fixed bug in MochiKit.Color.fromBackground (was using node.parent instead of node.parentNode)
• Consider a 304 (NOT_MODIFIED) response from XMLHttpRequest to be success
• Disabled Mozilla map(...) fast-path due to Deer Park compatibility issues
• Possible workaround for Safari issue with swapDOM, where it would get confused because two elements were in the DOM at the same time with the same id
• Added missing THEAD convenience function to MochiKit.DOM
• Added lstrip, rstrip, strip to MochiKit.Format
• Added updateNodeAttributes, appendChildNodes, replaceChildNodes to MochiKit.DOM
• MochiKit.Iter.iextend now has a fast-path for array-like objects
• Added HSV color space support to MochiKit.Visual
• Fixed a bug in the sortable_tables example, it now converts types correctly
• Fixed a bug where MochiKit.DOM referenced MochiKit.Iter.next from global scope

While the Flash 8 runtime is still in public beta, quite a bit about its new features have already been discovered by reverse engineering. One "feature" I haven't heard much about is the new "security" model.

It's ridiculously broken and stupid (not that this should be a surprise).

Specifically, movies running on the local machine are not allowed to do any network communication whatsoever (even to localhost) without popping up a really obnoxious warning to the user! No other application or content development environments I've seen have any such brainless restrictions. Not HTML, not QuickTime, not anything else. Hell, even most email clients will load images from the internet by default. Give it up Macromedia, you don't know what you're doing. Who are you trying to help here anyway? Certainly not your customers.

With this in place, forget about writing dashboard widgets, network screensavers, locally run network games, etc. Users don't want to see a security warning when they run your content, and explicitly allow every single application (widget, or whatever else) to talk to a server. Especially since it's a couple clicks to do so! I can also imagine this causing Really Bad Things to happen for some uses of Flash (dialog popping up in the context of a screen saver or in dashboard, mostly).

If the user clicks "OK", like they are conditioned to do, the Flash app can't talk on the network and probably won't work. If they click "edit settings" it opens up a browser window pointing somewhere on macromedia.com with a Flash application to manage Flash's preferences. This page doesn't immediately appear to know what you were doing, and doesn't make it at all obvious how to enable a paritcular local movie to talk on the internet. At this point, users will either give up or click "always allow" -- which of course turns this "feature" off for everything everywhere. Why bother having it on by default if everyone is going to either give up on Flash or turn it off anyway?

The actual procedure for enabling a single Flash application to talk on the internet (or LAN, or the loopback interface!) is five steps:

1. Security dialog
2. Macromedia.com security panel
3. Edit locations pulldown
4. Add locations panel
5. Copy and paste URL

What if your content optionally talks over a network (download high scores, report statistics, check for upgrades, etc.), but the user can't currently make a connection to macromedia.com? You guessed it, dialog every time! They can't use the security panel unless they're online. Awesome.

Oh, and this applies to Flash content with any authoring version whatsoever. It's a player feature that's enabled unconditionally for all Flash content.

The workaround of course, is to simply not buy Flash 8 and distribute only Flash 7-built projectors, which are unsigned executable files (that's security!).

P.S. I'm sorry that Macromedia Central was a flop, but you don't have to take that out on everyone else who is trying to use Flash locally.

UPDATE: There is some more information from the horse's mouth in this Flashcoders post. They do plan on having a security model that allows networking with local SWFs; however, they are still planning to break all existing networked content that plays locally. You will have to download and run a command line tool, or buy Flash 8 and republish all of your existing content. WTF? At least I can see a business case for it now; this breakage means more people will buy Flash 8 to ensure that their content will continue to work. I thought they were bought out by Adobe, not Microsoft :)

UPDATE (again): Flash 8 security whitepapers

MochiKit 0.70 has been released! This release includes a new example, a bunch of new features, a minor API change, and some bug fixes.

In addition to the new features, there's a spiffy new example: Ajax Tables, which is an extension of the Sortable Tables from Scratch example that loads data with XMLHttpRequest (in either JSON or XML format!).

Thanks to some help from Alex Russell, MochiKit is now packed by the custom Rhino interpreter from Dojo Toolkit (though I wouldn't have needed help if it were documented ;). Despite the new features, packed/MochiKit/MochiKit.js is 33% smaller than the 0.60 release (chocolate rations are up to 72k from 108k this week)!

2005-08-04 v0.70

• New ajax_tables example, which shows off XMLHttpRequest, ajax, json, and a little TAL-ish DOM templating attribute language.
• sendXMLHttpRequest and functions that use it (loadJSONDoc, etc.) no longer ignore requests with status == 0, which seems to happen for cached or local requests
• Added sendXMLHttpRequest to MochiKit.Async.EXPORT, d'oh.
• Changed scrapeText API to return a string by default. This is API-breaking! It was dumb to have the default return value be the form you almost never want. Sorry.
• Added special form to swapDOM(dest, src). If src is null, dest is removed (where previously you'd likely get a DOM exception).
• Added three new functions to MochiKit.Base for dealing with URL query strings: urlEncode, queryString, parseQueryString
• MochiKit.DOM.createDOM will now use attr[k] = v for all browsers if the name starts with "on" (e.g. "onclick"). If v is a string, it will set it to new Function(v).
• Another workaround for Internet "worst browser ever" Explorer's setAttribute usage in MochiKit.DOM.createDOM (checked -> defaultChecked).
• Added UL, OL, LI convenience createDOM aliases to MochiKit.DOM
• Packing is now done by Dojo's custom Rhino interpreter, so it's much smaller now!