Materialize/test/sqllogictest/transactions.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

# Start from a pristine server
reset-server

statement ok
CREATE TABLE t (a int)

statement ok
INSERT INTO t (a) VALUES (1)

#### next transaction

statement ok
BEGIN

query I rowsort
SELECT * FROM t
----
1

statement ok
COMMIT

#### next transaction

statement ok
BEGIN

query I rowsort
SELECT * FROM t
----
1

statement ok
ROLLBACK

#### next transaction

statement ok
START TRANSACTION

query I rowsort
SELECT * FROM t
----
1

statement ok
COMMIT

#### next transaction

statement ok
START TRANSACTION

query I rowsort
SELECT * FROM t
----
1

statement ok
ROLLBACK

# Multiple INSERTs.
simple
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);
----
COMPLETE 1
COMPLETE 1

# INSERT in explicit transactions.
statement ok
BEGIN

simple
INSERT INTO t VALUES (4);
----
COMPLETE 1

# Verify ROLLBACK works by not expecting 4 below.
statement ok
ROLLBACK

# INSERT rolled up from implicit txn into explicit not ok because mixed
# with a read.
simple
INSERT INTO t VALUES (5);
BEGIN;
SELECT * FROM t;
----
db error: ERROR: transaction in write-only mode

# This COMMIT should be ignored due to the failure above.
statement ok
COMMIT

# INSERT allowed in explicit transactions.
simple
BEGIN; INSERT INTO t VALUES (6);
----
COMPLETE 0
COMPLETE 1

# Verify that the to-be-inserted data is not readable by another connection.
simple conn=read
SELECT * FROM t WHERE a=6
----
COMPLETE 0

statement ok
COMMIT

simple
INSERT INTO t VALUES (7), (8)
----
COMPLETE 2

# Verify contents of table at the end.
query I
SELECT * FROM t ORDER BY a
----
1
2
3
6
7
8

# The only thing we support multiple of in an implicit transaction
# (multiple statements in the same query string) is row-returning
# statements.
simple
CREATE TABLE u (i INT); SELECT 1;
----
db error: ERROR: CREATE TABLE u (i int4) cannot be run inside a transaction block

# Multiple reads in the same query string are ok.
simple
SELECT 1; SELECT 2
----
1
COMPLETE 1
2
COMPLETE 1

# Verify that `SHOW` queries work in transactions.

simple
BEGIN
----
COMPLETE 0

query TT rowsort
SHOW TABLES
----
t (empty)

simple
COMMIT
----
COMPLETE 0

# Regression for database-issues#1768
statement ok
CREATE TABLE t5727 (i INT)

simple
BEGIN;
INSERT INTO t VALUES (1);
----
COMPLETE 0
COMPLETE 1

simple conn=drop
DROP TABLE t
----
COMPLETE 0

simple
COMMIT
----
db error: ERROR: unknown catalog item 'u1'

# Verify SUBSCRIBE must be only read statement in a transaction.
# We use FETCH 0 with SUBSCRIBE below so that we don't need to worry about
# timestamps in this slt file.

statement ok
CREATE TABLE t (a int)

statement ok
INSERT INTO t VALUES (1)

simple
DECLARE c CURSOR FOR SUBSCRIBE t;
FETCH 0 c;
DECLARE d CURSOR FOR SUBSCRIBE t;
FETCH 0 d;
----
db error: ERROR: SUBSCRIBE in transactions must be the only read statement

simple
DECLARE c CURSOR FOR SUBSCRIBE t;
FETCH 0 c;
SELECT * FROM t LIMIT 0;
----
db error: ERROR: SUBSCRIBE in transactions must be the only read statement

simple
SELECT * FROM t LIMIT 0;
DECLARE c CURSOR FOR SUBSCRIBE t;
FETCH 0 c;
----
db error: ERROR: transaction in read-only mode
DETAIL: SELECT queries cannot be combined with other query types, including SUBSCRIBE.

