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

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?

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