From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Tim Kane <tim(dot)kane(at)gmail(dot)com> |
Cc: | Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Alter domain type / avoiding table rewrite |
Date: | 2019-04-17 16:15:25 |
Message-ID: | 53d45372-e189-317e-b4c3-6317cb195b11@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 4/17/19 8:34 AM, Tim Kane wrote:
>
>
> On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(at)[local]=# create domain old_type as varchar(9);
> CREATE DOMAIN
>
> postgres(at)[local]=# create table test (values old_type);
> CREATE TABLE
>
> postgres(at)[local]=# with data as (select generate_series(1,1000000),
> md5(random()::text))
> postgres(at)[local]-# insert into test select substring(md5, 1, 9) from data;
> INSERT 0 1000000 <tel:0%201000000>
> Time: 4097.162 ms
>
> postgres(at)[local]=# \d test
> Table "alpha_core.test"
> Column | Type | Modifiers
> --------+----------+-----------
> values | old_type |
>
> postgres(at)[local]=# \dD old_type
> List of domains
> Schema | Name | Type | Modifier | Check
> ------------+----------+----------------------+----------+-------
> alpha_core | old_type | character varying(9) | |
> (1 row)
>
> postgres(at)[local]=# select count(*) from test;
> count
> ---------
> 1000000 <tel:1000000>
> (1 row)
>
>
>
> postgres(at)[local]=# select relfilenode from pg_class where relname='test';
> relfilenode
> -------------
> 20689856 <tel:20689856>
> (1 row)
>
>
> postgres(at)[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(at)[local]=# select relfilenode from pg_class where relname='test';
> relfilenode
> -------------
> 20691283 <tel:20691283>
> (1 row)
>
> postgres(at)[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(at)[local]=# select relfilenode from pg_class where relname='test';
> relfilenode
> -------------
> 20691283 <tel:20691283>
> (1 row)
>
> postgres(at)[local]=# drop table test;
> DROP TABLE
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2019-04-17 16:41:57 | Re: PostgreSQL ping/pong to client |
Previous Message | Tim Kane | 2019-04-17 15:34:02 | Re: Alter domain type / avoiding table rewrite |