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

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
PRIMARYKEY (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;

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

Tell me if you need some more detailed information.

Stephan