github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/rename_table (about) 1 # LogicTest: !3node-tenant 2 statement error pgcode 42P01 relation "foo" does not exist 3 ALTER TABLE foo RENAME TO bar 4 5 statement ok 6 ALTER TABLE IF EXISTS foo RENAME TO bar 7 8 statement ok 9 CREATE TABLE kv ( 10 k INT PRIMARY KEY, 11 v INT 12 ) 13 14 statement ok 15 INSERT INTO kv VALUES (1, 2), (3, 4) 16 17 query II rowsort 18 SELECT * FROM kv 19 ---- 20 1 2 21 3 4 22 23 query TTT 24 SHOW TABLES 25 ---- 26 public kv table 27 28 statement ok 29 ALTER TABLE kv RENAME TO new_kv 30 31 statement error pgcode 42P01 relation "kv" does not exist 32 SELECT * FROM kv 33 34 query II rowsort 35 SELECT * FROM new_kv 36 ---- 37 1 2 38 3 4 39 40 query TTT 41 SHOW TABLES 42 ---- 43 public new_kv table 44 45 # check the name in the descriptor, which is used by SHOW GRANTS, is also changed 46 query TTTTT 47 SHOW GRANTS ON TABLE new_kv 48 ---- 49 test public new_kv admin ALL 50 test public new_kv root ALL 51 52 statement error invalid table name: "" 53 ALTER TABLE "" RENAME TO foo 54 55 statement error invalid table name: "" 56 ALTER TABLE new_kv RENAME TO "" 57 58 statement ok 59 ALTER TABLE new_kv RENAME TO new_kv 60 61 statement ok 62 CREATE TABLE t ( 63 c1 INT PRIMARY KEY, 64 c2 INT 65 ) 66 67 statement ok 68 INSERT INTO t VALUES (4, 16), (5, 25) 69 70 statement error pgcode 42P07 relation "new_kv" already exists 71 ALTER TABLE t RENAME TO new_kv 72 73 user testuser 74 75 statement error user testuser does not have DROP privilege on relation t 76 ALTER TABLE test.t RENAME TO t2 77 78 user root 79 80 statement ok 81 GRANT DROP ON TABLE test.t TO testuser 82 83 statement ok 84 create database test2 85 86 user testuser 87 88 statement error user testuser does not have CREATE privilege on database test 89 ALTER TABLE test.t RENAME TO t2 90 91 user root 92 93 statement ok 94 GRANT CREATE ON DATABASE test TO testuser 95 96 statement ok 97 ALTER TABLE test.t RENAME TO t2 98 99 query TTT 100 SHOW TABLES 101 ---- 102 public new_kv table 103 public t2 table 104 105 user testuser 106 107 statement error user testuser does not have CREATE privilege on database test2 108 ALTER TABLE test.t2 RENAME TO test2.t 109 110 user root 111 112 statement ok 113 GRANT CREATE ON DATABASE test2 TO testuser 114 115 statement ok 116 GRANT DROP ON test.new_kv TO testuser 117 118 user testuser 119 120 statement ok 121 ALTER TABLE test.new_kv RENAME TO test2.t 122 123 statement ok 124 ALTER TABLE test.t2 RENAME TO test2.t2 125 126 query TTT 127 SHOW TABLES 128 ---- 129 130 query TTT 131 SHOW TABLES FROM test2 132 ---- 133 public t table 134 public t2 table 135 136 user root 137 138 query II rowsort 139 SELECT * FROM test2.t 140 ---- 141 1 2 142 3 4 143 144 query II rowsort 145 SELECT * FROM test2.t2 146 ---- 147 4 16 148 5 25 149 150 statement ok 151 CREATE VIEW test2.v1 AS SELECT c1,c2 FROM test2.t2 152 153 statement ok 154 ALTER TABLE test2.v1 RENAME TO test2.v2 155 156 statement ok 157 ALTER TABLE test2.v2 RENAME TO test2.v1 158 159 statement error cannot rename relation "test2.public.t2" because view "v1" depends on it 160 ALTER TABLE test2.t2 RENAME TO test2.t3 161 162 # Tests that uncommitted database or table names can be used by statements 163 # in the same transaction. Also tests that if the transaction doesn't commit 164 # the names are discarded and cannot be used by future transactions. 165 166 statement ok 167 BEGIN 168 169 statement ok 170 CREATE DATABASE d; CREATE TABLE d.kv (k CHAR PRIMARY KEY, v CHAR); 171 172 statement ok 173 INSERT INTO d.kv (k,v) VALUES ('a', 'b') 174 175 statement ok 176 COMMIT 177 178 statement ok 179 INSERT INTO d.kv (k,v) VALUES ('c', 'd') 180 181 # A table rename disallows the use of the old name 182 statement ok 183 BEGIN 184 185 statement ok 186 ALTER TABLE d.kv RENAME TO d.kv2 187 188 statement ok 189 INSERT INTO d.kv2 (k,v) VALUES ('e', 'f') 190 191 statement error pgcode 42P01 relation \"d.kv\" does not exist 192 INSERT INTO d.kv (k,v) VALUES ('g', 'h') 193 194 statement ok 195 ROLLBACK 196 197 # A database rename disallows the use of the old name. 198 statement ok 199 BEGIN 200 201 statement ok 202 ALTER DATABASE d RENAME TO dnew 203 204 statement ok 205 INSERT INTO dnew.kv (k,v) VALUES ('e', 'f') 206 207 statement error pgcode 42P01 relation \"d.kv\" does not exist 208 INSERT INTO d.kv (k,v) VALUES ('g', 'h') 209 210 statement ok 211 ROLLBACK 212 213 # The reuse of a name is allowed. 214 statement ok 215 BEGIN 216 217 statement ok 218 ALTER DATABASE d RENAME TO dnew 219 220 statement ok 221 CREATE DATABASE d 222 223 statement ok 224 CREATE TABLE d.kv (k CHAR PRIMARY KEY, v CHAR) 225 226 statement ok 227 INSERT INTO d.kv (k,v) VALUES ('a', 'b') 228 229 statement ok 230 COMMIT 231 232 # Check that on a rollback a database name cannot be used. 233 statement ok 234 BEGIN 235 236 statement ok 237 CREATE DATABASE dd; CREATE TABLE dd.kv (k CHAR PRIMARY KEY, v CHAR) 238 239 statement ok 240 INSERT INTO dd.kv (k,v) VALUES ('a', 'b') 241 242 statement ok 243 ROLLBACK 244 245 statement error pgcode 42P01 relation "dd\.kv" does not exist 246 INSERT INTO dd.kv (k,v) VALUES ('c', 'd') 247 248 # Check that on a rollback a table name cannot be used. 249 statement ok 250 BEGIN 251 252 statement ok 253 CREATE TABLE d.kv2 (k CHAR PRIMARY KEY, v CHAR) 254 255 statement ok 256 INSERT INTO d.kv2 (k,v) VALUES ('a', 'b') 257 258 statement ok 259 ROLLBACK 260 261 statement error pgcode 42P01 relation \"d.kv2\" does not exist 262 INSERT INTO d.kv2 (k,v) VALUES ('c', 'd') 263 264 statement ok 265 USE d 266 267 query TTT 268 SHOW TABLES 269 ---- 270 public kv table 271 272 query TTT 273 EXPLAIN ALTER TABLE kv RENAME TO kv2 274 ---- 275 · distributed false 276 · vectorized false 277 rename table · · 278 279 # Verify that the EXPLAIN above does not actually rename the table (#30543) 280 query TTT 281 SHOW TABLES 282 ---- 283 public kv table