Re: ERROR : 'tuple concurrently updated'

On 18/10/13 18:01, Amit Kapila wrote:
> On Wed, Oct 16, 2013 at 5:55 PM, Stéphan BEUZE
> <stephan(dot)beuze(at)douane(dot)finances(dot)gouv(dot)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