将列从时间戳更改为timestamptz锁定表？ [英] Will changing column from timestamp to timestamptz lock the table?

问题描述

我想将一个列从 timestamp （没有时区）迁移到 timestamptz 类型。

我在Postgres 9.3.9。

我需要知道这个操作是否会导致表重写）因为我的表很大，db是活的。

我在 9.2发行说明：

增加varchar或varbit列的长度限制，或完全删除限制，不再需要表重写。类似地，增加数字列的允许精度，或将列从受限数字更改为不受约束数字，不再需要表重写。

这听起来很有前途，但实际上并没有详细说明

如果此操作是要锁定表，我会感谢您建议如何解决这个问题

解决方案

首先，你似乎混淆了锁和表重写。发行说明中的​​注意事项介绍 表重写 - 始终使用 ACCESS EXCLUSIVE 锁定。

您需要：

  ALTER TABLE tbl ALTER ts_col TYPE timestamptz; 
  

除非您想在转换中设置特定时区，而不是会话的当前时区：

  ALTER TABLE tbl ALTER ts_col TYPE timestamptz USING ts_col AT TIME ZONE'Europe / London'; 
  

请务必使用时区名称 在这种情况下，不是一个简单的偏移量或缩写。详情：

• 具有相同属性的时区名称在应用到时间戳记时会产生不同的结果


• 在Rails和PostgreSQL中完全忽略时区

文档：

ALTER TABLE 更改现有表的定义。有
几个子窗体如下所述。请注意，所需的锁级别
对于每个子窗体可能不同。 ACCESS EXCLUSIVE 锁定，除非明确指出
。

ALTER column_name TYPE data_type 采用这种 ACCESS EXCLUSIVE 锁定。虽然 timestamp 和 timestamptz 的内部存储格式是相同的，值通常由转换（取决于会话的时区设置）进行更改。 Postgres必须在表中写入每一行的新版本，因此这需要表重写。由于操作采用 ACCESS EXCLUSIVE 锁，因此无需保留旧行版本，转换后您将看不到死的元组。 p>

SQL Fiddle 演示时区设置对转换的作用。我还添加了一个将 varchar 转换为 text 的示例，其中不是 - 除非你移动到较短的长度修饰符。

_{注意我如何将输出转换为 text （ ts_col： ：text ），以保持JDBC层在sqlfiddle中添加一些更多的（通常不想要的！）表示魔术。}

并发事务在您的交易开始后尝试访问表格 等待此交易完成。

>可以尝试通过在后台准备新表来保持锁定时间，删除旧表并重命名新的表，但这将使并发事务失败，并显示以下错误：

错误：无法与OID打开关系123456

详细信息： / p>

• 填充新列的最佳方法在大表中？

timestamp / timestamptz

像 varchar 或 numeric timestamp ， time 和 interval types 允许强>。例如，时间戳在默认情况下最多可以存储6位小数，但您可以修改： timestamp（0）不存储小数秒。

从 varchar（10） - > varchar（20）需要重写表，因为源类型中的值也保证在目标类型中适合（二进制兼容）。

timestamp（0） - > timestamp 或 timestamptz（3） - > timestamptz（5）。这是手册在发布的引用段落中提及的内容注释：

在类似的情况下，也避免了表重写，涉及
/ code>， timestamp 和 timestamptz 。




I want to migrate a column from timestamp (no timezone) to timestamptz type.

I'm on Postgres 9.3.9.

I need to know if this operation will cause a table rewrite (lock the table) as my table is large and the db is live.

I found this in the 9.2 release notes:

Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite. Similarly, increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite. Table rewrites are also avoided in similar cases involving the interval, timestamp, and timestamptz types.

It sounds promising but doesn't actually detail what 'similar cases' might be.

If this operation is going to lock the table I'd appreciate suggestions for how to work around this on a live db without interrupting service.

解决方案

First of all, you seem to be confusing locks and table rewrites. The note in the release notes talks about table rewrites - which always take an ACCESS EXCLUSIVE lock on the table. But here are many other operations that also take locks.

You would need:

ALTER TABLE tbl ALTER ts_col TYPE timestamptz;

Unless you want to set a specific time zone in the conversion, not the current time zone of your session:

ALTER TABLE tbl ALTER ts_col TYPE timestamptz USING ts_col AT TIME ZONE 'Europe/London';

Be sure to use a time zone name in this case, not a simple offset nor an abbreviation. Details:

• Time zone names with identical properties yield different result when applied to timestamp
• Ignoring timezones altogether in Rails and PostgreSQL

The documentation:

ALTER TABLE changes the definition of an existing table. There are several subforms described below. Note that the lock level required may differ for each subform. An ACCESS EXCLUSIVE lock is held unless explicitly noted.

ALTER column_name TYPE data_type takes such an ACCESS EXCLUSIVE lock. While the internal storage format of timestamp and timestamptz are identical, the internal value is typically changed by the conversion (depending on the time zone setting of the session!). Postgres has to write a new version of every row in the table, so this requires a table rewrite as well. Since the operation took an ACCESS EXCLUSIVE lock there is no need to keep old row versions and you will see no dead tuples after the conversion.

SQL Fiddle demonstrating the role of the time zone setting on the conversion. I also added an example converting varchar to text, which does not require a table rewrite - unless you move to a shorter length modifier.
_{Note how I cast the output to text (ts_col::text) to keep the JDBC layer in sqlfiddle from adding some more (generally unwanted!) representation magic.}

Concurrent transactions trying to access the table after your transaction has started will wait until this transaction has finished.

You could try to keep the lock time short by preparing a new table in the background, delete the old table and rename the new, but this will make concurrent transactions fail with an error like:

ERROR: could not open relation with OID 123456

Details:

• Best way to populate a new column in a large table?

"Similar cases" for timestamp / timestamptz

Like varchar or numeric timestamp, time and interval types allow modifiers. For example, timestamps store up to 6 digits for fractional seconds per default, but you can modify that: timestamp(0) does not store fractional seconds.

The conversion from varchar(10) -> varchar(20) does not requires a table rewrite because values in the source type are guaranteed to fit (binary compatible) in the target type as well.

The same is true for timestamp (0) -> timestamp or timestamptz(3) -> timestamptz(5). That's what the manual refers to in the quoted passage in the release notes:

Table rewrites are also avoided in similar cases involving the interval, timestamp, and timestamptz types.

这篇关于将列从时间戳更改为timestamptz锁定表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！