Thread: Cannot insert a duplicate key into unique index
I keep getting this error from a complex, multi-table php app postgres[4513]: [4] ERROR: Cannot insert a duplicate key into unique index lang_pkey To help me find the problem, is there a way to get logging to show what table the error is caused by or what SQL code is causing it? Also, from researching this problem on the internet, it look slike the unique index would normally have three segments table_field_key .. any idea why this one is listed differently?
Brian Johnson wrote: >I keep getting this error from a complex, multi-table php app > >postgres[4513]: [4] ERROR: Cannot insert a duplicate key into unique index >lang_pkey > >To help me find the problem, is there a way to get logging to show what table the >error is caused by or what SQL code is causing it? > > >Also, from researching this problem on the internet, it look slike the unique index >would normally have three segments table_field_key .. any idea why this one is >listed differently? > > > PK indexes are different, because there can only be one pkey on a table, there is no need for the 'field' part. So your index name looks like <tablename>_pkey... I guess, this answers your question about 'what table the error is caused by'... As for 'what SQL code', you can set debug_print_query=true in postgresql.conf - this will make it print all the sql queries you execute to the log file... I hope, it helps... Dima
This same error happened to me this week, and I also saw a posting from
someone else. By chance, do you have a serial field on your lang_pkey?
And if you do, have you exported and reimported the table, dropped and
recreated the table or done something to the serial? I had done this and
when the table was recreated, it reset my serial counter to 1. I
eventually butted up against a PK with the same number already on the
system. You might check there
Patrick Hatcher
Macys.Com
Dmitry Tkach
<dmitry@openratings.com To: Brian Johnson <bjohnson@johnson-engineering.ca>
> cc: pgsql-novice@postgresql.org
Sent by: Subject: Re: [NOVICE] Cannot insert a duplicate key into unique
index
pgsql-novice-owner@post
gresql.org
07/16/2003 12:45 PM
Brian Johnson wrote:
>I keep getting this error from a complex, multi-table php app
>
>postgres[4513]: [4] ERROR: Cannot insert a duplicate key into unique index
>lang_pkey
>
>To help me find the problem, is there a way to get logging to show what
table the
>error is caused by or what SQL code is causing it?
>
>
>Also, from researching this problem on the internet, it look slike the
unique index
>would normally have three segments table_field_key .. any idea why this
one is
>listed differently?
>
>
>
PK indexes are different, because there can only be one pkey on a table,
there is no need for the 'field' part.
So your index name looks like <tablename>_pkey...
I guess, this answers your question about 'what table the error is
caused by'...
As for 'what SQL code', you can set debug_print_query=true in
postgresql.conf - this will make it print all the sql queries you
execute to the log file...
I hope, it helps...
Dima
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Well I had to do some voodoo on my redhat machine (enabling syslog facility in postgresql.conf and then create the syslog facility and restart both services) But I did get the sql statements to output. The tip about lang_pkey helped since I could check the source files and found that the it is not a serial as the primary key but a varchar field So now I have the sql statement too, and can hunt down the problem Thanks guys Patrick Hatcher (PHatcher@macys.com) wrote: > > >This same error happened to me this week, and I also saw a posting from >someone else. By chance, do you have a serial field on your lang_pkey? >And if you do, have you exported and reimported the table, dropped and >recreated the table or done something to the serial? I had done this and >when the table was recreated, it reset my serial counter to 1. I >eventually butted up against a PK with the same number already on the >system. You might check there > >Patrick Hatcher >Macys.Com > > > > > Dmitry Tkach > <dmitry@openratings.com To: Brian Johnson <bjohnson@johnson-engineering.ca> > > cc: pgsql-novice@postgresql.org > Sent by: Subject: Re: [NOVICE] Cannot insert a duplicate key into unique index > pgsql-novice-owner@post > gresql.org > > > 07/16/2003 12:45 PM > > > > > >Brian Johnson wrote: > >>I keep getting this error from a complex, multi-table php app >> >>postgres[4513]: [4] ERROR: Cannot insert a duplicate key into unique index >>lang_pkey >> >>To help me find the problem, is there a way to get logging to show what >table the >>error is caused by or what SQL code is causing it? >> >> >>Also, from researching this problem on the internet, it look slike the >unique index >>would normally have three segments table_field_key .. any idea why this >one is >>listed differently? >> >> >> >PK indexes are different, because there can only be one pkey on a table, >there is no need for the 'field' part. >So your index name looks like <tablename>_pkey... >I guess, this answers your question about 'what table the error is >caused by'... > >As for 'what SQL code', you can set debug_print_query=true in >postgresql.conf - this will make it print all the sql queries you >execute to the log file... > >I hope, it helps... > >Dima > > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > > >
Dmitry Tkach <dmitry@openratings.com> writes:
> As for 'what SQL code', you can set debug_print_query=true in
> postgresql.conf - this will make it print all the sql queries you
> execute to the log file...
In recent releases (7.3 for sure, not sure about older ones) there
is also a log_min_error_statement setting that can be tweaked to log
only queries generating errors.
regards, tom lane
Hi all,
I have a genuine novice question. What's the best "postgres way" to
lock tables in the following work flow circumstances:
A)
1) Begin work;
2) select max(Id) from table;
3) insert into table record with Id=(max+1);
4) commit;
I want to be absolutely certain no other user can run this identical
query concurrently (read the same max(Id)) causing two identical
records to be built with the same Id=(max+1) between steps 2 and 4.
This would require locking the entire table with a "Lock table"
statement between steps 1 and 2, yes? Best syntax?
B)
1) Begin work;
2) Select User from table where Id=n;
3) If User is null then:
Update row Id=n to User="me"
4) commit;
I want to be absolutely certain no other user can update the tuple to
User="not me" between steps 2 and 3. This would require me to add a
"Lock" statement that would prevent reads on this tuple between steps
1 and 2, yes (or a "Select with lock" statment)? Again, a suggestion
for the explicit lock type would be awesome.
I'm especially getting confused by "lock table in row exclusive mode"
without including in this statement which rows to lock...the manual
pages don't offer clear enough examples for this particual newbie.
Thanks!
AB
--
Allan Berger
Bright Eyes & Bushy Tails Veterinary Service
3005 Highway 1 NE
Iowa City, IA 52240
(319) 351-4256 (voice)
(319) 341-8445 (fax) http://www.BEBT.com