Re: ERROR : 'tuple concurrently updated' - Mailing list pgsql-hackers

Le 19/10/2013 05:21, Amit Kapila a écrit :
> On Fri, Oct 18, 2013 at 3:43 PM, Stéphan BEUZE
> <stephan.beuze@douane.finances.gouv.fr>  wrote:
>> Here I provide more details about the environment where the error occurs:
>>
>> * ENVIRONMENT
>> Client:
>>       Java Web Application running on JBoss 5.0.0.GA - JDK 1.6.0_24 64bit
>>
>> Server:
>>      Postgresql 9.2.4, compiled by Visual C++ build 1600, 64bit
>>
>> Client and Server run on the same platform:
>>      Windows 7 Professional SP1 (2009)
>>
>>
>> * STRUCTURES
>> CREATE ROLE rec LOGIN  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE
>> NOREPLICATION;
>> CREATE ROLE rec_lct LOGIN  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE
>> NOREPLICATION;
>>
>> CREATE SCHEMA rec  AUTHORIZATION rec;
>>
>> GRANT ALL ON SCHEMA rec TO rec;
>> GRANT USAGE ON SCHEMA rec TO rec_lct;
>>
>> ALTER ROLE rec SET search_path = rec;
>> ALTER ROLE rec_lct SET search_path = rec;
>>
>> SET SCHEMA 'rec'
>>
>> CREATE SEQUENCE stats_sequence
>>    INCREMENT 1
>>    MINVALUE 1
>>    MAXVALUE 9223372036854775807
>>    START 1
>>    CACHE 120
>>    CYCLE;
>> ALTER TABLE stats_sequence OWNER TO rec;
>> GRANT ALL ON TABLE stats_sequence TO rec;
>> GRANT UPDATE ON TABLE stats_sequence TO rec_lct;
>>
>>    CREATE TABLE my_stat
>>
>>      (
>>        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 my_stat _pkey PRIMARY KEY (id)
>>
>>      )
>>      WITH (
>>        OIDS=FALSE
>>      );
>>
>> ALTER TABLE statistiques_connexions OWNER TO rec;
>> GRANT ALL ON TABLE statistiques_connexions TO rec;
>> GRANT SELECT, INSERT ON TABLE statistiques_connexions TO rec_lct;
> Is this table statistiques_connexions used for something different
> from my_stat or this is actual name of my_stat used in your
> application?
Sorry, I forgot to translate this part of my code to plain english.
Instead of *statistiques_connexions* please read *my_stat* anywhere it
appears.

>> CREATE INDEX statistiques_connexions_idx_creation
>>    ON statistiques_connexions
>>    USING btree
>>    (creation);
>>
>> CREATE INDEX statistiques_connexions_idx_ukey
>>    ON statistiques_connexions
>>    USING btree
>>    (creation, pid, client_addr COLLATE pg_catalog."default", usename);
>>
>>
>> * CONTEXT
>> Two Java threads are created. One is connected with 'rec' user, while the
>> other one
>> is connected with 'rec_lct' user.
>>
>> The threads don't create themselves their JDBC connections.
>> Instead, they each have their own pooled datasource preconfigured.
>> The pooled datasources are managed by the same connection pool
>> library: c3p0 0.9.1. The pooled datasources each open 3 connections
>> on startup. They can make this number of connections variate from 1 to 5
>> connections.
>>
>> In our development context, this number of connections stay at 3.
>>
>> The threads run the following query every 500 ms.
> With the above information, it is difficult to imagine the cause of
> problem, is it possible for you to write a separate test which you can
> post here, if you can write using some scripts or libpq, that would
> also be sufficient.
Is it OK if I send a test case written in Java ? Or is there a well
defined way to post test case ?


>>
>>>      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;
>>
>> What can be observed first is that, at the beginning, everything run
>> smoothly.
>> Then unpredictably, the error 'tuple concurrently updated' appears...
>> Needless to say, that it disappears too... unpredictably.
>> Sometimes, it can shows up contisnously.
> Do you see any other problem due to this error in your database?
No I don't see anything else. The problem appears only when two
concurrent sessions , with different users in my case,
performs the above query.

Stephan