Thread: BUG #3692: Conflicting create table statements throw unexpected error
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.
"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. +