[Resolved] Best cross-RDMS alternative to "REPLACE INTO"??

Bloodwine

I've been using MySQL for some time now, but plan to move to PostgreSQL in the semi-near future, so I am trying to make my SQL as compliant as possible.

MySQL's "REPLACE INTO" is not compliant. I am currently using MyISAM tables instead of InnoDB, so transactions are out of the question.

Here are four alternatives off the top of my head (in pseudocode):

method 1: select ? update : insert;
method 2: if (!update) { insert; }
method 3: if (!insert) { update; }
method 4: if (select) { delete; } insert;

method 4 is how "REPLACE INTO" works, but methods 2 and 3 use one less query and I am trying to minimize the # of queries per page execution.

The problem is that the first 3 are subject to race conditions, and #4 would require extra care to ensure key integrity (especially in auto_increment tables)

I have used method#2 in a couple places, but unfortunately have ended up with some duplicate records. This has only happened in my traffic logging code so I figure two people with the same user_agents hit the site at the same time and both "!update" checks occured concurrently so they both inserted.

I would think #3 would be less succeptable to race conditions because it would be very unlikely that the record would be deleted between "!insert" check and the update. In fact, with rare exception, traffic records are never cleared (but I may have some sort of archiving system in the future).

I use #1 in some places, but it is a little clunky (like #4) and shares the same weakness as #3.

Are there other methods that I am not thinking of? I am trying to avoid MySQL-specific tricks such as "ON DUPLICATE KEY ..."

If there are no other better methods, which do you think is best? Performance is not nearly as much of an issue as the race conditions.

Thank you for your time.

mrhappiness

LOCK TABLES tablename WRITE

if !UPDATE
INSERT

UNLOCK TABLES

Sxooter

The problem with locking the whole table is that by the time you NEED to do because of race conditions, it's quite likely you'll have lots of parallel users, and locking the table is death to a system with lots of parallel users.

Unfortunately, postgresql has no method that is race free for the update ? insert if !update scenario.

The best practices are to do something more like:

update
if update fails insert
if insert fails update
if update fails insert
repeat, wash, rinse x number of times then give up.

(Note reversing the update / insert is fine, it's still the same method.)

Note that while a race condition may cause the first insert or update to fail, the chances of a use scenario appearing where the appears and disappears over and over are pretty slim. The advantage to this method is that readers are never blocked for your update. The disadvantage is that you have to code a fair bit of logic into your application to do this.

If this race condition is likely to happen a lot, it might be best to put the logic in a user defned function you just hand the data to and it tries two or three times to do the insert and then returns a yes or no if it succeeds or not.

Bloodwine

Thank you for the replies.

It might not be the most elegant approach, but I can write some sort of sqlReplace() user-function where the first argument is an insert query and the second argument is an update query

I'd figure no more than one try both ways would be necessary.

EDIT: oops, I want error code 1062, not 1022

public function sqlReplace($arg_insert_query, $arg_update_query) {
   $this->flag_output_errors = 0;
   $this->runQuery($arg_insert_query);
   // MySQL error code 1062 = ER_DUP_ENTRY (Duplicate entry '%s' for key %d)
   if ($this->getErrorCode() == 1062) {
      // try updating on duplicate key error
      $this->runQuery($arg_update_query);
      if ($this->getNumAffectedRows() == 0) {
         // try inserting (again) if update failed
         $this->flag_output_errors = 1;
         $this->runQuery($arg_insert_query);
      }
   }
   $this->flag_output_errors = 1;
}

Sxooter

Note that error codes are NOT returned proprely by all the database implementations in php, including postgresql. sigh. But the postgresql libs in php do return the error message, which can be parsed for the work duplicate or failed or something like that.

Bloodwine

Yes, I went to the docs at php.net to see if there was a PostgreSQL equivolent to mysql_errno(), and to my dismay there is not.

I guess I can just check to see if pg_query() returns FALSE for the insert. Not as granular error checking as with MySQL, but should still work in most cases.

Chances are that if I am getting an insert error other than a duplicate key one then I have bigger problems anyways.

I did switch my above code around to do update->insert->update instead of insert->update->insert as I realized that i'd be updating the records more often than inserting them (e.g.: traffic tracking, sessions, etc.)

From my tests thus far, it is working like a champ.

Sxooter

The really sad thing here is that someone made a patch that returns the errocode and posted a link to it on the docs of the php site. This was for an early 4.0 version. And it still has never been incorporated into php core. WHy oh why? I'll never know.