Re: ERROR : 'tuple concurrently updated'

The following query is performed concurrently by two threads logged in
with two different users:

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;

From time to time, I get the following error: "tuple concurrently updated"

I can't figure out what throw this error and why this error is thrown.
Can you shed a light ?

-------------------------------
Here is the sql definition of the table mystat.

**mystats.sql**

CREATE TABLE mystat
(
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 statistiques_connexions_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);

On Wed, Oct 16, 2013 at 8:25 AM, Stéphan BEUZE
<stephan(dot)beuze(at)douane(dot)finances(dot)gouv(dot)fr> wrote:
> The following query is performed concurrently by two threads logged in with
> two different users:
>
> 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;
>
> From time to time, I get the following error: "tuple concurrently updated"
>
> I can't figure out what throw this error and why this error is thrown. Can
> you shed a light ?

Hmm. That error isn't supposed to happen; it's denoted in the source
code by elog() rather than ereport(), which means that it's just there
as a backstop, and never really intended to be become user-visible.
So I'd say you've found a bug. What PostgreSQL version is this?

There are actually two places where that error can happen:
simple_heap_update and simple_heap_delete. If you set the error
verbosity to verbose, you should be able to see which function is at
fault. The thing is, I don't see anything in that query which would
update or delete any tuples, so there must be more to the story. If
you have the ability to build from source, you could try setting a
long sleep just before that error is thrown. Then run your test case
until it hangs at that spot and get a stack backtrace. But that may
be more troubleshooting than you want to get into. Are there any
triggers on any of these tables? Any noteworthy extensions installed?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> What PostgreSQL version is this?
I'm using "Postgresql 9.2.4, compiled by Visual C++ build 1600, 64-bit"

> Are there any triggers on any of these tables?
There are no triggers.

> Any noteworthy extensions installed?
Here is the results returned by "select * from pg_available_extensions"

name ; default_version ; installed_version
------------------------------------------------------------------------
adminpack ; 1.0 ;
autoinc ; 1.0 ;
btree_gin ; 1.0 ;
btree_gist ; 1.0 ;
chkpass ; 1.0 ;
citext ; 1.0 ;
cube ; 1.0 ;
dblink ; 1.0 ;
dict_int ; 1.0 ;
dict_xsyn ; 1.0 ;
earthdistance ; 1.0 ;
file_fdw ; 1.0 ;
fuzzystrmatch ; 1.0 ;
hstore ; 1.1 ;
insert_username ; 1.0 ;
intagg ; 1.0 ;
intarray ; 1.0 ;
isn ; 1.0 ;
lo ; 1.0 ;
ltree ; 1.0 ;
moddatetime ; 1.0 ;
pageinspect ; 1.0 ;
pgcrypto ; 1.0 ;
pgrowlocks ; 1.0 ;
pgstattuple ; 1.0 ;
pg_buffercache ; 1.0 ;
pg_freespacemap ; 1.0 ;
pg_stat_statements ; 1.1 ;
pg_trgm ; 1.0 ;
pldbgapi ; 1.0 ;
plperl ; 1.0 ;
plperlu ; 1.0 ;
plpgsql ; 1.0 ; 1.0
plpython2u ; 1.0 ;
plpython3u ; 1.0 ;
plpythonu ; 1.0 ;
pltcl ; 1.0 ;
pltclu ; 1.0 ;
refint ; 1.0 ;
seg ; 1.0 ;
sslinfo ; 1.0 ;
tablefunc ; 1.0 ;
tcn ; 1.0 ;
test_parser ; 1.0 ;
timetravel ; 1.0 ;
tsearch2 ; 1.0 ;
unaccent ; 1.0 ;
uuid-ossp ; 1.0 ;
xml2 ; 1.0 ;

Le 17/10/2013 14:18, Robert Haas a écrit :
> Hmm. That error isn't supposed to happen; it's denoted in the source
> code by elog() rather than ereport(), which means that it's just there
> as a backstop, and never really intended to be become user-visible.
> So I'd say you've found a bug. What PostgreSQL version is this?
>
> There are actually two places where that error can happen:
> simple_heap_update and simple_heap_delete. If you set the error
> verbosity to verbose, you should be able to see which function is at
> fault. The thing is, I don't see anything in that query which would
> update or delete any tuples, so there must be more to the story. If
> you have the ability to build from source, you could try setting a
> long sleep just before that error is thrown. Then run your test case
> until it hangs at that spot and get a stack backtrace. But that may
> be more troubleshooting than you want to get into. Are there any
> triggers on any of these tables? Any noteworthy extensions installed?
>

On Wed, Oct 16, 2013 at 5:55 PM, Stéphan BEUZE
<stephan(dot)beuze(at)douane(dot)finances(dot)gouv(dot)fr> wrote:
> The following query is performed concurrently by two threads logged in with
> two different users:
>
> 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;
>
> From time to time, I get the following error: "tuple concurrently updated"
>
> I can't figure out what throw this error and why this error is thrown. Can
> you shed a light ?

I have tried by using this query in a loop of 5000 and run the loop
in 2 different connections with different users, but could not get the
error.
What I understood from sql statement is that it will insert new
rows when there are new/different connections, so simply running this
sql statement
from 2 connections might not insert any new rows.
a. Are there any new connections happening, how this table is
getting populated?
b. How did you concluded that above sql statement leads to error,
because this error doesn't seem to occur in path of above sql
statement.
c. Are there any other sql statements in connection where you see this error?

Can you explain a bit more about your scenario, so that this error
can be reproduced easily.

> -------------------------------
> Here is the sql definition of the table mystat.
>
> **mystats.sql**
>
> CREATE TABLE mystat
> (
> 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 statistiques_connexions_pkey PRIMARY KEY (id)
> )
> WITH (
> OIDS=FALSE
> );

Some comments about SQL statements:
a. table name provided as part of schema (mystat) is different
from one used in sql statement(my_stat)
b. definition of sequence mystat_sequence is missing, although it
doesn't seem to be necessary, but if you can provide the definition
you are using
then it will be better.

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

On 18/10/13 18:01, Amit Kapila wrote:
> On Wed, Oct 16, 2013 at 5:55 PM, Stéphan BEUZE
> <stephan(dot)beuze(at)douane(dot)finances(dot)gouv(dot)fr> wrote:
>> The following query is performed concurrently by two threads logged in with
>> two different users:
>>
>> 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;
>>
>> From time to time, I get the following error: "tuple concurrently updated"
>>
>> I can't figure out what throw this error and why this error is thrown. Can
>> you shed a light ?
>
> I have tried by using this query in a loop of 5000 and run the loop
> in 2 different connections with different users, but could not get the
> error.
> What I understood from sql statement is that it will insert new
> rows when there are new/different connections, so simply running this
> sql statement
> from 2 connections might not insert any new rows.
> a. Are there any new connections happening, how this table is
> getting populated?
> b. How did you concluded that above sql statement leads to error,
> because this error doesn't seem to occur in path of above sql
> statement.
> c. Are there any other sql statements in connection where you see this error?
>
> Can you explain a bit more about your scenario, so that this error
> can be reproduced easily.
>
>> -------------------------------
>> Here is the sql definition of the table mystat.
>>
>> **mystats.sql**
>>
>> CREATE TABLE mystat
>> (
>> 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 statistiques_connexions_pkey PRIMARY KEY (id)
>> )
>> WITH (
>> OIDS=FALSE
>> );
>
> Some comments about SQL statements:
> a. table name provided as part of schema (mystat) is different
> from one used in sql statement(my_stat)
> b. definition of sequence mystat_sequence is missing, although it
> doesn't seem to be necessary, but if you can provide the definition
> you are using
> then it will be better.
>

Stephen - what framework or system are you using to run these two
threads? That sort of error looks very like the type of thing you would
get by sharing the connection object/pointer between two threads...

Cheers

Mark

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

You may find additional answers in my last email.
However, I'll try to adress some of your questions.

> a. Are there any new connections happening, how this table is getting populated?
Check my last email.

> b. How did you concluded that above sql statement leads to error,
> because this error doesn't seem to occur in path of above sql
> statement.
The errors appear when I added the second threads.

> c. Are there any other sql statements in connection where you see this error?
This is the only statement that generat this error.

> Can you explain a bit more about your scenario, so that this error
> can be reproduced easily.
Please check my last full detailed email.

> Some comments about SQL statements:
> a. table name provided as part of schema (mystat) is different
> from one used in sql statement(my_stat)
Sorry, for the typos

