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
Attachments:
set_data_type.patchtext/x-patch; filename=set_data_type.patch; name=set_data_type.patchDownload+2-1
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/
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
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:
datatype.patchtext/x-diff; charset=utf-8Download+6-0
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 - [cid:c58e7220-eb5f-452f-9c51-2b24b75b1f8f]
• www.qualis-consulting.com<http://www.qualis-consulting.com/> •
_________________________________
Attachments:
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
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>hello Pascal,</p>
<p><br>
</p>
<p>I do it in 2020 from postgresql 9.6 to 12 .</p>
<p><br>
</p>
<p>I do with a shell linux script using pd_dump and pg_restore from
a database save.</p>
<p><br>
</p>
<p>I first mount a new postgresql 12 server, prepare with new hard
parameters in postgresql.conf, pg_hba.conf, .pgpass and all of the
scripts and clients i need (pgadmin, phpadmin, shape2pgsql) with
new IP.</p>
<p><br>
</p>
<p>When It's ok, with crontab i execute a script dump and restore
from old server to new in a week-end and on monday morning when i
see the new server with 1To data is ok i reverse IP of 2 serveurs.</p>
<p><br>
</p>
<p>take care to prepare the new server with care, and do test
migration to prevent (without reverse IP) data loss or
missconfiguration. You need to know how much time takes the
process to be cool.</p>
<p><br>
</p>
<p>Have a good day!<br>
</p>
<div class="moz-signature">
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 2.3 (Win32)">
<meta name="AUTHOR" content="dreal-ra">
<meta name="CREATED" content="20110608;16534339">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGED" content="20120220;8494106">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<style type="text/css">
<!--
@page { size: 21cm 29.7cm; margin: 2cm }
P { margin-bottom: 0.21cm }
-->
</style>
<pre><b>Yann Convers</b>
Gestionnaire de l'infrastructure, des référentiels et des outils<p>
<font color="#000000"><b>DREAL Auvergne-Rhône-Alpes</b></font></p>
<pre>Service Connaissance, Information, Développement Durable, Autorité Environnementale - Pole Système d Information Géographique
Tél : 04 26 28 67 89
courriel : <a href="mailto:yann.convers@developpement-durable.gouv.fr">yann.convers@developpement-durable.gouv.fr</a></pre>adresse de la boite d’unité : <a href="mailto:sig.dreal-ara@developpement-durable.gouv.fr">boite unité</a>
<p>Pour toute commande interne de prestations, merci d utilisez le formulaire suivant : <a href="http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new">http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new</a></p>
<b>Adresse postale :</b> DREAL Auvergne-Rhône-Alpes, CIDDAE - SIG
69453 LYON CEDEX 06
<b>Adresse physique :</b> 5
place Jules Ferry (immeuble Lugdunum - métro Brotteaux) -
69006 Lyon
<b>Standard :</b> 04 26 28 60 00
<b>Liens :</b><a href="http://intra.dreal-rhone-alpes.i2/">Site
Intranet</a> <a href="http://www.auvergne-rhone-alpes.developpement-durable.gouv.fr/">Site
Internet</a>
<p style="margin-bottom: 0cm">
</p>
</pre>
</div>
<div class="moz-cite-prefix">Le 19/10/2021 à 17:41, >
pascal.crozet (par Internet, dépôt
<a class="moz-txt-link-abbreviated" href="mailto:pgsql-docs-owner+m63745-354560@lists.postgresql.org">pgsql-docs-owner+m63745-354560@lists.postgresql.org</a>) a écrit :<br>
</div>
<blockquote
cite="mid:PAXP251MB00981D48C6A39C9607449A71A0BD9@PAXP251MB0098.EURP251.PROD.OUTLOOK.COM"
type="cite">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
<div style="font-family: "Lucida Console", Monaco,
monospace; font-size: 10pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
Hi PG experts,</div>
<div style="font-family: "Lucida Console", Monaco,
monospace; font-size: 10pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
<br>
</div>
<div style="font-family: "Lucida Console", Monaco,
monospace; font-size: 10pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
During next year, I've the goal to migrate an instance of 4
databases from PostGreSql 9.5 to 12 or maybe more 14.</div>
<div style="font-family: "Lucida Console", Monaco,
monospace; font-size: 10pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
What's the best tools I've to use ? Do a simple pg_dumpall in
text format can archive this goal ?</div>
<div style="font-family: "Lucida Console", Monaco,
monospace; font-size: 10pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
What other files more than pg_hba.conf and postgresql.conf do I
need to checK ?<br>
</div>
<div>
<div style="font-family: "Lucida Console", Monaco,
monospace; font-size: 10pt; color: rgb(0, 0, 0);">
And what issues can I find during this upgrade ?<br>
</div>
<div id="Signature">
<div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<p style="color: rgb(32, 31, 30); text-align: start;
background-color: rgb(255, 255, 255); font-size: 11pt;
font-family: Calibri, sans-serif; margin: 0px;">
<span style="margin: 0px; font-weight: normal;
font-family: Calibri, sans-serif, serif, EmojiFont;
color: windowtext;"> </span></p>
<p style="color: rgb(32, 31, 30); text-align: start;
font-size: 11pt; font-family: Calibri, sans-serif;
background-color: white; margin: 0px;">
<b><span style="margin: 0px; font-size: 12pt; color:
rgb(33, 33, 33);">_________________________________</span></b><span
style="margin: 0px; font-size: 12pt; color: black;"></span></p>
<p style="color: rgb(32, 31, 30); font-size: 15px;
text-align: start; background-color: white;">
<span style="margin: 0px; font-size: 12pt; color: black;
background-color: white;">Cordialement,<span> </span><b>Pascal
CROZET</b></span><span style="margin: 0px;
font-size: 12pt; color: black;"><br>
</span><b><span style="margin: 0px; font-size: 12pt;
color: rgb(117, 123, 128);" lang="en-US">DBA</span></b><b><span
style="margin: 0px; font-size: 10pt; color: rgb(117,
123, 128);" lang="en-US"><span> </span> -<span> </span></span></b><b><span
style="margin: 0px; font-size: 10pt; color: rgb(117,
123, 128);" lang="en-US"><br>
</span></b><span style="margin: 0px; font-size: 12pt;
color: rgb(112, 173, 71);">•</span><span
style="margin: 0px; font-size: 12pt; color: rgb(33,
33, 33);"> </span><span style="margin: 0px; font-size:
12pt; color: black;"><a moz-do-not-send="true"
href="http://www.qualis-consulting.com/"
target="_blank" rel="noopener noreferrer"
style="margin:0px"><span style="margin: 0px;
font-size: 9pt; color: rgb(127, 127, 127);">www.qualis-consulting.com</span></a></span><span
style="margin: 0px; font-size: 12pt; color: rgb(127,
127, 127);"> </span><span style="margin: 0px;
font-size: 12pt; color: rgb(112, 173, 71);">•</span></p>
<p style="color: rgb(32, 31, 30); font-size: 15px;
text-align: start; background-color: white;">
<b><span style="margin: 0px; font-size: 12pt; color:
rgb(33, 33, 33);">_________________________________</span></b></p>
</div>
</div>
</div>
</div>
</blockquote>
<br>
</body>
</html>
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/
‐‐‐‐‐‐‐ 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:
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/