Subject: TOAST table created for partitioned tables

From: Amit Langote <[email-masked]>
Date: Tue, 16 Jan 2018 19:13:41 +0900

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
From b84ee9f5aed9519d5976dc1a6c8f501d0122a686 Mon Sep 17 00:00:00 2001
From: amit <[email-masked]>
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

---

From: Robert Haas <[email-masked]>
Date: Tue, 16 Jan 2018 15:32:35 -0500

> select pg_table_size('p');

Aargh.  Will apply this patch break pg_upgrade from v10?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Amit Langote <[email-masked]>
Date: Wed, 17 Jan 2018 13:09:52 +0900

> 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

---

From: Tom Lane <[email-masked]>
Date: Tue, 16 Jan 2018 23:38:58 -0500

> Whereas, any partitioned tables created with the patched won't have a

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

---

From: Michael Paquier <[email-masked]>
Date: Wed, 17 Jan 2018 13:51:06 +0900

> 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

---

From: Amit Langote <[email-masked]>
Date: Wed, 17 Jan 2018 15:03:24 +0900

> 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

---

From: Robert Haas <[email-masked]>
Date: Thu, 22 Mar 2018 13:51:26 -0400

> works normally and like Michael says, the TOAST table remains.

I have committed your patch.

---

From: Amit Langote <[email-masked]>
Date: Fri, 23 Mar 2018 09:28:57 +0900

> I have committed your patch.

Thank you!

Regards,
Amit