# Using an AS OF in the SELECT or SUBSCRIBE allows lifting that restriction.

simple
DECLARE c CURSOR FOR SUBSCRIBE t AS OF 18446744073709551615;
FETCH 0 c;
DECLARE d CURSOR FOR SUBSCRIBE t;
FETCH 0 d;
----
COMPLETE 0
COMPLETE 0
COMPLETE 0
COMPLETE 0

simple
DECLARE c CURSOR FOR SUBSCRIBE t AS OF 18446744073709551615;
FETCH 0 c;
SELECT * FROM t LIMIT 0;
----
COMPLETE 0
COMPLETE 0
COMPLETE 0

simple
SELECT * FROM t LIMIT 0;
DECLARE c CURSOR FOR SUBSCRIBE t AS OF 18446744073709551615;
FETCH 0 c;
----
COMPLETE 0
COMPLETE 0
COMPLETE 0

simple
DECLARE c CURSOR FOR SUBSCRIBE t;
FETCH 0 c;
SELECT * FROM t LIMIT 0 AS OF AT LEAST 0;
----
COMPLETE 0
COMPLETE 0
COMPLETE 0

simple
SELECT * FROM t LIMIT 0 AS OF AT LEAST 0;
DECLARE c CURSOR FOR SUBSCRIBE t;
FETCH 0 c;
----
COMPLETE 0
COMPLETE 0
COMPLETE 0

simple
SELECT 1;
SELECT * FROM t;
----
db error: ERROR: querying the following items "materialize.public.t" is not allowed from the "mz_catalog_server" cluster
DETAIL: The transaction is executing on the mz_catalog_server cluster, maybe having been routed there by the first statement in the transaction.

statement ok
CREATE SCHEMA other

statement ok
CREATE TABLE other.t (i INT)

simple
SELECT * FROM t;
SELECT * FROM other.t;
----
db error: ERROR: Transactions can only reference objects in the same timedomain. See https://materialize.com/docs/sql/begin/#same-timedomain-error
DETAIL: The following relations in the query are outside the transaction's time domain:
"materialize.other.t"
Only the following relations are available:
"materialize.public.t"
"materialize.public.t5727"
"mz_catalog.mz_array_types"
"mz_catalog.mz_base_types"
"mz_catalog.mz_columns"
"mz_catalog.mz_connections"
"mz_catalog.mz_databases"
"mz_catalog.mz_functions"
"mz_catalog.mz_index_columns"
"mz_catalog.mz_indexes"
"mz_catalog.mz_list_types"
"mz_catalog.mz_map_types"
"mz_catalog.mz_materialized_views"
"mz_catalog.mz_operators"
"mz_catalog.mz_pseudo_types"
"mz_catalog.mz_role_members"
"mz_catalog.mz_role_parameters"
"mz_catalog.mz_roles"
"mz_catalog.mz_schemas"
"mz_catalog.mz_secrets"
"mz_catalog.mz_sinks"
"mz_catalog.mz_sources"
"mz_catalog.mz_system_privileges"
"mz_catalog.mz_tables"
"mz_catalog.mz_types"
"mz_catalog.mz_views"
"mz_internal.mz_aggregates"
"mz_internal.mz_comments"
"mz_internal.mz_continual_tasks"
"mz_internal.mz_object_dependencies"
"mz_internal.mz_type_pg_metadata"

# Verify that changed tables and views don't change during a transaction.

statement ok
CREATE MATERIALIZED VIEW v AS SELECT COUNT(*) FROM T

simple conn=read
BEGIN;
SELECT * FROM t;
SELECT * FROM v;
----
COMPLETE 0
1
COMPLETE 1
1
COMPLETE 1

simple conn=write
INSERT INTO t VALUES (3)
----
COMPLETE 1

simple conn=write
SELECT * FROM t;
SELECT * FROM v;
----
1
3
COMPLETE 2
2
COMPLETE 1

