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

In response to Alvaro Herrera <alvherre@commandprompt.com>:

> Bill Moran wrote:
> > In response to Tom Lane <tgl@sss.pgh.pa.us>:
> >
> > > "Bill Moran" <wmoran@collaborativefusion.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".

That was my hope, but I'm hoping from a position of ignorance, as
I've yet to have a chance to look at the code, and doubt I'll get a
chance for at least a week.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023