Thread: Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
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@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Attachment
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> 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.
This makes some really quite unacceptable assumptions about the meaning
and encoding of typmod; in fact I don't believe it's even correct for
NUMERIC, which uses a two-field encoding in typmod. NUMERIC(18,2) to
NUMERIC(20,0) cannot be a work-free conversion. Given the plans to
allow user-defined types to have their own interpretation of typmod,
you can't just blithely assume you know the semantics of a typmod change.
I'm also wondering what's the point of comparing attbyval etc when
you've already checked it's the same type OID.
regards, tom lane
Re: Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
From
"Jonah H. Harris"
Date:
On 10/26/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > This makes some really quite unacceptable assumptions about > the meaning and encoding of typmod ... True, so VARCHAR seems like the only one? That's the only one I've really encountered in the field on a fairly regular basis. > I'm also wondering what's the point of comparing attbyval etc when > you've already checked it's the same type OID. True. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> On 10/26/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This makes some really quite unacceptable assumptions about
>> the meaning and encoding of typmod ...
> True, so VARCHAR seems like the only one? That's the only one I've
> really encountered in the field on a fairly regular basis.
Well, you could either hardwire some specific cases for specific types
here, or think about inventing a general-purpose mechanism that would
let types register a function to report whether a given typmod change
requires actual work. I'm not sure it's worth the latter though.
One point worth thinking about is that varchar(any) --> text could be
a "free" coercion too, along with cases such as replacing a domain
by its base type. I think we can detect this today by the
expedient of noting whether the coercion ends up being just a
RelabelType expression --- I'm actually a bit surprised that that
knowledge doesn't seem to be in the code already.
OTOH ... RelabelType means the bits are the same but it doesn't imply
that the semantics of the bits are the same, eg, OID has a different
sort order than int4. So ISTM that in general it'd still be necessary
to recheck constraints and rebuild indexes. This might be a sufficient
reason for limiting the optimization to a few known-safe cases like
varchar/text, rather than trying to do it for any binary-compatible
conversion.
Another thought is that some cases would amount to checking constraints
but not changing any bits on-disk, as in replacing a base type with a
domain. Is it worth having these go through the non-rewriting code
path? How would we be sure we didn't need to rebuild indexes?
regards, tom lane
Re: [HACKERS] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
From
Gregory Stark
Date:
"Jonah H. Harris" <jonah.harris@gmail.com> writes: > On 10/26/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> This makes some really quite unacceptable assumptions about >> the meaning and encoding of typmod ... > > True, so VARCHAR seems like the only one? That's the only one I've > really encountered in the field on a fairly regular basis. I think what you want is to add a new method entry in pg_type to allow a type to declare a method to tell you whether a change is work-free or not. Then any type, even user-defined types, can allow some changes to be work-free and some not without exposing any implementation details outside the type. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
From
"Jonah H. Harris"
Date:
On 10/26/06, Gregory Stark <stark@enterprisedb.com> wrote: > I think what you want is to add a new method entry in pg_type to > allow a type to declare a method to tell you whether a change > is work-free or not. Then any type, even user-defined types, > can allow some changes to be work-free and some not without > exposing any implementation details outside the type. Seems like too much work for a fairly simple use-case. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/