Re: ALTER TYPE 1: recheck index-based constraints

On Sun, Jan 9, 2011 at 5:00 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> When ALTER TABLE rewrites a table, it reindexes, but the reindex does not
> revalidate UNIQUE/EXCLUDE constraints.  This behaves badly in cases like this,
> neglecting to throw an error on the new UNIQUE violation:
>
> CREATE TABLE t (c numeric UNIQUE);
> INSERT INTO t VALUES (1.1),(1.2);
> ALTER TABLE t ALTER c TYPE int;
>
> The comment gave a reason for skipping the checks: it would cause deadlocks when
> we rewrite a system catalog.  So, this patch changes things to only skip the
> check for system catalogs.

It looks like this behavior was introduced by Tom's commit
1ddc2703a936d03953657f43345460b9242bbed1 on 2010-02-07, and it appears
to be quite broken. The behavior is reasonable in the case of VACUUM
FULL and CLUSTER, but your example demonstrates that it's completely
broken in the case of ALTER TABLE. This strikes me as something we
need to fix in REL9_0_STABLE as well as master, and my gut feeling is
that we ought to fix it not by excluding system relations but by
making ALTER TABLE work differently from VACUUM FULL and CLUSTER.
There's an efficiency benefit to skipping this even on normal
relations in cases where it isn't necessary, and it shouldn't be
necessary if we're rewriting the rows unchanged.

Also, you need to start adding these patches to the CF app.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company