Message-ID: <e5cae85815165cf6b92988db53504cd2.squirrel@sq.gransy.com>
In-Reply-To: <4CEB38B0.8050901@gmx.net>
References: <4CEB38B0.8050901@gmx.net>
Date: Tue, 23 Nov 2010 05:11:37 +0100
Subject: Re: Howto  "insert or update" ?
From: tv@fuzzy.cz
To: "Andreas" <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

> Currently I have a trigger function that should store a value in tableX
> whenever a certain column in tableY gets changed.
> I do it with:
> a)   delete from tableX where key = ( A, B )  ( regardless if there is one
> )
> b)   insert into tableX
>
> This seems not very efficient though it works.
> Is there a better way?

Efficiency matters only if the solution is correct, and that's not the
case of your function - there's a quite trivial race condition. Imagine
there are two transactions running at the same time, executing the
function concurrently.

The first one will succeed, while the other one will fail because of
unique constraint violation. A correct solution is something like this

BEGIN
  INSERT INTO ...
EXCEPTION
  -- the key already exists, so let's update
  WHEN unique_violation THEN
     UPDATE ...
END;

Regarding efficiency - I'm not aware of a better solution. There are plans
to implement true MERGE but that's in the future. All you can do right now
is to make sure the key is indexed (I guess it's a PK anyway) so that the
INSERT/UPDATE are fast.

Well, actually there's one other thing you could do - you can do a BEFORE
INSERT trigger that checks if the key already exists, and in case it does
switch to UPDATE. Something like

CREATE OR REPLACE FUNCTION my_trigger() RETURNS trigger as $$
BEGIN
   UPDATE my_table SET .... WHERE key = NEW.key;
   IF (FOUND) THEN
      -- updated, do not execute the insert
      RETURN NULL;
   END IF;

   -- execute the insert
   RETURN NEW;
END;
$$ language plpgsql;

CREATE TRIGGER merge_trigger BEFORE INSERT ON my_table FOR EACH ROW
EXECUTE PROCEDURE my_trigger();

This is probably more elegant - just execute INSERT statements and it will
handle all the work. The only problem is it does not report the number of
updated rows (it just returns 0 in that case).

But generally it's just as efficient as the solution described above.

regards
Tomas