Обсуждение: BUG #10316: Alter table rename fails if the "new name" starts with '_'

The following bug has been logged on the website:

Bug reference:      10316
Logged by:          Parthi
Email address:      parthibanp2@gmail.com
PostgreSQL version: 9.3.2
Operating system:   Mac OSX 10.9.2
Description:

Hi I'm facing one peculiar behaviour(bug?) with Alter table rename query
SQL.

-- I have created one new table with name t1

test=# \d
No relations found.
test=# create table t1 (name varchar(10));
CREATE TABLE

-- After that i tried to rename 't1' table as '_t1', now the Postgres server
saying '_t1' is already exists. i'm sure there is no other table in the
database other than 't1'.

test=# alter table t1 rename to _t1;
ERROR:  type "_t1" already exists

-- Now i renaming table from 't1' to '__t1' and again i renaming from '__t1'
to '_t1' now its working.

test=# alter table t1 rename to __t1;
ALTER TABLE
test=# alter table __t1 rename to _t1;
ALTER TABLE

-- Again i renamed '_t1' to 't1', once again i tried the first query , now
its working.

test=# alter table _t1 rename to t1;
ALTER TABLE
test=# alter table t1 rename to _t1;
ALTER TABLE


May i know , why the rename query failed initially? if it is bug then how
the same query succeeded during second time?

parthibanp2@gmail.com writes:
> -- I have created one new table with name t1

> test=# \d
> No relations found.
> test=# create table t1 (name varchar(10));
> CREATE TABLE

> -- After that i tried to rename 't1' table as '_t1', now the Postgres server
> saying '_t1' is already exists. i'm sure there is no other table in the
> database other than 't1'.

> test=# alter table t1 rename to _t1;
> ERROR:  type "_t1" already exists

Note it's saying *type* _t1 already exists.  This is the internal name
of the type t1[], that is, arrays of the rowtype of t1.  In general,
our convention for the internal name of an array type is to prepend
a _ to the name of its element type.  (There's some logic to choose
a different name if this name is already taken, but that doesn't help
you when it's the array type that got there first.)

> -- Now i renaming table from 't1' to '__t1' and again i renaming from '__t1'
> to '_t1' now its working.

You could have renamed to anything else and then back to _t1; the trick is
just that the table's rowtype and array type have to get renamed out of
the way first.  By and large, though, it's probably best to avoid using
table names that begin with an underscore in PG, since you'll tend to
hit this kind of conflict with rowtype names if you do that.

            regards, tom lane