
One of my machines has two CPUs, and in some cases I build a pair
of indexes in parallel to take advantage of this.  (I can't seem
to do an ALTER TABLE ADD PRIMARY KEY in parallel with an index
build, though.) Recently, though, I received the message "ERROR:
simple_heap_update: tuple concurrently updated." Can anybody tell
me more about this error and its consequences?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 18 Jul 2002 09:12:10 +0900 (JST)
Curt Sampson <cjs@cynic.net>
tuple concurrently updated
I guess two transactions updated a tuple concurrently.
Because versioning scheme allows old versions can be
read by another transaction, the old version can be updated too.

For example,

We have a row whose value is 1
create table t1 (i1 integer);
insert into t1 values(1);

And,

Tx1 executes  update t1 set i1=2 where i1=1
Tx2 executes  update t1 set i1=10 where i1=1

Now suppose that
Tx1 read i1 with value 1
Tx2 read i1 with value 1
Tx1 updates i1 to 2 by inserting 2 into t1 according to versioning scheme.
Tx2 tries to update i1 to 10 but fails because it is already updated by Tx1.

Because you created different index with different transaction,
The concurrently updated tuple cannot be any of index node.

If the index on the same class,
two concurrent CREATE INDEX command can update pg_class.relpages
at the same time.

I guess that is not a bug of pgsql, but a weak point of
MVCC DBMS.

"Curt Sampson" <cjs@cynic.net> wrote in message
news:Pine.NEB.4.44.0207180848400.681-100000@angelic.cynic.net...
>
> One of my machines has two CPUs, and in some cases I build a pair
> of indexes in parallel to take advantage of this.  (I can't seem
> to do an ALTER TABLE ADD PRIMARY KEY in parallel with an index
> build, though.) Recently, though, I received the message "ERROR:
> simple_heap_update: tuple concurrently updated." Can anybody tell
> me more about this error and its consequences?
>
> cjs
> --
> Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
>     Don't you know, in this new Dark Age, we're all light.  --XTC
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html



Fri, 26 Jul 2002 05:01:08 +0900
"Kangmo, Kim" <ilvsusie@hanafos.com>
Re: tuple concurrently updated
A solution to this problem is not versioning catalog tables but in-place
updating them.
Of course anther transaction that wants to update the same row in the
catalog table should wait,
which leads to bad concurrency.
But this problem can be solved by commiting every DDL right after its
execution successfully ends.
Note that catalog table updators are DDL, not DML.
I think that's why Oracle commits on execution of every DDL.

"Kangmo, Kim" <ilvsusie@hanafos.com> wrote in message
news:ahple2$1rgh$1@news.hub.org...
> I guess two transactions updated a tuple concurrently.
> Because versioning scheme allows old versions can be
> read by another transaction, the old version can be updated too.
>
> For example,
>
> We have a row whose value is 1
> create table t1 (i1 integer);
> insert into t1 values(1);
>
> And,
>
> Tx1 executes  update t1 set i1=2 where i1=1
> Tx2 executes  update t1 set i1=10 where i1=1
>
> Now suppose that
> Tx1 read i1 with value 1
> Tx2 read i1 with value 1
> Tx1 updates i1 to 2 by inserting 2 into t1 according to versioning scheme.
> Tx2 tries to update i1 to 10 but fails because it is already updated by
Tx1.
>
> Because you created different index with different transaction,
> The concurrently updated tuple cannot be any of index node.
>
> If the index on the same class,
> two concurrent CREATE INDEX command can update pg_class.relpages
> at the same time.
>
> I guess that is not a bug of pgsql, but a weak point of
> MVCC DBMS.
>
> "Curt Sampson" <cjs@cynic.net> wrote in message
> news:Pine.NEB.4.44.0207180848400.681-100000@angelic.cynic.net...
> >
> > One of my machines has two CPUs, and in some cases I build a pair
> > of indexes in parallel to take advantage of this.  (I can't seem
> > to do an ALTER TABLE ADD PRIMARY KEY in parallel with an index
> > build, though.) Recently, though, I received the message "ERROR:
> > simple_heap_update: tuple concurrently updated." Can anybody tell
> > me more about this error and its consequences?
> >
> > cjs
> > --
> > Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
> >     Don't you know, in this new Dark Age, we're all light.  --XTC
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
>



Fri, 26 Jul 2002 05:19:24 +0900
"Kangmo, Kim" <ilvsusie@hanafos.com>
Re: tuple concurrently updated
"Kangmo, Kim" <ilvsusie@hanafos.com> writes:
> If the index on the same class,
> two concurrent CREATE INDEX command can update pg_class.relpages
> at the same time.

Or try to, anyway.  The problem here is that the code that updates
system catalogs is not prepared to cope with concurrent updates
to the same tuple.

