> Sometimes it's desirable to first to a SELECT to see if a row exists,
> and then INSERT if it doesn't already, or UPDATE if it does.
MySQL's got a really handy 'REPLACE INTO' for just this circumstance,
some have suggested that PostgreSQL should support this in future.
Your solution looks good.
Stuart.
Sometimes it's desirable to first to a SELECT to see if a row exists,
and then INSERT if it doesn't already, or UPDATE if it does.
Using this pattern on a busy website, I noticed a race condition:
two SELECTs could happen at nearly the same time, and both decide to
INSERT. The second INSERT fails, unless a check is more for it.
Here's the recipe I'm trying to out to address the race condition now,
targeting PostgreSQL.
First, I construct INSERT that embeds the SELECT statement, so it only
happens if the row doesn't already exist.
Second, I check to see if the INSERT fails with "duplicate" error, which
is part of the string that PostgreSQL returns if you try to insert the
same primary key twice.
Seem reasonable? Here's the key code, which is executed
in a larger eval{} block, with RaiseError = 1;
my $sth = $dbh->prepare(
"INSERT INTO " . $self->table_name . " (a_session,id) SELECT ?, ?
WHERE NOT EXISTS (SELECT 1 FROM " . $self->table_name . "
WHERE id=? LIMIT 1)");
$sth->bind_param(1,$datastr,{ pg_type => $type });
$sth->bind_param(2, $sid);
$sth->bind_param(3, $sid); # in the SELECT statement
my $rv = '';
eval { $rv = $sth->execute(); };
if ( $rv eq '0E0' or (defined $@ and $@ =~ m/duplicate/i) ) {
my $sth = $dbh->prepare("UPDATE " . $self->table_name . " SET
a_session=? WHERE id=?");
$sth->bind_param(1,$datastr,{ pg_type => $type });
$sth->bind_param(2,$sid);
$sth->execute;
}
Thanks!
Mark
Stuart Cooper wrote:
>> Sometimes it's desirable to first to a SELECT to see if a row exists,
>> and then INSERT if it doesn't already, or UPDATE if it does.
>
> MySQL's got a really handy 'REPLACE INTO' for just this circumstance,
> some have suggested that PostgreSQL should support this in future.
The SQL 2003 Standard has a really handy "MERGE INTO" for just this
circumstance, too. You can search for "MERGE INTO" on this page to read
about it:
http://www.varlena.com/GeneralBits/73.php
I hope both PostgreSQL and MySQL support this in the future.
> Your solution looks good.
Thanks.
Mark
--
http://mark.stosberg.com/