Re: Alter domain type / avoiding table rewrite

On 4/17/19 8:34 AM, Tim Kane wrote:
> 
> 
> On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> 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@aklaver.com