From: Vik Fearing <vik.fearing@2ndquadrant.com>
Organization: 2ndQuadrant France
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Renaming a table to an array's autogenerated name
Message-ID: <0f4ade49-4f0b-a9a3-c120-7589f01d1eb8@2ndquadrant.com>
Date: Thu, 25 May 2017 14:19:37 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.1.1
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------AF65BDD48FD5D4CA76A3E155"
Content-Language: en-US
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------AF65BDD48FD5D4CA76A3E155
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

In commit 9aa3c782c93, Tom fixed a bug in which creating a table _foo
when an array type of that name already existed would make the array
type change its name to get out of the way. But it missed a trick in
that renaming a table would still cause a conflict.

Steps to reproduce:

postgres=# create table foo (id int);
CREATE TABLE
postgres=# create table bar (id int);
CREATE TABLE
postgres=# alter table bar rename to _foo;
ERROR:  type "_foo" already exists

Attached is a patch that fixes this bug.

One interesting thing to note however, is that if you rename a table to
its own array's name (which was my case when I found this bug), the new
array name will be ___foo instead of just __foo.  I don't know if it's
worth worrying about that.
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

--------------AF65BDD48FD5D4CA76A3E155
Content-Type: text/x-patch;
 name="rename-_foo-v01.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="rename-_foo-v01.patch"

diff --git a/src/backend/catalog/pg_type.c b/src/backend/catalog/pg_type.c
index 04c10c6347..9e4e46f9f5 100644
--- a/src/backend/catalog/pg_type.c
+++ b/src/backend/catalog/pg_type.c
@@ -695,6 +695,7 @@ RenameTypeInternal(Oid typeOid, const char *newTypeName, Oid typeNamespace)
 	HeapTuple	tuple;
 	Form_pg_type typ;
 	Oid			arrayOid;
+	Oid			typoid;
 
 	pg_type_desc = heap_open(TypeRelationId, RowExclusiveLock);
 
@@ -708,10 +709,22 @@ RenameTypeInternal(Oid typeOid, const char *newTypeName, Oid typeNamespace)
 
 	arrayOid = typ->typarray;
 
+	typoid = GetSysCacheOid2(TYPENAMENSP,
+							 CStringGetDatum(newTypeName),
+							 ObjectIdGetDatum(typeNamespace));
+
+	/*
+	 * See if it's an autogenerated array type, and if so
+	 * rename it out of the way.
+	 */
+	if (OidIsValid(typoid) && get_typisdefined(typoid))
+	{
+		if (moveArrayTypeName(typoid, newTypeName, typeNamespace))
+			typoid = InvalidOid;
+	}
+
 	/* Just to give a more friendly error than unique-index violation */
-	if (SearchSysCacheExists2(TYPENAMENSP,
-							  CStringGetDatum(newTypeName),
-							  ObjectIdGetDatum(typeNamespace)))
+	if (OidIsValid(typoid))
 		ereport(ERROR,
 				(errcode(ERRCODE_DUPLICATE_OBJECT),
 				 errmsg("type \"%s\" already exists", newTypeName)));
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index c88fd76848..142a782bff 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -128,6 +128,15 @@ SELECT * FROM tmp_new2;
 
 DROP TABLE tmp_new;
 DROP TABLE tmp_new2;
+-- renaming to an array's autogenerated name (the array's name should get out of the way)
+CREATE TABLE tmp_array (id int);
+CREATE TABLE tmp_array2 (id int);
+ALTER TABLE tmp_array2 RENAME TO _tmp_array;
+DROP TABLE _tmp_array;
+DROP TABLE tmp_array;
+CREATE TABLE tmp_array (id int);
+ALTER TABLE tmp_array RENAME TO _tmp_array;
+DROP TABLE _tmp_array;
 -- ALTER TABLE ... RENAME on non-table relations
 -- renaming indexes (FIXME: this should probably test the index's functionality)
 ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index c0e29720dc..1a893e9e2c 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -165,6 +165,16 @@ SELECT * FROM tmp_new2;
 DROP TABLE tmp_new;
 DROP TABLE tmp_new2;
 
+-- renaming to an array's autogenerated name (the array's name should get out of the way)
+CREATE TABLE tmp_array (id int);
+CREATE TABLE tmp_array2 (id int);
+ALTER TABLE tmp_array2 RENAME TO _tmp_array;
+DROP TABLE _tmp_array;
+DROP TABLE tmp_array;
+
+CREATE TABLE tmp_array (id int);
+ALTER TABLE tmp_array RENAME TO _tmp_array;
+DROP TABLE _tmp_array;
 
 -- ALTER TABLE ... RENAME on non-table relations
 -- renaming indexes (FIXME: this should probably test the index's functionality)

--------------AF65BDD48FD5D4CA76A3E155
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--------------AF65BDD48FD5D4CA76A3E155--