Re: on duplicate key

On Thu, 2008-09-25 at 18:25 +0200, A B wrote:
> My solution up till now has been a function with the
>
> BEGIN
> insert ....
> EXCEPTION WHEN OTHERS THEN
> update ...
> END;

Here is the appropriate documentation link, where they have an example:
http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

The primary difference is that they use a loop, which is more robust. In
theory, if you delete the record between when the INSERT happens and
when the UPDATE happens, you will get no effect, which isn't what you're
looking for. The loop will correct for this by ensuring that something
happens before it terminates.

> I think this is not possible to run this kind of commands on the
> command line. Correct?

This specific construct is in the language PL/pgSQL, which only exists
as a procedural language, and can only be used to create functions.

However, you can do the same thing on the command line using
subtransactions, a.k.a. SAVEPOINTs:
http://www.postgresql.org/docs/8.3/static/sql-savepoint.html

This is how PL/pgSQL works internally, and how you can use other
procedural languages to accomplish the same thing.

Using SQL on the command line doesn't allow you to loop directly, so the
best solution is usually to use a function (and PL/pgSQL is a good
language for this).

> As I see it, there are three ways
> 1) a function

This is probably the right approach, if you use the BEGIN...EXCEPTION
method in the first link I provided.

> 2) try to rewrite it as two separate queries insert ... ; update
> ... ; where the insert will fail sometimes

There's a race in the case of a DELETE happening between the INSERT and
the UPDATE. PostgreSQL is designed for high concurrency, and this is
great for performance (especially scalable and consistent performance),
but race conditions are more likely with many things happening at once.

> 3) try to search and see if there were any result back, but that would
> require the IF THEN construct which is also not available outside of
> functions, right?

The same race condition exists here.

> Is it correct to assume that a function that is searching for the key
> and then choosing to insert or update depending on what it found, is
> about as fast as doing an insert within a begin- exception-end
> statement or are there some inherent speed differences?

Again, be careful of race conditions, but the speed should be
comparable.

> What should I answer the mysql-user?

To ask questions on pgsql-general, just like you did ;)

> By the way, is there any work done on getting this functionality? I
> must admit that it would be handy some times ;-)
> Shouldn't this kind of question be added to the FAQ?

Here's a starting place:
http://archives.postgresql.org/pgsql-hackers/2008-04/msg01475.php

You can find other threads about the status of the work by browsing the
mailing list archives.

Regards,
Jeff Davis