duplicate key violates unique contraint on pg_type_typname_nsp_index

From: Andrea Suisani <sickpig(at)opinioni(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: duplicate key violates unique contraint on pg_type_typname_nsp_index
Date: 2010-03-09 12:28:05
Message-ID: 4B963ED5.2050305@opinioni.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(at)c[3189]ERROR: table "check_unfitted_strata_col" does not exist
2010-03-08 12:58:53 CET p(at)c[3189]STATEMENT: drop table check_unfitted_strata_col
2010-03-08 12:58:53 CET p(at)c[3189]LOG: duration: 0.018 ms statement: Begin
2010-03-08 12:59:41 CET p(at)c[3189]ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
2010-03-08 12:59:41 CET p(at)c[3189]STATEMENT: create table check_unfitted_strata_col as select * from sampling.sample_gen_design limit 0
2010-03-08 12:59:41 CET p(at)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert Haas 2010-03-09 14:32:19 Re: Bug in triggers
Previous Message Boguk Maxim 2010-03-09 10:18:09 BUG #5367: TEMP TABLES with ON COMMIT DELETE ROWS and different pg_stat features