[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
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
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'
Le 19/10/2013 05:21, Amit Kapila a écrit : On Fri, Oct 18, 2013 at 3:43 PM, Stéphan BEUZE 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