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;
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 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
