public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ashesh Vashi <[email protected]>
To: Dave Page <[email protected]>
Cc: Akshay Joshi <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Cc: Shirley Wang <[email protected]>
Cc: Robert Eckhardt <[email protected]>
Subject: Re: Declarative partitioning in pgAdmin4
Date: Wed, 14 Jun 2017 17:36:16 +0530
Message-ID: <CAG7mmow7a1fhhL1WoWZFUDCe4mro+C_Gt=VCrA4db80e2xf1Aw@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxoy3PV8iH8OrpH=yXWCR3GgHQ1v4tqiXpVMhD5Dg_fQhBQ@mail.gmail.com>
References: <CANxoLDcZND0pjXtrDKRip2xjddzjWiMgY2AMmrqqFE_Yu4+tHw@mail.gmail.com>
<CA+OCxowUuaNRX9jHmEVFpqT7JCbjn6vaxw+JJ6yrvVp69FZscg@mail.gmail.com>
<CAPG3WN5NY-Xsa_+6HUQ3NMU_n7jRgJ8L6rjHfyzSLSHS=zZC0Q@mail.gmail.com>
<CA+OCxoy1v+mq2P4ZL2v7mmyHmjwQmL=v8RR8CSRra_SV96nJDQ@mail.gmail.com>
<CANxoLDeBGRmq_kUUNNySXimzJO2Ebj0aQBdjNM+0JvP3_Yr9Dw@mail.gmail.com>
<CAAtBm9Ve2FX4_jY9tv11UqK2BhNoLn118aeT4y=TieSAovL+AA@mail.gmail.com>
<CA+OCxozkEdTmVUtJEBdHT97EbiUK_+cwW+rv21tuHyxSnN3HOg@mail.gmail.com>
<CAAtBm9UHyp+bkxcyYL+1qb9knps_cdh6N0tvwMy5uY-eVjWcPg@mail.gmail.com>
<CANxoLDdgp46uAZzda+cHBn16YibodXgtyH7O1hp39TKT=cv_ig@mail.gmail.com>
<CA+OCxowpGBLT1q2DzL9VpRG5So8zYssP9SAdd=3Mc6dk8_-p7A@mail.gmail.com>
<CANxoLDdP945GEfzeYaPjO41D4VoRN2kDMVhHZCOqCXWKegSEHw@mail.gmail.com>
<CA+OCxowCzLAFybtfnsay9NB0BFORP5yXiitruxh9tvMoADNKRQ@mail.gmail.com>
<CANxoLDcqudMZ5j-30EcFEL9KpQxyvrMWo0mVrWdg0p6_8e7peQ@mail.gmail.com>
<CA+OCxow5pXNkDxrL1dbWbheJjpSseefSdvUs5tiwx7k5o3vB7Q@mail.gmail.com>
<CANxoLDeNovspn8mm0XuYh+F2ShGotwRCAikU5JY9qF1GgFQ9rg@mail.gmail.com>
<CA+OCxowtH1WJpXA1MKSLrzx_qbKAA36GTEk1t5=3VAS8fegBiA@mail.gmail.com>
<CANxoLDeLHGvz0NxH_MM7dCe0muA8Sxe54V65b18iHTAESzt97g@mail.gmail.com>
<CANxoLDeuXKCqrdNwiBut5m7FKQwzRjbPrqR6wHf8qKqgLDnwgQ@mail.gmail.com>
<CAPG3WN4tiMGoFadBZ9KjB8NfNDVfvDnfUHhS=aya5A0o-jZ3Xw@mail.gmail.com>
<CANxoLDfN_RvNc0AsVCtrDC-03L53crHzE8JZjmxna3f08KWVqw@mail.gmail.com>
<CAPG3WN5QA88fNmY4jZZhBY+HUn2FAKecHuvyFjnq2x_vGu4_0w@mail.gmail.com>
<CANxoLDfjy6sWQVHy5m5Rj1R5_=x_XwPzz6Mndj3xXfnEYpU_zg@mail.gmail.com>
<CAPG3WN7haKwrQzrgVh7JSunGcP9_6wj=_q_C9J-yYgsZbhWmEw@mail.gmail.com>
<CANxoLDeZ-izo=RSaHRnFNaAAQjxhd9-x6stx5FyLYU2ZA3A3vA@mail.gmail.com>
<CAPG3WN6sKefWWYfg9A5=f-QOO9HAsg7krsuQ6FZwvojEuvSjCA@mail.gmail.com>
<CAAtBm9Xw0qpvqRUb87AoSDdu56iaS8TaoVym3KkBJGjOgLU8cA@mail.gmail.com>
<CANxoLDegWFzkbUi=8KSL-3cPb0masCjD1HwxaMDhV6fs2uOObw@mail.gmail.com>
<CAAtBm9VpHahO2pbPM_ATowUU-YLT--RwWHmvW1Q+BtUGiCetyA@mail.gmail.com>
<CANxoLDc53XkKDO=8FHG1i7KnvPCCiR2-1DjCTQoV9_K4Z11pRQ@mail.gmail.com>
<CA+OCxoyEAPAra-nkS4qPVYEk3hHyVfRN-FQFPRfjSPrshwhsUg@mail.gmail.com>
<CAPG3WN72DS8gQmrFR_nBObYaeMaxiqVuyjsVqHaZR1BT4LDqHg@mail.gmail.com>
<CA+OCxozRODSQ9mdLnJWq4cbgHthQ9EqE7AE80kLbi6YPHBQMYg@mail.gmail.com>
<CAAtBm9Ua5WMPnXRb87Dr3+FMeuaSWsHSgpYX8AB=TS+PF63pPw@mail.gmail.com>
<CA+OCxozEKKgCNL9ng7KegYYeFdTU6hy+TdQFBp80W=Ew4XDesg@mail.gmail.com>
<CAAtBm9V89ndB8ZqU0MPsAsUQ-RMEzbjaG2nFfMmFr1vtaY=v=g@mail.gmail.com>
<CANxoLDeC9e+=ESBzoCSQeg4zgxwTz5zGG8HwYs9JNr90x4a-tA@mail.gmail.com>
<CA+OCxoy3PV8iH8OrpH=yXWCR3GgHQ1v4tqiXpVMhD5Dg_fQhBQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
On Wed, Jun 14, 2017 at 1:59 PM, Dave Page <[email protected]> wrote:
>
>
> On Tue, Jun 13, 2017 at 2:59 PM, Akshay Joshi <
> [email protected]> wrote:
>
>> Hi All
>>
>> For further implementation following task needs to be work upon:
>>
>> - How to parse and show partitions keys. For example user has created
>> below partitioned table
>>
>> CREATE TABLE public.sales
>> (
>> country character varying COLLATE pg_catalog."default" NOT NULL,
>> sales bigint,
>> saledate date
>> ) PARTITION BY RANGE (*country, date_part('year'::text, sale date)*)
>>
>> When user open the properties dialog I am not able to figure out how to
>> parse keys(displayed in bold in above example) and show them in our control
>> that we used. For the time being I have hide that control in 'Edit' mode
>> (Refer Attach Partition.png)
>>
>>
> I assume psql with display that info with \dt or similar? What does it do?
> Failing that, look at pg_dump?
>
psql, and pg_dump use 'pg_get_partkeydef' function for reverse engineering,
and we too.
They don't need particular key information.
In properties dialog, we need to find out - what individual partition key
is? (column/expression).
Let me give an example.
I have a partition table with the following definition (with two partition
keys).
*CREATE TABLE public.sales*
*(*
* country character varying COLLATE pg_catalog."default" NOT NULL,*
* sales bigint,*
* saledate date*
*) PARTITION BY RANGE (country, EXTRACT(year from saledate));*
And, the following query will give as:
*SELECT relname, pg_get_partkeydef(oid) FROM pg_catalog.pg_class WHERE
relname like 'sal%';*
relname | *pg_get_partkeydef*
---------+----------------------------------------------------
sales | *RANGE (country, date_part('year'::text, saledate))*
Here - we have two option in edit mode.
1. Parse the output of the '*pg_get_partkeydef'*, and identify all
individual keys, and its detailed information (i.e. column/expression)
2. Show that output about the partition keys in static control, and hide
the Partition type, partition keys controls.
I prefer the second option, as user can not modify the partition keys/type
(RANGE/LIST), and we will not have to write logic to parse the keys from
that output.
What do you say?
-- Thanks, Ashesh
>
>
>>
>> - *Support of sub partitioning*: To implement sub-partitioning,
>> specify the PARTITION BY clause in the commands used to create individual
>> partitions, for example:
>> -
>>
>> CREATE TABLE measurement_y2006 PARTITION OF measurement
>> FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
>> PARTITION BY RANGE (peaktemp);
>>
>>
>> To achieve above I have made some changes in GUI (Refer Sub
>> Partition.png).
>> *Complex and challenging part here is "measurement_y2006" is
>> partition of "measurement" and parent table for other partitions too which
>> user can create later. How we will going to show this in browser tree? *
>> One option could be
>> Tables
>> ->measurement(table)
>> ->Partitions
>> ->measurement_y2006(Partition of measurement and parent
>> of p1)
>> ->Partitions
>> ->p1
>>
>
> Urgh. But yeah. I think that makes logical sense.
>
>
>>
>> - *Attach Partitions*: To implement attach N partitions I have made
>> some changes in GUI( Refer Attach Partition.png). Attach Partitions
>> control will only be visible in "Edit" mode.
>>
>> I have only modified the UI changes, there are lots of work needs to be
>> done to complete that.
>>
>
> I don't think I'd include Attach on the dialog. I think it should be a
> separate menu option, with a simple dialogue to let the user choose the
> table to attach.
>
> The reason for that is that Attach is an action not a property. On the
> Properties panels we expect any changes we make to be the same the next
> time the dialogue is opened - e.g. if you toggle "Enable Trigger" to
> disabled and hit OK, then next time you open the dialogue you see the
> switch in the same position. With Attach, that's not the case - you'll list
> one or more tables to attach, hit OK, and when you next open the Properties
> dialogue, those partitions will be listed in the partition list, not the
> Attach list.
>
>
>> Please review the design. Suggestions/Comments are welcome.
>>
>>
>> On Tue, Jun 6, 2017 at 4:30 PM, Robert Eckhardt <[email protected]>
>> wrote:
>>
>>>
>>>
>>> On Tue, Jun 6, 2017 at 4:32 AM, Dave Page <[email protected]> wrote:
>>>
>>>>
>>>> For roll up this pattern seems obvious, identify the n partitions you
>>>>> need/want to combine and then run a job to combine them.
>>>>>
>>>>
>>>> You're thinking Greenplum :-). There is no roll up in PostgreSQL,
>>>> unless you're thinking we should create such a feature in pgAdmin.
>>>>
>>>> Of course, I have no objection to extending what we do in PG to add GP
>>>> feature support, but let's start with PG.
>>>>
>>>
>>> No not at all. That was a very specific and consistent pattern described
>>> by users leveraging time based range partitions in Postgres. I'm not sure
>>> if that same use case will be supported with partitioning as implemented in
>>> Postgres 10 but it is a Postgres pattern.
>>>
>>> -- Rob
>>>
>>>
>>>>
>>>>
>>>>>
>>>>> For other patterns such as creating indexes and such it requires a bit
>>>>> more thought. Generally users described wanting to treat all of the
>>>>> children like a single table (just like Oracle), however, other users
>>>>> described potentially modifying chunks of partitions differently depending
>>>>> on some criterion. This means that users will need to identify the subset
>>>>> they want to optimize and then ideally be able to act on them all at once.
>>>>>
>>>>
>>>> Right.
>>>>
>>>>
>>>>>
>>>>> -- Rob
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>
>>>>>> So... it sounds like we're on the right lines :-)
>>>>>>
>>>>>>
>>>>>>>
>>>>>>> For the former, this can be addressed by enabling users to modify
>>>>>>> one or more child partitions at the same time. For the latter, that is a
>>>>>>> workflow that might be addressed outside of the create table with partition
>>>>>>> workflow we're working on currently.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Mon, Jun 5, 2017 at 5:21 AM Dave Page <[email protected]> wrote:
>>>>>>>
>>>>>>>> On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi <
>>>>>>>> [email protected]> wrote:
>>>>>>>>
>>>>>>>>> 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)
>>>>>>>>>
>>>>>>>>> That's looking good to me :-)
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Wed, May 24, 2017 at 8:00 PM, Robert Eckhardt <
>>>>>>>>> [email protected]> wrote:
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi <
>>>>>>>>>> [email protected]> 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-9517 <+91%2020%203058%209517>Mobile: +91
>>>>>>>>> 976-788-8246 <+91%2097678%2088246>*
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Sent via pgadmin-hackers mailing list (
>>>>>>>>> [email protected])
>>>>>>>>> To make changes to your subscription:
>>>>>>>>> http://www.postgresql.org/mailpref/pgadmin-hackers
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Dave Page
>>>>>>>>
>>>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>>>> Twitter: @pgsnake
>>>>>>>>
>>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>>>> The Enterprise PostgreSQL Company
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Dave Page
>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>> Twitter: @pgsnake
>>>>>>
>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>> The Enterprise PostgreSQL Company
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Dave Page
>>>> Blog: http://pgsnake.blogspot.com
>>>> Twitter: @pgsnake
>>>>
>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>> The Enterprise PostgreSQL Company
>>>>
>>>
>>>
>>
>>
>> --
>> *Akshay Joshi*
>> *Principal Software Engineer *
>>
>>
>>
>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246
>> <+91%2097678%2088246>*
>>
>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
view thread (77+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Declarative partitioning in pgAdmin4
In-Reply-To: <CAG7mmow7a1fhhL1WoWZFUDCe4mro+C_Gt=VCrA4db80e2xf1Aw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox