[database] Merge queries

[Larry Garfield]

So much progress has been made, now that I have a working Postgres install 
thanks to James Walker and Sammy Spets. :-)

The next item on the list is Merge queries.  

http://en.wikibooks.org/wiki/SQL_dialects_reference/Write_queries/Replace_query

Basically nothing supports the SQL:2003 merge query AFAIK.  

In MySQL, we're using ON DUPLICATE KEY UPDATE, which is working like a champ.

For Postgres, the original concept was to do:

// start transaction.
try {
  db_insert();
}
catch (Exception $e) {
  db_update();
}
// end transaction

Since the return value from db_update() is the number of rows *affected*, not 
*changed*, which means our usual update-or-insert routine doesn't work.

However, Sammy pointed out that logic will not work, period.  If the INSERT 
statement throws an exception, it's because the database itself is now in an 
invalid state and will not accept anything other than a rollback.  Even if we 
didn't have the transaction wrapping there, we couldn't have any transactions 
active at all for a merge query with this logic.  So this is not an option.

So we need a way to handle merge queries in Postgres.  Josh, this is where you 
come in. :-)  How can we do an atomic, non-exception-using merge query or 
reasonable emulation?  We've just kicked Postgres 7.4 to the curb, so 
whatever Postgres 8.1 can do is available to us.

-- 
Larry Garfield
larry at garfieldtech.com