Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

Hi!

Alexander Korotkov, Robert Haas - thanks for fixes and feedbacks!

This email is a starting point for further work.
There are two files attached to this email:
v32-0001-Implement-ALTER-TABLE-.-MERGE-PARTITIONS-.-comma.patch,
v32-0002-Implement-ALTER-TABLE-.-SPLIT-PARTITION-.-comman.patch.

They contains changes from reverted commits 1adf16b8fb, 87c21bb941, and
subsequent fixes and improvements including df64c81ca9, c99ef1811a,
9dfcac8e15, 885742b9f8, 842c9b2705, fcf80c5d5f, 96c7381c4c, f4fc7cb54b,
60ae37a8bc, 259c96fa8f, 449cdcd486, 3ca43dbbb6, 2a679ae94e, 3a82c689fd,
fbd4321fd5, d53a4286d7, c086896625, 4e5d6c4091.
I didn't include fix 04158e7fa3 into patches because Robert Haas 
objected to its use.

A short list of known issues and questions (see more details in [1] and 
[2]):

1. Function createPartitionTable() should be rewritten using partitioned 
table OID (not name) and without using ProcessUtility().

2. Should it be considered an error when we split a partition owned by 
another user and get partitions that owned by our user?
(I think this is not a problem. Perhaps disallow merging other users' 
partitions would be too strict a restriction.)

3. About the functional index "create index on foo (run_me(a));".
(Should we disallow merging of another user's partitions when 
partitioned table has functional indexes? SECURITY_RESTRICTED_OPERATION?)

4. Need to decide what is correct in case there are per-partition 
constraints or triggers on a split partition. They not duplicated to the 
new partitions now. (But might be in this case we should have an error 
or warning?)

5. "If we're merging partitions, wouldn't it be better to adjust the 
constraints on the first partition - or perhaps the largest partition if 
we want to be clever -- and insert the data from all of the others into 
it? Maybe that would even have syntax that puts the user in control of 
which partition survives, e.g. ALTER TABLE tab1 MERGE PARTITION part1 
WITH part2, part3, .... That would also make it really obvious to the 
user what all of the properties of part1 will be after the merge: they 
will be exactly the same as they were before the merge, except that the 
partition constraint will have been adjusted."
(Similar optimization was proposed in [3] but was rejected [4]).

Links.
[1] 
https://www.postgresql.org/message-id/CA%2BTgmobHYix%3DNn8D4RUHa6fhUVPR88KGAMq1pBfnGfOfEjRixA%40mail.gmail.com.
[2] 
https://www.postgresql.org/message-id/CA%2BTgmoY0%3DbT_xBP8csR%3DMFE%3DFxGE2n2-me2-31jBOgEcLvW7ug%40mail.gmail.com
[3] 
https://www.postgresql.org/message-id/c3730d78-6081-4c41-9715-d1d192734576%40postgrespro.ru, 
see v31-0003-Additional-patch-for-ALTER-TABLE-.-MERGE-PARTITI.patch
[4] 
https://www.postgresql.org/message-id/CAPpHfdtj7YsPaASoVPN%2BN3H4_Ct%2BkQw8QY1d_9u7FPnbghkicw%40mail.gmail.com

-- 
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com