simple conn=read
SELECT * FROM t;
SELECT * FROM v;
COMMIT;
----
1
COMPLETE 1
1
COMPLETE 1
COMPLETE 0

# Test replacing a non-materialized view in a different transaction.

statement ok
CREATE VIEW v1 AS SELECT 1

simple conn=t1
BEGIN;
SELECT * FROM v1;
----
COMPLETE 0
1
COMPLETE 1

simple conn=t2
CREATE OR REPLACE VIEW v1 AS SELECT 2;
----
COMPLETE 0

simple conn=t2
SELECT * FROM v1;
----
2
COMPLETE 1

# Our catalog doesn't respect SQL transactions, so we see the new v1.
# Unmaterialized views with no dependencies exist outside of any particular
# timedomain.
simple conn=t1
SELECT * FROM v1;
COMMIT;
----
2
COMPLETE 1
COMPLETE 0

simple conn=t1
ROLLBACK;
----
COMPLETE 0

# Verify an error is produce during write transactions if the commit fails.

statement ok
CREATE TABLE insert_fail (i int)

statement ok
INSERT INTO insert_fail VALUES (1)

simple conn=t1
BEGIN;
INSERT into insert_fail VALUES (2);
----
COMPLETE 0
COMPLETE 1

simple conn=t2
DROP table insert_fail;
----
COMPLETE 0

simple conn=t1
COMMIT;
----
db error: ERROR: unknown catalog item 'u8'

# Test transaction syntax that we don't support.

statement ok
BEGIN ISOLATION LEVEL SERIALIZABLE

statement error CHAIN not yet supported
COMMIT AND CHAIN

statement error CHAIN not yet supported
ROLLBACK AND CHAIN

statement ok
ROLLBACK

# This is a noop, but is supported syntax.
statement ok
BEGIN ISOLATION LEVEL REPEATABLE READ

statement ok
COMMIT

# Access modes.

statement ok
BEGIN TRANSACTION READ WRITE

statement ok
COMMIT

statement ok
BEGIN TRANSACTION READ ONLY

query I
SELECT 1
----
1

statement ok
COMMIT

statement ok
BEGIN TRANSACTION READ ONLY

statement error transaction in read-only mode
INSERT INTO t (a) VALUES (1)

statement ok
ROLLBACK

## BEGIN does not lose READ ONLY bit

statement ok
BEGIN READ ONLY

statement ok
BEGIN

statement error transaction in read-only mode
INSERT INTO t (a) VALUES (1)

statement ok
ROLLBACK

## READ ONLY -> READ WRITE valid only if no queries issued yet

statement ok
BEGIN READ ONLY

statement ok
BEGIN READ WRITE

statement ok
INSERT INTO t (a) VALUES (1)

statement ok
ROLLBACK

statement ok
BEGIN READ ONLY

query I
SELECT 1
----
1

statement error transaction read-write mode must be set before any query
BEGIN READ WRITE

statement ok
COMMIT

## READ WRITE -> READ ONLY valid, but cannot switch back if any queries issued

statement ok
BEGIN READ WRITE

query I
SELECT 1
----
1

statement ok
BEGIN READ ONLY

statement error transaction read-write mode must be set before any query
BEGIN READ WRITE

statement ok
COMMIT

# Test read-only -> read-write with subscribe
statement ok
BEGIN READ ONLY

simple
DECLARE c CURSOR FOR SUBSCRIBE t;
FETCH 0 c;
----
COMPLETE 0
COMPLETE 0

statement error transaction read-write mode must be set before any query
BEGIN READ WRITE

statement ok
COMMIT

# Test that multi-table write transactions aren't supported

statement ok
CREATE TABLE foo(a int)

statement ok
CREATE TABLE bar(a int)

statement ok
BEGIN

statement ok
INSERT INTO foo VALUES (42)

statement ok
INSERT INTO bar VALUES (43)

