Alter domain type / avoiding table rewrite

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Alter domain type / avoiding table rewrite

Tim Kane
So I have a situation where I would like to modify a field that is currently a domain type over a varchar(9)

Specifically:
CREATE DOMAIN old_type AS varchar(9)

This isn't ideal, let's just say.. legacy.


I wish to modify this type.. ideally to a text type with a length constraint.. or even just a slightly larger varchar(12) would suffice.. 

CREATE DOMAIN new_type AS text;
ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <= 12)) NOT VALID;

ALTER TABLE target ALTER
COLUMN value SET DATA TYPE new_type;


But it seems impossible to achieve either without a full table rewrite.

This seems to boil down to DOMAIN types not being considered as binary compatible..

I've tried using a custom CAST..

CREATE CAST (old_type AS new_type) WITHOUT FUNCTION AS IMPLICIT;

But that blows up, with:

WARNING:  cast will be ignored because the source date is a domain
ERROR: domain data types must not Be marked binary compatible


So I'm a little stuck at this point.

I feel like - if I can prove that the binary representation of both domains are truly identical - I might be forced to modify the system tables as a work around.. that scares me on a production system.

Is there a way around this that i'm not seeing?

I'm on PostgreSQL 9.6.2

Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Tom Lane-2
Tim Kane <[hidden email]> writes:
> So I have a situation where I would like to modify a field that is
> currently a domain type over a varchar(9)
> Specifically:
> CREATE DOMAIN old_type AS varchar(9)

> I wish to modify this type.. ideally to a text type with a length
> constraint.. or even just a slightly larger varchar(12) would suffice..
> CREATE DOMAIN new_type AS text;
> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <=
> 12)) NOT VALID;

> ALTER TABLE target ALTER
> COLUMN value SET DATA TYPE new_type;
> But it seems impossible to achieve either without a full table rewrite.

No, that's not going to work: coercing to a domain that has any
constraints is considered to require a rewrite.

You could cast down to varchar(9) without a rewrite, and you could cast
from there to varchar(12) without a rewrite, and it should work to do that
in one step.

If you really want a domain in there, I'd try creating the domain without
any constraint, then doing the ALTER TABLE, then adding the constraint
with ALTER DOMAIN.  But TBH, that "new_type" is going to be a huge
performance drag compared to plain varchar(12).  I'd only recommend
using a domain when there is no other way to get the check you need.
PG just doesn't support domains very well (especially before the work
I did for v12...)

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Adrian Klaver-4
On 4/16/19 7:12 AM, Tom Lane wrote:

> Tim Kane <[hidden email]> writes:
>> So I have a situation where I would like to modify a field that is
>> currently a domain type over a varchar(9)
>> Specifically:
>> CREATE DOMAIN old_type AS varchar(9)
>
>> I wish to modify this type.. ideally to a text type with a length
>> constraint.. or even just a slightly larger varchar(12) would suffice..
>> CREATE DOMAIN new_type AS text;
>> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <=
>> 12)) NOT VALID;
>
>> ALTER TABLE target ALTER
>> COLUMN value SET DATA TYPE new_type;
>> But it seems impossible to achieve either without a full table rewrite.
>
> No, that's not going to work: coercing to a domain that has any
> constraints is considered to require a rewrite.
>
> You could cast down to varchar(9) without a rewrite, and you could cast
> from there to varchar(12) without a rewrite, and it should work to do that
> in one step.

I suspect the OP wants the type to text with a CHECK constraint to allow
for increasing the length of field values in the future by just changing
the CHECK setting. If that is the case would changing the type to text
and then adding a CHECK NOT VALID work without too much pain?


>
> If you really want a domain in there, I'd try creating the domain without
> any constraint, then doing the ALTER TABLE, then adding the constraint
> with ALTER DOMAIN.  But TBH, that "new_type" is going to be a huge
> performance drag compared to plain varchar(12).  I'd only recommend
> using a domain when there is no other way to get the check you need.
> PG just doesn't support domains very well (especially before the work
> I did for v12...)
>
> regards, tom lane
>
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Adrian Klaver-4
On 4/16/19 7:16 AM, Adrian Klaver wrote:

