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 (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers