github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/rename_database (about) 1 # LogicTest: !3node-tenant 2 query T 3 SHOW DATABASES 4 ---- 5 defaultdb 6 postgres 7 system 8 test 9 10 query TTTT 11 SHOW GRANTS ON DATABASE test 12 ---- 13 test crdb_internal admin ALL 14 test crdb_internal root ALL 15 test information_schema admin ALL 16 test information_schema root ALL 17 test pg_catalog admin ALL 18 test pg_catalog root ALL 19 test pg_extension admin ALL 20 test pg_extension root ALL 21 test public admin ALL 22 test public root ALL 23 24 statement ok 25 CREATE TABLE kv ( 26 k INT PRIMARY KEY, 27 v INT 28 ) 29 30 statement ok 31 INSERT INTO kv VALUES (1, 2), (3, 4), (5, 6), (7, 8) 32 33 query II rowsort 34 SELECT * FROM kv 35 ---- 36 1 2 37 3 4 38 5 6 39 7 8 40 41 statement ok 42 SET sql_safe_updates = TRUE; 43 44 statement error RENAME DATABASE on current database 45 ALTER DATABASE test RENAME TO u 46 47 statement ok 48 SET sql_safe_updates = FALSE; 49 ALTER DATABASE test RENAME TO u 50 51 statement error pgcode 42P01 relation "kv" does not exist 52 SELECT * FROM kv 53 54 statement error target database or schema does not exist 55 SHOW GRANTS ON DATABASE test 56 57 query T 58 SHOW DATABASES 59 ---- 60 defaultdb 61 postgres 62 system 63 u 64 65 # check the name in descriptor is also changed 66 query TTTT 67 SHOW GRANTS ON DATABASE u 68 ---- 69 u crdb_internal admin ALL 70 u crdb_internal root ALL 71 u information_schema admin ALL 72 u information_schema root ALL 73 u pg_catalog admin ALL 74 u pg_catalog root ALL 75 u pg_extension admin ALL 76 u pg_extension root ALL 77 u public admin ALL 78 u public root ALL 79 80 statement ok 81 SET DATABASE = u 82 83 query II rowsort 84 SELECT * FROM kv 85 ---- 86 1 2 87 3 4 88 5 6 89 7 8 90 91 statement error pgcode 42601 empty database name 92 ALTER DATABASE "" RENAME TO u 93 94 statement error pgcode 42601 empty database name 95 ALTER DATABASE u RENAME TO "" 96 97 statement ok 98 ALTER DATABASE u RENAME TO u 99 100 statement ok 101 CREATE DATABASE t 102 103 statement error the new database name "u" already exists 104 ALTER DATABASE t RENAME TO u 105 106 statement ok 107 GRANT ALL ON DATABASE t TO testuser 108 109 user testuser 110 111 statement error only users with the admin role are allowed to ALTER DATABASE ... RENAME 112 ALTER DATABASE t RENAME TO v 113 114 query T 115 SHOW DATABASES 116 ---- 117 t 118 119 user root 120 121 # Test that renames aren't allowed while views refer to any of a DB's tables, 122 # both for views in that database and for views in a different database. 123 124 statement ok 125 CREATE VIEW t.v AS SELECT k,v FROM u.kv 126 127 query TTT 128 SHOW TABLES FROM u 129 ---- 130 public kv table 131 132 statement error cannot rename database because relation "t.public.v" depends on relation "u.public.kv" 133 ALTER DATABASE u RENAME TO v 134 135 statement ok 136 DROP VIEW t.v 137 138 statement ok 139 ALTER DATABASE u RENAME TO v 140 141 statement ok 142 CREATE VIEW v.v AS SELECT k,v FROM v.kv 143 144 statement error cannot rename database because relation "v.public.v" depends on relation "v.public.kv"\s.*you can drop "v.public.v" instead 145 ALTER DATABASE v RENAME TO u 146 147 # Check that the default databases can be renamed like any other. 148 statement ok 149 ALTER DATABASE defaultdb RENAME TO w; 150 ALTER DATABASE postgres RENAME TO defaultdb; 151 ALTER DATABASE w RENAME TO postgres 152 153 query T 154 SHOW DATABASES 155 ---- 156 defaultdb 157 postgres 158 system 159 t 160 v 161 162 query TTT 163 EXPLAIN ALTER DATABASE v RENAME TO x 164 ---- 165 · distributed false 166 · vectorized false 167 rename database · · 168 169 # Verify that the EXPLAIN above does not actually rename the database (#30543) 170 query T colnames 171 SHOW DATABASES 172 ---- 173 database_name 174 defaultdb 175 postgres 176 system 177 t 178 v 179 180 # Test dependent sequences on different databases upon renames 181 # return the appropriate error message, as well as testing 182 # renaming databases with sequences in the same DB is successful. 183 subtest regression_45411 184 185 statement ok 186 CREATE DATABASE db1; CREATE SEQUENCE db1.seq 187 188 statement ok 189 CREATE DATABASE db2; CREATE TABLE db2.tbl (a int DEFAULT nextval('db1.seq')) 190 191 statement error cannot rename database because relation "db2.public.tbl" depends on relation "db1.public.seq"\s.*you can drop the column default "a" of "db1.public.seq" referencing "db2.public.tbl" 192 ALTER DATABASE db1 RENAME TO db3 193 194 statement ok 195 DROP DATABASE db2 CASCADE; DROP DATABASE db1 CASCADE 196 197 statement ok 198 CREATE DATABASE db1; CREATE SEQUENCE db1.a_seq; CREATE SEQUENCE db1.b_seq; USE db1; 199 200 statement ok 201 CREATE TABLE db1.a (a int default nextval('a_seq') + nextval('b_seq') + 1); ALTER DATABASE db1 RENAME TO db2; USE db2; 202 203 statement error cannot rename database because relation "db2.public.a" depends on relation "db2.public.a_seq"\s.*you can drop the column default "a" of "db2.public.a_seq" referencing "db2.public.a" or modify the default to not reference the database name "db2" 204 DROP TABLE db2.a; CREATE TABLE db2.a (a int default nextval('a_seq') + nextval('db2.b_seq') + 1); ALTER DATABASE db2 RENAME TO db1 205 206 statement error cannot rename database because relation "db2.public.a" depends on relation "db2.public.a_seq"\s.*you can drop the column default "a" of "db2.public.a_seq" referencing "db2.public.a" or modify the default to not reference the database name "db2" 207 DROP TABLE db2.a; CREATE TABLE db2.a (a int default nextval('a_seq') + nextval('db2.public.b_seq') + 1); ALTER DATABASE db2 RENAME TO db1 208 209 statement ok 210 DROP TABLE db2.a; CREATE TABLE db2.a (a int default nextval('a_seq') + nextval('public.b_seq') + 1); ALTER DATABASE db2 RENAME TO db1 211 212 statement ok 213 USE defaultdb; DROP DATABASE db1 CASCADE