> On 4/16/19 7:12 AM, Tom Lane wrote:
>> Tim Kane <[hidden email]> writes:
>>> So I have a situation where I would like to modify a field that is
>>> currently a domain type over a varchar(9)
>>> Specifically:
>>> CREATE DOMAIN old_type AS varchar(9)
>>
>>> I wish to modify this type.. ideally to a text type with a length
>>> constraint.. or even just a slightly larger varchar(12) would suffice..
>>> CREATE DOMAIN new_type AS text;
>>> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <=
>>> 12)) NOT VALID;
>>
>>> ALTER TABLE target ALTER
>>> COLUMN value SET DATA TYPE new_type;
>>> But it seems impossible to achieve either without a full table rewrite.
>>
>> No, that's not going to work: coercing to a domain that has any
>> constraints is considered to require a rewrite.
>>
>> You could cast down to varchar(9) without a rewrite, and you could cast
>> from there to varchar(12) without a rewrite, and it should work to do
>> that
>> in one step.
>
> I suspect the OP wants the type to text with a CHECK constraint to allow
                        ^ to change

> for increasing the length of field values in the future by just changing
> the CHECK setting. If that is the case would changing the type to text
> and then adding a CHECK NOT VALID work without too much pain?
>
>
>>
>> If you really want a domain in there, I'd try creating the domain without
>> any constraint, then doing the ALTER TABLE, then adding the constraint
>> with ALTER DOMAIN.  But TBH, that "new_type" is going to be a huge
>> performance drag compared to plain varchar(12).  I'd only recommend
>> using a domain when there is no other way to get the check you need.
>> PG just doesn't support domains very well (especially before the work
>> I did for v12...)
>>
>>             regards, tom lane
>>
>>
>>
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Ron-2
In reply to this post by Tim Kane
On 4/16/19 4:22 AM, Tim Kane wrote:

> So I have a situation where I would like to modify a field that is
> currently a domain type over a varchar(9)
>
> Specifically:
> CREATE DOMAIN old_type AS varchar(9)
>
> This isn't ideal, let's just say.. legacy.
>
>
> I wish to modify this type.. ideally to a text type with a length
> constraint.. or even just a slightly larger varchar(12) would suffice..
>
> CREATE DOMAIN new_type AS text;
> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <=
> 12)) NOT VALID;
>
> ALTER TABLE target ALTER
> COLUMN value SET DATA TYPE new_type;
>
>
> But it seems impossible to achieve either without a full table rewrite.

But the column only has -- at most -- 9 characters of data in it. Won't the
CHECK constraint instantly fail?  (ISTM that you should add the check
constraint AFTER modifying the length and updating your data.)

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Adrian Klaver-4
On 4/16/19 7:19 AM, Ron wrote:

> On 4/16/19 4:22 AM, Tim Kane wrote:
>> So I have a situation where I would like to modify a field that is
>> currently a domain type over a varchar(9)
>>
>> Specifically:
>> CREATE DOMAIN old_type AS varchar(9)
>>
>> This isn't ideal, let's just say.. legacy.
>>
>>
>> I wish to modify this type.. ideally to a text type with a length
>> constraint.. or even just a slightly larger varchar(12) would suffice..
>>
>> CREATE DOMAIN new_type AS text;
>> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE)
>> <= 12)) NOT VALID;
>>
>> ALTER TABLE target ALTER
>> COLUMN value SET DATA TYPE new_type;
>>
>>
>> But it seems impossible to achieve either without a full table rewrite.
>
> But the column only has -- at most -- 9 characters of data in it. Won't
> the CHECK constraint instantly fail?  (ISTM that you should add the
> check constraint AFTER modifying the length and updating your data.)
>

Not sure how?:

create table check_test (id integer, fld_1 varchar(12));
CREATE TABLE
test=> insert into check_test values (1, '123456789'), (2, '');
INSERT 0 2
 

test=> select length(fld_1) from check_test ;
 

  length
 

--------
 

       9
 

       0
 

(2 rows)

The lengths would be less then or equal to 12.

Also the NOT VALID will push the check into the future:

https://www.postgresql.org/docs/9.6/sql-altertable.html

"... If the constraint is marked NOT VALID, the potentially-lengthy
initial check to verify that all rows in the table satisfy the
constraint is skipped. The constraint will still be enforced against
subsequent inserts or updates (that is, they'll fail unless there is a
matching row in the referenced table, in the case of foreign keys; and
they'll fail unless the new row matches the specified check
constraints). But the database will not assume that the constraint holds
for all rows in the table, until it is validated by using the VALIDATE
CONSTRAINT option."


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Tom Lane-2
In reply to this post by Adrian Klaver-4
Adrian Klaver <[hidden email]> writes:
> I suspect the OP wants the type to text with a CHECK constraint to allow
> for increasing the length of field values in the future by just changing
> the CHECK setting. If that is the case would changing the type to text
> and then adding a CHECK NOT VALID work without too much pain?

