sql: pg_catalog.pg_statistic_ext mistakenly tries to report rows for dropped tables and fails with an error

[knz]

Describe the problem

CREATE TABLE t(x) AS SELECT generate_series(1,100);
CREATE STATISTICS st ON x FROM t;
DROP TABLE t;
TABLE pg_catalog.pg_statistic_ext;

Observe the last query fail:

(error encountered after some results were delivered)
ERROR: descriptor is being dropped

If the client waits some time after DROP and before looking at pg_statistic_ext, the following error is reported instead:

ERROR: relation "[54]" does not exist
SQLSTATE: 42P01

This is especially visible because it breaks \d in the SQL shell starting in v23.1.

Expected behavior

pg_statistic_ext must not contain rows for dropped tables.

Workaround

Until this bug is fixed, the following SQL statement can be run with the admin role to clear the SQL stats for dropped tables:

DELETE FROM system.table_statistics AS ts
      WHERE NOT EXISTS(
                    SELECT 1
                      FROM crdb_internal.tables AS tb
                     WHERE     ts."tableID" = tb.table_id
                           AND tb.state != 'DROP'
                );

Jira issue: CRDB-30650

Epic CRDB-27601

[knz]

Note: this issue is a side effect of #100482 -- when a node crashes or gets restarted, any pending stats updates caused by table creation/drop are not processed any more (the pending stats updated were stored in a Go channel, and are not persisted). With table drops in particular, the stats for the dropped table are never deleted.

This may be a separate bug to file? Unsure. cc @michae2

[nexdrew]

Possibly related to #108482

[michae2]

Note: this issue is a side effect of #100482 -- when a node crashes or gets restarted, any pending stats updates caused by table creation/drop are not processed any more (the pending stats updated were stored in a Go channel, and are not persisted). With table drops in particular, the stats for the dropped table are never deleted.

This may be a separate bug to file? Unsure. cc @michae2

For drops in particular, this was actually fixed recently in #105364, which is in v23.1.5 behind the cluster setting sql.stats.garbage_collection_interval.

That being said, even with garbage collection turned on there will still be a lag between DROP TABLE and deletion of rows from system.table_statistics. I think one way to make pg_statistic_ext more robust to this would be to change the following query to join to crdb_internal.tables to make sure we're only reading stats for live tables:

cockroach/pkg/sql/pg_catalog.go

Line 3583 in aba50b9

query := `SELECT "tableID", name, "columnIDs", "statisticID", '{d}'::"char"[] FROM system.table_statistics;`