Re: Revisited: Transactions, insert unique.

I have not researched this issue thoroughly, or even superficially, but
I understand that PostgreSQL has something called multi-version
concurrency control (MVCC). This is, I'm told, a GOOD THING, and I
believe it. But the documentation and tutorials on MVCC are far too
terse for me to feel comfortable with my understanding. If both
transactions (yours and your competitor's) are serializable, then you
cannot see his insertion, and his transaction will be aborted (I think?
maybe?). This is not standard SQL, but it has the look and feel of a
better way.

I wish there were more information available on MVCC, and how to use it
effectively.

PostgreSQL General Digest (mime)  -   Volume 1 : Issue 97

Today's Topics:
  RE: Revisited: Transactions, insert unique.
                                         ["Hiroshi Inoue"
<Inoue@tpf.co.jp>]

 Subject:
         RE: Revisited: Transactions, insert unique.
   Date:
         Tue, 25 Apr 2000 09:18:52 +0900
   From:
         "Hiroshi Inoue" <Inoue@tpf.co.jp>
     To:
         "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
     CC:
         <pgsql-general@postgresql.org>



> -----Original Message-----
> From: pgsql-general-owner@hub.org
[mailto:pgsql-general-owner@hub.org]On
> Behalf Of Ross J. Reedstrom
>
> And this interpretation will guide the developers in _extending_
> the standard in a consistent way. I know, because the developers that
> implemented the constraints for 7.0 used this (and the SQL3 spec) as
> guides. How's that?
>

I don't know what is standard.
However as far as I see,few people prefer entire rollback on abort.
The problem is that PostgreSQL lacks a per statement rollback
functionality and unfortunately it isn't easy to implement.
Vadim has already planned the implementation. AFAIK one of the
purpose of WAL is to implement savepoint functionality. Savepoint
functionality would enable per statement rollback functionality easily.

The following is an extract of Vadim's posting about 10 months ago.

Re: [HACKERS] RE: [GENERAL] Transaction logging
Well, I'm thinking about WAL last two weeks. Hiroshi pointed me
problems in my approach to savepoints (when a tuple was marked
for update and updated after it) and solution would require
new tid field in header and both t_cmin/t_cmax => bigger header.
I don't like it and so I switched my mind -:).

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp



 Subject:
         Re: Revisited: Transactions, insert unique.
   Date:
         Tue, 25 Apr 2000 08:52:49 +0800
   From:
         Lincoln Yeoh <lylyeoh@mecomb.com>
     To:
         davidb@vectormath.com, <pgsql-general@postgresql.org>



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