On Wed, Jun 14, 2017 at 1:06 PM, Ashesh Vashi <ashesh.va...@enterprisedb.com > wrote:
> On Wed, Jun 14, 2017 at 1:59 PM, Dave Page <dp...@pgadmin.org> wrote: > >> >> >> On Tue, Jun 13, 2017 at 2:59 PM, Akshay Joshi < >> akshay.jo...@enterprisedb.com> 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? > I agree. > > -- 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 <reckha...@pivotal.io> >>> wrote: >>> >>>> >>>> >>>> On Tue, Jun 6, 2017 at 4:32 AM, Dave Page <dp...@pgadmin.org> 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 <dp...@pgadmin.org> wrote: >>>>>>>> >>>>>>>>> On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi < >>>>>>>>> akshay.jo...@enterprisedb.com> 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 < >>>>>>>>>> reckha...@pivotal.io> wrote: >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi < >>>>>>>>>>> akshay.jo...@enterprisedb.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-9517 <+91%2020%203058%209517>Mobile: +91 >>>>>>>>>> 976-788-8246 <+91%2097678%2088246>* >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> Sent via pgadmin-hackers mailing list ( >>>>>>>>>> pgadmin-hackers@postgresql.org) >>>>>>>>>> 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 >> > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company