Re: ALTER TYPE 1: recheck index-based constraints

On Tue, Jan 11, 2011 at 10:03:11PM -0500, Robert Haas wrote:
> On Sun, Jan 9, 2011 at 5:00 PM, Noah Misch <noah@leadboat.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.

Something like the attached?

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

Done for all.