Re: Allow to collect statistics on virtual generated columns - Mailing list pgsql-hackers

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