Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL

Поиск
Список
Период
Сортировка
От Amit Langote
Тема Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL
Дата
Msg-id CA+HiwqE35_+z0_gFw9mp=_0MLgPfb=+QY910LG7C-sBNTvqdgg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hi,

When I do the following:

ALTER TABLE table ADD COLUMN numeric(x) DEFAULT NULL;

The table is rewritten whereas notes section on the manual page for
ALTER TABLE says otherwise (which holds true for most of the cases
though).

http://www.postgresql.org/docs/devel/static/sql-altertable.html

As an example,

postgres=# create table test as select generate_series(1,1000000) as a;
SELECT 1000000

postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16709
(1 row)

postgres=# alter table test add column b numeric(2) DEFAULT NULL;
ALTER TABLE

-- rewritten
postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16713
(1 row)

postgres=# alter table test add column c int DEFAULT NULL;
ALTER TABLE

-- not rewritten
postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16713
(1 row)

postgres=# alter table test add column d char(5) DEFAULT NULL;
ALTER TABLE

-- rewritten, again
postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16717


So, when the type of the new column has type modifier like numeric(x),
char(x) etc. do,  this happens.

Is this intentional and/or documented somewhere else? If not, should
it be documented?

--
Amit


В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: COPY v. java performance comparison
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL