Le 19/10/2013 05:21, Amit Kapila a écrit :
On Fri, Oct 18, 2013 at 3:43 PM, Stéphan BEUZE
<stephan.be...@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?
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
