Re: Alter domain type / avoiding table rewrite

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

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

relfilenode

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

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

(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

(1 row)

Time: 0.331 ms

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

Tim

>