pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16803: create a table only one text/varchar column, storage options toast_tuple_target doesn't work
2+ messages / 2 participants
[nested] [flat]

* BUG #16803: create a table only one text/varchar column, storage options toast_tuple_target doesn't work
@ 2021-01-04 14:53 PG Bug reporting form <noreply@postgresql.org>
  2021-01-04 18:15 ` Re: BUG #16803: create a table only one text/varchar column, storage options toast_tuple_target doesn't work Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2021-01-04 14:53 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: mailtch@163.com

The following bug has been logged on the website:

Bug reference:      16803
Logged by:          osdba
Email address:      mailtch@163.com
PostgreSQL version: 11.10
Operating system:   CentOS7.6
Description:        

create a table only one text/varchar colume,  storage options
toast_tuple_target doesn't work, see:

[pg03@pgtrain pgdata]$ psql
psql (11.10)
Type "help" for help.

postgres=# create table test01(id int, t text);
CREATE TABLE
postgres=# alter table test01 set (toast_tuple_target=1024);
ALTER TABLE
postgres=# insert into test01 select seq, repeat('a', 1800) from
generate_series(1, 1000) as seq;
INSERT 0 1000
postgres=# select pg_total_relation_size('test01');
 pg_total_relation_size
------------------------
                2080768
(1 row)

postgres=# truncate table test01;
TRUNCATE TABLE
postgres=# insert into test01 select seq, repeat('a', 2800) from
generate_series(1, 1000) as seq;
INSERT 0 1000
postgres=# select pg_total_relation_size('test01');
 pg_total_relation_size
------------------------
                 114688
(1 row)

because I set toast_tuple_target to 1024,so trigger toast compress when
insert data that length is 1800 or 2800, but only trigger toast compress
when data length is 2800.

when I create table have more than one text column, then storage options
toast_tuple_target can be work correctly: 

postgres=# create table test01(id int, t1 text, t2 text);
CREATE TABLE
postgres=# alter table test01 set (toast_tuple_target=1024);
ALTER TABLE
postgres=# insert into test01 select seq, repeat('a', 1800), repeat('b',
1800) from generate_series(1, 1000) as seq;
INSERT 0 1000
postgres=# select pg_total_relation_size('test01');
 pg_total_relation_size
------------------------
                 139264
(1 row)

postgres=# truncate table test01;
TRUNCATE TABLE
postgres=# insert into test01 select seq, repeat('a', 2800), repeat('b',
2800) from generate_series(1, 1000) as seq;
INSERT 0 1000
postgres=# select pg_total_relation_size('test01');
 pg_total_relation_size
------------------------
                 163840
(1 row)

you can see, when table have two text column, then storage options
toast_tuple_target can be work correctly.

this is a bug?



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #16803: create a table only one text/varchar column, storage options toast_tuple_target doesn't work
  2021-01-04 14:53 BUG #16803: create a table only one text/varchar column, storage options toast_tuple_target doesn't work PG Bug reporting form <noreply@postgresql.org>
@ 2021-01-04 18:15 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2021-01-04 18:15 UTC (permalink / raw)
  To: mailtch@163.com; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> writes:
> create a table only one text/varchar colume,  storage options
> toast_tuple_target doesn't work, see:

The reason your first example isn't doing anything is that the tuples are
shorter than TOAST_TUPLE_THRESHOLD, which is the threshold row length for
applying the toasting code at all.  The reloption doesn't affect that;
it only changes the target length once we've gotten into the toaster.

It's not real clear to me why whoever provided a knob for
TOAST_TUPLE_TARGET didn't see fit to also provide one for
TOAST_TUPLE_THRESHOLD, but I suppose that was a deliberate choice.
The documentation is certainly clear enough that you can only
adjust the target.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2021-01-04 18:15 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-04 14:53 BUG #16803: create a table only one text/varchar column, storage options toast_tuple_target doesn't work PG Bug reporting form <noreply@postgresql.org>
2021-01-04 18:15 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox