Re: Atomic operations?

On 18-Mar-08, at 11:05 AM, Paul Tomblin wrote:

> On Tue, Mar 18, 2008 at 10:17 AM, Dave Cramer <pg@fastcrypt.com>
> wrote:
>>>> On 18-Mar-08, at 9:40 AM, Paul Tomblin wrote:
>>>>> I'm doing a simple delete/insert on a table, rather than trying to
>>>>> figure out whether the row exists or not and then doing an
>>>>> update or
>>>>> an insert.  But what I'm discovering is that every now and then I
>
>>>> Can't you just check the return value from the delete to see if
>>>> anything was deleted ?
>>>
>>> I'm not sure how that would help.  If I do the delete and it didn't
>>> delete anything, that doesn't tell me if some other process inserted
>>> it in the meantime.
>>>
>> which ever process succeeds in deleting should do the insert.
>
> Doesn't help in the case where the record wasn't in there in the
> first place.
>

What you are looking for is upsert. It's on the todo list http://www.postgresql.org/docs/faqs.TODO.html

In the meantime you can lock the table or create a function to do a
select and update or insert.

Dave
> --
> For my assured failures and derelictions I ask pardon beforehand of my
> betters and my equals in my Calling here assembled, praying that in
> the hour of my temptations, weakness and weariness, the memory of this
> my Obligation and of the company before whom it was entered into, may
> return to me to aid, comfort and restrain.
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc