Thread: 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_pkeyPRIMARY KEY (id) ) WITH ( OIDS=FALSE );
On Wed, Oct 16, 2013 at 8:25 AM, Stéphan BEUZE
<stephan.beuze@douane.finances.gouv.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.beuze@douane.finances.gouv.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
tableis
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.beuze@douane.finances.gouv.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
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
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.
Stéphan BEUZE <stephan.beuze@douane.finances.gouv.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.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
Le 19/10/2013 05:21, Amit Kapila a écrit :
> 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?
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.beuze@douane.finances.gouv.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.beuze@douane.finances.gouv.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