Alter domain type / avoiding table rewrite

Tim Kane <tim.kane@gmail.com> 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

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

Tim Kane <tim.kane@gmail.com> 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
adrian.klaver@aklaver.com

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

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

Tim Kane <tim.kane@gmail.com> 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
adrian.klaver@aklaver.com

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.

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
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> 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

On 4/16/19 7:42 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> 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
adrian.klaver@aklaver.com

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.

On 4/16/19 9:42 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> 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.

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 :)

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
adrian.klaver@aklaver.com

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

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

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

On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <adrian.klaver@aklaver.com>
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 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
---------
1000000
(1 row)

postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
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
-------------
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
-------------
20691283
(1 row)

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

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

On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> 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
adrian.klaver@aklaver.com