Postgresql - ERROR : tuple concurrently updated

Question

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 mystat_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

Fenn

@user2511414 the default isolation level is used – Fenn

Answer 1

I manage to solve my problem by changing my query to this one:

INSERT INTO my_stat(id, client_addr, pid, usename)
    SELECT
        nextval('mystat_sequence'), client_addr, pid, usename
    FROM (
        SELECT
           host(client_addr) as client_addr,
           pid ,
           usename
        FROM
           pg_stat_activity
        WHERE
           usename = current_user
    ) 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
        );

I think something happened under the hood right from the Postgresql internals but I can't figure out what ...

Fenn

Answer 2

This isn't really an answer - so much as maybe helping someone else who stumbles on this error.

In my case, I was trying to be fancy and encapsulate the creation of all my functions within one function.

Something like

CREATE OR REPLACE FUNCTION main_func()
BEGIN

   CREATE OR REPLACE FUNCTION child_func1()
   BEGIN
   END

   CREATE OR REPLACE FUNCTION child_func1()
   BEGIN
   END

   main func stuff...
END

For whatever reason, I could call this function no problem from inside pgAdmin. And I could call it as much as I wanted from Java -> MyBatis.

However, as soon as I started calling the function from two different threads, I got the error from the OP: ERROR : tuple concurrently updated

The fix was, simply take those child functions out of the main function, and maintain them separately.

Looking back on it, it's a pretty bad idea to be creating functions as a result of calling a function. However, the idea was to 'encapsulate' all the functionality together.

Hope this helps someone.

Gennie

Thanks for your answer. – Fenn

Answer 3

If the pg hackers threads are anything to go by, the error kicks in when the same row is concurrently being updated by competing transactions. In your case it's likely due to the not exists() clause, which can potentially yield true and two competing inserts of the same tuple.

To work around it, you'd want to either use more robust locking (e.g. a predicate lock), serializable isolation level, or place the needed logic in an upsert statement (can be done using a function with an exception block).

Calipash

Is it possible that two disctinct transaction see the same value from nextval('mystat_sequence') ? – Fenn

It isn't, but there might be some bizarre stuff going on in there related to how the pg_stat_activity table look-up might interact with some other code that you didn't highlight. If changing the isolation level to serializable doesn't fix the issue, methinks asking the pg hackers list is the best venue; the PG developers will likely have a much more precise answer, e.g. postgresql.org/message-id/[email protected] – Calipash

Answer 4

From the docs(https://www.postgresql.org/docs/current/functions-sequence.html) from Postgres, Because sequences are non-transactional, changes made by setval are not undone if the transaction rolls back.

It means that you need to update provide thread safety by yourself using transaction so running the query inside transaction might fix your problem.

Flatware