From: | Lincoln Yeoh <lylyeoh(at)mecomb(dot)com> |
---|---|
To: | davidb(at)vectormath(dot)com, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Revisited: Transactions, insert unique. |
Date: | 2000-04-25 00:52:49 |
Message-ID: | 3.0.5.32.20000425085249.008d4ad0@pop.mecomb.po.my |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)vectormath(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ing. Roberto Andrade Fonseca | 2000-04-25 01:32:22 | Is 7.0 ready? |
Previous Message | Hiroshi Inoue | 2000-04-25 00:18:52 | RE: Revisited: Transactions, insert unique. |