Re: Alter domain type / avoiding table rewrite

[Tim Kane]

On Tue, 16 Apr 2019 at 18:04, Adrian Klaver <adrian.kla...@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.



PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where
relname='test';

relfilenode

-------------

    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

(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

(1 row)



Time: 0.331 ms



Apologies if this formats badly :-/ transcribing between devices not well
suited to email.

Tim

>