Re: UPSERT

From: Hannu Krosing <hannu(at)skype(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, Jonathan Scher <js(at)oxado(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: UPSERT
Date: 2007-03-04 12:55:47
Message-ID: 1173012947.3132.6.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Ühel kenal päeval, R, 2007-03-02 kell 10:13, kirjutas Tom Lane:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > My instinct would be to follow your first strategy, i.e. detect which
> > path is needed rather than try one and then if it fails do the other.
>
> The very first thing you need to think about is how to solve the race
> condition problem, ie, two backends concurrently trying to insert
> identical data.

Then one of them will update the data inserted by whoeved got the insert
first.

> Until you have a plausible mechanism for that, the
> whole thing is pie-in-the-sky.

Is'nt the standard way of doing it thus:

UPDATE
IF NOT FOUND THEN
INSERT
IF DUPLICATE KEY THEN
UPDATE
END IF
END IF

At least this is how UPSERT is usually done in plpgsql

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

In response to

Responses

  • Re: UPSERT at 2007-03-04 13:46:58 from Bruno Wolff III
  • Re: UPSERT at 2007-03-04 15:49:49 from Martijn van Oosterhout

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruno Wolff III 2007-03-04 13:46:58 Re: UPSERT
Previous Message Simon Riggs 2007-03-04 11:54:30 Re: Synchronized Scan update