statement ok
COMMIT

query I
SELECT a FROM foo
----
42

query I
SELECT a FROM bar
----
43

# Test that constant reads are allowed in write-only transactions

statement ok
BEGIN

query I
SELECT 1
----
1

statement ok
SELECT now()

query T
SELECT INTERVAL '1 day'
----
1 day

statement ok
INSERT INTO t VALUES (1), (3)

query I
SELECT 1
----
1

statement ok
SELECT now()

query T
SELECT INTERVAL '1 day'
----
1 day

statement ok
COMMIT

# Verify that different kinds of INSERT INTO ... statements are correctly
# rejected when a transaction is in read-only mode.
#
# At least inserts of non-constant values internally get translated to
# read-then write statements, which has caused trouble with maintaining the
# read-only bit in the past.

statement ok
CREATE TABLE baz(a text)

statement ok
BEGIN

# A constant value
statement ok
INSERT INTO baz VALUES ('hello')

statement ok
COMMIT

statement ok
BEGIN

# A non-constant value
statement ok
INSERT INTO baz VALUES (now()::text)

statement ok
COMMIT

statement ok
BEGIN

statement ok
SELECT * FROM baz

# A constant value, should be rejected
statement error transaction in read-only mode
INSERT INTO baz VALUES ('ciao')

statement ok
ROLLBACK

statement ok
BEGIN

statement ok
SELECT * FROM baz

# A non-constant value, should be rejected as well
statement error transaction in read-only mode
INSERT INTO baz VALUES (now()::text)

statement ok
ROLLBACK

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET allow_real_time_recency = true
----
COMPLETE 0

statement ok
SET REAL_TIME_RECENCY TO TRUE

statement ok
CREATE TABLE rtr (a INT)

statement ok
INSERT INTO rtr VALUES (1)

query I
SELECT * FROM rtr
----
1

statement ok
INSERT INTO rtr SELECT * FROM rtr

query I
SELECT * FROM rtr
----
1
1

statement ok
DROP TABLE t CASCADE;

statement ok
CREATE TABLE t (a INT);

# AS OF should work in the middle of a transaction

statement ok
BEGIN

query T
SELECT * FROM t AS OF AT LEAST 1683131452106;
----

# Give t a chance to advance.
statement ok
SELECT mz_unsafe.mz_sleep(2)

query T
SELECT * FROM t AS OF AT LEAST 1683131452106;
----

query T
SELECT * FROM t;
----

# Give t a chance to advance.
statement ok
SELECT mz_unsafe.mz_sleep(2)

query T
SELECT * FROM t AS OF AT LEAST 1683131452106;
----

statement ok
COMMIT

statement ok
BEGIN

statement ok
SELECT * FROM t

statement error SET TRANSACTION ISOLATION LEVEL must be called before any query
SET TRANSACTION_ISOLATION TO serializable

statement ok
ROLLBACK

statement ok
BEGIN

statement ok
SELECT * FROM t

statement error SET TRANSACTION ISOLATION LEVEL must be called before any query
SET TRANSACTION ISOLATION LEVEL serializable

statement ok
ROLLBACK

statement ok
BEGIN

statement ok
SET TRANSACTION_ISOLATION TO serializable

statement ok
SELECT * FROM t

statement error SET TRANSACTION ISOLATION LEVEL must be called before any query
RESET TRANSACTION_ISOLATION

statement ok
ROLLBACK

# Test explicit single statement transactions.

reset-server

statement ok
BEGIN

statement ok
CREATE TABLE t (i INT)

# Should not have executed.
simple conn=c1
SHOW tables
----
COMPLETE 0

statement ok
COMMIT

simple conn=c1
SHOW tables
----
t,
COMPLETE 1

statement ok
BEGIN

# No error yet because we didn't try to execute.
statement ok
CREATE TABLE t (i INT)

statement error db error: ERROR: table "materialize\.public\.t" already exists
COMMIT

statement ok
BEGIN

# No error yet because we didn't try to execute.
statement ok
CREATE TABLE t (i INT)

statement error db error: ERROR: this transaction can only execute a single statement
SELECT 1

statement ok
ROLLBACK

# Test CREATE SOURCE for its off thread purify.
statement ok
BEGIN

statement ok
CREATE SOURCE s FROM LOAD GENERATOR COUNTER

statement ok
COMMIT

simple
SHOW SOURCES
----
s,load-generator,quickstart,
s_progress,progress,NULL,
COMPLETE 2

# Test a statement that doesn't work even in this mode because of ambiguous responses.
statement ok
CREATE DEFAULT INDEX ON s

statement ok
BEGIN

statement error db error: ERROR: ALTER INDEX s_primary_idx SET \(RETAIN HISTORY = FOR '1000 hours'\) cannot be run inside a transaction block
ALTER INDEX s_primary_idx SET (RETAIN HISTORY = FOR '1000 hours')

query error db error: ERROR: current transaction is aborted, commands ignored until end of transaction block
SELECT 1

statement ok
ROLLBACK

# Test ALTER and DROP which go through separate paths to determine the correct response.
statement ok
BEGIN

statement ok
ALTER SOURCE s RENAME TO v

statement ok
COMMIT

statement ok
BEGIN

statement ok
DROP SOURCE v

statement ok
COMMIT

simple
SHOW SOURCES
----
COMPLETE 0

# Test that the cluster cannot change mid-transaction.

statement ok
DROP TABLE t CASCADE

statement ok
CREATE TABLE t (a INT)

statement ok
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'))

statement ok
BEGIN

statement ok
SELECT * FROM t

statement error SET cluster cannot be called in an active transaction
SET CLUSTER TO c

statement ok
ROLLBACK

statement ok
BEGIN

statement ok
SELECT * FROM t

statement error SET cluster cannot be called in an active transaction
SET LOCAL CLUSTER TO c

statement ok
ROLLBACK

# Test that the cluster can change at the start of a transaction.

statement ok
BEGIN

statement ok
SET CLUSTER TO c

statement ok
SELECT * FROM t

statement ok
COMMIT

statement ok
SET CLUSTER TO default

statement ok
BEGIN

statement ok
SET LOCAL CLUSTER TO c

statement ok
SELECT * FROM t

statement ok
COMMIT

statement ok
SET CLUSTER TO default

# Test that the cluster is selected at the start of a transaction and doesn't change.

## Auto-routing selects mz_catalog_server at the start of transaction.

statement ok
BEGIN

statement ok
SHOW VIEWS

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT *, generate_series(1, 100) FROM mz_views
----
Explained Query:
  CrossJoin type=differential
    ArrangeBy keys=[[]]
      ReadIndex on=mz_views mz_views_ind=[*** full scan ***]
    ArrangeBy keys=[[]]
      Constant
        total_rows (diffs absed): 100
        first_rows:
          - (1)
          - (2)
          - (3)
          - (4)
          - (5)
          - (6)
          - (7)
          - (8)
          - (9)
          - (10)
          - (11)
          - (12)
          - (13)
          - (14)
          - (15)
          - (16)
          - (17)
          - (18)
          - (19)
          - (20)

Used Indexes:
  - mz_catalog.mz_views_ind (*** full scan ***)

Target cluster: mz_catalog_server

EOF

statement ok
COMMIT

## Auto-routing doesn't select mz_catalog_server in the middle of a transaction.

# Since mz_views uses custom types, the postgres client will look it up in the catalog on
# first use. If the first use happens to be in a transaction, then we can get unexpected time
# domain errors. This is an annoying hack to load the information in the postgres client before
# we start any transactions.
statement ok
SELECT * FROM mz_views LIMIT 0
----

statement ok
BEGIN

