Re: BUG #18468: CREATE TABLE ... LIKE leaves orphaned column reference in extended statistics

PG Bug reporting form <noreply(at)postgresql(dot)org> 于2024年5月16日周四 05:41写道：

> The following bug has been logged on the website:
>
> Bug reference: 18468
> Logged by: Alexander Lakhin
> Email address: exclusion(at)gmail(dot)com
> PostgreSQL version: 16.3
> Operating system: Ubuntu 22.04
> Description:
>
> The following script:
> CREATE TABLE t0 (a int, c text, b text);
> CREATE STATISTICS ext_stat ON (a || b) FROM t0;
> ALTER TABLE t0 DROP COLUMN c;
> CREATE TABLE t1 (LIKE t0 INCLUDING ALL);
>
> produces an invalid statistics definition:
> \d+ t1
> ERROR: invalid attnum 3 for relation "t1"
>
> SELECT stxname, stxexprs FROM pg_statistic_ext;
> ext_stat | ({OPEXPR :opno 2780 ... {VAR :varno 1 :varattno 3 ...
> t1_expr_stat | ({OPEXPR :opno 2780 ... {VAR :varno 1 :varattno 3 ...
>
> Though an index definition transferred correctly:
> CREATE TABLE t0 (a int, c text, b text);
> CREATE INDEX t_idx ON t0((a || b));
> ALTER TABLE t0 DROP COLUMN c;
> CREATE TABLE t1 (LIKE t0 INCLUDING ALL);
> \d+ t1
> ...
> Indexes:
> "t1_expr_idx" btree ((a || b))
>
> SELECT relname, indexprs FROM pg_index, pg_class WHERE indexrelid = oid
> AND relname = 't1_expr_idx';
> t1_expr_idx | ({OPEXPR :opno 2780 ... {VAR :varno 1 :varattno 2 ...
>
>
Thanks for reporting. I can reproduce on HEAD.
I found that since this commit a4d75c8, we support statistics on
expressions.
Before a4d75c8, CREATE STATISTICS ext_stat ON (a || b) FROM t0;
will report error.

I will continue to look into this issue.
--
Tender Wang
OpenPie: https://en.openpie.com/