Re: ERROR : 'tuple concurrently updated'

Le 19/10/2013 05:21, Amit Kapila a écrit :
> On Fri, Oct 18, 2013 at 3:43 PM, Stéphan BEUZE
> <stephan(dot)beuze(at)douane(dot)finances(dot)gouv(dot)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