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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(at)[local]=# select relfilenode from pg_class where
> relname='test';____
>
> relfilenode____
>
> -------------____
>
> 20669469 <tel:20669469>____
>
> (1 row)____
>
> __ __
>
> PSQL:alpha_db/postgres(at)[local]=# alter table test alter COLUMN exec_id
> set data type varchar(9);____
>
> ALTER TABLE____
>
> Time: 6605.454 ms____
>
>
> PSQL:alpha_db/postgres(at)[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(at)[local]=# alter table test alter COLUMN exec_id
> set data type execid_t;____
>
> ALTER TABLE____
>
> Time: 1.360 ms____
>
> PSQL:alpha_db/postgres(at)[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(dot)klaver(at)aklaver(dot)com