Re: [HACKERS] ERROR : 'tuple concurrently updated'

[Stéphan BEUZE]

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'

[Amit Kapila]

On Mon, Oct 28, 2013 at 3:22 PM, Stéphan BEUZE
stephan.be...@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.be...@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


-- 
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'

[Craig Ringer]

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


-- 
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'

[Mark Kirkwood]

On 18/10/13 18:01, Amit Kapila wrote:

On Wed, Oct 16, 2013 at 5:55 PM, Stéphan BEUZE
stephan.be...@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



--
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'

[Stéphan BEUZE]

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'

[Stéphan BEUZE]

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'

[Cédric Villemain]

   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

signature.asc
Description: This is a digitally signed message part.

Re: [HACKERS] ERROR : 'tuple concurrently updated'

[Stéphan BEUZE]

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'

[Tom Lane]

=?ISO-8859-1?Q?St=E9phan_BEUZE?= stephan.be...@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


-- 
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'

[Amit Kapila]

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?


 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


-- 
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'

[Robert Haas]

On Wed, Oct 16, 2013 at 8:25 AM, Stéphan BEUZE
stephan.be...@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


-- 
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'

[Stéphan BEUZE]

 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

Re: [HACKERS] ERROR : 'tuple concurrently updated'

[Amit Kapila]

On Wed, Oct 16, 2013 at 5:55 PM, Stéphan BEUZE
stephan.be...@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.

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


-- 
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'

[Stéphan BEUZE]

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

Re: [HACKERS] ERROR: tuple concurrently updated

[Tom Lane]

Stephen Frost [EMAIL PROTECTED] writes:
 I havn't built a reliable test case yet but I *think* the tuple
 concurrently updated problem is with an analyze being run inside of a
 function and also being run by autovacuum.

If so it should be fixed as of 8.2 --- I believe we changed the locking
rules to ensure only one ANALYZE at a time for any one table.

Conflicts from concurrent ANALYZEs are the only cases I've heard of
before that make this error occur in the field, but I suppose it would
be possible to get it from other things such as concurrently trying to
CREATE OR REPLACE the same function.

 The SysCache stuff I was
 thinking about previously was actually for another problem that I hadn't
 seen in a long time (because I hadn't been doing a particular set of
 operations, not because it's that difficult to have happen) but just ran
 into again today:
 ERROR:  cache lookup failed for relation ...

I think we've got a solution for that in 8.2, also --- at least, the
only common case I know of should be fixed, namely where a RENAME or
similar has caused the same table name to be assigned to a new OID.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] ERROR: tuple concurrently updated

[Stephen Frost]

* Tom Lane ([EMAIL PROTECTED]) wrote:
 Stephen Frost [EMAIL PROTECTED] writes:
  I havn't built a reliable test case yet but I *think* the tuple
  concurrently updated problem is with an analyze being run inside of a
  function and also being run by autovacuum.
 
 If so it should be fixed as of 8.2 --- I believe we changed the locking
 rules to ensure only one ANALYZE at a time for any one table.
[...]
 I think we've got a solution for that in 8.2, also --- at least, the
 only common case I know of should be fixed, namely where a RENAME or
 similar has caused the same table name to be assigned to a new OID.

Great!  These were on 8.1 and I was actually just working to try and
reproduce them on 8.2 (without success so far!).  I'll see about
upgrading the production systems to 8.2 soon and will let ya'll know if
I see them again there.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] ERROR: tuple concurrently updated

[Tom Lane]

Stephen Frost [EMAIL PROTECTED] writes:
   Subject pretty much says it all.  I've put up with this error in the
   past when it has caused me trouble but it's now starting to hit our
   clients on occation which is just unacceptable.

Have you tracked down the exact scenario making it happen?

   If this is correct then the solution seems to be either add versioning
   to the SysCache data,

You have provided no evidence that that would fix anything at all.
To my mind a concurrently updated failure is more likely to mean
insufficient locking around update operations.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] ERROR: tuple concurrently updated

[Stephen Frost]

* Tom Lane ([EMAIL PROTECTED]) wrote:
 Stephen Frost [EMAIL PROTECTED] writes:
Subject pretty much says it all.  I've put up with this error in the
past when it has caused me trouble but it's now starting to hit our
clients on occation which is just unacceptable.
 
 Have you tracked down the exact scenario making it happen?

I think I might have confused two different issues, sorry. :/

If this is correct then the solution seems to be either add versioning
to the SysCache data,
 
 You have provided no evidence that that would fix anything at all.
 To my mind a concurrently updated failure is more likely to mean
 insufficient locking around update operations.

I havn't built a reliable test case yet but I *think* the tuple
concurrently updated problem is with an analyze being run inside of a
function and also being run by autovacuum.  The SysCache stuff I was
thinking about previously was actually for another problem that I hadn't
seen in a long time (because I hadn't been doing a particular set of
operations, not because it's that difficult to have happen) but just ran
into again today:
ERROR:  cache lookup failed for relation ...

I first started seeing this happen, iirc, when I created a function 
which went against pg_class/pg_attribute/pg_type and used
pg_table_is_visible().  This function (and another which uses pg_class,
pg_constraint and pg_attribute) gets used over and over again from
another pl/pgsql function.  Basically we are walking through a list of
tables pulling the column names and primary keys and then running some
checks on the tables.  Anyhow, while this is running (and it takes
upwards of half an hour to run) other activity on the database (this
time it was creating a view in a seperate completely unrelated schema)
can cause the lookup failure which kills the long-running function
(which gets to be very frustrating...).

Thanks,

Stephen


signature.asc
Description: Digital signature

[HACKERS] ERROR: tuple concurrently updated

[Stephen Frost]

Greetings,

  Subject pretty much says it all.  I've put up with this error in the
  past when it has caused me trouble but it's now starting to hit our
  clients on occation which is just unacceptable.

  The way I've seen it happen, and this is just empirically so I'm not
  sure that it's exactly right, is something like this:

  Running with pg_autovacuum on the system
  Run a long-running PL/PgSQL function which creates tables
  Wait for some sort of overlap, and the PL/PgSQL function dies with the
  above error.

  I've also seen it happen when I've got a long-running PL/PgSQL
  function going and I'm creating tables in another back-end.

  From a prior discussion I *think* the issue is the lack of
  versioning/visibility information in the SysCache which means that if
  the long-running function attempts to look-up data about a table which
  was created *after* the long-running function started but was put into
  the common SysCache by another backend, the long-running function gets
  screwed by the 'tuple concurrently updated' query and ends up failing
  and being rolled back.

  If this is correct then the solution seems to be either add versioning
  to the SysCache data, or have an overall 'this SysCache is only good
  for data past transaction X' so that a backend which is prior to that
  version could just accept that it can't use the SysCache and fall back
  to accessing the data directly (if that's possible).  I'm not very
  familiar with the way the SysCache system is envisioned but I'm not a
  terrible programmer (imv anyway) and given some direction on the
  correct approach to solving this problem I'd be happy to spend some
  time working on it.  I'd *really* like to see this error just go away
  completely for all non-broken use-cases.

Thanks,

Stephen


signature.asc
Description: Digital signature