github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/event_log (about) 1 # LogicTest: !3node-tenant 2 ################## 3 # TABLE DDL 4 ################## 5 6 # Create two tables + superfluous "IF NOT EXISTS" 7 ################## 8 9 statement ok 10 CREATE TABLE a (id INT PRIMARY KEY) 11 12 statement ok 13 CREATE TABLE IF NOT EXISTS b (id INT PRIMARY KEY) 14 15 statement ok 16 CREATE TABLE IF NOT EXISTS a (id INT PRIMARY KEY) 17 18 19 # Verify that two create tables were logged - the second 20 # NOT EXISTS should not result in a log message. 21 ################## 22 23 query IT rowsort 24 SELECT "reportingID", info::JSONB->>'TableName' 25 FROM system.eventlog 26 WHERE "eventType" = 'create_table' 27 ---- 28 1 test.public.a 29 1 test.public.b 30 31 # Verify the contents of the 'Info' field of each log message using a LIKE 32 # statement. 33 ################## 34 35 query IT 36 SELECT "reportingID", info::JSONB->>'TableName' 37 FROM system.eventlog 38 WHERE "eventType" = 'create_table' 39 AND info::JSONB->>'Statement' LIKE 'CREATE TABLE a%' 40 ---- 41 1 test.public.a 42 43 query IT 44 SELECT "reportingID", info::JSONB->>'TableName' 45 FROM system.eventlog 46 WHERE "eventType" = 'create_table' 47 AND info::JSONB->>'Statement' LIKE 'CREATE TABLE IF NOT EXISTS b%' 48 ---- 49 1 test.public.b 50 51 # Sanity check - check for a non-matching info value. 52 ################## 53 54 query I 55 SELECT count(*) 56 FROM system.eventlog 57 WHERE "eventType" = 'create_table' 58 AND info LIKE '%CREATE TABLE badtable%' 59 ---- 60 0 61 62 # Alter the table. Expect "alter_table" and "finish_schema_change" events. 63 ################## 64 65 query IT rowsort 66 SELECT "reportingID", info::JSONB->>'TableName' FROM system.eventlog 67 WHERE "eventType" = 'alter_table' 68 ---- 69 70 statement ok 71 ALTER TABLE a ADD val INT 72 73 query IT rowsort 74 SELECT "reportingID", info::JSONB->>'TableName' FROM system.eventlog 75 WHERE "eventType" = 'alter_table' 76 ---- 77 1 test.public.a 78 79 query IT rowsort 80 SELECT "reportingID", info::JSONB->>'MutationID' FROM system.eventlog 81 WHERE "eventType" = 'finish_schema_change' 82 ---- 83 1 1 84 85 query I 86 SELECT "reportingID" FROM system.eventlog 87 WHERE "eventType" = 'reverse_schema_change' 88 ---- 89 90 # Verify the contents of the 'Info' field of each log message using a LIKE 91 # statement. 92 ################## 93 query IT 94 SELECT "reportingID", info::JSONB->>'TableName' FROM system.eventlog 95 WHERE "eventType" = 'alter_table' 96 AND info::JSONB->>'Statement' LIKE 'ALTER TABLE a%' 97 ---- 98 1 test.public.a 99 100 # Add a UNIQUE constraint to the table in a way that will ensure the schema 101 # change is reversed. 102 ################## 103 104 statement ok 105 INSERT INTO a VALUES (1, 1), (2, 1) 106 107 statement error pgcode 23505 violates unique constraint \"foo\" 108 ALTER TABLE a ADD CONSTRAINT foo UNIQUE(val) 109 110 query IT rowsort 111 SELECT "reportingID", info::JSONB->>'TableName' FROM system.eventlog 112 WHERE "eventType" = 'alter_table' 113 ---- 114 1 test.public.a 115 1 test.public.a 116 117 query IT rowsort 118 SELECT "reportingID", info::JSONB->>'MutationID' FROM system.eventlog 119 WHERE "eventType" = 'finish_schema_change' 120 ---- 121 1 1 122 123 query IT rowsort 124 SELECT "reportingID", info::JSONB->>'MutationID' FROM system.eventlog 125 WHERE "eventType" = 'reverse_schema_change' 126 ---- 127 1 2 128 129 130 query IT rowsort 131 SELECT "reportingID", info::JSONB->>'MutationID' FROM system.eventlog 132 WHERE "eventType" = 'finish_schema_change_rollback' 133 ---- 134 1 2 135 136 # Create an Index on the table 137 ################# 138 139 statement ok 140 CREATE INDEX a_foo ON a (val) 141 142 query ITT 143 SELECT "reportingID", info::JSONB->>'TableName', info::JSONB->>'IndexName' FROM system.eventlog 144 WHERE "eventType" = 'create_index' 145 AND info::JSONB->>'Statement' LIKE 'CREATE INDEX a_foo%' 146 ---- 147 1 test.public.a a_foo 148 149 query IT rowsort 150 SELECT "reportingID", info::JSONB->>'MutationID' FROM system.eventlog 151 WHERE "eventType" = 'finish_schema_change' 152 ---- 153 1 1 154 1 3 155 156 statement ok 157 CREATE INDEX ON a (val) 158 159 query ITT 160 SELECT "reportingID", info::JSONB->>'TableName', info::JSONB->>'IndexName' FROM system.eventlog 161 WHERE "eventType" = 'create_index' 162 AND info::JSONB->>'Statement' LIKE 'CREATE INDEX ON%' 163 ---- 164 1 test.public.a a_val_idx 165 166 query IT rowsort 167 SELECT "reportingID", info::JSONB->>'MutationID' FROM system.eventlog 168 WHERE "eventType" = 'finish_schema_change' 169 ---- 170 1 1 171 1 3 172 1 4 173 174 175 # Drop the index 176 ################# 177 178 statement ok 179 DROP INDEX a@a_foo 180 181 query ITT 182 SELECT "reportingID", info::JSONB->>'TableName', info::JSONB->>'IndexName' FROM system.eventlog 183 WHERE "eventType" = 'drop_index' 184 AND info::JSONB->>'Statement' LIKE 'DROP INDEX%a_foo' 185 ---- 186 1 test.public.a a_foo 187 188 query IT rowsort 189 SELECT "reportingID", info::JSONB->>'MutationID' FROM system.eventlog 190 WHERE "eventType" = 'finish_schema_change' 191 ---- 192 1 1 193 1 3 194 1 4 195 1 5 196 197 # Truncate a table 198 ################## 199 200 statement ok 201 TRUNCATE TABLE a 202 203 query IT rowsort 204 SELECT "reportingID", info::JSONB->>'TableName' 205 FROM system.eventlog 206 WHERE "eventType" = 'truncate_table' 207 ---- 208 1 test.public.a 209 210 # Drop both tables + superfluous "IF EXISTS" 211 ################## 212 213 statement ok 214 DROP TABLE a 215 216 statement ok 217 DROP TABLE IF EXISTS b 218 219 statement ok 220 DROP TABLE IF EXISTS b 221 222 223 # Verify that two drop table events were logged - the second IF EXISTS statement 224 # should have failed. 225 ################## 226 227 query IT rowsort 228 SELECT "reportingID", info::JSONB->>'TableName' 229 FROM system.eventlog 230 WHERE "eventType" = 'drop_table' 231 ---- 232 1 test.public.a 233 1 test.public.b 234 235 # Verify the contents of the 'info' field of each event. 236 ################## 237 238 query IT 239 SELECT "reportingID", info::JSONB->>'TableName' 240 FROM system.eventlog 241 WHERE "eventType" = 'drop_table' 242 AND info::JSONB->>'Statement' LIKE 'DROP TABLE a%' 243 ---- 244 1 test.public.a 245 246 query IT 247 SELECT "reportingID", info::JSONB->>'TableName' 248 FROM system.eventlog 249 WHERE "eventType" = 'drop_table' 250 AND info::JSONB->>'Statement' LIKE 'DROP TABLE IF EXISTS b%' 251 ---- 252 1 test.public.b 253 254 255 ################## 256 # DATABASE DDL 257 ################## 258 259 # Create two databases + superfluous "IF NOT EXISTS" 260 ################## 261 262 statement ok 263 CREATE DATABASE eventlogtest 264 265 statement ok 266 CREATE DATABASE IF NOT EXISTS othereventlogtest 267 268 statement ok 269 CREATE DATABASE IF NOT EXISTS othereventlogtest 270 271 272 # Verify the two events that were logged. 273 ################## 274 275 query IT 276 SELECT "reportingID", info::JSONB->>'DatabaseName' 277 FROM system.eventlog 278 WHERE "eventType" = 'create_database' 279 AND info::JSONB->>'Statement' LIKE 'CREATE DATABASE eventlogtest%' 280 ---- 281 1 eventlogtest 282 283 query IT 284 SELECT "reportingID", info::JSONB->>'DatabaseName' 285 FROM system.eventlog 286 WHERE "eventType" = 'create_database' 287 AND info::JSONB->>'Statement' LIKE 'CREATE DATABASE IF NOT EXISTS othereventlogtest%' 288 ---- 289 1 othereventlogtest 290 291 # Add some tables to eventlogtest. 292 ################## 293 294 statement ok 295 SET DATABASE = eventlogtest 296 297 statement ok 298 CREATE TABLE eventlogtest.testtable (id int PRIMARY KEY) 299 300 statement ok 301 CREATE TABLE eventlogtest.anothertesttable (id int PRIMARY KEY) 302 303 # drop both databases. 304 ################## 305 306 statement ok 307 DROP DATABASE eventlogtest CASCADE 308 309 statement ok 310 DROP DATABASE IF EXISTS othereventlogtest CASCADE 311 312 statement ok 313 DROP DATABASE IF EXISTS othereventlogtest CASCADE 314 315 # verify contents of drop event 316 ################## 317 318 # verify event is there, and cascading table drops are logged. 319 320 query IT 321 SELECT "reportingID", info::JSONB->>'DroppedSchemaObjects' 322 FROM system.eventlog 323 WHERE "eventType" = 'drop_database' 324 AND info::JSONB->>'Statement' LIKE 'DROP DATABASE eventlogtest%' 325 ---- 326 1 ["eventlogtest.public.anothertesttable", "eventlogtest.public.testtable"] 327 328 query IT 329 SELECT "reportingID", info::JSONB->>'DroppedSchemaObjects' 330 FROM system.eventlog 331 WHERE "eventType" = 'drop_database' 332 AND info::JSONB->>'Statement' LIKE 'DROP DATABASE IF EXISTS othereventlogtest%' 333 ---- 334 1 [] 335 336 statement ok 337 SET DATABASE = test 338 339 ################## 340 # Cluster Settings 341 ################## 342 343 # Set and unset a cluster setting 344 ################## 345 346 statement ok 347 SET CLUSTER SETTING kv.allocator.load_based_lease_rebalancing.enabled = false 348 349 statement ok 350 SET CLUSTER SETTING kv.allocator.load_based_lease_rebalancing.enabled = DEFAULT 351 352 statement ok 353 PREPARE set_setting AS SET CLUSTER SETTING cluster.organization = $1 354 355 statement ok 356 EXECUTE set_setting('some string') 357 358 # verify setting changes are logged 359 ################## 360 query IIT 361 SELECT "targetID", "reportingID", "info" 362 FROM system.eventlog 363 WHERE "eventType" = 'set_cluster_setting' 364 AND info NOT LIKE '%version%' AND info NOT LIKE '%sql.defaults.distsql%' AND info NOT LIKE '%cluster.secret%' 365 AND info NOT LIKE '%sql.stats.automatic_collection.enabled%' 366 AND info NOT LIKE '%sql.defaults.vectorize%' 367 AND info NOT LIKE '%sql.testing.vectorize.batch_size%' 368 ORDER BY "timestamp" 369 ---- 370 0 1 {"SettingName":"diagnostics.reporting.enabled","Value":"true","User":"root"} 371 0 1 {"SettingName":"kv.range_merge.queue_enabled","Value":"false","User":"root"} 372 0 1 {"SettingName":"sql.stats.automatic_collection.min_stale_rows","Value":"5","User":"root"} 373 0 1 {"SettingName":"kv.allocator.load_based_lease_rebalancing.enabled","Value":"false","User":"root"} 374 0 1 {"SettingName":"kv.allocator.load_based_lease_rebalancing.enabled","Value":"DEFAULT","User":"root"} 375 0 1 {"SettingName":"cluster.organization","Value":"'some string'","User":"root"} 376 377 # Set and unset zone configs 378 ################## 379 380 statement ok 381 CREATE TABLE a (id INT PRIMARY KEY) 382 383 statement ok 384 ALTER TABLE a CONFIGURE ZONE USING range_max_bytes = 67108865, range_min_bytes = 16777216 385 386 statement ok 387 ALTER TABLE a CONFIGURE ZONE DISCARD 388 389 # verify zone config changes are logged 390 ################## 391 query IT 392 SELECT "reportingID", "info" 393 FROM system.eventlog 394 WHERE "eventType" = 'set_zone_config' 395 ORDER BY "timestamp" 396 ---- 397 1 {"Target":"TABLE test.public.a","Options":"range_max_bytes = 67108865, range_min_bytes = 16777216","User":"root"} 398 399 query IT 400 SELECT "reportingID", "info" 401 FROM system.eventlog 402 WHERE "eventType" = 'remove_zone_config' 403 ORDER BY "timestamp" 404 ---- 405 1 {"Target":"TABLE test.public.a","User":"root"} 406 407 statement ok 408 DROP TABLE a 409 410 # Sequences 411 412 statement ok 413 CREATE SEQUENCE s 414 415 statement ok 416 ALTER SEQUENCE s START 10 417 418 statement ok 419 DROP SEQUENCE s 420 421 query TIT rowsort 422 SELECT "eventType", "reportingID", info::JSONB->>'SequenceName' 423 FROM system.eventlog 424 WHERE "eventType" in ('create_sequence', 'alter_sequence', 'drop_sequence') 425 ---- 426 create_sequence 1 test.public.s 427 alter_sequence 1 test.public.s 428 drop_sequence 1 test.public.s 429 430 # Views 431 432 statement ok 433 CREATE VIEW v AS SELECT 1 434 435 statement ok 436 DROP VIEW v 437 438 query TIT rowsort 439 SELECT "eventType", "reportingID", info::JSONB->>'ViewName' 440 FROM system.eventlog 441 WHERE "eventType" in ('create_view', 'drop_view') 442 ---- 443 create_view 1 test.public.v 444 drop_view 1 test.public.v