I don't think we really support NOT VALID on domain constraints do we?

In any case, the point remains that domains are pretty inefficient
compared to native types like varchar(12); partly because the system
can't reason very well about arbitrary check constraints as compared
to simple length constraints, and partly because the whole feature
just isn't implemented very completely or efficiently.  So you'll be
paying *a lot* for some hypothetical future savings.

(Having said that, you're already paying a fair chunk of that
overhead with your existing domain type, so maybe it's not bothering
you.  But I'm worried that going from domain-without-check-constraint
to domain-with-check-constraint is going to bite you.)

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Adrian Klaver-4
On 4/16/19 7:42 AM, Tom Lane wrote:
> Adrian Klaver <[hidden email]> writes:
>> I suspect the OP wants the type to text with a CHECK constraint to allow
>> for increasing the length of field values in the future by just changing
>> the CHECK setting. If that is the case would changing the type to text
>> and then adding a CHECK NOT VALID work without too much pain?
>
> I don't think we really support NOT VALID on domain constraints do we?

I was not clear. I was thinking the OP could modify your suggestion.

Instead of:

old_type --> varchar(9) --> varchar(12)

doing:

old_type --> varchar(9) --> text --> CHECK ((length(VALUE) <= 12)) NOT VALID

>
> In any case, the point remains that domains are pretty inefficient
> compared to native types like varchar(12); partly because the system
> can't reason very well about arbitrary check constraints as compared
> to simple length constraints, and partly because the whole feature
> just isn't implemented very completely or efficiently.  So you'll be
> paying *a lot* for some hypothetical future savings.
>
> (Having said that, you're already paying a fair chunk of that
> overhead with your existing domain type, so maybe it's not bothering
> you.  But I'm worried that going from domain-without-check-constraint
> to domain-with-check-constraint is going to bite you.)
>
> regards, tom lane
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Ron-2
In reply to this post by Adrian Klaver-4
On 4/16/19 9:28 AM, Adrian Klaver wrote:

> On 4/16/19 7:19 AM, Ron wrote:
>> On 4/16/19 4:22 AM, Tim Kane wrote:
>>> So I have a situation where I would like to modify a field that is
>>> currently a domain type over a varchar(9)
>>>
>>> Specifically:
>>> CREATE DOMAIN old_type AS varchar(9)
>>>
>>> This isn't ideal, let's just say.. legacy.
>>>
>>>
>>> I wish to modify this type.. ideally to a text type with a length
>>> constraint.. or even just a slightly larger varchar(12) would suffice..
>>>
>>> CREATE DOMAIN new_type AS text;
>>> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <=
>>> 12)) NOT VALID;
>>>
>>> ALTER TABLE target ALTER
>>> COLUMN value SET DATA TYPE new_type;
>>>
>>>
>>> But it seems impossible to achieve either without a full table rewrite.
>>
>> But the column only has -- at most -- 9 characters of data in it. Won't
>> the CHECK constraint instantly fail?  (ISTM that you should add the check
>> constraint AFTER modifying the length and updating your data.)
>>
>
> Not sure how?:
>
> create table check_test (id integer, fld_1 varchar(12));
> CREATE TABLE
> test=> insert into check_test values (1, '123456789'), (2, '');
> INSERT 0 2
>
> test=> select length(fld_1) from check_test ;
>
>  length
>
> --------
>
>       9
>
>       0
>
> (2 rows)
>
> The lengths would be less then or equal to 12.

But there's no CHECK constraint.

>
> Also the NOT VALID will push the check into the future:
>
> https://www.postgresql.org/docs/9.6/sql-altertable.html
>
> "... If the constraint is marked NOT VALID, the potentially-lengthy
> initial check to verify that all rows in the table satisfy the constraint
> is skipped. The constraint will still be enforced against subsequent
> inserts or updates

NOT VALID is the part that obviates my concern.

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Ron-2
In reply to this post by Tom Lane-2
On 4/16/19 9:42 AM, Tom Lane wrote:

> Adrian Klaver <[hidden email]> writes:
>> I suspect the OP wants the type to text with a CHECK constraint to allow
>> for increasing the length of field values in the future by just changing
>> the CHECK setting. If that is the case would changing the type to text
>> and then adding a CHECK NOT VALID work without too much pain?
> I don't think we really support NOT VALID on domain constraints do we?
>
> In any case, the point remains that domains are pretty inefficient
> compared to native types like varchar(12); partly because the system
> can't reason very well about arbitrary check constraints as compared
> to simple length constraints, and partly because the whole feature
> just isn't implemented very completely or efficiently.  So you'll be
> paying *a lot* for some hypothetical future savings.

