Обсуждение: Is it safe to increase pg_attribute.atttypmod ?

Hi-

We need to increase the length of a varchar field, and couldn't find a way
to do it using "alter table". Since the alternative is copying & re-creating
a very large table, we starting poking around under the hood & found that
pg_attribute.atttypmod seems to be always equal to the length plus 4 in the
case of varchar fields.

We created a test table and altered the varchar length on a field by
increasing pg_attribute.atttypmod, and the length seemed to change and act
as expected.

The update to change a varchar(10) field to varchar(40) looked like this:

update pg_attribute set atttypmod = 44
where attrelid = ( select oid from pg_class where relname = 'test' )
and attname = 'oldtest' ;

Is this a smart thing to do?

Are there other hidden related bits of data that will come back to haunt us
later?

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/

"Nick Fankhauser" <nickf@ontko.com> writes:
> The update to change a varchar(10) field to varchar(40) looked like this:

> update pg_attribute set atttypmod = 44
> where attrelid = ( select oid from pg_class where relname = 'test' )
> and attname = 'oldtest' ;

> Is this a smart thing to do?

Yup, that's the standard hack.

            regards, tom lane

Nick Fankhauser wrote:
 > Hi-
 >
 > We need to increase the length of a varchar field, and couldn't find
 > a way to do it using "alter table". Since the alternative is copying
 > & re-creating a very large table, we starting poking around under
 > the hood & found that pg_attribute.atttypmod seems to be always
 > equal to the length plus 4 in the case of varchar fields.

Yes, the first 4 bytes are for an integer which represents length, and
the rest is the data itself.

 >
 > We created a test table and altered the varchar length on a field by
 > increasing pg_attribute.atttypmod, and the length seemed to change
 > and act as expected.
 >
 > The update to change a varchar(10) field to varchar(40) looked like
 > this:
 >
 > update pg_attribute set atttypmod = 44 where attrelid = ( select oid
 > from pg_class where relname = 'test' ) and attname = 'oldtest' ;
 >
 > Is this a smart thing to do?
 >
 > Are there other hidden related bits of data that will come back to
 > haunt us later?
 >

IIRC, this has been discussed a number of times before on one or another
of the PostgreSQL lists. The short answer, I think, is that the
procedure you describe should work just fine (but I do recommend you
take a look at the past threads on the subject). You may need to be a
bit more careful if you were going the other direction, i.e. varchar(40)
to varchar(10).

There was some talk of embedding this knowledge in an ALTER TABLE ALTER
COLUMN command for 7.3, but I haven't seen anything on that recently.

HTH,

Joe

Tom, Joe:

> Yup, that's the standard hack.

Thanks very much! This saved us hours.

-Nick