Обсуждение: duplicate key violates unique contraint on pg_type_typname_nsp_index
Hi all,
I'm running Postgresql 8.3.9 on debian lenny amd64
the box has 8GB of ram, the db runs on a separate
software raid-1 device, that's the output of select version();
# SELECT version () ;
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.3.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2
(1 row)
I'm experiencing something weird. here the session's log
that involves the prob I mentioned
2010-03-08 12:58:53 CET p@c[3189]ERROR: table "check_unfitted_strata_col" does not exist
2010-03-08 12:58:53 CET p@c[3189]STATEMENT: drop table check_unfitted_strata_col
2010-03-08 12:58:53 CET p@c[3189]LOG: duration: 0.018 ms statement: Begin
2010-03-08 12:59:41 CET p@c[3189]ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
2010-03-08 12:59:41 CET p@c[3189]STATEMENT: create table check_unfitted_strata_col as select * from
sampling.sample_gen_designlimit 0
2010-03-08 12:59:41 CET p@c[3189]LOG: duration: 0.016 ms statement: rollback
as you can see firstly I try to drop the table
check_unfitted_strata_col (search_path is set to
default so the complete name is public.check_unfitted_strata_col)
then I try to create a table with the very same name and fileds definition
(I'm to emulate DROP IF EXISTS for backward compatibility).
But for some reasons sometimes CREATE TABLE fails violate unique constraint
on pg_type_typname_nsp_index... I googled around a bit but everything I find
seems related to temp table, which is not my case :/
http://archives.postgresql.org/pgsql-novice/2004-11/msg00241.php
http://markmail.org/message/iusg4626iwdcmrg2#query:+page:1+mid:iusg4626iwdcmrg2+state:results
http://archives.postgresql.org/pgsql-general/2005-07/msg00412.php
those sql commands could be executed in separate
postgres back-ends almost simultaneously (isolation level
is read committed), but I always thought wrapping the CREATE TABLE
command inside a transaction avoid any misbehavior (a part from the
fact that the second process will wait for the first to commit or rollback)
am I missing something?
(FWIW testing the same path using psql, emulating concurrent
sessions did not raise any problems)
it's worthy to note that the problem seems
to goes away after restarting postgresql (the
box is turned off during the night).
The sql command are submitted by a tcl/tk applications
that use a persistent connection via libpgtcl.
Unfortunately I'm not able to set up a reproducible test-case :/
Andrea
Andrea Suisani <sickpig@opinioni.net> writes:
> I'm experiencing something weird. here the session's log
> that involves the prob I mentioned
> 2010-03-08 12:59:41 CET p@c[3189]ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
> 2010-03-08 12:59:41 CET p@c[3189]STATEMENT: create table check_unfitted_strata_col as select * from
sampling.sample_gen_designlimit 0
> those sql commands could be executed in separate
> postgres back-ends almost simultaneously (isolation level
> is read committed), but I always thought wrapping the CREATE TABLE
> command inside a transaction avoid any misbehavior (a part from the
> fact that the second process will wait for the first to commit or rollback)
Well, it does avoid any "misbehavior", it's just not producing exactly
the error message you expect. If you have two sessions trying to create
the same table name at exactly the same time, this is a likely result.
They both have to create pg_type entries for the table's row type,
so they can collide on that as well as on the table name itself.
There have been some unexplained reports of similar error messages in
cases where it didn't seem that any concurrent creation could be going
on. But I don't see any reason to think there's anything very
mysterious here, if you do have sessions trying to create the same table
concurrently.
regards, tom lane
On 09/03/2010 16:51, Tom Lane wrote: > Andrea Suisani<sickpig@opinioni.net> writes: >> I'm experiencing something weird. here the session's log >> that involves the prob I mentioned > >> 2010-03-08 12:59:41 CET p@c[3189]ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" >> 2010-03-08 12:59:41 CET p@c[3189]STATEMENT: create table check_unfitted_strata_col as select * from sampling.sample_gen_designlimit 0 > >> those sql commands could be executed in separate >> postgres back-ends almost simultaneously (isolation level >> is read committed), but I always thought wrapping the CREATE TABLE >> command inside a transaction avoid any misbehavior (a part from the >> fact that the second process will wait for the first to commit or rollback) > > Well, it does avoid any "misbehavior", it's just not producing exactly > the error message you expect. If you have two sessions trying to create > the same table name at exactly the same time, this is a likely result. I'm not 100% sure but it's quite unlikely that they try to create the table at exactly the same time. With "almost simultaneously" I meant to indicate some kind of time overlapping between the two session. > They both have to create pg_type entries for the table's row type, > so they can collide on that as well as on the table name itself. take for granted the "at exactly same time" hypothesis, it does maks sense. just after I've sent the email I've checked to which table pg_type_typname_nsp_index belongs to and found that pg_type's the "owner" > There have been some unexplained reports of similar error messages in > cases where it didn't seem that any concurrent creation could be going > on. But I don't see any reason to think there's anything very > mysterious here, if you do have sessions trying to create the same table > concurrently. I will dig deeper and I'll try to monitor the process for the next few days trying to get more infos. I'll keep you posted thanks Andrea
On 09/03/2010 17:12, Andrea Suisani wrote: > On 09/03/2010 16:51, Tom Lane wrote: >> Andrea Suisani<sickpig@opinioni.net> writes: >>> I'm experiencing something weird. here the session's log >>> that involves the prob I mentioned >> >>> 2010-03-08 12:59:41 CET p@c[3189]ERROR: duplicate key value violates >>> unique constraint "pg_type_typname_nsp_index" >>> 2010-03-08 12:59:41 CET p@c[3189]STATEMENT: create table >>> check_unfitted_strata_col as select * from sampling.sample_gen_design >>> limit 0 >> >>> those sql commands could be executed in separate >>> postgres back-ends almost simultaneously (isolation level >>> is read committed), but I always thought wrapping the CREATE TABLE >>> command inside a transaction avoid any misbehavior (a part from the >>> fact that the second process will wait for the first to commit or >>> rollback) >> >> Well, it does avoid any "misbehavior", it's just not producing exactly >> the error message you expect. If you have two sessions trying to create >> the same table name at exactly the same time, this is a likely result. > > I'm not 100% sure but it's quite unlikely that they > try to create the table at exactly the same time. scratch that :/ $ zgrep -C1 'duplicate key value violates unique constraint "pg_type_typname_nsp_index"' postgresql-8.3.log.2 2010-03-08 12:59:41 CET p@c[7839]ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" 2010-03-08 12:59:41 CET p@c[7839]STATEMENT: create table check_unfitted_strata_col as select * from sampling.sample_gen_designlimit 0 2010-03-08 12:59:41 CET p@c[3189]ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" 2010-03-08 12:59:41 CET p@c[3189]STATEMENT: create table check_unfitted_strata_col as select * from sampling.sample_gen_designlimit 0 after a better parsing of the log I changed my mind it's *quite likely* they were trying to create the table at exactly the same time. sorry for the noise. thanks Andrea