<blockquote>
<p>really, even if the system is running on UTC so that nothing changes?</p>
</blockquote>
<p>There are a few pointers in the documentation and source code:</p>
<p>According to: <a href="https://doxygen.postgresql.org/tablecmds_8c_source.html#l09176" rel="nofollow">https://doxygen.postgresql.org/tablecmds_8c_source.html#l09176</a> (points to relevant Postgres source code):</p>
<pre><code> * When the data type of a column is changed, a rewrite might not be required
* if the new type is sufficiently identical to the old one, and the USING
* clause isn't trying to insert some other value. It's safe to skip the
* rewrite if the old type is binary coercible to the new type, or if the
* new type is an unconstrained domain over the old type. In the case of a
* constrained domain, we could get by with scanning the table and checking
* the constraint rather than actually rewriting it, but we don't currently
* try to do that.
*/
</code></pre>
<p>According to <a href="https://www.postgresql.org/docs/9.5/sql-altertable.html" rel="nofollow">https://www.postgresql.org/docs/9.5/sql-altertable.html</a></p>
<p><em>Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt.</em></p>
<p>My conclusion would be (TBC):</p>
<ul>
<li>In a pure UTC-0 set up (no summer/winter time), the value should not change, timestamp & timestamptz are not constrained, hence no rewrite should be necessary. An index rebuild <em>is</em> necessary, though.</li>
<li>In a non-UTC set up, a rewrite is necessary, as the value changes (<code>timestamp</code> has to be converted to the local timezone). Index rebuild is also necessary.</li>
</ul>
<details>
<summary>Why is the timestamp conversion not binary-coercible?</summary>
<pre><code>openstreetmap=# select * from pg_cast where castsource='timestamp'::regtype and casttarget='timestamptz'::regtype;
castsource | casttarget | castfunc | castcontext | castmethod
------------+------------+----------+-------------+------------
1114 | 1184 | 2028 | i | f
(1 row)
</code></pre>
<p>According to <a href="https://www.postgresql.org/docs/9.5/catalog-pg-cast.html" rel="nofollow">https://www.postgresql.org/docs/9.5/catalog-pg-cast.html</a></p>
<p>Field castmethod: Indicates how the cast is performed.</p>
<ul>
<li>f means that the function specified in the castfunc field is used.</li>
<li>i means that the input/output functions are used.</li>
<li>b means that the types are binary-coercible, thus no conversion is required.</li>
</ul>
</details>
<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />You are receiving this because you are subscribed to this thread.<br />Reply to this email directly, <a href="https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441428320">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/ABWnLYm8LhyEBoJWu_9SjopaqMjOKwTCks5uymjzgaJpZM4A1Xpw">mute the thread</a>.<img src="https://github.com/notifications/beacon/ABWnLWRZy2xDKZlrI8wQDJ7wYJ8Ar8MUks5uymjzgaJpZM4A1Xpw.gif" height="1" width="1" alt="" /></p>
<script type="application/json" data-scope="inboxmarkup">{"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/openstreetmap/openstreetmap-website","title":"openstreetmap/openstreetmap-website","subtitle":"GitHub repository","main_image_url":"https://assets-cdn.github.com/images/email/message_cards/header.png","avatar_image_url":"https://assets-cdn.github.com/images/email/message_cards/avatar.png","action":{"name":"Open in GitHub","url":"https://github.com/openstreetmap/openstreetmap-website"}},"updates":{"snippets":[{"icon":"PERSON","message":"@mmd-osm in #375: \u003e really, even if the system is running on UTC so that nothing changes?\r\n\r\nThere are a few pointers in the documentation and source code:\r\n\r\nAccording to: https://doxygen.postgresql.org/tablecmds_8c_source.html#l09176 (points to relevant Postgres source code):\r\n\r\n```\r\n * When the data type of a column is changed, a rewrite might not be required\r\n * if the new type is sufficiently identical to the old one, and the USING\r\n * clause isn't trying to insert some other value. It's safe to skip the\r\n * rewrite if the old type is binary coercible to the new type, or if the\r\n * new type is an unconstrained domain over the old type. In the case of a\r\n * constrained domain, we could get by with scanning the table and checking\r\n * the constraint rather than actually rewriting it, but we don't currently\r\n * try to do that.\r\n */\r\n```\r\n\r\nAccording to https://www.postgresql.org/docs/9.5/sql-altertable.html\r\n\r\n_Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt._\r\n\r\nMy conclusion would be (TBC):\r\n\r\n* In a pure UTC-0 set up (no summer/winter time), the value should not change, timestamp \u0026 timestamptz are not constrained, hence no rewrite should be necessary. An index rebuild *is* necessary, though.\r\n* In a non-UTC set up, a rewrite is necessary, as the value changes (`timestamp` has to be converted to the local timezone). Index rebuild is also necessary.\r\n\r\n\r\n\u003cdetails\u003e\r\n\u003csummary\u003eWhy is the timestamp conversion not binary-coercible?\u003c/summary\u003e\r\n\r\n```\r\nopenstreetmap=# select * from pg_cast where castsource='timestamp'::regtype and casttarget='timestamptz'::regtype;\r\n castsource | casttarget | castfunc | castcontext | castmethod \r\n------------+------------+----------+-------------+------------\r\n 1114 | 1184 | 2028 | i | f\r\n(1 row)\r\n```\r\n\r\n\r\nAccording to https://www.postgresql.org/docs/9.5/catalog-pg-cast.html\r\n\r\nField castmethod: Indicates how the cast is performed. \r\n- f means that the function specified in the castfunc field is used. \r\n- i means that the input/output functions are used.\r\n- b means that the types are binary-coercible, thus no conversion is required.\r\n\u003c/details\u003e"}],"action":{"name":"View Issue","url":"https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441428320"}}}</script>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441428320",
"url": "https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441428320",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
},
{
"@type": "MessageCard",
"@context": "http://schema.org/extensions",
"hideOriginalBody": "false",
"originator": "AF6C5A86-E920-430C-9C59-A73278B5EFEB",
"title": "Re: [openstreetmap/openstreetmap-website] Switch to TIMESTAMP WITH TIME ZONE for current_* tables (#375)",
"sections": [
{
"text": "",
"activityTitle": "**mmd**",
"activityImage": "https://assets-cdn.github.com/images/email/message_cards/avatar.png",
"activitySubtitle": "@mmd-osm",
"facts": [
]
}
],
"potentialAction": [
{
"name": "Add a comment",
"@type": "ActionCard",
"inputs": [
{
"isMultiLine": true,
"@type": "TextInput",
"id": "IssueComment",
"isRequired": false
}
],
"actions": [
{
"name": "Comment",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"IssueComment\",\n\"repositoryFullName\": \"openstreetmap/openstreetmap-website\",\n\"issueId\": 375,\n\"IssueComment\": \"{{IssueComment.value}}\"\n}"
}
]
},
{
"name": "Close issue",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"IssueClose\",\n\"repositoryFullName\": \"openstreetmap/openstreetmap-website\",\n\"issueId\": 375\n}"
},
{
"targets": [
{
"os": "default",
"uri": "https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441428320"
}
],
"@type": "OpenUri",
"name": "View on GitHub"
},
{
"name": "Unsubscribe",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"MuteNotification\",\n\"threadId\": 13990512\n}"
}
],
"themeColor": "26292E"
}
]</script>