pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedALTER TABLE ... SET DATA TYPE removes statistics
8+ messages / 4 participants
[nested] [flat]
* ALTER TABLE ... SET DATA TYPE removes statistics
@ 2021-10-08 09:03 nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 20:15 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Euler Taveira <euler@eulerto.com>
0 siblings, 1 reply; 8+ messages in thread
From: nikolai.berkoff @ 2021-10-08 09:03 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
The current documentation does not mention that the column statistics are removed which I can see they are in src/backend/commands/tablecmds.c
ATExecAlterColumnType
/*
* Drop any pg_statistic entry for the column, since it's now wrong type
*/
RemoveStatistics(RelationGetRelid(rel), attnum);
Although this might be obvious it tripped me up. For example renaming and SET STATISTICS preserves statistics. Patch attached.
Regards,
Nikolai
Attachments:
[text/x-patch] set_data_type.patch (907B, ../../vCc8XnwDmlP4ZnHBQLIVxzD405BiYHVC9qZlhIF7IsfxK0gC9mZ4PUUOH0-3y6kv5p-87-3_ljqT1KvQVAnb8OoWhPU3kcqWn2ZpmxRBCQg=@pm.me/2-set_data_type.patch)
download | inline diff:
diff --git i/doc/src/sgml/ref/alter_table.sgml w/doc/src/sgml/ref/alter_table.sgml
index 8129157..ef59db0 100644
--- i/doc/src/sgml/ref/alter_table.sgml
+++ w/doc/src/sgml/ref/alter_table.sgml
@@ -187,7 +187,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form changes the type of a column of a table. Indexes and
simple table constraints involving the column will be automatically
converted to use the new column type by reparsing the originally
- supplied expression.
+ supplied expression. Note this will remove the column's statistics
+ so will normally be followed by an <link linkend="sql-analyze"><command>ANALYZE</command></link> of the table.
The optional <literal>COLLATE</literal> clause specifies a collation
for the new column; if omitted, the collation is the default for the
new column type.
[application/pgp-signature] signature.asc (507B, ../../vCc8XnwDmlP4ZnHBQLIVxzD405BiYHVC9qZlhIF7IsfxK0gC9mZ4PUUOH0-3y6kv5p-87-3_ljqT1KvQVAnb8OoWhPU3kcqWn2ZpmxRBCQg=@pm.me/3-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ALTER TABLE ... SET DATA TYPE removes statistics
2021-10-08 09:03 ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
@ 2021-10-18 20:15 ` Euler Taveira <euler@eulerto.com>
2021-10-19 03:52 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 8+ messages in thread
From: Euler Taveira @ 2021-10-18 20:15 UTC (permalink / raw)
To: nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Fri, Oct 8, 2021, at 6:03 AM, nikolai.berkoff wrote:
> The current documentation does not mention that the column statistics are removed which I can see they are in src/backend/commands/tablecmds.c
>
> ATExecAlterColumnType
> /*
> * Drop any pg_statistic entry for the column, since it's now wrong type
> */
> RemoveStatistics(RelationGetRelid(rel), attnum);
>
> Although this might be obvious it tripped me up. For example renaming and SET STATISTICS preserves statistics. Patch attached.
I agree that it might surprise an user and it would be good to document it.
However, it does not belong to the description. I would add it to the Notes
section at the end of the ALTER TABLE page.
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ALTER TABLE ... SET DATA TYPE removes statistics
2021-10-08 09:03 ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 20:15 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Euler Taveira <euler@eulerto.com>
@ 2021-10-19 03:52 ` Michael Paquier <michael@paquier.xyz>
2021-10-19 15:16 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Paquier @ 2021-10-19 03:52 UTC (permalink / raw)
To: Euler Taveira <euler@eulerto.com>; +Cc: nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Mon, Oct 18, 2021 at 05:15:59PM -0300, Euler Taveira wrote:
> I agree that it might surprise an user and it would be good to document it.
> However, it does not belong to the description. I would add it to the Notes
> section at the end of the ALTER TABLE page.
No objections to the suggested addition and the location of the
addition (paragraph of SET DATA TYPE rather than "Notes"), but I think
that the phrasing could be better:
"The column's statistics are removed, hence a follow-up ANALYZE is
suited to update the statistics to the new column type."
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../YW5BDcUqz8kucDPr@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ALTER TABLE ... SET DATA TYPE removes statistics
2021-10-08 09:03 ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 20:15 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Euler Taveira <euler@eulerto.com>
2021-10-19 03:52 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Michael Paquier <michael@paquier.xyz>
@ 2021-10-19 15:16 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-20 03:24 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Michael Paquier <michael@paquier.xyz>
2021-10-22 15:20 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Euler Taveira <euler@eulerto.com>
2021-10-26 14:24 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
0 siblings, 3 replies; 8+ messages in thread
From: Alvaro Herrera @ 2021-10-19 15:16 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Euler Taveira <euler@eulerto.com>; nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On 2021-Oct-19, Michael Paquier wrote:
> On Mon, Oct 18, 2021 at 05:15:59PM -0300, Euler Taveira wrote:
> > I agree that it might surprise an user and it would be good to document it.
> > However, it does not belong to the description. I would add it to the Notes
> > section at the end of the ALTER TABLE page.
>
> No objections to the suggested addition and the location of the
> addition (paragraph of SET DATA TYPE rather than "Notes"), but I think
> that the phrasing could be better:
> "The column's statistics are removed, hence a follow-up ANALYZE is
> suited to update the statistics to the new column type."
Dunno, putting it in the middle of the existing paragraph looks odd to
me. I would put it in a separate one instead, as in the attached.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Attachments:
[text/x-diff] datatype.patch (645B, ../../202110191516.eufiqzha2etw@alvherre.pgsql/2-datatype.patch)
download | inline diff:
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 81291577f8..86fc15c8b3 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -198,6 +198,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
clause must be provided if there is no implicit or assignment
cast from old to new type.
</para>
+
+ <para>
+ When this form is used, the column's statistics are removed,
+ so running <command>ANALYZE</command> on the table afterwards
+ is recommended.
+ </para>
</listitem>
</varlistentry>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ALTER TABLE ... SET DATA TYPE removes statistics
2021-10-08 09:03 ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 20:15 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Euler Taveira <euler@eulerto.com>
2021-10-19 03:52 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Michael Paquier <michael@paquier.xyz>
2021-10-19 15:16 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2021-10-20 03:24 ` Michael Paquier <michael@paquier.xyz>
2 siblings, 0 replies; 8+ messages in thread
From: Michael Paquier @ 2021-10-20 03:24 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Euler Taveira <euler@eulerto.com>; nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Tue, Oct 19, 2021 at 12:16:44PM -0300, Alvaro Herrera wrote:
> Dunno, putting it in the middle of the existing paragraph looks odd to
> me. I would put it in a separate one instead, as in the attached.
Fine by me. Thanks!
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../YW+L6awMBHtY5ZHD@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ALTER TABLE ... SET DATA TYPE removes statistics
2021-10-08 09:03 ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 20:15 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Euler Taveira <euler@eulerto.com>
2021-10-19 03:52 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Michael Paquier <michael@paquier.xyz>
2021-10-19 15:16 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2021-10-22 15:20 ` Euler Taveira <euler@eulerto.com>
2 siblings, 0 replies; 8+ messages in thread
From: Euler Taveira @ 2021-10-22 15:20 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; Michael Paquier <michael@paquier.xyz>; +Cc: nikolai.berkoff <nikolai.berkoff@pm.me>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Tue, Oct 19, 2021, at 12:16 PM, Alvaro Herrera wrote:
> On 2021-Oct-19, Michael Paquier wrote:
>
> > On Mon, Oct 18, 2021 at 05:15:59PM -0300, Euler Taveira wrote:
> > > I agree that it might surprise an user and it would be good to document it.
> > > However, it does not belong to the description. I would add it to the Notes
> > > section at the end of the ALTER TABLE page.
> >
> > No objections to the suggested addition and the location of the
> > addition (paragraph of SET DATA TYPE rather than "Notes"), but I think
> > that the phrasing could be better:
> > "The column's statistics are removed, hence a follow-up ANALYZE is
> > suited to update the statistics to the new column type."
>
> Dunno, putting it in the middle of the existing paragraph looks odd to
> me. I would put it in a separate one instead, as in the attached.
LGTM. I'm not sure if it is worth mentioning that the user needs to ANALYZE
only the removed column instead of the whole table. Anyway, it is good to have
statistics from the same snapshot.
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ALTER TABLE ... SET DATA TYPE removes statistics
2021-10-08 09:03 ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 20:15 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Euler Taveira <euler@eulerto.com>
2021-10-19 03:52 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Michael Paquier <michael@paquier.xyz>
2021-10-19 15:16 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2021-10-26 14:24 ` nikolai.berkoff <nikolai.berkoff@pm.me>
2021-11-26 17:39 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Alvaro Herrera <alvherre@alvh.no-ip.org>
2 siblings, 1 reply; 8+ messages in thread
From: nikolai.berkoff @ 2021-10-26 14:24 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Michael Paquier <michael@paquier.xyz>; Euler Taveira <euler@eulerto.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, October 19th, 2021 at 16:16, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> I would put it in a separate one instead, as in the attached.
Thank you, this reads better.
Is there a reason in the docs there is sometimes
<link linkend="sql-analyze"><command>ANALYZE</command></link>
and sometimes only
<command>ANALYZE</command>
? I prefer the link if there is no hard rule.
Regards,
Nikolai
Attachments:
[application/pgp-keys] publickey - nikolai.berkoff@pm.me - 0xD189792D.asc (1.8K, ../../GV3rRfobdQ0wG9-xP_YWxQxpKM_XXNlUMsFi-nYeJIohckxEMVeVR3lWXr21Tb3Wp0KxtcuQnvv5SIUjON4v4LuxPz08KHs6oE9sH_aL1Ds=@pm.me/2-publickey%20-%20nikolai.berkoff@pm.me%20-%200xD189792D.asc)
download
[application/pgp-signature] signature.asc (507B, ../../GV3rRfobdQ0wG9-xP_YWxQxpKM_XXNlUMsFi-nYeJIohckxEMVeVR3lWXr21Tb3Wp0KxtcuQnvv5SIUjON4v4LuxPz08KHs6oE9sH_aL1Ds=@pm.me/3-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ALTER TABLE ... SET DATA TYPE removes statistics
2021-10-08 09:03 ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 20:15 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Euler Taveira <euler@eulerto.com>
2021-10-19 03:52 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Michael Paquier <michael@paquier.xyz>
2021-10-19 15:16 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-26 14:24 ` Re: ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
@ 2021-11-26 17:39 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2021-11-26 17:39 UTC (permalink / raw)
To: nikolai.berkoff <nikolai.berkoff@pm.me>; +Cc: Michael Paquier <michael@paquier.xyz>; Euler Taveira <euler@eulerto.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On 2021-Oct-26, nikolai.berkoff wrote:
> Thank you, this reads better.
> Is there a reason in the docs there is sometimes
>
> <link linkend="sql-analyze"><command>ANALYZE</command></link>
>
> and sometimes only
>
> <command>ANALYZE</command>
> ? I prefer the link if there is no hard rule.
I pushed this on November 5th; it is commit
df80f9da5c6541e744eeb20eaca919c7fc189999, including this suggestion to
cross-ref to ANALYZE.
Thanks for reporting this
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-11-26 17:39 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-08 09:03 ALTER TABLE ... SET DATA TYPE removes statistics nikolai.berkoff <nikolai.berkoff@pm.me>
2021-10-18 20:15 ` Euler Taveira <euler@eulerto.com>
2021-10-19 03:52 ` Michael Paquier <michael@paquier.xyz>
2021-10-19 15:16 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-10-20 03:24 ` Michael Paquier <michael@paquier.xyz>
2021-10-22 15:20 ` Euler Taveira <euler@eulerto.com>
2021-10-26 14:24 ` nikolai.berkoff <nikolai.berkoff@pm.me>
2021-11-26 17:39 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox