Skip site navigation (1) Skip section navigation (2)

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

From: Bill Moran <wmoran(at)collaborativefusion(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3692: Conflicting create table statements throw unexpected error
Date: 2007-10-24 12:47:25
Message-ID: 20071024084725.3e933dcb.wmoran@collaborativefusion.com (view raw or flat)
Thread:
Lists: pgsql-bugs
In response to Alvaro Herrera <alvherre(at)commandprompt(dot)com>:

> 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".

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(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-10-24 13:27:14
Subject: Re: Possible planner bug/regression introduced in 8.2.5
Previous:From: Jakub OuhrabkaDate: 2007-10-24 08:57:18
Subject: Possible planner bug/regression introduced in 8.2.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group