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.