X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 71E319DC9A4 for ; Wed, 22 Mar 2006 16:14:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40905-09 for ; Wed, 22 Mar 2006 16:14:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 350069DC9F3 for ; Wed, 22 Mar 2006 16:14:43 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k2MKEii0019260; Wed, 22 Mar 2006 15:14:44 -0500 (EST) To: "Kevin Grittner" cc: pgsql-docs@postgresql.org, "Robert Treat" Subject: Re: ALTER TABLE x ALTER COLUMN y TYPE z In-reply-to: <442158A9.EE98.0025.0@wicourts.gov> References: <44213BD5.EE98.0025.0@wicourts.gov> <200603221400.33513.xzilla@users.sourceforge.net> <442158A9.EE98.0025.0@wicourts.gov> Comments: In-reply-to "Kevin Grittner" message dated "Wed, 22 Mar 2006 14:01:13 -0600" Date: Wed, 22 Mar 2006 15:14:44 -0500 Message-ID: <19259.1143058484@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.113 required=5 tests=[AWL=0.113] X-Spam-Score: 0.113 X-Spam-Level: X-Archive-Number: 200603/31 X-Sequence-Number: 3488 "Kevin Grittner" writes: > The user comment's recommended technique includes this line: > ALTER TABLE tab DROP COLUMN temp_name; > Would this cause a table rewrite? (Not a rhetorical question. I > really don't know.) No, it wouldn't. The UPDATE is the only part that modifies the table contents. However, the arguments Robert pointed to had to do with concurrent access to the table, and somehow I am not seeing the use-case for ALTER COLUMN TYPE on a table that's being actively used by other clients. It seems unlikely that you could do that without needing to also update your client software. I'd be a tad worried about stale-cached-plan problems too, in current PG releases. Finally, since the first ALTER will take an exclusive lock that won't be released until COMMIT, this approach doesn't avoid the problem of holding exclusive lock for a long time. regards, tom lane