pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19833: ALTER TABLE that rebuilds an extended statistics object silently drops its SET STATISTICS target (st
3+ messages / 3 participants
[nested] [flat]

* BUG #19833: ALTER TABLE that rebuilds an extended statistics object silently drops its SET STATISTICS target (st
@ 2026-11-05 09:37  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 3+ messages in thread

From: PG Bug reporting form @ 2026-11-05 09:37 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com

The following bug has been logged on the website:

Bug reference:      19833
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   Linux x86_64 (official Docker image postgres:lates
Description:        

**Description:**

`ALTER TABLE ... ALTER COLUMN TYPE` (and `ALTER COLUMN ... SET EXPRESSION`)
drops and rebuilds extended statistics objects that reference the column.
The rebuild preserves the definition (columns/expressions/kinds), the owner,
and the comment — but silently resets `stxstattarget` (the value set by
`ALTER STATISTICS ... SET STATISTICS N`) to NULL. The next ANALYZE then uses
the default target instead of the user's requested one, degrading the
statistics detail and planner estimates.

Since `pg_dump` dumps `ALTER STATISTICS ... SET STATISTICS N` as a persisted
property, dropping it on an unrelated ALTER TABLE is an inconsistency, and
the owner/comment preservation shows the rebuild path is *meant* to carry
user-visible properties across.

**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)

-- Control: altering an UNREFERENCED column does not rebuild, target
survives:
ALTER STATISTICS st2_s SET STATISTICS 333;
ALTER TABLE st2 ALTER COLUMN d TYPE bigint;
SELECT s.stxstattarget FROM pg_statistic_ext s WHERE s.stxname = 'st2_s';
-- 333

-- Second trigger: virtual generated column SET EXPRESSION also rebuilds and
drops the target
CREATE TABLE st3 (a int, g int GENERATED ALWAYS AS (a * 2) VIRTUAL);
CREATE STATISTICS st3_s ON g, (a + g) FROM st3;
ALTER STATISTICS st3_s SET STATISTICS 400;
ALTER TABLE st3 ALTER COLUMN g SET EXPRESSION AS (a * 3);
SELECT s.stxstattarget FROM pg_statistic_ext s WHERE s.stxname = 'st3_s';
-- NULL (was 400)
```

**Root cause:** the statistics-object rebuild path in
`src/backend/commands/tablecmds.c` (`RememberStatisticsForRebuilding` and
friends) restores owner and comment but not `stxstattarget`.

**Expected behavior:** the rebuild should preserve `stxstattarget`, the same
way it preserves the owner and the comment.








^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #19833: ALTER TABLE that rebuilds an extended statistics object silently drops its SET STATISTICS target (st
@ 2026-11-11 08:27  Rahila Syed <rahilasyed90@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Rahila Syed @ 2026-11-11 08:27 UTC (permalink / raw)
  To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #19833: ALTER TABLE that rebuilds an extended statistics object silently drops its SET STATISTICS target (st
@ 2026-11-12 02:55  Michael Paquier <michael@paquier.xyz>
  parent: Rahila Syed <rahilasyed90@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Michael Paquier @ 2026-11-12 02:55 UTC (permalink / raw)
  To: Rahila Syed <rahilasyed90@gmail.com>; +Cc: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

On Wed, Nov 11, 2026 at 04:27:37PM +0800, Rahila Syed wrote:
> 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)

Hmm, yes, that feels counter-intuitive to not preserve that.

> I will look into writing a fix for this.

Cool, thanks.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2026-11-12 02:55 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-05 09:37 BUG #19833: ALTER TABLE that rebuilds an extended statistics object silently drops its SET STATISTICS target (st PG Bug reporting form <noreply@postgresql.org>
2026-11-11 08:27 ` Rahila Syed <rahilasyed90@gmail.com>
2026-11-12 02:55   ` Michael Paquier <michael@paquier.xyz>

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