statement ok
SELECT * FROM t, mz_views

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SHOW VIEWS
----
Explained Query:
  With
    cte l0 =
      Project (#0{id}..=#2{name}, #4{comment})
        Join on=(#0{id} = #3{id}) type=differential
          ArrangeBy keys=[[#0{id}]]
            Project (#0{id}, #2{schema_id}, #3{name})
              ReadStorage mz_catalog.mz_views
          ArrangeBy keys=[[#0{id}]]
            Project (#0{id}, #3{comment})
              Filter (#2{object_sub_id}) IS NULL AND (#1{object_type} = "view")
                ReadStorage mz_internal.mz_comments
    cte l1 =
      Project (#0{id}, #3{name})
        Filter (#2{schema_id} = "u3")
          ReadStorage mz_catalog.mz_views
  Return
    Project (#0{name}, #2)
      Map (coalesce(#1{comment}, ""))
        Union
          Map (null)
            Union
              Negate
                Project (#1{name})
                  Join on=(#0{id} = #2{id}) type=differential
                    ArrangeBy keys=[[#0{id}]]
                      Get l1
                    ArrangeBy keys=[[#0{id}]]
                      Distinct project=[#0{id}]
                        Project (#0{id})
                          Get l0
              Project (#1{name})
                Get l1
          Project (#2{name}, #3{comment})
            Filter (#1{schema_id} = "u3")
              Get l0

Source mz_catalog.mz_views
Source mz_internal.mz_comments
  filter=((#1{object_type} = "view") AND (#2{object_sub_id}) IS NULL)

Target cluster: quickstart

EOF

statement ok
COMMIT

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_alter_swap = true;
----
COMPLETE 0

statement ok
CREATE SCHEMA blue;

statement ok
CREATE SCHEMA green;

query TT
SELECT name, id FROM mz_schemas WHERE id LIKE 'u%' ORDER BY name;
----
blue  u9
green  u10
public  u3

statement ok
BEGIN;

statement ok
ALTER SCHEMA blue SWAP WITH green;

statement ok
COMMIT;

query TT
SELECT name, id FROM mz_schemas WHERE id LIKE 'u%' ORDER BY name;
----
blue  u10
green  u9
public  u3

statement ok
BEGIN;

statement ok
ALTER SCHEMA blue SWAP WITH green;

statement ok
ALTER SCHEMA green RENAME TO purple;

statement ok
ROLLBACK;

query TT
SELECT name, id FROM mz_schemas WHERE id LIKE 'u%' ORDER BY name;
----
blue  u10
green  u9
public  u3

statement ok
BEGIN;

statement ok
ALTER SCHEMA green RENAME TO purple;

# Modify the Catalog from a different session while a transaction is open.

simple conn=mz_system,user=mz_system
CREATE TABLE yellow_t1 (x int);
----
COMPLETE 0

statement error db error: ERROR: object state changed while transaction was in progress
ALTER SCHEMA blue RENAME to pink;

statement ok
ROLLBACK;

query TT
SELECT name, id FROM mz_schemas WHERE id LIKE 'u%' ORDER BY name;
----
blue  u10
green  u9
public  u3

statement ok
BEGIN;

statement ok
ALTER SCHEMA blue RENAME TO pink;

statement ok
ALTER SCHEMA pink RENAME TO purple;

statement ok
ALTER SCHEMA purple RENAME TO orange;

statement ok
ALTER SCHEMA orange RENAME TO red;

statement ok
ALTER SCHEMA red RENAME TO orange;

statement ok
COMMIT;

query TT
SELECT name, id FROM mz_schemas WHERE id LIKE 'u%' ORDER BY name;
----
green  u9
orange  u10
public  u3

statement ok
BEGIN;

statement error db error: ERROR: schema 'green' already exists
ALTER SCHEMA orange RENAME TO green;

statement ok
COMMIT;

query TT
SELECT name, id FROM mz_schemas WHERE id LIKE 'u%' ORDER BY name;
----
green  u9
orange  u10
public  u3

statement ok
BEGIN;

statement ok
ALTER SCHEMA green RENAME TO red;

statement error db error: ERROR: schema 'red' already exists
ALTER SCHEMA orange RENAME TO red;

statement ok
COMMIT;

# Transaction should be rolled back and nothing should change.

query TT
SELECT name, id FROM mz_schemas WHERE id LIKE 'u%' ORDER BY name;
----
green  u9
orange  u10
public  u3

statement ok
CREATE TABLE green_t1 (x int);

statement ok
CREATE VIEW green_v1 AS ( SELECT SUM(x) FROM green_t1 );

statement ok
CREATE CLUSTER green_compute SIZE '1';

statement ok
CREATE MATERIALIZED VIEW green_mv1 IN CLUSTER green_compute AS ( SELECT AVG(x) FROM green_t1 );

statement ok
BEGIN;

statement ok
ALTER TABLE green_t1 RENAME TO blue_t1;

statement ok
ALTER VIEW green_v1 RENAME TO blue_v1;

statement ok
ALTER MATERIALIZED VIEW green_mv1 RENAME TO blue_mv1;

statement ok
ALTER CLUSTER green_compute RENAME TO blue_compute;

statement ok
COMMIT;

statement ok
INSERT INTO blue_t1 VALUES (10), (20), (30);

query I
SELECT * FROM blue_v1;
----
60

query I
SELECT * FROM blue_mv1;
----
20

query TT
SELECT name, id FROM mz_clusters WHERE id LIKE 'u%' AND name != 'quickstart' ORDER BY name;
----
blue_compute  u3
c  u2

statement ok
BEGIN;

statement ok
INSERT INTO blue_t1 VALUES (40), (50), (60);

statement error db error: ERROR: transaction in write-only mode
ALTER TABLE blue_t1 RENAME TO red_t1;

statement ok
COMMIT;

query I
SELECT * FROM blue_t1 LIMIT 1;
----
10

statement ok
BEGIN;

query I
SELECT * FROM blue_v1;
----
60

statement error db error: ERROR: transaction in read-only mode
ALTER TABLE blue_t1 RENAME TO red_t1;

statement ok
COMMIT;

query I
SELECT * FROM blue_t1 LIMIT 1;
----
10

statement ok
BEGIN;

statement ok
ALTER TABLE blue_t1 RENAME TO purple_t1;

statement error db error: ERROR: transactions which modify objects are restricted to just modifying objects
SELECT * FROM blue_mv1;

statement ok
COMMIT;

query I
SELECT * FROM blue_t1 LIMIT 1;
----
10

# Make sure SHOW COLUMNS does not use a different cluster during a transaction.
#
# SHOW COLUMNS is planned separately from other SHOW statements, and previously it would ignore
# the mz_catalog_server auto-routing. This resulted in its dependents being outside the timedomain
# of the transaction.

statement ok
BEGIN;

query T
SELECT name FROM mz_columns WHERE name = 'foobar';
----

query TTTT
SHOW COLUMNS IN mz_columns
----
id       false  text    The␠unique␠ID␠of␠the␠table,␠source,␠or␠view␠containing␠the␠column.
name     false  text    The␠name␠of␠the␠column.
position false  uint8   The␠1-indexed␠position␠of␠the␠column␠in␠its␠containing␠table,␠source,␠or␠view.
nullable false  boolean Can␠the␠column␠contain␠a␠`NULL`␠value?
type     false  text    The␠data␠type␠of␠the␠column.
default  true   text    The␠default␠expression␠of␠the␠column.
type_oid false  oid     The␠OID␠of␠the␠type␠of␠the␠column␠(references␠`mz_types`).
type_mod false  integer The␠packed␠type␠identifier␠of␠the␠column.

statement ok
COMMIT;

# Cleanup.

statement ok
DROP CLUSTER blue_compute CASCADE;