Re: mysql replace in postgreSQL?

On Sun, Oct 30, 2005 at 11:47:41PM +0800, Lincoln Yeoh wrote:
> At 06:29 AM 10/30/2005 -0800, David Fetter wrote:
>
> >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
>
> Erm, doesn't it have the same race conditions?

No, don't believe it does.  Have you found some?

> If you have the appropriate uniqueness constraint, you'll be fine
> whatever you do as long as you're not doing something too stupid.
>
> If it is possible to lock on something that already exists or has
> "yet to exist" then maybe you can do such an insert/update.
>
> e.g. "SELECT .... FOR INSERT WHERE field1=x"
>
> How about customizable user locking? e.g. lock string "tablename
> field1=x"
>
> Anyway, I used to lock tables before doing selects before
> inserts/updates.

That's a real performance killer.

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

Remember to vote!