Re: ERROR : 'tuple concurrently updated'

On Wed, Oct 16, 2013 at 5:55 PM, 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 ?
  I have tried by using this query in a loop of 5000 and run the loop
in 2 different connections with different users, but could not get the
error.  What I understood from sql statement is that it will insert new
rows when there are new/different connections, so simply running this
sql statement  from 2 connections might not insert any new rows.  a. Are there any new connections happening, how this
tableis 
getting populated?  b. How did you concluded that above sql statement leads to error,
because this error doesn't seem to occur in path of above sql
statement.  c. Are there any other sql statements in connection where you see this error?
  Can you explain a bit more about your scenario, so that this error
can be reproduced easily.

> -------------------------------
> Here is the sql definition of the table mystat.
>
> **mystats.sql**
>
>     CREATE TABLE mystat
>     (
>       id bigint NOT NULL,
>       creation date NOT NULL DEFAULT current_date,
>
>       client_addr text NOT NULL,
>       pid integer NOT NULL,
>       usename name NOT NULL,
>       CONSTRAINT statistiques_connexions_pkey PRIMARY KEY (id)
>     )
>     WITH (
>       OIDS=FALSE
>     );

Some comments about SQL statements:    a. table name provided as part of schema (mystat) is different
from one used in sql statement(my_stat)    b. definition of sequence mystat_sequence is missing, although it
doesn't seem to be necessary, but if you can provide the definition
you are using        then it will be better.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com