Thread: ALTER TABLE ... SET DATA TYPE removes statistics
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
Attachment
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.cATExecAlterColumnType/** 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.
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
Attachment
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/
Attachment
Hi PG experts,
During next year, I've the goal to migrate an instance of 4 databases from PostGreSql 9.5 to 12 or maybe more 14.
What's the best tools I've to use ? Do a simple pg_dumpall in text format can archive this goal ?
What other files more than pg_hba.conf and postgresql.conf do I need to checK ?
And what issues can I find during this upgrade ?
_________________________________
Cordialement, Pascal CROZET
DBA -
• www.qualis-consulting.com •
_________________________________
Attachment
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
Attachment
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 tome. 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.
‐‐‐‐‐‐‐ 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
Attachment
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/