public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: pgadmin-hackers <[email protected]>
Cc: Dave Page <[email protected]>
Cc: Shirley Wang <[email protected]>
Subject: Re: Declarative partitioning in pgAdmin4
Date: Thu, 18 May 2017 16:11:45 +0530
Message-ID: <CANxoLDeuXKCqrdNwiBut5m7FKQwzRjbPrqR6wHf8qKqgLDnwgQ@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDeLHGvz0NxH_MM7dCe0muA8Sxe54V65b18iHTAESzt97g@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>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi All
I have started implementation for Declarative Partitioning in pgAdmin4.
Following are the tasks that I have implemented till now:
- Show partitioned table and it's partitions under the parent table.
Refer Partitioned_Table.png
- To implement above I have created 'partitions' collection node and
'partition' node under table node which is nothing but table node itself.
To reduce redundant/duplicate code I have made following changes:
- Create new file "*utils.py*" under tables folder. Create a new
class BaseTableView(PGChildNodeView): derived from PGChildNodeView.
TableView and PartitionsView (new class for partition table) is
derived from BaseTableView.
- Move the common logic like dependencies, dependents, reversed
engineered sql, statistics, reset statistics in BaseTableView class
functions and then call that function from derived class like
BaseTableView.get_table_dependencies(self, tid)
- Will move more generic logic as we progress on this task.
- Updated supported nodes list in DataGrid(View Data),
Backup, Maintenance, Restore to show context menu for partitions.
- Make sure dependencies, dependents, statistics, truncate, delete/drop
and Reset Statistics works with partitions.
- Updated jinja template to show correct reversed engineered sql for
partitioned table. Please refer the "List_with_expression.png" for List
partition and "Range_with_column_expression.png" for Range partition.
- Updated jinja template to show correct sql for partitions of parent
table. Please refer "SQL_Range_Partitions.png" and "SQL_List_Partitions.
png". Some R&D is still require for other syntax too.
Please let me know above looks good and am I going in right direction.
On Thu, May 11, 2017 at 7:06 PM, Akshay Joshi <[email protected]
> wrote:
> Hi Dave
>
> On Thu, May 11, 2017 at 6:54 PM, Dave Page <[email protected]>
> wrote:
>
>>
>>
>> On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <
>> [email protected]> wrote:
>>
>>> Hi
>>>
>>> On Thu, May 4, 2017 at 4:00 PM, Dave Page <[email protected]> wrote:
>>>
>>>> Hi
>>>>
>>>> On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <
>>>> [email protected]> wrote:
>>>>
>>>>> Hi All
>>>>>
>>>>> On Wed, May 3, 2017 at 5:35 PM, Dave Page <[email protected]> wrote:
>>>>>
>>>>>> Great, thanks.
>>>>>>
>>>>>> I think it's clear that we need to display the child partitions in
>>>>>> the treeview. I don't see any other sensible way of enabling those
>>>>>> operations without an extremely contrived dialogue design.
>>>>>>
>>>>>> Please now document how those features will be implemented; e.g, for
>>>>>> each one:
>>>>>>
>>>>>> - View table data: Parent and partition context menu.
>>>>>> - Attach/detach partitions: Parent properties dialogue
>>>>>> ...
>>>>>>
>>>>>> That will then give us a list of places we'll need to (re)design
>>>>>> dialogues and menus etc. for.
>>>>>>
>>>>>
>>>>> As per my knowledge on Partitioning, I think we will have to
>>>>> implement following things in parent and child:
>>>>>
>>>>> Parent:
>>>>>
>>>>> 1. View Table data : No need to change any logic, it's working.
>>>>> 2. Correct jinja template to show correct SQL in SQL pane.
>>>>> 3. Create partitioned table -
>>>>> - Add one switch control ("Partitioned Table?") in General tab
>>>>> of Table dialog.
>>>>> - Add new tab "Partitions".
>>>>> - Add one select2 control (Partition Type :Range/List) in
>>>>> "Partitions" tab.
>>>>> - Create one subnode control to specify number of key columns
>>>>> with expressions. For List partition only one row will be there + button
>>>>> will be disabled, and for Range partition + button will be enabled. Here is
>>>>> the syntax as per documentation [ PARTITION BY { RANGE | LIST }
>>>>> ( { *column_name* | ( *expression* ) } [ COLLATE *collation* ]
>>>>> [ *opclass* ] [, ... ] ) ]. *Design discussion required here
>>>>> for how user will specify expression, collate and opclass*.
>>>>> 4. Create N number of partitions:
>>>>> - Design one control (subnode control) so that user will add N
>>>>> number of partitions. Here is the syntax as per documentation CREATE
>>>>> TABLE *table_name PARTITION OF parent_table [ ( {
>>>>> column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] |
>>>>> table_constraint } [, ... ] ) ] FOR VALUES partition_bound_spec *
>>>>> *partition_bound_spec* is:
>>>>>
>>>>> { IN ( { *bound_literal* | NULL } [, ...] ) |
>>>>> FROM ( { *bound_literal* | UNBOUNDED } [, ...] ) TO ( { *bound_literal* | UNBOUNDED } [, ...] ) }
>>>>>
>>>>> - *Design discussion required here for how user will specify
>>>>> all the above combinations.*
>>>>> 5. Properties dialog "Partitions" Tab:
>>>>> - Partition Type control must be disabled.
>>>>> - User will be able to create/modify existing partitions. User
>>>>> won't be able to delete partitions as there are two modes Detach/Drop and
>>>>> we will have separate menu for it.
>>>>> 6. Drop/ Drop cascade, Truncate: No need to change any logic, it's
>>>>> working.
>>>>> 7. Attach Partitions: Create context menu on partitioned table.
>>>>> When user clicks, open one dialog with some controls to provide
>>>>> table(to be attach) and partition_bound_spec*. Design discussion
>>>>> required here.*
>>>>> 8. Not able to create constraints excluding check constraint: We
>>>>> will have to disable context menu, remove child nodes from browser tree for
>>>>> constraints and disable controls from the dialog.
>>>>>
>>>>> Child:
>>>>>
>>>>> 1. View Table Data: Add context menu.
>>>>> 2. Detach partition: Create context menu, when user click popped
>>>>> up confirmation message box.
>>>>> 3. View partition scheme in SQL pane: Changes required in jinja
>>>>> template.
>>>>> 4. Create primary/foreign/.. key constraint: No need to change any
>>>>> logic on GUI, but may need to change queries to fetch the partitioned
>>>>> tables.
>>>>> 5. Drop/ Drop cascade, Truncate: No need to change any logic.
>>>>>
>>>>> Apart from above it may be possible that I miss something, so we
>>>>> need to cover that too.
>>>>>
>>>>
>>>> OK, good. So now, let's break that down into a list of tasks, that we
>>>> can prioritise with Shirley. The initial list should be prioritised based
>>>> on your understanding I think, given the following criteria:
>>>>
>>>> - Changes that prevent pgAdmin breaking
>>>> - Changes that prevent pgAdmin showing incorrect data/info
>>>> - Changes that enable pgAdmin to show correct info
>>>> - Changes that add functionality for creating/dropping partitioned
>>>> tables as one unit
>>>> - Changes that add functionality for modifying individual partitions
>>>> independently
>>>>
>>>> Please document the requirements and initial plan on the pgAdmin
>>>> Redmine Wiki.
>>>>
>>>
>>> I have updated Redmine Wiki page regarding what needs to be
>>> implemented for partitioning. Can we discuss prioritisation of the task
>>> based on above criteria in the our meeting. Meanwhile I have started
>>> working on showing correct SQL for partitioned table.
>>>
>>
>> Which meeting?
>>
>
> Meeting with Shirley, which wasn't schedule last Friday as I was on
> leave.
>
>>
>> --
>> Dave Page
>> VP, Chief Architect, Tools & Installers
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>
>
>
> --
> *Akshay Joshi*
> *Principal Software Engineer *
>
>
>
> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
>
--
*Akshay Joshi*
*Principal Software Engineer *
*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[image/png] List_with_expression.png (119.4K, 3-List_with_expression.png)
download | view image
[image/png] Partitioned_Table.png (106.4K, 4-Partitioned_Table.png)
download | view image
[image/png] Range_with_column_expression.png (123.2K, 5-Range_with_column_expression.png)
download | view image
[image/png] SQL_List_Partitions.png (91.6K, 6-SQL_List_Partitions.png)
download | view image
[image/png] SQL_Range_Partitions.png (91.2K, 7-SQL_Range_Partitions.png)
download | view image
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]
Subject: Re: Declarative partitioning in pgAdmin4
In-Reply-To: <CANxoLDeuXKCqrdNwiBut5m7FKQwzRjbPrqR6wHf8qKqgLDnwgQ@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