8

When doing an UPDATE query, we got the following error message:

ERROR:  duplicate key value violates unique constraint "tableA_pkey"
DETAIL:  Key (id)=(47470) already exists.

However, our UPDATE query does not affect the primary key. Here is a simplified version:

UPDATE tableA AS a
SET
    items = (
        SELECT array_to_string(
            array(
                SELECT b.value
                FROM tableB b
                WHERE b.a_id = b.id
                GROUP BY b.name
            ),
            ','
        )
    )
WHERE
    a.end_at BETWEEN now() AND  now() - interval '1 day';

We ensured the primary key sequence was already synced:

\d tableA_id_seq

Which produces:

    Column     |  Type   |          Value           
---------------+---------+--------------------------
 sequence_name | name    | tableA_id_seq
 last_value    | bigint  | 50364
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | t

Looking for maximum table index:

select max(id) from tableA;

We got a lower value:

  max  
-------
 50363
(1 row)

Have you any idea on why such a behavior? If we exclude the problematic id, it works.

Another strange point is that replacing the previous UPDATE by:

UPDATE tableA AS a
SET
    items = (
        SELECT array_to_string(
            array(
                SELECT b.value
                FROM tableB b
                WHERE b.a_id = b.id
                GROUP BY b.name
            ),
            ','
        )
    )
WHERE a.id = 47470;

It works well. Are we missing something?

EDIT: triggers

I have no user-defined triggers on this table:

SELECT t.tgname, c.relname
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE
    c.relname = 'tableA'
    AND
    t.tgisinternal = false
;

Which returns no row.

Note: I am using psql (PostgreSQL) 9.3.4 version.

9
  • 1
    Are there any triggers on update tableA? Commented Jun 27, 2014 at 9:21
  • What is the version of your Postgres server ? Commented Jun 27, 2014 at 12:01
  • @pozs: I added the request I did to find the triggers. I got 16 ones on this table. Have you any request to give more details about it? Commented Jun 27, 2014 at 14:46
  • 1
    @JonathanPetitcolas all user-defined ones (WHERE pg_trigger.tgisinternal = FALSE), which fires before & after update, with the triggers' functions' bodies. Commented Jun 27, 2014 at 14:54
  • 1
    No user defined triggers. :( Commented Jun 27, 2014 at 14:57

1 Answer 1

2

Not really sure what was the cause. However, deleting the two (non vital) records corresponding to already existing ids (?) solved the issue.

Sign up to request clarification or add additional context in comments.

1 Comment

You should probably accept your own answer if it worked for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.