Re: ERROR: tuple concurrently updated

Hello.

Testing of the concurrent access to database objects leaded to the following error:

  ERROR: tuple concurrently updated

  SQL state: XX000

According to the "PostgreSQL Error Codes" table in the documentation, "XX000" is the PostgreSQL internal error code.

How to reproduce such an error:

1. Create a table in some database and view:

create table t1 ( id int );

create temp view v1 as select * from t1;

2. Concurrent access to table t1 is performed by 2 clients (further C1 and C2) particularly in this order:

C1: begin; drop view v1;

C2: drop table t1;

C1: commit;

 And finally, transaction of the client C2 is terminated with this "expected" error.

What's wrong?

"Zubkovsky, Sergey" <Sergey(dot)Zubkovsky(at)transas(dot)com> writes:
> create table t1 ( id int );
> create temp view v1 as select * from t1;
> C1: begin; drop view v1;
> C2: drop table t1;
> C1: commit;

This seems a variant of the problem noted by Michael Fuhr some time ago:
http://archives.postgresql.org/pgsql-hackers/2007-01/msg00937.php

but I think it shows that the solution I proposed in that thread is
still not adequate. To deal with the above, it seems like dropping v1
would have to acquire a shared lock on t1, thereby preventing the drop
of t1 from starting until after v1 is safely gone. Or maybe have the
drop of t1 recheck to see if v1 is still there after it has acquired
lock on v1, but I'm afraid that might be too late to prevent an update
conflict on the pg_depend row that both transactions want to delete.
Ick.

regards, tom lane

It seems that solution with a shared lock on t1 is working.

Though in this case I have the following questions:

1. If the session in which v1 was created is terminated abnormally, then
there is a chance of the initial situation iteration, because v1 is a
temporary view. What about this?

2. If I use an advisory lock instead of LOCK TABLE then what is the
sequence of the following implicit objects deallocation: advisory locks
and temporary views?

As far as I understand from your reply, there is an unresolved problem
of concurrent access to the system catalog in PostgreSQL, which was
noticed in case which I have described.

I understand that I should try not to execute DLL operators in
concurrent transactions. However I cannot avoid it in my case.

What can you suggest to do in this case, taking into account that
PostgreSQL does not provide necessary stability?
The main problem here is how to make safely the deletion of the
temporary view (v1) in one session, and deletion of tables which are
pointed by this temporary view (v1) in another session.

Thank you.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, March 28, 2007 10:02 PM
To: Zubkovsky, Sergey
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] ERROR: tuple concurrently updated

"Zubkovsky, Sergey" <Sergey(dot)Zubkovsky(at)transas(dot)com> writes:
> create table t1 ( id int );
> create temp view v1 as select * from t1;
> C1: begin; drop view v1;
> C2: drop table t1;
> C1: commit;

This seems a variant of the problem noted by Michael Fuhr some time ago:
http://archives.postgresql.org/pgsql-hackers/2007-01/msg00937.php

but I think it shows that the solution I proposed in that thread is
still not adequate. To deal with the above, it seems like dropping v1
would have to acquire a shared lock on t1, thereby preventing the drop
of t1 from starting until after v1 is safely gone. Or maybe have the
drop of t1 recheck to see if v1 is still there after it has acquired
lock on v1, but I'm afraid that might be too late to prevent an update
conflict on the pg_depend row that both transactions want to delete.
Ick.

regards, tom lane