Thread

Commits

Document that ALTER TABLE .. TYPE removes statistics
- df80f9da5c65 15.0 landed

ALTER TABLE ... SET DATA TYPE removes statistics

nikolai.berkoff <nikolai.berkoff@pm.me> — 2021-10-08T09:03:34Z

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

Re: ALTER TABLE ... SET DATA TYPE removes statistics

Euler Taveira <euler@eulerto.com> — 2021-10-18T20:15:59Z

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/

Re: ALTER TABLE ... SET DATA TYPE removes statistics

Michael Paquier <michael@paquier.xyz> — 2021-10-19T03:52:45Z

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

Re: ALTER TABLE ... SET DATA TYPE removes statistics

Alvaro Herrera <alvherre@alvh.no-ip.org> — 2021-10-19T15:16:44Z

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/

migrate from PG 9.5 to PG 12 or more

Pascal CROZET <pascal.crozet@qualis-consulting.com> — 2021-10-19T15:41:50Z

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/> •

_________________________________

Re: ALTER TABLE ... SET DATA TYPE removes statistics

Michael Paquier <michael@paquier.xyz> — 2021-10-20T03:24:25Z

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

Re: migrate from PG 9.5 to PG 12 or more

CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr> — 2021-10-20T05:20:06Z

<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, &gt;
      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: &quot;Lucida Console&quot;, Monaco,
        monospace; font-size: 10pt; color: rgb(0, 0, 0);
        background-color: rgb(255, 255, 255);">
        Hi PG experts,</div>
      <div style="font-family: &quot;Lucida Console&quot;, Monaco,
        monospace; font-size: 10pt; color: rgb(0, 0, 0);
        background-color: rgb(255, 255, 255);">
        <br>
      </div>
      <div style="font-family: &quot;Lucida Console&quot;, 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: &quot;Lucida Console&quot;, 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: &quot;Lucida Console&quot;, 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: &quot;Lucida Console&quot;, 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>

Re: ALTER TABLE ... SET DATA TYPE removes statistics

Euler Taveira <euler@eulerto.com> — 2021-10-22T15:20:55Z

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/

Re: ALTER TABLE ... SET DATA TYPE removes statistics

nikolai.berkoff <nikolai.berkoff@pm.me> — 2021-10-26T14:24:01Z

‐‐‐‐‐‐‐ 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

Re: ALTER TABLE ... SET DATA TYPE removes statistics

Alvaro Herrera <alvherre@alvh.no-ip.org> — 2021-11-26T17:39:18Z

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/