> b. definition of sequence mystat_sequence is missing, although it
> doesn't seem to be necessary, but if you can provide the definition
> you are using
> then it will be better.
The definition of the sequence is provided in my detailed email among
other things too.

> > What PostgreSQL version is this?
>
> I'm using "Postgresql 9.2.4, compiled by Visual C++ build 1600,
> 64-bit"
> > Are there any triggers on any of these tables?
>
> There are no triggers.
>
> > Any noteworthy extensions installed?
>
> Here is the results returned by "select * from
> pg_available_extensions"

Those extensions are installed in the system, so you can install them in
PostgreSQL.
You may also have contrib run by servers without being pure extension.

So the question is about used extensions or contrib. (it can be loaded
by server, or in a session with LOAD, it can be auto-explain,
pg_stat_statement, ....).

> > There are actually two places where that error can happen:
> > simple_heap_update and simple_heap_delete. If you set the error
> > verbosity to verbose, you should be able to see which function is at
> > fault. The thing is, I don't see anything in that query which would
> > update or delete any tuples, so there must be more to the story. If
> > you have the ability to build from source, you could try setting a
> > long sleep just before that error is thrown. Then run your test
> > case
> > until it hangs at that spot and get a stack backtrace. But that may
> > be more troubleshooting than you want to get into.

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

> Those extensions are installed in the system, so you can install them in
> PostgreSQL.
> You may also have contrib run by servers without being pure extension.
>
> So the question is about used extensions or contrib. (it can be loaded
> by server, or in a session with LOAD, it can be auto-explain,
> pg_stat_statement, ....).
>
I don't use any used extensions or contrib.

=?ISO-8859-1?Q?St=E9phan_BEUZE?= <stephan(dot)beuze(at)douane(dot)finances(dot)gouv(dot)fr> writes:
>> So the question is about used extensions or contrib. (it can be loaded
>> by server, or in a session with LOAD, it can be auto-explain,
>> pg_stat_statement, ....).

> I don't use any used extensions or contrib.

Well, you're doing *something* that you have not told us about. As
Robert said, the only places where that error can be thrown are
simple_heap_update and simple_heap_delete, and neither of those are
reachable from an INSERT command unless something is happening behind
the scenes. Maybe you have an ON INSERT trigger on that table?

Another point here is that the NOT EXISTS coding seems to be trying to
prevent insertion of any duplicate rows into my_stat, but it will fail
miserably as soon as there are multiple processes doing that command
concurrently, since the NOT EXISTS check will only examine rows that were
committed before the command starts, not any that get committed while
it runs. I wonder whether you have code you've not shown us that
depends on the assumption of no duplicates in my_stat, and will lead to
multiple-update attempts somewhere else as soon as such duplicates appear.

regards, tom lane

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

Le 19/10/2013 05:21, Amit Kapila a écrit :
> 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?
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

On Mon, Oct 28, 2013 at 3:22 PM, Stéphan BEUZE
<stephan(dot)beuze(at)douane(dot)finances(dot)gouv(dot)fr> wrote:
> Le 19/10/2013 05:21, Amit Kapila a écrit :
>> On Fri, Oct 18, 2013 at 3:43 PM, Stéphan BEUZE
>> <stephan(dot)beuze(at)douane(dot)finances(dot)gouv(dot)fr> wrote:
>>> * 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 ?

It is better if you can give simplified 'C' test, but I don't think
there is any problem with Java test case, might be someone knows java
can try with that test. You can post the Java test and see if someone
could reproduce and tell you the exact problem, else you can write a
'C' test and post that as well.

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

On 10/28/2013 05:52 PM, Stéphan BEUZE wrote:
> Is it OK if I send a test case written in Java ? Or is there a well
> defined way to post test case ?

A standalone test case written in Java is pretty easy to run. Just
provide build and run instructions - for example, if it's a stand-alone
file, install the JDK for your OS (install OpenJDK from package
management if on Linux) then:

javac TheClass.java
java -cp postgresql-9.2-1003.jdbc3.jar: TheClass

Most PostgreSQL users on this list won't have much if any Java tooling
installed, won't know Ant, Maven, JDBC drivers, etc. So Java test cases
will need to be documented for a from-scratch start. I'm happy to run
any test case, and I _have_ used a bunch of Java tools.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services