Re: ERROR: Cannot insert a duplicate key into unique index pg_class_relname_nsp_index

On Sat, Jan 10, 2004 at 11:20:11AM +1100, Martijn van Oosterhout wrote:
> Not really related to your problem, but given you're in a transaction, why
> do you need to lock anything? What's wrong with:
>
> > The daemon that gets this error does the following every 15 seconds:
> > - start a transaction
> > - delete the contents of the other table
> > - execute a complex and sometimes slow SELECT INTO query, creating a
> >   temporary table
> > - copy the contents of the temporary table into the other table
> > - drop the temporary table (again, embarrassing, sorry)
> > - commit
>
> Maybe I'm missing something?

We don't need to lock anything.  We just thought we did.  We'd observed
that accessing a table inside a transaction (at the default READ COMMITTED
isolation level) could show us records created by other transactions since
this transaction started (i.e. it doesn't guarantee repeatable reads),
even if we'd already accessed the table.

So, lacking a thorough understanding of section 12.2 (or transaction
isolation levels in general), we thought we might have to lock the table
to keep someone else from accessing it while it was partly empty.
We were wrong, but I didn't know that until this afternoon.

Thank you very much for your help!

-Kragen