Re: [HACKERS] ERROR : 'tuple concurrently updated'
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
Re: [HACKERS] ERROR : 'tuple concurrently updated'
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
Re: [HACKERS] ERROR : 'tuple concurrently updated'
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR : 'tuple concurrently updated'
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR : 'tuple concurrently updated'
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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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
);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
