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