Horton

Thread

Commits

  1. Fix handling of extended expression statistics in CREATE TABLE LIKE.

  2. Extended statistics on expressions

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

    The Post Office <noreply@postgresql.org> — 2024-05-15T11:00:01Z

    The following bug has been logged on the website:

Bug reference:      18468
Logged by:          Alexander Lakhin
Email address:      exclusion@gmail.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 ...

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

    Tender Wang <tndrwang@gmail.com> — 2024-05-16T03:15:29Z

    PG Bug reporting form <noreply@postgresql.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@gmail.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/

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

    Tender Wang <tndrwang@gmail.com> — 2024-05-16T08:38:19Z

    generateClonedExtStatsStmt just copy t0's ext_stat info to t1.
I think we could do something in transformExtendedStatistics() as the
comments said.
in transformCreateStmt， has below code:
/*
* Postprocess extended statistics.
*/
transformExtendedStatistics(&cxt);

Right now, there is nothing to do in transformExtendedStatistics.
Can we update the varattno to the right value here?
-- 
Tender Wang
OpenPie:  https://en.openpie.com/

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-16T18:45:18Z

    Tender Wang <tndrwang@gmail.com> writes:
> generateClonedExtStatsStmt just copy t0's ext_stat info to t1.

So this is a complete mess.  The fundamental problem is that
transformTableLikeClause believes it can process LIKE_STATISTICS
requests immediately, because:

     * We may copy extended statistics if requested, since the representation
     * of CreateStatsStmt doesn't depend on column numbers.

That was true apparently in the original, limited implementation
where we only supported CREATE STATISTICS on plain columns.
It's completely wrong for statistics on expressions.  IMO what we
need to do is make LIKE_STATISTICS work more like LIKE_INDEXES:
postpone the transformation until expandTableLikeClause when we know
the mapping from source columns to destination columns, and have a
chance at converting the expression trees properly, comparably to
generateClonedIndexStmt.

> Right now, there is nothing to do in transformExtendedStatistics.
> Can we update the varattno to the right value here?

We don't know the column mapping there, either.  What we need to have
our hands on is the "attmap" computed in expandTableLikeClause, and
then we can pass the stats expressions through map_variable_attnos().

I think this might not need to be a really large patch, but it
definitely has to change where generateClonedExtStatsStmt is
called from.  I can have a go at it if Tomas doesn't want to.

			regards, tom lane

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

    Tender Wang <tndrwang@gmail.com> — 2024-05-17T03:26:26Z

    Tom Lane <tgl@sss.pgh.pa.us> 于2024年5月17日周五 02:45写道：

> Tender Wang <tndrwang@gmail.com> writes:
> > generateClonedExtStatsStmt just copy t0's ext_stat info to t1.
>
> So this is a complete mess.  The fundamental problem is that
> transformTableLikeClause believes it can process LIKE_STATISTICS
> requests immediately, because:
>
>      * We may copy extended statistics if requested, since the
> representation
>      * of CreateStatsStmt doesn't depend on column numbers.
>

The above comments in  transformTableLikeClause and below comments in
generateClonedExtStatsStmt:
   * Now handle expressions, if there are any. The order (with respect to
   * regular attributes) does not really matter for extended stats, so we
   * simply append them after simple column references.
now should do some revise.

That was true apparently in the original, limited implementation
> where we only supported CREATE STATISTICS on plain columns.
> It's completely wrong for statistics on expressions.  IMO what we
> need to do is make LIKE_STATISTICS work more like LIKE_INDEXES:
> postpone the transformation until expandTableLikeClause when we know
> the mapping from source columns to destination columns, and have a
> chance at converting the expression trees properly, comparably to
> generateClonedIndexStmt.

> Right now, there is nothing to do in transformExtendedStatistics.
> > Can we update the varattno to the right value here?
>
> We don't know the column mapping there, either.  What we need to have
> our hands on is the "attmap" computed in expandTableLikeClause, and
> then we can pass the stats expressions through map_variable_attnos().


This fix looks better than what I said in  transformExtendedStatistics.


>
I think this might not need to be a really large patch, but it
> definitely has to change where generateClonedExtStatsStmt is
> called from.  I can have a go at it if Tomas doesn't want to.
>
>                         regards, tom lane
>


-- 
Tender Wang
OpenPie:  https://en.openpie.com/

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-17T20:39:05Z

    I wrote:
> We don't know the column mapping there, either.  What we need to have
> our hands on is the "attmap" computed in expandTableLikeClause, and
> then we can pass the stats expressions through map_variable_attnos().

> I think this might not need to be a really large patch, but it
> definitely has to change where generateClonedExtStatsStmt is
> called from.  I can have a go at it if Tomas doesn't want to.

Yeah, this doesn't require a whole lot more than moving the
processing in transformTableLikeClause to expandTableLikeClause.

I chose to get rid of transformExtendedStatistics, because not
only is there nothing for it to do, there will never be any
statements for it to do nothing to.

I plan to sit on this till after the beta1 freeze.

			regards, tom lane

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

    Tender Wang <tndrwang@gmail.com> — 2024-05-20T07:31:10Z

    Tom Lane <tgl@sss.pgh.pa.us> 于2024年5月18日周六 04:39写道：

> I wrote:
> > We don't know the column mapping there, either.  What we need to have
> > our hands on is the "attmap" computed in expandTableLikeClause, and
> > then we can pass the stats expressions through map_variable_attnos().
>
> > I think this might not need to be a really large patch, but it
> > definitely has to change where generateClonedExtStatsStmt is
> > called from.  I can have a go at it if Tomas doesn't want to.
>
> Yeah, this doesn't require a whole lot more than moving the
> processing in transformTableLikeClause to expandTableLikeClause.
>
> I chose to get rid of transformExtendedStatistics, because not
> only is there nothing for it to do, there will never be any
> statements for it to do nothing to.
>

The patch looks good to me.

-- 
Tender Wang
OpenPie:  https://en.openpie.com/