Doc: Add note for running ANALYZE after ALTER TABLE ... SET EXPRESSION

On Mon, Aug 4, 2025 at 3:14 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:

Hi,

The documentation recommends running ANALYZE after the "ALTER TABLE ... SET DATA TYPE"
command since the statistics will become wrong after that.

That same thing can be applied to "ALTER TABLE ... SET EXPRESSION" commands,
but that is not described in the documentation, so I've attached a patch
to add this statement.

+1

+      When this form is used, the column's statistics are removed,
+      so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
+      on the table afterwards is recommended.

ANALYZE is not needed for virtual generated columns, since they
don't have statistics in the first place? If so, would it be clearer to
explicitly mention that? How about rephrasing it like this?

------------------------------
When this form is used on a stored generated column, its statistics
are removed, so running <link linkend="sql-analyze"><command>ANALYZE
</command></link> on the table afterwards is recommended.
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
------------------------------

Regards,

--
Fujii Masao

On Mon, 4 Aug 2025 20:01:34 +0900
Fujii Masao <masao.fujii@gmail.com> wrote:

On Mon, Aug 4, 2025 at 3:14 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:

Hi,

The documentation recommends running ANALYZE after the "ALTER TABLE ... SET DATA TYPE"
command since the statistics will become wrong after that.

That same thing can be applied to "ALTER TABLE ... SET EXPRESSION" commands,
but that is not described in the documentation, so I've attached a patch
to add this statement.

+1

+      When this form is used, the column's statistics are removed,
+      so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
+      on the table afterwards is recommended.

ANALYZE is not needed for virtual generated columns, since they
don't have statistics in the first place? If so, would it be clearer to
explicitly mention that? How about rephrasing it like this?

------------------------------
When this form is used on a stored generated column, its statistics
are removed, so running <link linkend="sql-analyze"><command>ANALYZE
</command></link> on the table afterwards is recommended.
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
------------------------------

Thank you for looking at it. I overlooked that virtual generated columns
don't have statistics currently (I'm working on this at [1]/messages/by-id/20250801002830.143b25971fb9594b89d96aee@sraoss.co.jp, though).

Your suggestion makes sense, so I've attached an updated patch.
I also mentioned virtual generated column in the description
of SET DATA TYPE.

[1]: /messages/by-id/20250801002830.143b25971fb9594b89d96aee@sraoss.co.jp

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>

On Tue, Aug 5, 2025 at 10:09 AM Yugo Nagata <nagata@sraoss.co.jp> wrote:

Thank you for looking at it. I overlooked that virtual generated columns
don't have statistics currently (I'm working on this at [1], though).

Your suggestion makes sense, so I've attached an updated patch.
I also mentioned virtual generated column in the description
of SET DATA TYPE.

Thanks for updating the patch!

As for back-patching, ANALYZE recommendation for SET EXPRESSION should
be back-patched to v17, where that feature was introduced? Similarly,
the note about virtual generated columns should go into v18,
since that's when they were added?

Regards,

--
Fujii Masao

On Wed, 6 Aug 2025 13:20:18 +0900
Fujii Masao <masao.fujii@gmail.com> wrote:

On Tue, Aug 5, 2025 at 10:09 AM Yugo Nagata <nagata@sraoss.co.jp> wrote:

Thank you for looking at it. I overlooked that virtual generated columns
don't have statistics currently (I'm working on this at [1], though).

Your suggestion makes sense, so I've attached an updated patch.
I also mentioned virtual generated column in the description
of SET DATA TYPE.

Thanks for updating the patch!

As for back-patching, ANALYZE recommendation for SET EXPRESSION should
be back-patched to v17, where that feature was introduced? Similarly,
the note about virtual generated columns should go into v18,
since that's when they were added?

Yes. I believe the back-patching would be better to both v17 and v18.
I've attached a patch for v17.

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>

On Wed, Aug 6, 2025 at 3:44 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:

Yes. I believe the back-patching would be better to both v17 and v18.
I've attached a patch for v17.

Thanks for the patch for v17! I've pushed the patches.

Regards,

--
Fujii Masao