Обсуждение: Useless toast
Using version 16, seems strange when toast needs to be created. Tested with domain being numeric or varchar(10) with the same results.
And If that domain is integer then no toast is created.
I think none of these tables should have a toast, right ?
postgres=# create domain mynum as numeric(15,2);
CREATE DOMAIN
postgres=# create table tab1(id integer, num numeric(15,2));
CREATE TABLE
postgres=# create table tab2(id integer, num mynum);
CREATE TABLE
postgres=# create table tab3(id integer, num mynum storage main);
CREATE TABLE
postgres=# create table tab4(id integer, num mynum storage plain);
CREATE TABLE
postgres=# select relname, reltoastrelid from pg_class where relname ~ 'tab\d$' order by 1;
relname | reltoastrelid
---------+---------------
tab1 | 0
tab2 | 25511
tab3 | 25516
tab4 | 0
(4 rows)
CREATE TABLE
postgres=# create table tab2(id integer, num mynum);
CREATE TABLE
postgres=# create table tab3(id integer, num mynum storage main);
CREATE TABLE
postgres=# create table tab4(id integer, num mynum storage plain);
CREATE TABLE
postgres=# select relname, reltoastrelid from pg_class where relname ~ 'tab\d$' order by 1;
relname | reltoastrelid
---------+---------------
tab1 | 0
tab2 | 25511
tab3 | 25516
tab4 | 0
(4 rows)
regards
Marcos
On 23.07.24 20:35, Marcos Pegoraro wrote: > Using version 16, seems strange when toast needs to be created. > Tested with domain being numeric or varchar(10) with the same results. > > And If that domain is integer then no toast is created. > > I think none of these tables should have a toast, right ? The mechanism that determines whether a toast table is needed only considers the data type, not the "typmod" (arguments of the data type). So this is perhaps suboptimal, but this logic just doesn't exist. Also, note that varchar(10) means 10 characters, not 10 bytes, so you can't necessarily draw conclusions about storage size from that. There aren't any supported character encodings that would encode 10 characters into more bytes than the toast threshold, so this is just theoretical, but it would be hard to decide what the actual threshold would be in practice.
Marcos Pegoraro <marcos@f10.com.br> writes:
> Using version 16, seems strange when toast needs to be created. Tested with
> domain being numeric or varchar(10) with the same results.
Domains are fairly opaque when it comes to maximum length.
I cannot get excited about adding code to make them less so.
regards, tom lane
Peter Eisentraut <peter@eisentraut.org> writes:
> On 23.07.24 20:35, Marcos Pegoraro wrote:
>> I think none of these tables should have a toast, right ?
> The mechanism that determines whether a toast table is needed only
> considers the data type, not the "typmod" (arguments of the data type).
> So this is perhaps suboptimal, but this logic just doesn't exist.
Not true, see type_maximum_size() in format_type.c. But I'm
uninterested in making that drill down into domains, or at least
that would not be my first concern if we were trying to improve it.
(The first concern would be to let extension types in on the fun.)
regards, tom lane