Re: Alter domain type / avoiding table rewrite

On 4/17/19 2:14 AM, Tim Kane wrote:
> 
> 
> On Tue, 16 Apr 2019 at 18:04, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
> 
> 
>     Where are you seeing the rewrite in your case?
> 
> 
> 
> I’m unfamiliar with ctid (which I now plan to read up on)… I’ve been 
> looking at /relfilenode____/
> 
> I’ve observed that relfilenode changes when altering from /old_type 
> //à varchar(9) /and the operation takes 6 seconds on this data set.____

The table definition and the size of the data set would help with 
interpreting the below.

> 
> __
> 
> __
> 
> PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where 
> relname='test';____
> 
> relfilenode____
> 
> -------------____
> 
> 20669469 <tel:20669469>____
> 
> (1 row)____
> 
> __ __
> 
> PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id 
> set data type varchar(9);____
> 
> ALTER TABLE____
> 
> Time: 6605.454 ms____
> 
> 
> PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where 
> relname='test';____
> 
> relfilenode____
> 
> -------------____
> 
> 20671802 <tel:20671802>____
> 
> (1 row)
> 
> __ __
> 
> And then the other way… from /varchar(9) //à old_type____/
> 
> refilenode does not change, and the operation takes 0.3ms____
> 
> __ __
> 
> PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id 
> set data type execid_t;____
> 
> ALTER TABLE____
> 
> Time: 1.360 ms____
> 
> PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where 
> relname='test';____
> 
> relfilenode____
> 
> -------------____
> 
> 20671802 <tel:20671802>____
> 
> (1 row)____
> 
> __ __
> 
> Time: 0.331 ms____
> 
> __
> 
> 
> Apologies if this formats badly :-/ transcribing between devices not 
> well suited to email.
> 
> Tim
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com