check which alter does a rewrite

gistfile1.txt

postgres=# set client_min_messages TO debug1;

SET

postgres=# create table t(col1 int primary key);

DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"

DEBUG: building index "t_pkey" on table "t" serially

DEBUG: index "t_pkey" can safely use deduplication

CREATE TABLE

postgres=# create or replace function f() returns int as $$ select random()::int + 10 $$ language sql volatile;

CREATE FUNCTION

postgres=# \df f

            List of functions

 Schema | Name | Result data type | Argument data types | Type

--------+------+------------------+---------------------+------

 public | f  | integer     |           | func

(1 row)


postgres=# \df+ f

                                      List of functions

 Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner  | Security | Access privil

eges | Language | Internal name | Description

--------+------+------------------+---------------------+------+------------+----------+----------+----------+--------------

-----+----------+---------------+-------------

 public | f  | integer     |           | func | volatile  | unsafe  | postgres | invoker |

   | sql   |        |

(1 row)


postgres=# alter table t add column col2 int default f();

DEBUG: rewriting table "t"

DEBUG: building index "t_pkey" on table "t" serially

DEBUG: index "t_pkey" can safely use deduplication

ALTER TABLE

postgres=# create or replace function f() returns int as $$ select 10 $$ language sql immutable;

CREATE FUNCTION

postgres=# alter table t add column col3 int default f();

ALTER TABLE

postgres=# alter table t alter column col1 type bigint;

DEBUG: rewriting table "t"

DEBUG: building index "t_pkey" on table "t" serially

DEBUG: index "t_pkey" can safely use deduplication

ALTER TABLE

postgres=# alter table t alter column col1 type text using col1::text;

DEBUG: building index "pg_toast_16415_index" on table "pg_toast_16415" serially

DEBUG: index "pg_toast_16415_index" can safely use deduplication

DEBUG: building index "pg_toast_16437_index" on table "pg_toast_16437" serially

DEBUG: index "pg_toast_16437_index" can safely use deduplication

DEBUG: rewriting table "t"

DEBUG: building index "t_pkey" on table "t" serially

DEBUG: index "t_pkey" can safely use deduplication

ALTER TABLE

list of binary coercible types

postgres=# SELECT castsource::regtype::text, array_agg(casttarget::regtype order by casttarget::regtype::text) casttargets
FROM pg_cast
WHERE castmethod = 'b'
GROUP BY 1
ORDER BY 1;
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
castsource | casttargets

-------------------+--------------------------------------------------------------------------------------------------------

bit | {"bit varying"}
bit varying | {bit}
character varying | {character,text}
cidr | {inet}
integer | {oid,regclass,regcollation,regconfig,regdictionary,regnamespace,regoper,regoperator,regproc,regprocedur
e,regrole,regtype}
oid | {integer,regclass,regcollation,regconfig,regdictionary,regnamespace,regoper,regoperator,regproc,regproc
edure,regrole,regtype}
pg_dependencies | {bytea}
pg_mcv_list | {bytea}
pg_ndistinct | {bytea}
pg_node_tree | {text}
regclass | {integer,oid}
regcollation | {integer,oid}
regconfig | {integer,oid}
regdictionary | {integer,oid}
regnamespace | {integer,oid}
regoper | {integer,oid,regoperator}
regoperator | {integer,oid,regoper}
regproc | {integer,oid,regprocedure}
regprocedure | {integer,oid,regproc}
regrole | {integer,oid}
regtype | {integer,oid}
text | {character,"character varying"}
xml | {character,"character varying",text}
(23 rows)