Re: BUG #3692: Conflicting create table statements throw unexpected error

Bill Moran wrote:
> In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
> > "Bill Moran" <wmoran(at)collaborativefusion(dot)com> writes:
> > > Issuing a statement like:
> > > CREATE TABLE table2 AS SELECT * FROM table1;
> > > simultaneously in two separate sessions should result in an error like
> > > "ERROR: relation "table2" already exists" (in one or the other of the
> > > sessions, depending on the exact timing of things).
> >
> > This isn't really fixable, or at least the cure would be worse than the
> > disease. The "already exists" message is just a pre-check and it cannot
> > detect an uncommitted concurrent attempt to insert the same table name.
> > The place where the rubber really meets the road is during unique index
> > insertion. We might be able to fix things so that you get a unique
> > index complaint about pg_class.relname instead of pg_type, but that
> > would be about it.
>
> I figured it was something along those lines, otherwise it would have
> already been "fixed".
>
> I haven't had time to look at the code, so I'm speaking from a position
> of ignorance, but would it be terribly difficult to catch the unique
> constraint error, then re-run the pre-check to determine if it's
> occurring as a result of trying to create an existing table, and
> translate the error to a friendlier one before reporting to the client?

The problem we have with that is that unique index violations are not
separable from the elog(ERROR) they generate, so yes, it is terribly
difficult.

Maybe it would work to have a PG_TRY block around that code and compare
the error code with the one for unique index violation, in which case
the error is turned into "relation already exists".

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support