[imp] horde prefs and pgsql errors

[Stephen Grier]

I have now managed to stop horde/imp from generating these pgsql 'duplicate key'
errors. I've included the changes to horde-2.0/lib/Prefs/sql.php below (in the
form of a unified diff). These changes will force sql.php to check for the
existence of a particular preference value using a select statement. It will then
perform an sql insert if a value does not already exist, or an update statement if
one does.

I notice the code of horde/lib/Prefs/sql.php has not changed in the cvs. Perhaps
someone might look like to look at my changes below. We're using it here, and it
seems to be behaving sensibly.

Stephen.


Stephen Grier wrote:
> 
> We are currently running horde-2.0 and imp-3.0, with postgresql-7.1.2 storing user
> preferences and turba address books. I recently noticed a large number of the
> following messages in the pgsql and info system logs:
> 
> postgres[557]: [ID 553393 local0.warning] [544] ERROR:  Cannot insert a duplicate
> key into unique index horde_prefs_pkey
> 
> One such error message occurs every time a user logs into imp. Most often, horde
> seems to be trying to set the last_login preference value.
> 
> I traced the cause of these messages to the store() function in
> horde-2.0/lib/Prefs/sql.php. I was supprised to see that when setting a pref value
> the code tries to do an sql insert, and then an update if the insert fails. This
> means that every time a user (with an existing last_login value) logs into imp, an
> error message is produced when the insert statement breaks the primary key
> constraint on the horde_prefs table.
> 
> Would it not be more reasonable for horde to do a select statement on the
> horde_prefs table first to see if the value already exists, followed by an update
> if it does, or an insert if not? Is there a reason why this would not work? I'll
> be making this change to our code if not.
> 
> Thanks.
> 
> --
> 
> Stephen Grier                           s.e.grier@qmul.ac.uk
> Systems Developer                     
> Computing Services
> Queen Mary, University of London
> 
> --
> IMP mailing list
> Frequently Asked Questions: http://horde.org/faq/
> To unsubscribe, mail: imp-unsubscribe@lists.horde.org




--- sql.php.orig        Mon Dec 10 19:14:40 2001
+++ sql.php     Tue May 14 16:23:07 2002
@@ -245,22 +245,44 @@
         }
 
         /*
-         * Loop through the "dirty" preferences.  If our attempt to insert
-         * a new row fails, try to update an existing one.
+         * Loop through the "dirty" preferences.  If a row exists for this
preference,
+        * attempt to update it. If not, do an insert.
          */
         foreach ($dirty_prefs as $name) {
             $scope = $this->getScope($name);
 
-            /* Attempt an insert. */
-            $query  = 'insert into ' . $this->params['table'] . ' ';
-            $query .= '(pref_uid, pref_scope, pref_name, pref_value) values';
-            $query .= '(' . $this->db->quote($this->user) . ', ';
-            $query .= $this->db->quote($scope) . ', ' . $this->db->quote($name) .
', ';
-            $query .= $this->db->quote($this->getValue($name)) . ')';
-            $result = $this->db->query($query);
+           // does a value exist for this pref?
+           $query = 'select pref_value from ';
+           $query .= $this->params['table'] . ' ';
+           $query .= 'where pref_uid = ' . $this->db->quote($this->user);
+           $query .= ' and pref_name = ' . $this->db->quote($name);
+           $query .= ' and (pref_scope = ' . $this->db->quote($this->scope);
+           $query .= " or pref_scope = 'horde')";
+
+           $result = $this->db->query($query);
+
+           if (!isset($result) || DB::isError($result)) {
+               Horde::logMessage(_("Failed retrieving prefs for " . $this->user),
__FILE__, __LINE__, LOG_NOTICE);
+               return (new PEAR_Error(_("Failed retrieving preferences.")));
+           }
+
+           $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
+           if (!$row || DB::isError($row)) {
+
+                /* Attempt an insert. */
+                $query  = 'insert into ' . $this->params['table'] . ' ';
+                $query .= '(pref_uid, pref_scope, pref_name, pref_value) values';
+                $query .= '(' . $this->db->quote($this->user) . ', ';
+                $query .= $this->db->quote($scope) . ', ' .
$this->db->quote($name) . ', ';
+                $query .= $this->db->quote($this->getValue($name)) . ')';
+                $result = $this->db->query($query);
+
+               if ($result !== DB_OK)
+                   Horde::fatal($result, __FILE__, __LINE__);
+           }
+           else {
 
-            /* If the insert failed, attempt an update. */
-            if ($result !== DB_OK) {
+                /* Attempt an update. */
                 $query = 'update ' . $this->params['table'] . ' ';
                 $query .= 'set pref_value = ' .
$this->db->quote($this->getValue($name));
                 $query .= ' where pref_uid = ' . $this->db->quote($this->user);





-- 

Stephen Grier				s.e.grier@qmul.ac.uk
Systems Developer		
Computing Services
Queen Mary, University of London