public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yugo Nagata <[email protected]>
To: Andres Freund <[email protected]>
Cc: [email protected]
Subject: Re: Allow to collect statistics on virtual generated columns
Date: Tue, 24 Jun 2025 17:05:33 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <kp43uuykq64fyqxnlihq2rzntarextvzibuzturajfucl6bayo@6fcnflboemtt>
References: <[email protected]>
<kp43uuykq64fyqxnlihq2rzntarextvzibuzturajfucl6bayo@6fcnflboemtt>
On Tue, 17 Jun 2025 10:43:41 -0400
Andres Freund <[email protected]> wrote:
> 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%2F571
Thank you for letting me know it.
I've attached an updated patch to fix the test failure.
However, I'm now reconsidering the current approach, where the expression
of a virtual generated column is expanded at the time of creating extended
statistics. This seems not be ideal, as the statistics would become useless
if the expression is later modified.
Instead, I'm thinking of an alternative approach: expanding the expression
at the time statistics are collected.
Best regards,
Yugo Nagata
>
> Fails e.g. with:
> https://api.cirrus-ci.com/v1/artifact/task/5921189782093824/testrun/build/testrun/regress/regress/re...
>
> 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
>
>
--
Yugo Nagata <[email protected]>
view thread (21+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Allow to collect statistics on virtual generated columns
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox