Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 my_stat.

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 mystat_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
On one hand, you have a useless level of nesting with the subqueries - but I think it has nothing to do with your problem. Instead, have a look here - that comment suggests that the original problem is somewhere else. –  dezso Oct 17 '13 at 11:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.