Ye olde "relation doesn't quite exist" problem

I just gave an incorrect answer on pgsql-interfaces concerning the
following bug, which has been around for quite a while:

regression=> create table bug1 (f1 int28 primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'bug1_pkey' for table 'bug1'
ERROR:  Can't find a default operator class for type 22.

-- That's fine, since we have no index support for int28.  But:

regression=> create table bug1 (f1 int28);
ERROR:  Relation 'bug1' already exists

-- Oops.

regression=> drop table bug1;
ERROR:  Relation 'bug1' does not exist

-- Double oops.


I'm pretty sure I recall a discussion to the effect that CREATE TABLE
was failing in this case because pgsql/data/base/dbname/bug1 had already
been created and wasn't deleted at transaction abort.  That may have
been the case in older versions of Postgres, but we seem to have fixed
that problem: with current sources the database file *is* removed at
transaction abort.  Unfortunately the bug still persists :-(.

Some quick tracing indicates that the reason the second CREATE TABLE
fails is that there's still an entry for bug1 in the system cache: the
search in RelnameFindRelid(),       tuple = SearchSysCacheTuple(RELNAME,
PointerGetDatum(relname),                                  0, 0, 0);
 
is finding an entry!  (If you quit the backend and start a new one,
things go back to normal, since the new backend's relcache doesn't
have the bogus entry.)

So, apparently the real problem is that the relname cache is not cleaned
of bogus entries inserted during a failed transaction.  This strikes me
as a fairly serious problem, especially if it applies to all the
SysCache tables.  That could lead to all kinds of erroneous behavior
after an aborted transaction.  I think this is a "must fix" issue...
        regards, tom lane