From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mailtch@163.com
Subject: BUG #16803: create a table only one text/varchar column, storage options toast_tuple_target doesn't work
Date: Mon, 04 Jan 2021 14:53:12 +0000
Message-ID: <16803-0acffe06339b12be@postgresql.org> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mailtch@163.com
Subject: Re: BUG #16803: create a table only one text/varchar column, storage options toast_tuple_target doesn't work
In-Reply-To: <16803-0acffe06339b12be@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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