BUG #3692: Conflicting create table statements throw unexpected error

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

regards, tom lane

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?

That doesn't seem unreasonable to me, but (as I already admitted) I
haven't looked at the code yet ...

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

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

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

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

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

Added to TODO:

o Prevent concurrent CREATE TABLE table1 from sometimes returning
a cryptic error message

http://archives.postgresql.org/pgsql-bugs/2007-10/msg00169.php

---------------------------------------------------------------------------

Bill Moran wrote:

The following bug has been logged online:

Bug reference: 3692
Logged by: Bill Moran
Email address: wmoran@collaborativefusion.com
PostgreSQL version: 8.2.5
Operating system: FreeBSD
Description: Conflicting create table statements throw unexpected
error
Details:

(also occurs on 8.1.10)

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

However, if table1 has enough rows that the command takes a while to execute
(a few seconds seems to be all it takes) the error is far more cryptic:
ERROR: duplicate key violates unique constraint "pg_type_typname_nsp_index"

It seems to me that there's some sort of race condition that if the second
command starts before the first has completed, the backend doesn't really
understand what went wrong.

For a front end, this is tough to parse. A "relation exists" error on a
table should probably be 42P07, but the duplicate key violation results in
23505, which means a front end will likely behave incorrectly.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +