TOAST table created for partitioned tables
Hi.
I used to think that $subject didn't happen, but it actually does and ends
up consuming a fixed 8192 bytes on the disk.
create table p (a int[]) partition by list (a);
CREATE TABLE
select pg_table_size('p');
pg_table_size
---------------
8192
(1 row)
select pg_relation_size(c1.oid) as p_size,
pg_relation_size(c1.reltoastrelid) as p_toast_heap_size,
pg_relation_size(c2.oid) as p_toast_index_size
from pg_class c1, pg_class c2, pg_index i
where c1.relname = 'p' and
c1.reltoastrelid = i.indrelid and
c2.oid = i.indexrelid;
p_size | p_toast_heap_size | p_toast_index_size
--------+-------------------+--------------------
0 | 0 | 8192
(1 row)
I think we should prevent this, a fix for which is implemented by the
attached patch.
Thanks,
Amit
Attachments:
v1-0001-Do-not-create-TOAST-table-for-partitioned-tables.patchtext/plain; charset=UTF-8; name=v1-0001-Do-not-create-TOAST-table-for-partitioned-tables.patchDownload
From b84ee9f5aed9519d5976dc1a6c8f501d0122a686 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Tue, 16 Jan 2018 19:08:11 +0900
Subject: [PATCH v1] Do not create TOAST table for partitioned tables
---
src/backend/catalog/toasting.c | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 0b4b5631a1..5e84f28201 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -393,6 +393,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
* (1) there are any toastable attributes, and (2) the maximum length
* of a tuple could exceed TOAST_TUPLE_THRESHOLD. (We don't want to
* create a toast table for something like "f1 varchar(20)".)
+ * No need to create a TOAST table for partitioned tables.
*/
static bool
needs_toast_table(Relation rel)
@@ -404,6 +405,9 @@ needs_toast_table(Relation rel)
int32 tuple_length;
int i;
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ return false;
+
tupdesc = rel->rd_att;
for (i = 0; i < tupdesc->natts; i++)
--
2.11.0
On Tue, Jan 16, 2018 at 5:13 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
I used to think that $subject didn't happen, but it actually does and ends
up consuming a fixed 8192 bytes on the disk.create table p (a int[]) partition by list (a);
CREATE TABLEselect pg_table_size('p');
pg_table_size
---------------
8192
(1 row)select pg_relation_size(c1.oid) as p_size,
pg_relation_size(c1.reltoastrelid) as p_toast_heap_size,
pg_relation_size(c2.oid) as p_toast_index_size
from pg_class c1, pg_class c2, pg_index i
where c1.relname = 'p' and
c1.reltoastrelid = i.indrelid and
c2.oid = i.indexrelid;
p_size | p_toast_heap_size | p_toast_index_size
--------+-------------------+--------------------
0 | 0 | 8192
(1 row)
Aargh. Will apply this patch break pg_upgrade from v10?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Wed, Jan 17, 2018 at 5:32 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Jan 16, 2018 at 5:13 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:I used to think that $subject didn't happen, but it actually does and ends
up consuming a fixed 8192 bytes on the disk.create table p (a int[]) partition by list (a);
CREATE TABLEselect pg_table_size('p');
pg_table_size
---------------
8192
(1 row)select pg_relation_size(c1.oid) as p_size,
pg_relation_size(c1.reltoastrelid) as p_toast_heap_size,
pg_relation_size(c2.oid) as p_toast_index_size
from pg_class c1, pg_class c2, pg_index i
where c1.relname = 'p' and
c1.reltoastrelid = i.indrelid and
c2.oid = i.indexrelid;
p_size | p_toast_heap_size | p_toast_index_size
--------+-------------------+--------------------
0 | 0 | 8192
(1 row)Aargh. Will apply this patch break pg_upgrade from v10?
AFAICS, it doesn't. Partitioned tables that used to have a TOAST
table in v10 cluster will continue to have it after upgrading.
Whereas, any partitioned tables created with the patched won't have a
TOAST table.
Thanks,
Amit
Amit Langote <amitlangote09@gmail.com> writes:
On Wed, Jan 17, 2018 at 5:32 AM, Robert Haas <robertmhaas@gmail.com> wrote:
Aargh. Will apply this patch break pg_upgrade from v10?
AFAICS, it doesn't. Partitioned tables that used to have a TOAST
table in v10 cluster will continue to have it after upgrading.
Whereas, any partitioned tables created with the patched won't have a
TOAST table.
Yeah, pg_upgrade already has to cope with cases where the newer version
thinks a table needs a toast table when the older version didn't, or
vice versa. This looks like it ought to fall into that category.
Not that testing it wouldn't be a good idea.
regards, tom lane
On Tue, Jan 16, 2018 at 11:38:58PM -0500, Tom Lane wrote:
Yeah, pg_upgrade already has to cope with cases where the newer version
thinks a table needs a toast table when the older version didn't, or
vice versa. This looks like it ought to fall into that category.
Not that testing it wouldn't be a good idea.
As far as I can see this statement is true. If you create a parent
partition table in a v10 cluster, and then upgrade to HEAD with this
patch applied, you'll be able to notice that the relation still has its
toast table present, while newly-created parent partitions would have
nothing. (Just tested, I didn't review the patch in details).
--
Michael
On Wed, Jan 17, 2018 at 1:51 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Tue, Jan 16, 2018 at 11:38:58PM -0500, Tom Lane wrote:
Yeah, pg_upgrade already has to cope with cases where the newer version
thinks a table needs a toast table when the older version didn't, or
vice versa. This looks like it ought to fall into that category.
Not that testing it wouldn't be a good idea.As far as I can see this statement is true. If you create a parent
partition table in a v10 cluster, and then upgrade to HEAD with this
patch applied, you'll be able to notice that the relation still has its
toast table present, while newly-created parent partitions would have
nothing. (Just tested, I didn't review the patch in details).
Thanks for checking. I too checked that pg_upgrading v10 cluster
containing partitioned tables that have a TOAST table attached to it
works normally and like Michael says, the TOAST table remains.
Thanks,
Amit
On Wed, Jan 17, 2018 at 1:03 AM, Amit Langote <amitlangote09@gmail.com> wrote:
On Wed, Jan 17, 2018 at 1:51 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Tue, Jan 16, 2018 at 11:38:58PM -0500, Tom Lane wrote:
Yeah, pg_upgrade already has to cope with cases where the newer version
thinks a table needs a toast table when the older version didn't, or
vice versa. This looks like it ought to fall into that category.
Not that testing it wouldn't be a good idea.As far as I can see this statement is true. If you create a parent
partition table in a v10 cluster, and then upgrade to HEAD with this
patch applied, you'll be able to notice that the relation still has its
toast table present, while newly-created parent partitions would have
nothing. (Just tested, I didn't review the patch in details).Thanks for checking. I too checked that pg_upgrading v10 cluster
containing partitioned tables that have a TOAST table attached to it
works normally and like Michael says, the TOAST table remains.
I have committed your patch.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 2018/03/23 2:51, Robert Haas wrote:
On Wed, Jan 17, 2018 at 1:03 AM, Amit Langote <amitlangote09@gmail.com> wrote:
On Wed, Jan 17, 2018 at 1:51 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Tue, Jan 16, 2018 at 11:38:58PM -0500, Tom Lane wrote:
Yeah, pg_upgrade already has to cope with cases where the newer version
thinks a table needs a toast table when the older version didn't, or
vice versa. This looks like it ought to fall into that category.
Not that testing it wouldn't be a good idea.As far as I can see this statement is true. If you create a parent
partition table in a v10 cluster, and then upgrade to HEAD with this
patch applied, you'll be able to notice that the relation still has its
toast table present, while newly-created parent partitions would have
nothing. (Just tested, I didn't review the patch in details).Thanks for checking. I too checked that pg_upgrading v10 cluster
containing partitioned tables that have a TOAST table attached to it
works normally and like Michael says, the TOAST table remains.I have committed your patch.
Thank you!
Regards,
Amit