Re: ERROR : 'tuple concurrently updated'

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?

>
> 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.

>
>
>> 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?

> Tell me if you need some more detailed information.
>
> Stephan

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