Re: duplicate key violates unique contraint on pg_type_typname_nsp_index

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