Re: mysql replace in postgreSQL? - Mailing list pgsql-general

From David Fetter
Subject Re: mysql replace in postgreSQL?
Date
Msg-id 20051030152440.GB1744@fetter.org
Whole thread Raw
In response to Re: mysql replace in postgreSQL?  (Tino Wildenhain <tino@wildenhain.de>)
Responses Re: mysql replace in postgreSQL?
List pgsql-general
On Sun, Oct 30, 2005 at 03:52:23PM +0100, Tino Wildenhain wrote:
> Am Sonntag, den 30.10.2005, 06:29 -0800 schrieb David Fetter:
> > On Fri, Oct 28, 2005 at 09:57:03PM -0400, blackwater dev wrote:
> > > In MySQL, I can use the replace statement which either updates
> > > the data there or inserts it.  Is there a comporable syntax to
> > > use in postgreSQL?
> >
> > Not really, but here's an example which doesn't have the
> > brokenness of MySQL's REPLACE INTO and doesn't have the race
> > conditions that some others' proposals have.
> >
> > http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
> >
> > Of course, it's not as nice and flexible as the SQL standard
> > MERGE, but until that day comes, you can use that example.
>
> In most cases, just DELETE and then INSERT should work perfectly.
> (UPDATE and MERGE would cause dead tuples in the same way so in the
> end they are only syntactical sugar)
>
> Another way is a rule for insert which turns it into an update in
> case the desired tuple is already existent.

That has a race condition in it.  What happens if something deletes
the tuple between the attempted INSERT and the UPDATE?

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

pgsql-general by date:

Previous
From: Tino Wildenhain
Date:
Subject: Re: mysql replace in postgreSQL?
Next
From: Lincoln Yeoh
Date:
Subject: Re: mysql replace in postgreSQL?