Re: Allow to collect statistics on virtual generated columns

Hi,

On 2025-04-22 18:10:06 +0900, Yugo Nagata wrote:
> With your feedback, I would like to progress or rework the patch.

Right now the tests seem to always fail:
https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F5714

Fails e.g. with:
https://api.cirrus-ci.com/v1/artifact/task/5921189782093824/testrun/build/testrun/regress/regress/regression.diffs

diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/stats_ext.out /tmp/cirrus-ci-build/build/testrun/regress/regress/results/stats_ext.out
--- /tmp/cirrus-ci-build/src/test/regress/expected/stats_ext.out 2025-05-26 00:59:01.813042000 +0000
+++ /tmp/cirrus-ci-build/build/testrun/regress/regress/results/stats_ext.out 2025-05-26 01:02:20.350387000 +0000
@@ -56,7 +56,6 @@
ERROR: unrecognized statistics kind "unrecognized"
-- incorrect expressions
CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference
-ERROR: extended statistics require at least 2 columns
CREATE STATISTICS tst ON y + z FROM ext_stats_test; -- missing parentheses
ERROR: syntax error at or near "+"
LINE 1: CREATE STATISTICS tst ON y + z FROM ext_stats_test;
@@ -69,25 +68,24 @@
-- statistics on virtual generated column not allowed
CREATE TABLE ext_stats_test1 (x int, y int, z int GENERATED ALWAYS AS (x+y) VIRTUAL, w xid);
CREATE STATISTICS tst on z from ext_stats_test1;
-ERROR: statistics creation on virtual generated columns is not supported
CREATE STATISTICS tst on (z) from ext_stats_test1;
-ERROR: statistics creation on virtual generated columns is not supported
+ERROR: statistics object "tst" already exists
CREATE STATISTICS tst on (z+1) from ext_stats_test1;
-ERROR: statistics creation on virtual generated columns is not supported
+ERROR: statistics object "tst" already exists
CREATE STATISTICS tst (ndistinct) ON z from ext_stats_test1;
-ERROR: statistics creation on virtual generated columns is not supported
+ERROR: statistics object "tst" already exists
-- statistics on system column not allowed
CREATE STATISTICS tst on tableoid from ext_stats_test1;
-ERROR: statistics creation on system columns is not supported
+ERROR: statistics object "tst" already exists
CREATE STATISTICS tst on (tableoid) from ext_stats_test1;
-ERROR: statistics creation on system columns is not supported
+ERROR: statistics object "tst" already exists
CREATE STATISTICS tst on (tableoid::int+1) from ext_stats_test1;
-ERROR: statistics creation on system columns is not supported
+ERROR: statistics object "tst" already exists
CREATE STATISTICS tst (ndistinct) ON xmin from ext_stats_test1;
-ERROR: statistics creation on system columns is not supported
+ERROR: statistics object "tst" already exists
-- statistics without a less-than operator not supported
CREATE STATISTICS tst (ndistinct) ON w from ext_stats_test1;
-ERROR: column "w" cannot be used in statistics because its type xid has no default btree operator class
+ERROR: statistics object "tst" already exists
DROP TABLE ext_stats_test1;
-- Ensure stats are dropped sanely, and test IF NOT EXISTS while at it
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);

Greetings,

Andres