[database] Merge queries

[David Timothy Strauss]

----- "Larry Garfield" <larry at garfieldtech.com> wrote:
> > The way I solve the issue is by doing the operations:
> > (1) UPDATE
> > (2) If updated changed zero rows, do a SELECT on the criteria
> > (3) If the SELECT returned zero rows, do an INSERT
> >
> > This would all be in a transaction.
> 
> Three queries?  Yipes.  I guess that could work as a generic version,
> although 
> I'd prefer to see something more Postgres-optimized in the Postgres
> driver if 
> at all possible.  Remember we're talking about the code that is the
> bulk of 
> variable_set() and potentially cache_set(), so it will frequently be
> called 
> multiple times in rapid succession.

I'd suggest creating a function in MySQL and PostgreSQL that handles the queries. The three queries in and of themselves don't take much time; the enemy is the latency of three queries. If an in-database function handles the logic, I think we'll be in good shape.

BTW, I've implemented the algorithm above as pressflow_transaction_update() in my Pressflow Transaction module.