> I guess that is not a bug of pgsql, but a weak point of
> MVCC DBMS.

No, it's not a limitation of MVCC per se, it's only an implementation
shortcut for catalog updates.  Fixing this across all system catalog
updates seems more trouble than it's worth.  It'd be nice if the
concurrent-CREATE-INDEX case worked, though.

			regards, tom lane

Thu, 25 Jul 2002 16:25:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: tuple concurrently updated 
How do you think about my suggestion to not versioning system catalogs?

p.s. It's unbelivable that I got a reply from legendary Tom Lane. :)

Best,
Kim.

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:20755.1027628748@sss.pgh.pa.us...
> "Kangmo, Kim" <ilvsusie@hanafos.com> writes:
> > If the index on the same class,
> > two concurrent CREATE INDEX command can update pg_class.relpages
> > at the same time.
>
> Or try to, anyway.  The problem here is that the code that updates
> system catalogs is not prepared to cope with concurrent updates
> to the same tuple.
>
> > I guess that is not a bug of pgsql, but a weak point of
> > MVCC DBMS.
>
> No, it's not a limitation of MVCC per se, it's only an implementation
> shortcut for catalog updates.  Fixing this across all system catalog
> updates seems more trouble than it's worth.  It'd be nice if the
> concurrent-CREATE-INDEX case worked, though.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org



Fri, 26 Jul 2002 05:34:25 +0900
"Kangmo, Kim" <ilvsusie@hanafos.com>
Re: tuple concurrently updated
"Kangmo, Kim" wrote:
> 
> How do you think about my suggestion to not versioning system catalogs?
> 
> p.s. It's unbelivable that I got a reply from legendary Tom Lane. :)
> 
> Best,
> Kim.

I can guess what Tom's going to say, since I argued your position
approx. 3 years ago. Implicitly committing transactions would not allow
for rollback of DDL statements. This is a great feature that PostgreSQL
has striven for that Oracle lacks. 3 years ago, it seemed to be a
problem, when a table created in an aborted transaction was not being
correctly cleaned up. It gets in the way of implementing an ALTER TABLE
DROP COLUMN easily, since rollback of a dropped column means that the
underlying data must be preserved. However, the developers have made
such great progress in properly rolling back DDL statements that it
would be a real shame to lose such a great feature. Additionally, it
could seriously break a lot of applications out there that do not
expect:

INSERT INTO foo VALUES (1);
CREATE TABLE bar (key integer);
ROLLBACK;

to fail to rollback the INSERT into foo. When the original discussion
came up, there were even a few of Oracle developers that didn't know
Oracle was committing their transactions behind their back.

Mike Mascari
mascarm@mascari.com

Thu, 25 Jul 2002 17:00:32 -0400
Mike Mascari <mascarm@mascari.com>
Re: tuple concurrently updated
On Thu, 25 Jul 2002, Tom Lane wrote:

> "Kangmo, Kim" <ilvsusie@hanafos.com> writes:
> > If the index on the same class,
> > two concurrent CREATE INDEX command can update pg_class.relpages
> > at the same time.
>
> Or try to, anyway.  The problem here is that the code that updates
> system catalogs is not prepared to cope with concurrent updates
> to the same tuple.

I see. So the error is basically harmless, and can be rectified anyway
with an ANALYZE, right?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Sun, 28 Jul 2002 18:02:44 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: tuple concurrently updated 
Curt Sampson <cjs@cynic.net> writes:
> On Thu, 25 Jul 2002, Tom Lane wrote:
>> "Kangmo, Kim" <ilvsusie@hanafos.com> writes:
> If the index on the same class,
> two concurrent CREATE INDEX command can update pg_class.relpages
> at the same time.
>> 
>> Or try to, anyway.  The problem here is that the code that updates
>> system catalogs is not prepared to cope with concurrent updates
>> to the same tuple.

> I see. So the error is basically harmless, and can be rectified anyway
> with an ANALYZE, right?

Other than the fact that the second CREATE INDEX fails and rolls back,
there's no problem ;-)

I was thinking that it might help to have UpdateStats not try to update
the pg_class tuple if the correct value is already present.  This will
just narrow the window for failure, not eliminate it; but it'd be a
simple change and would probably help some.  Another thing to look at
is merging that routine with setRelhasindex so that a CREATE INDEX
involves only one update to the table's pg_class row, not two.

			regards, tom lane

Sun, 28 Jul 2002 12:32:36 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: tuple concurrently updated 
On Sun, 28 Jul 2002, Tom Lane wrote:

> Other than the fact that the second CREATE INDEX fails and rolls back,
> there's no problem ;-)

Agh!

So what, in the current version of postgres, are my options for
doing parallel index builds?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Mon, 29 Jul 2002 01:39:09 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: tuple concurrently updated 