On 4/17/19 8:34 AM, Tim Kane wrote:
On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
The table definition and the size of the data set would help with
interpreting the below.
The below example shows the issue on a table with just a single field. I
can demonstrate the problem wether there are 100 records or a million
records.
In every case:
Altering the type from a domain of varchar(9) to a raw varchar(9)
results in a full table rewrite (as identified by relfilenode).
Altering the type from a raw varchar(9) to a domain of varchar(9)
occurs for free, with no change to relfilenode.
The timing of each ALTER operation appears to back this up.
I stand corrected. The logs back it up also. See log entries inline below.
postgres@[local]=# create domain old_type as varchar(9);
CREATE DOMAIN
postgres@[local]=# create table test (values old_type);
CREATE TABLE
postgres@[local]=# with data as (select generate_series(1,1000000),
md5(random()::text))
postgres@[local]-# insert into test select substring(md5, 1, 9) from data;
INSERT 0 1000000 <tel:0%201000000>
Time: 4097.162 ms
postgres@[local]=# \d test
Table "alpha_core.test"
Column | Type | Modifiers
--------+----------+-----------
values | old_type |
postgres@[local]=# \dD old_type
List of domains
Schema | Name | Type | Modifier | Check
------------+----------+----------------------+----------+-------
alpha_core | old_type | character varying(9) | |
(1 row)
postgres@[local]=# select count(*) from test;
count
---------
1000000 <tel:1000000>
(1 row)
postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20689856 <tel:20689856>
(1 row)
postgres@[local]=# alter table test alter COLUMN values set data type
varchar(9);
ALTER TABLE
Time: 993.271 ms
aklaver-2019-04-17 09:06:47.854 PDT-0LOG: statement: alter table test
alter COLUMN values set data type varchar(9);
aklaver-2019-04-17 09:06:47.884 PDT-38177DEBUG: rewriting table "test"
postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20691283 <tel:20691283>
(1 row)
postgres@[local]=# alter table test alter COLUMN values set data type
old_type;
ALTER TABLE
Time: 21.569 ms
aklaver-2019-04-17 09:07:46.027 PDT-0LOG: statement: alter table test
alter COLUMN values set data type old_type;
aklaver-2019-04-17 09:07:46.027 PDT-38178DEBUG: building index
"pg_toast_668193_index" on table "pg_toast_668193" serially
postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20691283 <tel:20691283>
(1 row)
postgres@[local]=# drop table test;
DROP TABLE
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
