Re: Revisited: Transactions, insert unique.

Hi David,

That can work if there's only one session using the database. But it's not
100% if there are multiple users. There's a small chance that a row may not
exist during the select, but exist by the time of the insert. If I'm wrong
please correct me - then I'll go optimize some code :).

By having the unorthodox locking mechanism suggested I can ensure at the
application level that no one else is going to insert stuff before my
select, update/insert, without having to lock the whole table.

So it will be
LOCK arbitrary
select
if exist update
else insert
UNLOCK arbitrary

Which would be faster- doing the lock arbitrary method, or doing an insert
with unique indexes and recovering if necessary (assuming postgresql does
what other databases do)? I suspect unique indexes could slow inserts and
updates down significantly.

If we don't want to do all that, how about we have a select for insert (and
update), which locks things? But I figured that it would be problematic to
implement in a number of scenarios tho.

Cheerio,

Link.

At 09:56 AM 24-04-2000 -0500, davidb@vectormath.com wrote:
>Hi Lincoln,
>
>I'm not sure I'm understanding your question, but it seems like this is
>something that
>ought to be handled programmatically.  That is, query the table to see if
>the row exists,
>then decide what you are going to do (insert or update) based on the results
>of your
>query.
>
>Am I completely missing the point?
>
>David Boerwinkle