pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: christoph.berg@credativ.de
Subject: BUG #17123: ALTER TABLE ... NO INHERIT leaves orphaned statistics behind
Date: Mon, 26 Jul 2021 12:54:22 +0000
Message-ID: <17123-734abc3934f500b6@postgresql.org> (raw )
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;
view thread (3+ messages) latest in thread
Message-ID: <17123-734abc3934f500b6@postgresql.org>
Permalink: ../17123-734abc3934f500b6@postgresql.org/
Also on: postgresql.org/message-id/17123-734abc3934f500b6@postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, christoph.berg@credativ.de
Subject: Re: BUG #17123: ALTER TABLE ... NO INHERIT leaves orphaned statistics behind
In-Reply-To: <17123-734abc3934f500b6@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox