Thread: why toasted is created when domain is used ?

select count(*) Total_Tables,
       count(*) filter (where t2.oid is not null) Toasted_Tables,
       count(*) filter (where t2.reltuples > 0) Toasted_with_records
from pg_class t1 left join pg_class t2 on t1.reltoastrelid = t2.oid and
t2.relkind = 't'
where t1.relkind = 'r'

total_tables    toasted_tables    toasted_with_records
11564            9521                    263

Then seeing structure of my tables only few of them really needs toasting,
so it´s ok to have few toast tables with records. The question is, why those
toasted tables were created ?
Then, doing a simple test using one of those tables ...

create table with_toast(
id_pk i32, Field2 i32 not null, Field3 i16 not null, Field4 numeric(7, 2),
Field5 i32 default public.DefaultValueForField(), Field6 datahora default
CURRENT_TIMESTAMP);

create table without_toast(
id_pk integer, Field2 integer not null, Field3 smallint not null, Field4
numeric(7, 2),
Field5 integer default public.DefaultValueForField(), Field6 timestamp
default CURRENT_TIMESTAMP);

As you can see this table doesn´t need to have a toast and probably will
never have records on it.

select t1.oid, t1.relname, t1.relkind, t1.oid, t2.oid, t2.relname
from pg_class t1 left join pg_class t2 on t1.reltoastrelid = t2.oid and
t2.relkind = 't'
inner join pg_namespace ns on ns.oid = t1.relnamespace where t1.relkind =
'r' and t1.relname ~* 'test';

oid            relname           relkind    oid            oid            relname
9375155    with_toast        r            9375155    9375160    pg_toast_9375155
9375167    without_toast   r            9375167    NULL            NULL

So, the question is, when I create a table using domains it creates a toast
for it, why ?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

PegoraroF10 <marcos@f10.com.br> writes:
> So, the question is, when I create a table using domains it creates a toast
> for it, why ?

For me, this doesn't happen for domains over fixed-width types, only
variable-width types ... which doesn't seem to match your example.

The reason it happens for variable-width cases is that
type_maximum_size() only knows about built-in types,
so the system can't determine the maximum row width.

            regards, tom lane

Well, I used only domains with fixed sizes.

create domain i32 as integer;
create domain i16 as smallint;
create domain datahora as timestamp;



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html