[openstreetmap/openstreetmap-website] Switch to TIMESTAMP WITH TIME ZONE for current_* tables (#375)

mmd notifications at github.com
Sun Nov 25 09:54:59 UTC 2018

> really, even if the system is running on UTC so that nothing changes?

There are a few pointers in the documentation and source code:

According to: https://doxygen.postgresql.org/tablecmds_8c_source.html#l09176 (points to relevant Postgres source 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.

According to https://www.postgresql.org/docs/9.5/sql-altertable.html

_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._

My conclusion would be (TBC):

* 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 *is* necessary, though.
* 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.

<summary>Why is the timestamp conversion not binary-coercible?</summary>

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)

According to https://www.postgresql.org/docs/9.5/catalog-pg-cast.html

Field castmethod: Indicates how the cast is performed. 
- f means that the function specified in the castfunc field is used. 
- i means that the input/output functions are used.
- b means that the types are binary-coercible, thus no conversion is required.

You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20181125/3cf819a9/attachment.html>

More information about the rails-dev mailing list