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

Eliminating phase 3 requirement for varlen increases via ALTER COLUMN

From: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Subject: Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
Date: 2006-10-26 18:31:56
Message-ID: 36e682920610261131o19a4e45emf65327988017802e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
The attached patch handles the simple case where a user wants to
increase the user-defined storage size of a variable length object,
such as VARCHAR or NUMERIC, without having to rebuild the table.

It does so by verifying that no transform was defined and testing
whether the user simply requested an increase to the size of a
variable length column.  If so, it skips type coercion and no longer
performs ATRewriteTables; instead, it allows ATExecAlterColumnType to
update the catalog to reflect said increase to the attribute's typmod
(as it currently does) and skips phase 3.  The former way to perform
this was by manually updating the catalog.

The current case of ALTERing a column to it's current data type
specification (often used to forcibly reclaim dropped column space)
has been maintained.

Use case:
CREATE TABLE test_tbl (
    test_id    NUMERIC(10) NOT NULL,
    test_name VARCHAR(32) NOT NULL,
    PRIMARY KEY (test_id));

ALTER TABLE test_tbl ALTER COLUMN test_name TYPE VARCHAR(64);

I can't find any case where this doesn't work... but perhaps you guys
know of some.

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 2nd Floor            | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

Attachment: attypmodvlincfix82.diff
Description: text/x-patch (3.8 KB)

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-10-26 18:32:07
Subject: Re: GUC description cleanup
Previous:From: Jim C. NasbyDate: 2006-10-26 18:27:33
Subject: Re: [HACKERS] Replication documentation addition

pgsql-patches by date

Next:From: Tom LaneDate: 2006-10-26 18:32:07
Subject: Re: GUC description cleanup
Previous:From: Neil ConwayDate: 2006-10-26 17:47:24
Subject: GUC description cleanup

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