Re: Adding support for Default partition in partitioning - Mailing list pgsql-hackers

From Keith Fiske
Subject Re: Adding support for Default partition in partitioning
Date
Msg-id CAG1_KcBfqk3zeZWcWusDb82nBQ+ZLyrQkG3a9jZ30tk6XaT4mg@mail.gmail.com
Whole thread Raw
In response to [HACKERS] Adding support for Default partition in partitioning  (Rahila Syed <rahilasyed90@gmail.com>)
Responses Re: Adding support for Default partition in partitioning  (Rahila Syed <rahilasyed90@gmail.com>)
Re: Adding support for Default partition in partitioning  (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
List pgsql-hackers

On Tue, Apr 4, 2017 at 9:30 AM, Rahila Syed <rahilasyed90@gmail.com> wrote:
Hello,

Please find attached an updated patch.
Following has been accomplished in this update:

1. A new partition can be added after default partition if there are no conflicting rows in default partition.
2. Solved the crash reported earlier.

Thank you,
Rahila Syed


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Installed and compiled against commit 60a0b2ec8943451186dfa22907f88334d97cb2e0 (Date: Tue Apr 4 12:36:15 2017 -0400) without any issue

However, running your original example, I'm getting this error

keith@keith=# CREATE TABLE list_partitioned (             
keith(#     a int
keith(# ) PARTITION BY LIST (a);
CREATE TABLE
Time: 4.933 ms
keith@keith=# CREATE TABLE part_default PARTITION OF list_partitioned FOR VALUES IN (DEFAULT);
CREATE TABLE
Time: 3.492 ms
keith@keith=# CREATE TABLE part_1 PARTITION OF list_partitioned FOR VALUES IN (4,5);
ERROR:  unrecognized node type: 216
Time: 0.979 ms

Also, I'm still of the opinion that denying future partitions of values in the default would be a cause of confusion. In order to move the data out of the default and into a proper child it would require first removing that data from the default, storing it elsewhere, creating the child, then moving it back. If it's only a small amount of data it may not be a big deal, but if it's a large amount, that could cause quite a lot of contention if done in a single transaction. Either that or the user would have to deal with data existing in the table, disappearing, then reappearing again.

This also makes it harder to migrate an existing table easily. Essentially no child tables for a large, existing data set could ever be created without going through one of the two situations above.

However, thinking through this, I'm not sure I can see a solution without the global index support. If this restriction is removed, there's still an issue of data duplication after the necessary child table is added. So guess it's a matter of deciding which user experience is better for the moment?

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

pgsql-hackers by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: PATCH: recursive json_populate_record()
Next
From: Tomas Vondra
Date:
Subject: Re: bug in SlabAlloc / VALGRIND_MAKE_MEM_DEFINED