sql: dropped table remains visible in the same transaction #57494
Description
Describe the problem
When you alter a table subsequently drop/rename it inside a transaction, the old table is still visible via pg_catalog, show tables (which uses pg_catalog tables), and information_schema.tables.
To Reproduce
With a drop:
root@localhost:26257/defaultdb> create table foo;
root@localhost:26257/defaultdb> show tables;
schema_name | table_name | type | owner | estimated_row_count
--------------+------------+-------+-------+----------------------
public | foo | table | root | 0
root@localhost:26257/defaultdb> begin;
root@localhost:26257/defaultdb OPEN> alter table foo rename to bar;
root@localhost:26257/defaultdb OPEN> show tables;
schema_name | table_name | type | owner | estimated_row_count
--------------+------------+-------+-------+----------------------
public | bar | table | root | 0
root@localhost:26257/defaultdb OPEN> drop table bar;
root@localhost:26257/defaultdb OPEN> show tables;
schema_name | table_name | type | owner | estimated_row_count
--------------+------------+-------+-------+----------------------
public | bar | table | root | 0
root@localhost:26257/defaultdb OPEN> select * from bar;
ERROR: relation "bar" does not exist
SQLSTATE: 42P01
With a rename:
root@localhost:26257/defaultdb> create table foo;
root@localhost:26257/defaultdb> begin;
Time: 0ms total (execution 0ms / network 0ms)
root@localhost:26257/defaultdb OPEN> alter table foo add column i int;
Time: 5ms total (execution 5ms / network 0ms)
root@localhost:26257/defaultdb OPEN> show tables;
schema_name | table_name | type | owner | estimated_row_count
--------------+------------+-------+-------+----------------------
public | foo | table | root | 0
root@localhost:26257/defaultdb OPEN> alter table foo rename to bar;
root@localhost:26257/defaultdb OPEN> show tables;
schema_name | table_name | type | owner | estimated_row_count
--------------+------------+-------+-------+----------------------
public | foo | table | root | 0
root@localhost:26257/defaultdb OPEN> select * from foo;
ERROR: relation "foo" does not exist
SQLSTATE: 42P01
With a rename using information_schema.tables:
root@localhost:26257/defaultdb> create table foo;
root@localhost:26257/defaultdb> begin;
root@localhost:26257/defaultdb OPEN> alter table foo rename to bar;
root@localhost:26257/defaultdb OPEN> show tables;
schema_name | table_name | type | owner | estimated_row_count
--------------+------------+-------+-------+----------------------
public | bar | table | root | 0
root@localhost:26257/defaultdb OPEN> select * from information_schema.tables where table_name = 'bar';
table_catalog | table_schema | table_name | table_type | is_insertable_into | version
----------------+--------------+------------+------------+--------------------+----------
defaultdb | public | bar | BASE TABLE | YES | 2
root@localhost:26257/defaultdb OPEN> drop table bar;
root@localhost:26257/defaultdb OPEN> select * from information_schema.tables where table_name = 'bar';
table_catalog | table_schema | table_name | table_type | is_insertable_into | version
----------------+--------------+------------+------------+--------------------+----------
defaultdb | public | bar | BASE TABLE | YES | 2
Expected behavior
The table should not be visible via after being dropped/renamed.
Additional Notes
This shows up in these roachtest failures:
#56081 (comment)
#56081 (comment)
#56081 (comment)
#56081 (comment)
#56081 (comment)
#56081 (comment)
Note that each alter table statement implicitly performs a select table table_name from [show tables] limit 1 when generating random SQL statements. The last alter table statement in each example should not be able to generate itself because the table does not exist. Because the table still exists via show tables, the last statement get generated using a non existing table.