Domains are great for maintaining data type consistency across many
tables/columns.  Normalization can obviate much of that need, and
denormalization increases it.

> (Having said that, you're already paying a fair chunk of that
> overhead with your existing domain type, so maybe it's not bothering
> you.  But I'm worried that going from domain-without-check-constraint
> to domain-with-check-constraint is going to bite you.)
>
> regards, tom lane
>
>

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Tim Kane
Thanks everyone..

It seems that the first step:

old_type --> varchar(9) 

still requires a table rewrite, while the reverse direction does not.


I'm curious about the performance implication of domain types, i expect that cost is only at insert/update time? I guess we've been wearing that cost up until now.

Adrian is correct - the intention for the DOMAIN with CHECK approach was to allow flexibility moving forward, as the data set is particularly large... 

I'm now thinking that since promotion to a larger size is a non-issue, and domain type seems to be not quite the panacea I hoped, then the use of varchar(n) is perhaps not so terrible!

Thanks for the advice/suggestions/discussion :)


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Adrian Klaver-4
On 4/16/19 9:18 AM, Tim Kane wrote:
> Thanks everyone..
>
> It seems that the first step:
>
> old_type --> varchar(9)
>
> still requires a table rewrite, while the reverse direction does not.

Hmm:

CREATE DOMAIN old_type AS varchar(9);

create table rewrite_test (id integer, fld_1 old_type);

insert into rewrite_test values (1, '123456789'), (2, '123');

select ctid from rewrite_test;
  ctid
-------
  (0,1)
  (0,2)

alter table rewrite_test alter COLUMN fld_1 set data type varchar(9);

select ctid from rewrite_test;
  ctid
-------
  (0,1)
  (0,2)

update rewrite_test set fld_1 = '1' where id =2;

select ctid from rewrite_test;
 

  ctid
 
 

-------
 
 

  (0,1)
 
 

  (0,3)

Where are you seeing the rewrite in your case?

>
>
> I'm curious about the performance implication of domain types, i expect
> that cost is only at insert/update time? I guess we've been wearing that
> cost up until now.
>
> Adrian is correct - the intention for the DOMAIN with CHECK approach was
> to allow flexibility moving forward, as the data set is particularly
> large...
>
> I'm now thinking that since promotion to a larger size is a non-issue,
> and domain type seems to be not quite the panacea I hoped, then the use
> of varchar(n) is perhaps not so terrible!
>
> Thanks for the advice/suggestions/discussion :)
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Tim Kane


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

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

    <a dir="ltr" href="tel:20669469">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

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

    <a dir="ltr" href="tel:20671802">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

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

    <a dir="ltr" href="tel:20671802">20671802

(1 row)

 

Time: 0.331 ms



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

Tim

Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Adrian Klaver-4
On 4/17/19 2:14 AM, Tim Kane wrote:

>
>
> On Tue, 16 Apr 2019 at 18:04, Adrian Klaver <[hidden email]
> <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Tim Kane


On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <[hidden email]> 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.






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 <a dir="ltr" href="tel:0%201000000" target="_blank" style="font-size:1rem">0 1000000
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
---------
 <a dir="ltr" href="tel:1000000" target="_blank" style="font-size:1rem">1000000
(1 row)



postgres@[local]=# select relfilenode from pg_class where relname='test';
 relfilenode
-------------
    <a dir="ltr" href="tel:20689856" target="_blank" style="font-size:1rem">20689856
(1 row)


postgres@[local]=# alter table test alter COLUMN values set data type varchar(9);
ALTER TABLE
Time: 993.271 ms


postgres@[local]=# select relfilenode from pg_class where relname='test';
 relfilenode
-------------
    <a dir="ltr" href="tel:20691283" target="_blank" style="font-size:1rem">20691283
(1 row)

postgres@[local]=# alter table test alter COLUMN values set data type old_type;
ALTER TABLE
Time: 21.569 ms


postgres@[local]=# select relfilenode from pg_class where relname='test';
 relfilenode
-------------
    <a dir="ltr" href="tel:20691283" target="_blank" style="font-size:1rem">20691283
(1 row)

postgres@[local]=# drop table test;
DROP TABLE

Reply | Threaded
Open this post in threaded view
|

Re: Alter domain type / avoiding table rewrite

Adrian Klaver-4
On 4/17/19 8:34 AM, Tim Kane wrote:

>
>
> On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <[hidden email]
> <mailto:[hidden email]>> 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
[hidden email]