Avoid index rebuilds for no-rewrite ALTER TABLE ALTER TYPE

From: Noah Misch <noah(at)leadboat(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Avoid index rebuilds for no-rewrite ALTER TABLE ALTER TYPE
Date: 2011-06-15 05:03:37
Message-ID: 20110615050337.GA10879@tornado.leadboat.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Previous version (at7-alt-index-opfamily.patch):
http://archives.postgresql.org/message-id/20110113230124.GA18733@tornado.gateway.2wire.net
Design:
http://archives.postgresql.org/message-id/20110524104029.GB18831@tornado.gateway.2wire.net

Patches committed in 2011CF1 allow ALTER TABLE ... ALTER ... SET DATA TYPE to
avoid rewriting the table in certain cases. We still rebuild all indexes and
recheck all foreign key constraints dependent on the changing column. This
patch avoids the rebuild for some indexes, using the operator family facility
to identify when that is safe.

Changes since last version:

* Instead of noting in comments that the operator family contracts do not make
the guarantees I desire and opining that the code is fine in practice anyway,
I amend those contracts in the documentation. See the aforementioned design
explanation, but note that its third and fifth sentences are incorrect. I
settled on requiring identical behavior among operators in the family after
implicit casts (of any castmethod) or binary coercion casts (of any
castcontext) on operands. I really only need the requirement for binary
coercion casts, but implicit casts seem like a obvious extension.

* Update for per-column collations. Index collations need to match.

* We don't assign meaning to GIN or GiST operator family groupings. For
access methods other than btree and hash, require an index rebuild unless the
operator classes match exactly. Even if we could do otherwise, it would
currently be academic; GIN "array_ops" is the only such family with more than
one constituent operator class. The only practical case I've observed to be
helped here is a conversion like varchar(2)[] -> varchar(4)[] with a GIN index
on the column; operator class comparison covers that fine.

* Remove support for avoiding foreign key constraint revalidation. This is
orthogonal to the index case, though they share many principles. If we get
this committed, I will submit a small patch for foreign keys at a later date.

* Original patch was submitted in two forms for comment on the relative
merits. The first form had tablecmds.c updating catalog entries pertaining to
an old index until it matched how the index would be recreated with the new
column data type. The second form actually dropped and recreated the index
using the usual facilities, then reattached the old RelFileNode to the "new"
index. Nobody commented, but I've come around to thinking the second approach
is clearly better. Therefore, I'm submitting only that.

* Change the division of labor between tablecmds.c and indexcmds.c.
* Test cases removed per 2011CF1 discussion.
* Sync with variable names changed since last submission.

This patch is fully independent of the "Eliding no-op varchar length
coercions" patch I've posted recently, though they do have synergy.

Thanks,
nm

Attachment Content-Type Size
at-index-opfamily-v2.patch text/plain 24.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jesper Krogh 2011-06-15 05:30:14 Re: pg_upgrade using appname to lock out other users
Previous Message Robert Haas 2011-06-15 04:19:45 Re: procpid?