Skip site navigation (1) Skip section navigation (2)

Re: ALTER TYPE 1: recheck index-based constraints

From: Noah Misch <noah(at)leadboat(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: ALTER TYPE 1: recheck index-based constraints
Date: 2011-01-12 13:14:51
Message-ID: 20110112131451.GA16787@tornado.leadboat.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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(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.

Something like the attached?

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

Done for all.

In response to

Responses

pgsql-hackers by date

Next:From: Alexey KlyukinDate: 2011-01-12 13:34:06
Subject: Re: arrays as pl/perl input arguments [PATCH]
Previous:From: Alexey KlyukinDate: 2011-01-12 13:14:17
Subject: Re: arrays as pl/perl input arguments [PATCH]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group