On Wed, Oct 16, 2013 at 8:25 AM, Stéphan BEUZE
<stephan.beuze@douane.finances.gouv.fr> wrote:
> The following query is performed concurrently by two threads logged in with
> two different users:
>
> WITH raw_stat AS (
> SELECT
> host(client_addr) as client_addr,
> pid ,
> usename
> FROM
> pg_stat_activity
> WHERE
> usename = current_user
> )
> INSERT INTO my_stat(id, client_addr, pid, usename)
> SELECT
> nextval('mystat_sequence'), t.client_addr, t.pid, t.usename
> FROM (
> SELECT
> client_addr, pid, usename
> FROM
> raw_stat s
> WHERE
> NOT EXISTS (
> SELECT
> NULL
> FROM
> my_stat u
> WHERE
> current_date = u.creation
> AND
> s.pid = u.pid
> AND
> s.client_addr = u.client_addr
> AND
> s.usename = u.usename
> )
> ) t;
>
> From time to time, I get the following error: "tuple concurrently updated"
>
> I can't figure out what throw this error and why this error is thrown. Can
> you shed a light ?
Hmm. That error isn't supposed to happen; it's denoted in the source
code by elog() rather than ereport(), which means that it's just there
as a backstop, and never really intended to be become user-visible.
So I'd say you've found a bug. What PostgreSQL version is this?
There are actually two places where that error can happen:
simple_heap_update and simple_heap_delete. If you set the error
verbosity to verbose, you should be able to see which function is at
fault. The thing is, I don't see anything in that query which would
update or delete any tuples, so there must be more to the story. If
you have the ability to build from source, you could try setting a
long sleep just before that error is thrown. Then run your test case
until it hangs at that spot and get a stack backtrace. But that may
be more troubleshooting than you want to get into. Are there any
triggers on any of these tables? Any noteworthy extensions installed?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
