Re: BUG #19833: ALTER TABLE that rebuilds an extended statistics object silently drops its SET STATISTICS target (st

Hi,

Thank you for the report.

> **Reproduction (self-contained SQL, works on the official image):**
>
> ```sql
> CREATE TABLE st2 (c int, d int);
> INSERT INTO st2 SELECT g, g FROM generate_series(1, 1000) g;
> CREATE STATISTICS st2_s ON c, (c + 1) FROM st2;
> ALTER STATISTICS st2_s SET STATISTICS 250;
> ANALYZE st2;
>
> SELECT s.stxstattarget AS target FROM pg_statistic_ext s WHERE s.stxname =
> 'st2_s';
> -- 250
>
> ALTER TABLE st2 ALTER COLUMN c TYPE bigint; -- rebuilds the statistics
> object
>
> SELECT s.oid, s.stxstattarget AS target,
> obj_description(s.oid, 'pg_statistic_ext') AS comment
> FROM pg_statistic_ext s WHERE s.stxname = 'st2_s';
> -- new oid, target = NULL, comment = 'keep me' (comment and definition
> survive)

This is reproducible on my end as well. It does seem worth fixing,
especially since
only the stxstattrarget from the pg_statistic_ext() is not restored post the
ALTER COLUMN TYPE. An AI search did not give me any
documentation line, code comment or commit which mentions why we
require users to SET STATISTICS target
again after ALTER COLUMN TYPE or any statement indicating that we do
not propagate
target settings after ALTER COLUMN TYPE.

Another trigger for such a behaviour is an index defined on a column
losing its statistics target after
the column type is altered.

CREATE INDEX st2_cb ON st2 ((c+d));
ALTER INDEX st2_cb ALTER COLUMN 1 SET STATISTICS 222;

SELECT i.indexrelid::regclass::text AS index_name, a.attstattarget
FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indexrelid AND
a.attnum = 1 WHERE i.indrelid = 'st2'::regclass;
index_name | attstattarget
------------+---------------
st2_cb | 222
(1 row)

ALTER TABLE st2 ALTER COLUMN c TYPE int;

SELECT i.indexrelid::regclass::text AS index_name, a.attstattarget
FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indexrelid AND
a.attnum = 1 WHERE i.indrelid = 'st2'::regclass;
index_name | attstattarget
------------+---------------
st2_cb |
(1 row)

I will look into writing a fix for this.

Thank you,
Rahila Syed