Обсуждение: BUG #17123: ALTER TABLE ... NO INHERIT leaves orphaned statistics behind

The following bug has been logged on the website:

Bug reference:      17123
Logged by:          Christoph Berg
Email address:      christoph.berg@credativ.de
PostgreSQL version: 10.16
Operating system:   Any
Description:

Removing the last/only inheritance child from a table using ALTER TABLE NO
INHERIT leaves orphaned statistics behind that ANALYZE doesn't remove.
Original customer complaint on 10.16, but 15devel has the same issue.

psql -ef inherit.sql 
create table log (ts timestamptz, data text);
CREATE TABLE
Zeit: 2,378 ms
insert into log values ('2021-07-01', '1');
INSERT 0 1
Zeit: 0,457 ms
insert into log values ('2021-08-01', '2');
INSERT 0 1
Zeit: 0,197 ms
create table log2 (like log) inherits (log);
psql:inherit.sql:4: HINWEIS:  00000: Spalte »ts« wird mit geerbter
Definition zusammengeführt
ORT:  MergeAttributes, tablecmds.c:2837
psql:inherit.sql:4: HINWEIS:  00000: Spalte »data« wird mit geerbter
Definition zusammengeführt
ORT:  MergeAttributes, tablecmds.c:2837
CREATE TABLE
Zeit: 1,396 ms
insert into log2 values ('2021-01-01', '3');
INSERT 0 1
Zeit: 0,351 ms
insert into log2 values ('2021-02-01', '4');
INSERT 0 1
Zeit: 0,174 ms
analyze log;
ANALYZE
Zeit: 1,093 ms
select * from pg_stats where tablename = 'log' and attname = 'ts' 
─[ RECORD 1
]──────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────
schemaname             │ public
tablename              │ log
attname                │ ts
inherited              │ f
null_frac              │ 0
avg_width              │ 8
n_distinct             │ -1
most_common_vals       │ ∅
most_common_freqs      │ ∅
histogram_bounds       │ {"2021-07-01 00:00:00+02","2021-08-01
00:00:00+02"}
correlation            │ 1
most_common_elems      │ ∅
most_common_elem_freqs │ ∅
elem_count_histogram   │ ∅
─[ RECORD 2
]──────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────
schemaname             │ public
tablename              │ log
attname                │ ts
inherited              │ t
null_frac              │ 0
avg_width              │ 8
n_distinct             │ -1
most_common_vals       │ ∅
most_common_freqs      │ ∅
histogram_bounds       │ {"2021-01-01 00:00:00+01","2021-02-01
00:00:00+01","2021-07-01 00:00:00+02","2021-08-01 00:00:00+02"}
correlation            │ -0.6
most_common_elems      │ ∅
most_common_elem_freqs │ ∅
elem_count_histogram   │ ∅

Zeit: 4,616 ms
select ctid, stainherit from pg_statistic where starelid = 'log'::regclass
and staattnum = 1;
  ctid   │ stainherit 
─────────┼────────────
 (18,17) │ f
 (18,19) │ t
(2 Zeilen)

Zeit: 0,856 ms
alter table log2 no inherit log;
ALTER TABLE
Zeit: 0,449 ms
analyze log;
ANALYZE
Zeit: 0,394 ms
select * from pg_stats where tablename = 'log' and attname = 'ts' 
─[ RECORD 1
]──────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────
schemaname             │ public
tablename              │ log
attname                │ ts
inherited              │ f
null_frac              │ 0
avg_width              │ 8
n_distinct             │ -1
most_common_vals       │ ∅
most_common_freqs      │ ∅
histogram_bounds       │ {"2021-07-01 00:00:00+02","2021-08-01
00:00:00+02"}
correlation            │ 1
most_common_elems      │ ∅
most_common_elem_freqs │ ∅
elem_count_histogram   │ ∅
─[ RECORD 2
]──────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────
schemaname             │ public
tablename              │ log
attname                │ ts
inherited              │ t          <-- not removed
null_frac              │ 0
avg_width              │ 8
n_distinct             │ -1
most_common_vals       │ ∅
most_common_freqs      │ ∅
histogram_bounds       │ {"2021-01-01 00:00:00+01","2021-02-01
00:00:00+01","2021-07-01 00:00:00+02","2021-08-01 00:00:00+02"}
correlation            │ -0.6
most_common_elems      │ ∅
most_common_elem_freqs │ ∅
elem_count_histogram   │ ∅

Zeit: 1,526 ms
select ctid, stainherit from pg_statistic where starelid = 'log'::regclass
and staattnum = 1;
  ctid   │ stainherit 
─────────┼────────────
 (18,21) │ f
 (18,19) │ t             <-- not touched
(2 Zeilen)

Zeit: 0,373 ms

cat inherit.sql
create table log (ts timestamptz, data text);
insert into log values ('2021-07-01', '1');
insert into log values ('2021-08-01', '2');
create table log2 (like log) inherits (log);
insert into log2 values ('2021-01-01', '3');
insert into log2 values ('2021-02-01', '4');

analyze log;
select * from pg_stats where tablename = 'log' and attname = 'ts' \gx
select ctid, stainherit from pg_statistic where starelid = 'log'::regclass
and staattnum = 1;

alter table log2 no inherit log;

analyze log;
select * from pg_stats where tablename = 'log' and attname = 'ts' \gx
select ctid, stainherit from pg_statistic where starelid = 'log'::regclass
and staattnum = 1;

PG Bug reporting form <noreply@postgresql.org> writes:
> Removing the last/only inheritance child from a table using ALTER TABLE NO
> INHERIT leaves orphaned statistics behind that ANALYZE doesn't remove.
> Original customer complaint on 10.16, but 15devel has the same issue.

Good catch.  I imagine DETACH PARTITION has related issues?

It seems like there are basically two ways to fix this:

1. Make ALTER TABLE NO INHERIT/DETACH PARTITION get rid of
no-longer-wanted stats.

2. Make ANALYZE check for and remove any inappropriate stats.

I'm kind of inclined to prefer #1, on the grounds that #2 would
add almost-always-useless cycles to every ANALYZE run forevermore.
On the other hand, #2 seems a bit more robust, in that we'd not
have to remember to consider this issue every time somebody invents
a new flavor of DDL that affects these things.

Thoughts?

            regards, tom lane

Re: Tom Lane
> Good catch.  I imagine DETACH PARTITION has related issues?

It has, but for partitioned tables I think the case is different.
Partitioned tables have inherited=true stats only, and in practice, as
the partition parent table is always empty, stale statistics on a
partitioned table without any partitions aren't hurting. Maybe one
could even argue that keeping the stats around in that case makes
sense since they might help with the next partition re-added, like
TRUNCATE doesn't reset stats, or like ANALYZE on an empty table
doesn't wipe the existing stats.

create table log (ts timestamptz) partition by range (ts);
create table log2 partition of log for values from ('2021-07-01') to ('2021-08-01');
insert into log values ('2021-07-01');
analyze log;
select * from pg_stats where tablename = 'log' and attname = 'ts' \gx
alter table log detach partition log2;
analyze log;
select * from pg_stats where tablename = 'log' and attname = 'ts' \gx

Christoph
-- 
Senior Berater, Tel.: +49 2166 9901 187
credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Sascha Heuer, Geoff Richardson,
Peter Lilley; Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz