Skip site navigation (1) Skip section navigation (2)

Re: Duplicate key value error

From: "Dickson S(dot) Guedes" <listas(at)guedesoft(dot)net>
To: "Itagaki Takahiro" <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, "Vlad Arkhipov" <arhipov(at)dc(dot)baikal(dot)ru>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Duplicate key value error
Date: 2009-07-17 16:25:25
Message-ID: op.uw7zwnkjij9ntq@analise3.cresoltec.com.br (view raw or flat)
Thread:
Lists: pgsql-hackers
Em Fri, 03 Apr 2009 04:23:10 -0300, Itagaki Takahiro  
<itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> escreveu:
> Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> wrote:
>
>> Is it possible to print which key value is duplicated when 'Duplicate
>> key value violates unique constraint' occurs? Foreign key violation
>> error reports such kind of information.
>
> I think it is not difficult from a technical standpoint.
> The attached patch adds DETAIL messages to duplicate key value error:
>
>     postgres=# INSERT INTO tbl(pk1, pk2) VALUES ('A', 1);
>     ERROR:  duplicate key value violates unique constraint "tbl_pkey"
>     DETAIL:  Key (pk1,pk2)=(A,1) already exists.
>
> If no objection, I'd like to submit the patch to the next commit-fest  
> (8.5).

Hi Takahiro, i'm the reviewer of your patch, and the following are my  
comments about it:

The patch was applied totalty clean to CVS HEAD and compiled in Ubuntu  
8.04, Ubuntu 8.10 and AIX 5.3, but failed in follow tests:

src/test/regress/expected/uuid.out
src/test/regress/expected/constraints.out
src/test/regress/expected/create_index.out
src/test/regress/expected/inherit.out
src/test/regress/expected/transactions.out
src/test/regress/expected/arrays.out
src/test/regress/expected/plpgsql.out
src/test/regress/expected/alter_table.out
src/test/regress/expected/tablespace.out

Would be good to modify the outputs to expect a new "DETAIL:" line.

Another comment is that the patch isn't in the standart context form, but  
unified.

About the feature, it work as expected when I've INSERTed in both single  
and compound-key or UPDATEd the key values to violates the constraint,  
also in concurrently transactions. As expected too, when i INSERT or  
UPDATE the key with a value thath overflow the 512 bytes i'm getting the  
output as follow:

---
guedes=# INSERT INTO test_dup_char_key VALUES (repeat('x',1024), 'qq');
ERROR:  duplicate key value violates unique constraint  
"test_dup_char_key_pkey"
DETALHE:  Key (...)=(...) already exists.
---

I'm thinking if could be better to shows Key (my_key)=(...) instead Key  
(...)=(...) -- well, i don't know how much people uses a key with more  
512B  and how often it is to they don't know wich key it is, (just reading  
a log, for example) to we consider this important.

On the other hand there is a comment by Tom [1] about "to refactor this so  
it's not btree-specific, but could be used by other index AMs", so could  
be better trying to think about this in a way to find another alternative,  
if it is possible.

[1] http://archives.postgresql.org/pgsql-hackers/2009-04/msg00234.php

Thanks for your patch!

[]s
Dickson S. Guedes
http://pgcon.postgresql.org.br
http://www.postgresql.org.br

In response to

Responses

pgsql-hackers by date

Next:From: Petr JelinekDate: 2009-07-17 16:44:12
Subject: Re: [PATCH] DefaultACLs
Previous:From: Kevin GrittnerDate: 2009-07-17 16:00:03
Subject: Re: slow count in window query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group