Re: Declarative partitioning in pgAdmin4

Hi All

Following are the further implementation updates to support Declarative
Partitioning:

- Show all the existing partitions of the parent table in Partitions tab
(Refer Existing_Partitions.png)
- Ability to create N partitions and detach existing partitions. Refer
(Create_Detach_Partition.png), in this example I have detach two
existing partition and create two new partitions.
- Added "Detach Partition" menu to partitions node only and user will be
able to detach from there as well. Refer (Detach.png)

On Wed, May 24, 2017 at 8:00 PM, Robert Eckhardt <reckhardt(at)pivotal(dot)io>
wrote:

>
>
> On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi <
> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>
>>
>> Taking average of two columns is just an example/representation of
>> expression, there is no use case of that. As I am also in learning phase.
>> Below are some use case that I can think of:
>>
>> -
>>
>> Partitions based on first letter of their username
>>
>> CREATE TABLE users (
>> id serial not null,
>> username text not null,
>> password text,
>> created_on timestamptz not null,
>> last_logged_on timestamptz not null
>> )PARTITION BY RANGE ( lower( left( username, 1 ) ) );
>> CREATE TABLE users_0
>> partition of users (id, primary key (id), unique (username))
>> for values from ('a') to ('g');
>> CREATE TABLE users_1
>> partition of users (id, primary key (id), unique (username))
>> for values from ('g') to (unbounded);
>>
>> - Partition based on country's sale for each month of an year.
>>
>> CREATE TABLE public.sales
>>
>> (
>>
>> country text NOT NULL,
>>
>> sales bigint NOT NULL,
>>
>> saledate date
>>
>> ) PARTITION BY RANGE (country, (extract (YEAR FROM saledate)),
>> (extract(MONTH FROM saledate)))
>>
>>
>> CREATE TABLE public.sale_usa_2017_jan PARTITION OF sales
>>
>> FOR VALUES FROM ('usa', 2017, 01) TO ('usa', 2017, 02);
>>
>> CREATE TABLE public.sale_india_2017_jan PARTITION OF sales
>>
>> FOR VALUES FROM ('india', 2017, 01) TO ('india', 2017, 02);
>>
>> CREATE TABLE public.sale_uk_2017_jan PARTITION OF sales
>>
>> FOR VALUES FROM ('uk', 2017, 01) TO ('uk', 2017, 02);
>>
>>
>> INSERT INTO sales VALUES ('india', 10000, '2017-1-15');
>>
>> INSERT INTO sales VALUES ('uk', 20000, '2017-1-08');
>>
>> INSERT INTO sales VALUES ('usa', 30000, '2017-1-10');
>>
>> Apart from above there may be N number of use cases that depends on
>> specific requirement of user.
>>
>
> Thank you for the example, you are absolutely correct and we were
> confused.
>
> Given our new found understanding do you mind if we iterate a bit on the
> UI/UX? What we were suggesting with the daily/monthly/yearly drop down was
> a specific example of an expression. Given that fact that doesn't seem to
> be required in an MVP, however, I do think a more interactive experience
> between the definition of the child partitions and the creation of the
> partitions would be optimal.
>
> I'm not sure where you are with respect to implementing the UI but I'd
> love to float some ideas and mock ups past you.
>
> -- Rob
>

--
*Akshay Joshi*
*Principal Software Engineer *

*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*