Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Wed, Jun 14, 2017 at 1:06 PM, Ashesh Vashiwrote: > On Wed, Jun 14, 2017 at 1:59 PM, Dave Page 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 >>> wrote: >>> On Tue, Jun 6, 2017 at 4:32 AM, Dave Page wrote: > > For roll up this pattern seems obvious, identify the n partitions you >> need/want to combine and then run a job to
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Tue, Jun 13, 2017 at 2:59 PM, Akshay Joshiwrote: > 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? > >- *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 > wrote: > >> >> >> On Tue, Jun 6, 2017 at 4:32 AM, Dave Page 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 wrote: >> >>> On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi < >>> akshay.jo...@enterprisedb.com> wrote: >>> Hi All Following are the further
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Akshay, Have you determined the minimum feature set you are shooting for before you commit this? The reason I ask is that we were thinking that some level of simple automation would probably be nice to make this super useful. Basically if you consider a simple example of partitioning 90 days of data by day the manual process of creating the names and to - from fields becomes rather painful. If you couple that with potentially wanting to do list subpartitioning if just multiplies the work. If we could get something committed then we could more easily work to define where simple automation makes sense and where it doesn't. -- Rob On Tue, Jun 13, 2017 at 6:59 AM, Akshay Joshiwrote: > 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) > > >- *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 > >- *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. > Please review the design. Suggestions/Comments are welcome. > > > On Tue, Jun 6, 2017 at 4:30 PM, Robert Eckhardt > wrote: > >> >> >> On Tue, Jun 6, 2017 at 4:32 AM, Dave Page 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 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) -
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Tue, Jun 6, 2017 at 4:32 AM, Dave Pagewrote: > > 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 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', 1, '2017-1-15'); INSERT INTO sales VALUES ('uk', 2, '2017-1-08'); INSERT INTO sales VALUES ('usa', 3, '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
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Mon, Jun 5, 2017 at 5:17 PM, Robert Eckhardtwrote: > On Mon, Jun 5, 2017 at 11:45 AM, Dave Page wrote: > >> >> >> The former is what I was bleating about when I said we needed to expose >> partitions to the user. The latter isn't relevant - declarative >> partitioning in Postgres doesn't use inheritance. >> > > The former is certainly the most interesting. We do need to expose the > partitions but only exposing them individually might be a bit overwhelming. > What we found was that the number of partitions users have, (given existing > means of leveraging partitions) vary from ~100 up to 10k. Basically what we > were thinking about was how we can create a workflow/interface that allows > users to modify one or more children at once. Furthermore, it would be nice > if we could figure out an easy (easy-ish) way for users to identify the one > or more partitions that need to be modified. > Yes, that does need more thought. > > 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. > > 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 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 > 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
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
I guess what I didn't say is YES. What Akshay and Ashesh are building is going to absolutely be fundamental to any workflows being defined in these interviews. -- Rob On Mon, Jun 5, 2017 at 12:17 PM, Robert Eckhardtwrote: > On Mon, Jun 5, 2017 at 11:45 AM, Dave Page wrote: > >> >> >> The former is what I was bleating about when I said we needed to expose >> partitions to the user. The latter isn't relevant - declarative >> partitioning in Postgres doesn't use inheritance. >> > > The former is certainly the most interesting. We do need to expose the > partitions but only exposing them individually might be a bit overwhelming. > What we found was that the number of partitions users have, (given existing > means of leveraging partitions) vary from ~100 up to 10k. Basically what we > were thinking about was how we can create a workflow/interface that allows > users to modify one or more children at once. Furthermore, it would be nice > if we could figure out an easy (easy-ish) way for users to identify the one > or more partitions that need to be modified. > > For roll up this pattern seems obvious, identify the n partitions you > need/want to combine and then run a job to combine them. > > 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. > > -- 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 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 > 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
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Mon, Jun 5, 2017 at 11:45 AM, Dave Pagewrote: > > > The former is what I was bleating about when I said we needed to expose > partitions to the user. The latter isn't relevant - declarative > partitioning in Postgres doesn't use inheritance. > The former is certainly the most interesting. We do need to expose the partitions but only exposing them individually might be a bit overwhelming. What we found was that the number of partitions users have, (given existing means of leveraging partitions) vary from ~100 up to 10k. Basically what we were thinking about was how we can create a workflow/interface that allows users to modify one or more children at once. Furthermore, it would be nice if we could figure out an easy (easy-ish) way for users to identify the one or more partitions that need to be modified. For roll up this pattern seems obvious, identify the n partitions you need/want to combine and then run a job to combine them. 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. -- 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 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 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', 1, '2017-1-15'); >> >> INSERT INTO sales VALUES ('uk', 2, '2017-1-08'); >> >> INSERT INTO sales VALUES ('usa', 3, '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. > >
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi On Mon, Jun 5, 2017 at 4:34 PM, Shirley Wangwrote: > Hi! > > Here's a summary of the interviews thus far: > > DBAs tend to create a partition strategy based on their experience and > some alignment on their end users goals (analysts, report writers, and app > developers). Once that partition strategy is created, they are usually > forced to iterate on that strategy later based on feedback from end users > of what the usage pattern are. > > We've identified a couple workflows that are key in partitioning based on > whether they are maintaining a successful strategy or iterating to improve > the strategy. > > One workflow is for rollups, which is for maintaining partitions at > different granularities as data ages. We've learned that older data is less > acted upon than recent data so users group together older data for viewing > purposes. The other workflow is for splits, which when users discover that > the data isn't granular enough so a single partition is being leveraged too > many times. Users need to then reevaluate their strategy and tune > partitions. > > To reevaluate strategies, DBAs ask themselves a few questions > - Is the partition stable? > - Are the queries analysts, report writers, and app developers are writing > getting the correct data? > - Are the partitions organized in a way that analysts, report writers, and > app developers are able to achieve their goals? (ex. goals for app > developer might be fast query while goal for report writer might be ability > to get data so they can turn out reports faster. Goals might be conflicting) > > There are two needs from DBAs in terms of tuning partitioning strategies > (there are more but addressing these two will provide the most value to > users). One is to modify one or more child partitions by adding indexes or > other such things, and the other is to recreate the parent table because > there is inheritance to consider. > The former is what I was bleating about when I said we needed to expose partitions to the user. The latter isn't relevant - declarative partitioning in Postgres doesn't use inheritance. 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 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 >>> 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,
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi! Here's a summary of the interviews thus far: DBAs tend to create a partition strategy based on their experience and some alignment on their end users goals (analysts, report writers, and app developers). Once that partition strategy is created, they are usually forced to iterate on that strategy later based on feedback from end users of what the usage pattern are. We've identified a couple workflows that are key in partitioning based on whether they are maintaining a successful strategy or iterating to improve the strategy. One workflow is for rollups, which is for maintaining partitions at different granularities as data ages. We've learned that older data is less acted upon than recent data so users group together older data for viewing purposes. The other workflow is for splits, which when users discover that the data isn't granular enough so a single partition is being leveraged too many times. Users need to then reevaluate their strategy and tune partitions. To reevaluate strategies, DBAs ask themselves a few questions - Is the partition stable? - Are the queries analysts, report writers, and app developers are writing getting the correct data? - Are the partitions organized in a way that analysts, report writers, and app developers are able to achieve their goals? (ex. goals for app developer might be fast query while goal for report writer might be ability to get data so they can turn out reports faster. Goals might be conflicting) There are two needs from DBAs in terms of tuning partitioning strategies (there are more but addressing these two will provide the most value to users). One is to modify one or more child partitions by adding indexes or other such things, and the other is to recreate the parent table because there is inheritance to consider. 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 Pagewrote: > 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 >> 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', 1, '2017-1-15'); INSERT INTO sales VALUES ('uk', 2, '2017-1-08'); INSERT INTO sales VALUES ('usa', 3, '2017-1-10'); Apart from above there may be N number of use cases that depends on specific requirement of user.
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshiwrote: > 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 > 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', 1, '2017-1-15'); >>> >>> INSERT INTO sales VALUES ('uk', 2, '2017-1-08'); >>> >>> INSERT INTO sales VALUES ('usa', 3, '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
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Wed, May 24, 2017 at 3:35 AM, Akshay Joshiwrote: > >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', 1, '2017-1-15'); > > INSERT INTO sales VALUES ('uk', 2, '2017-1-08'); > > INSERT INTO sales VALUES ('usa', 3, '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
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi Shirley On Tue, May 23, 2017 at 7:39 PM, Shirley Wangwrote: > It's less about implementing what's easy, and more about implementing what > we know for sure will provide user value. > Agreed, but if that feature(expression) is available in postgresql then users/QA will ask why that is not present in pgAdmin4. If implementation is complex then we could think to include it or not, if we will provide that its upto the user they want to use it or not. > > Given that in the expression you used, the variables 'a' and 'b' have > undefined values, there's still a chance that we're missing a large chunk > of context for successful implementation. > > Could you explain what 'a' and 'b' represent? > Where would users find values for 'a' and 'b'? (are they columns?) > a and b are columns. > What is the use case for partitioning by (a+b)/2? > How frequently will people partition this way? > That is just an example of expression, there is no use case for above example. > > It's possible to design for the range and list partitions and know we can > achieve success because we understand how users would go through this > workflow. Not sure about expressions. > > > > -- *Akshay Joshi* *Principal Software Engineer * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Tue, May 23, 2017 at 10:09 AM, Shirley Wangwrote: > > It's possible to design for the range and list partitions and know we can > achieve success because we understand how users would go through this > workflow. Not sure about expressions. > Maybe to pile on this a bit. When Shirley and I were discussing the workflows it was obvious when we were looking at 'normal' range or list partition use cases. Generally the only open question we had about the workflow was whether or not users would be building tables net new or whether they were more likely to have a table that was growing too large and therefore needed to create a new partitioned table. We couldn't think of a reason why a user would want to take the average of two columns and partition by this derived value. It added to the question of why/how a user would consider this as an idea a priori or whether this would be an insight given analysis of existing data. I assume this was supported for a specific use case. if you could share that it would be awesome. I guess the long and short of it is, we are having a difficult time imagining the workflow for this feature. -- Rob
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Thu, May 18, 2017 at 11:41 AM, Akshay Joshi < akshay.jo...@enterprisedb.com> wrote: > 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 is still require for other syntax too. > > Please let me know above looks good and am I going in right direction. > Certainly looks like it to me. We may want to tweak some things based on the work Shirley is doing, but I think we're on the right path. Good work! > > > On Thu, May 11, 2017 at 7:06 PM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi Dave >> >> On Thu, May 11, 2017 at 6:54 PM, Dave Page>> wrote: >> >>> >>> >>> On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi < >>> akshay.jo...@enterprisedb.com> wrote: >>> Hi On Thu, May 4, 2017 at 4:00 PM, Dave Page wrote: > Hi > > On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi All >> >> On Wed, May 3, 2017 at 5:35 PM, Dave Page 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: >>
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi Dave On Thu, May 11, 2017 at 6:54 PM, Dave Pagewrote: > > > On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi >> >> On Thu, May 4, 2017 at 4:00 PM, Dave Page wrote: >> >>> Hi >>> >>> On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi < >>> akshay.jo...@enterprisedb.com> wrote: >>> Hi All On Wed, May 3, 2017 at 5:35 PM, Dave Page 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 >>>
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Akshay, Dave, I'm filling in for Shirley this week. If you are referring to the weekly meeting tomorrow, I'm planning on joining that and can walk through an exercise to help figure out prioritization. Thanks, Anne On Thu, May 11, 2017 at 9:24 AM Dave Pagewrote: > On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi >> >> On Thu, May 4, 2017 at 4:00 PM, Dave Page wrote: >> >>> Hi >>> >>> On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi < >>> akshay.jo...@enterprisedb.com> wrote: >>> Hi All On Wed, May 3, 2017 at 5:35 PM, Dave Page 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
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi < akshay.jo...@enterprisedb.com> wrote: > Hi > > On Thu, May 4, 2017 at 4:00 PM, Dave Pagewrote: > >> Hi >> >> On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi < >> akshay.jo...@enterprisedb.com> wrote: >> >>> Hi All >>> >>> On Wed, May 3, 2017 at 5:35 PM, Dave Page 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
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi On Thu, May 4, 2017 at 10:29 AM, Akshay Joshiwrote: > Hi All > > On Wed, May 3, 2017 at 5:35 PM, Dave Page 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. Thanks! -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi All On Wed, May 3, 2017 at 5:35 PM, Dave Pagewrote: > 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. > > > On Wed, May 3, 2017 at 1:00 PM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi Dave >> >> As per my understanding below operations required >> >> Parent: >> >>- View table data. >>- View stats. >>- Create regular/partitioned table >>- Create N number of partitions. >>- Drop/ Drop cascade, Truncate. >>- Attach/Detach Partitions. >>- Not able to create constraints excluding check constraint. >> >> Child: >> >>- View Table Data. >>- View stats. >>- View partition scheme in SQL pane >>- Create primary/foreign/.. key constraint. >>- Drop/ Drop cascade, Truncate >> >> >> On Tue, May 2, 2017 at 8:25 PM, Dave Page wrote: >> >>> Hi >>> >>> On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi < >>> akshay.jo...@enterprisedb.com> wrote: >>> Hi All To implement Declarative Partitioning in existing Table dialog below changes should be implemented: 1. *Icon: *As we have separate icon for view and materialised view, we should have for partition table. I didn't find any in font awesome. They are really different object types though (even having their own >>> collections), which isn't the case here. I'm not against having a slightly >>> modified icon, but I don't think it's necessary. Note that the object icons >>> come from pgAdmin III, and were
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
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. On Wed, May 3, 2017 at 1:00 PM, Akshay Joshiwrote: > Hi Dave > > As per my understanding below operations required > > Parent: > >- View table data. >- View stats. >- Create regular/partitioned table >- Create N number of partitions. >- Drop/ Drop cascade, Truncate. >- Attach/Detach Partitions. >- Not able to create constraints excluding check constraint. > > Child: > >- View Table Data. >- View stats. >- View partition scheme in SQL pane >- Create primary/foreign/.. key constraint. >- Drop/ Drop cascade, Truncate > > > On Tue, May 2, 2017 at 8:25 PM, Dave Page wrote: > >> Hi >> >> On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi < >> akshay.jo...@enterprisedb.com> wrote: >> >>> Hi All >>> >>> To implement Declarative Partitioning in existing Table dialog >>> below changes should be implemented: >>> >>>1. *Icon: *As we have separate icon for view and materialised view, >>>we should have for partition table. I didn't find any in font awesome. >>> >>> They are really different object types though (even having their own >> collections), which isn't the case here. I'm not against having a slightly >> modified icon, but I don't think it's necessary. Note that the object icons >> come from pgAdmin III, and were custom designed for us. They aren't in font >> awesome etc. We'd need to tweak one of the existing ones. >> >>> >>>1. *Inheritance*: >>> - A partition cannot have any parents other than the partitioned >>> table it is a partition of, nor can a regular table inherit from a >>> partitioned table making the latter its parent. That means partitioned >>> tables and partitions do not participate in inheritance with regular >>> tables. >>> - When user creates regular table then Inherited from table(s) >>> control should not display partitioned table. >>>2. *Constraints*: >>> - Primary/Foreign/Unique/Exclusion constraints are not supported >>> on partitioned table. In that case respective controls should be >>> disabled >>> for partitioned table. >>> - We will have to check which constraints are applicable on >>> partitions(of partitioned table) still some R require. Can someone >>> help >>> me here. >>> - For regular tables in Foreign Key constraints tab References >>> control should not list partition tables. >>> - Check constraints : cannot add NO INHERIT constraint to >>> partitioned table, so that control is disabled for partition table. >>>3. *Advanced Tab*: >>> - Relation works with partition table theirs is an error if "With >>> indexes?" is set to Yes, so we need to disabled that for partition >>> table. >>> - "Has OIDs?" and "Unlogged?" works but not sure about "Fill >>> factor" and "Of type". >>>4. *Parameter Tab*: >>> - Gives error (unrecognized parameter "autovacuum_enabled") for >>> all parameters of Table Tab and working fine for "Toast Table" >>> it's working. >>> >>> Can you detail what operations someone would likely want (or need) to >> perform on the parent/child tables; e.g. >> >> Parent: >> >> - View stats >> - View data >> - Truncate >> - View/create columns >> - Bulk-create indexes >> - Bulk-create foreign keys >> >> Child: >> >> - View stats >> - View data >> - Truncate >> - Create indexes >> - Create foreign keys >> >> >> >>> Apart from above we will have to do following: >>> >>>- Required switch control to specify whether it is a regular table >>>or partitioned table. I have added it on General tab. Please refer >>>Partition_Switch.png >>>- Will have to add new tab "Partition" which will have one select2 >>>control to define its Range partition or List partition. Refer >>>Partition_Tab.png >>> >>> "Partitions"? >> >>> >>>- Design following controls in *Partition* tab: >>> - How to add columns in case of Range/List partition? LIST >>> partition key supports only one column. For RANGE user can specify >>> multiple >>> columns. >>> - How to specify expression, COLLATE while adding columns >>> for partition. >>> - We need subnode control so that user will add number of >>> partition with there values of the main table. Need lot of R for >>> this. >>>- We will have to provide "Create partition", "Attach Partition" and >>>"Detech partition" context menu options
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi Dave As per my understanding below operations required Parent: - View table data. - View stats. - Create regular/partitioned table - Create N number of partitions. - Drop/ Drop cascade, Truncate. - Attach/Detach Partitions. - Not able to create constraints excluding check constraint. Child: - View Table Data. - View stats. - View partition scheme in SQL pane - Create primary/foreign/.. key constraint. - Drop/ Drop cascade, Truncate On Tue, May 2, 2017 at 8:25 PM, Dave Pagewrote: > Hi > > On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi All >> >> To implement Declarative Partitioning in existing Table dialog >> below changes should be implemented: >> >>1. *Icon: *As we have separate icon for view and materialised view, >>we should have for partition table. I didn't find any in font awesome. >> >> They are really different object types though (even having their own > collections), which isn't the case here. I'm not against having a slightly > modified icon, but I don't think it's necessary. Note that the object icons > come from pgAdmin III, and were custom designed for us. They aren't in font > awesome etc. We'd need to tweak one of the existing ones. > >> >>1. *Inheritance*: >> - A partition cannot have any parents other than the partitioned >> table it is a partition of, nor can a regular table inherit from a >> partitioned table making the latter its parent. That means partitioned >> tables and partitions do not participate in inheritance with regular >> tables. >> - When user creates regular table then Inherited from table(s) >> control should not display partitioned table. >>2. *Constraints*: >> - Primary/Foreign/Unique/Exclusion constraints are not supported >> on partitioned table. In that case respective controls should be >> disabled >> for partitioned table. >> - We will have to check which constraints are applicable on >> partitions(of partitioned table) still some R require. Can someone >> help >> me here. >> - For regular tables in Foreign Key constraints tab References >> control should not list partition tables. >> - Check constraints : cannot add NO INHERIT constraint to >> partitioned table, so that control is disabled for partition table. >>3. *Advanced Tab*: >> - Relation works with partition table theirs is an error if "With >> indexes?" is set to Yes, so we need to disabled that for partition >> table. >> - "Has OIDs?" and "Unlogged?" works but not sure about "Fill >> factor" and "Of type". >>4. *Parameter Tab*: >> - Gives error (unrecognized parameter "autovacuum_enabled") for >> all parameters of Table Tab and working fine for "Toast Table" >> it's working. >> >> Can you detail what operations someone would likely want (or need) to > perform on the parent/child tables; e.g. > > Parent: > > - View stats > - View data > - Truncate > - View/create columns > - Bulk-create indexes > - Bulk-create foreign keys > > Child: > > - View stats > - View data > - Truncate > - Create indexes > - Create foreign keys > > > >> Apart from above we will have to do following: >> >>- Required switch control to specify whether it is a regular table or >>partitioned table. I have added it on General tab. Please refer >>Partition_Switch.png >>- Will have to add new tab "Partition" which will have one select2 >>control to define its Range partition or List partition. Refer >>Partition_Tab.png >> >> "Partitions"? > >> >>- Design following controls in *Partition* tab: >> - How to add columns in case of Range/List partition? LIST >> partition key supports only one column. For RANGE user can specify >> multiple >> columns. >> - How to specify expression, COLLATE while adding columns >> for partition. >> - We need subnode control so that user will add number of >> partition with there values of the main table. Need lot of R for >> this. >>- We will have to provide "Create partition", "Attach Partition" and " >>Detech partition" context menu options on Partitions collection node. >> >> OK. > > Thanks! This is a complex one :-( > > >> Let me know if I forgot something to add that we may need to >> handle/implement. >> >> On Thu, Apr 27, 2017 at 9:14 PM, Robert Eckhardt >> wrote: >> >>> The issues we consistently face: > >- The huge (often thousands sometimes tens of thousands) number of >partitions makes rendering all of the partitions painfully slow and >frequently not useful. > > Perhaps, though I doubt that number would be common in Postgres. The problem though, is that there are both stats and sub-objects (indexes and triggers for example) that are part of the child partitions, not the
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi Shirley On Wed, May 3, 2017 at 3:31 AM, Shirley Wangwrote: > > > On Tue, May 2, 2017 at 10:56 AM Dave Page wrote: > >> Hi >> >> On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi < >> akshay.jo...@enterprisedb.com> wrote: >> >>> Hi All >>> >>> To implement Declarative Partitioning in existing Table dialog >>> below changes should be implemented: >>> >>>1. *Icon: *As we have separate icon for view and materialised view, >>>we should have for partition table. I didn't find any in font awesome. >>> >>> They are really different object types though (even having their own >> collections), which isn't the case here. I'm not against having a slightly >> modified icon, but I don't think it's necessary. Note that the object icons >> come from pgAdmin III, and were custom designed for us. They aren't in font >> awesome etc. We'd need to tweak one of the existing ones. >> >>> >>>1. *Inheritance*: >>> - A partition cannot have any parents other than the partitioned >>> table it is a partition of, nor can a regular table inherit from a >>> partitioned table making the latter its parent. That means partitioned >>> tables and partitions do not participate in inheritance with regular >>> tables. >>> - When user creates regular table then Inherited from table(s) >>> control should not display partitioned table. >>>2. *Constraints*: >>> - Primary/Foreign/Unique/Exclusion constraints are not supported >>> on partitioned table. In that case respective controls should be >>> disabled >>> for partitioned table. >>> - We will have to check which constraints are applicable on >>> partitions(of partitioned table) still some R require. Can someone >>> help >>> me here. >>> - For regular tables in Foreign Key constraints tab References >>> control should not list partition tables. >>> - Check constraints : cannot add NO INHERIT constraint to >>> partitioned table, so that control is disabled for partition table. >>>3. *Advanced Tab*: >>> - Relation works with partition table theirs is an error if "With >>> indexes?" is set to Yes, so we need to disabled that for partition >>> table. >>> - "Has OIDs?" and "Unlogged?" works but not sure about "Fill >>> factor" and "Of type". >>>4. *Parameter Tab*: >>> - Gives error (unrecognized parameter "autovacuum_enabled") for >>> all parameters of Table Tab and working fine for "Toast Table" >>> it's working. >>> >>> Can you detail what operations someone would likely want (or need) to >> perform on the parent/child tables; e.g. >> >> Parent: >> >> - View stats >> - View data >> - Truncate >> - View/create columns >> - Bulk-create indexes >> - Bulk-create foreign keys >> >> Child: >> >> - View stats >> - View data >> - Truncate >> - Create indexes >> - Create foreign keys >> >> >> >>> Apart from above we will have to do following: >>> >>>- Required switch control to specify whether it is a regular table >>>or partitioned table. I have added it on General tab. Please refer >>>Partition_Switch.png >>>- Will have to add new tab "Partition" which will have one select2 >>>control to define its Range partition or List partition. Refer >>>Partition_Tab.png >>> >>> "Partitions"? >> > > Is there a reason why 'Partition' needs to open in a new tab? If there's > only one field, we should include it on the same page since the tabs don't > dictate necessary steps in a sequential order. Users will be able to find > what they need without navigating to another part of the dialog. > There are lots of controls yet to design like column(s), number of partitions, partition schemes will be part of "Partitions" Tab. We will need that. > > Example: > [image: tablepartition-1.png] > [image: tablepartition-2.png] > > >>>- Design following controls in *Partition* tab: >>> - How to add columns in case of Range/List partition? LIST >>> partition key supports only one column. For RANGE user can specify >>> multiple >>> columns. >>> - How to specify expression, COLLATE while adding columns >>> for partition. >>> - We need subnode control so that user will add number of >>> partition with there values of the main table. Need lot of R for >>> this. >>>- We will have to provide "Create partition", "Attach Partition" and >>>"Detech partition" context menu options on Partitions collection >>>node. >>> >>> OK. >> >> Thanks! This is a complex one :-( >> >> >>> Let me know if I forgot something to add that we may need to >>> handle/implement. >>> >>> On Thu, Apr 27, 2017 at 9:14 PM, Robert Eckhardt >>> wrote: >>> The issues we consistently face: >> >>- The huge (often thousands sometimes tens of thousands) number >>of partitions makes rendering all of the partitions painfully slow and >>
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Tue, May 2, 2017 at 10:56 AM Dave Pagewrote: > Hi > > On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi All >> >> To implement Declarative Partitioning in existing Table dialog >> below changes should be implemented: >> >>1. *Icon: *As we have separate icon for view and materialised view, >>we should have for partition table. I didn't find any in font awesome. >> >> They are really different object types though (even having their own > collections), which isn't the case here. I'm not against having a slightly > modified icon, but I don't think it's necessary. Note that the object icons > come from pgAdmin III, and were custom designed for us. They aren't in font > awesome etc. We'd need to tweak one of the existing ones. > >> >>1. *Inheritance*: >> - A partition cannot have any parents other than the partitioned >> table it is a partition of, nor can a regular table inherit from a >> partitioned table making the latter its parent. That means partitioned >> tables and partitions do not participate in inheritance with regular >> tables. >> - When user creates regular table then Inherited from table(s) >> control should not display partitioned table. >>2. *Constraints*: >> - Primary/Foreign/Unique/Exclusion constraints are not supported >> on partitioned table. In that case respective controls should be >> disabled >> for partitioned table. >> - We will have to check which constraints are applicable on >> partitions(of partitioned table) still some R require. Can someone >> help >> me here. >> - For regular tables in Foreign Key constraints tab References >> control should not list partition tables. >> - Check constraints : cannot add NO INHERIT constraint to >> partitioned table, so that control is disabled for partition table. >>3. *Advanced Tab*: >> - Relation works with partition table theirs is an error if "With >> indexes?" is set to Yes, so we need to disabled that for partition >> table. >> - "Has OIDs?" and "Unlogged?" works but not sure about "Fill >> factor" and "Of type". >>4. *Parameter Tab*: >> - Gives error (unrecognized parameter "autovacuum_enabled") for >> all parameters of Table Tab and working fine for "Toast Table" >> it's working. >> >> Can you detail what operations someone would likely want (or need) to > perform on the parent/child tables; e.g. > > Parent: > > - View stats > - View data > - Truncate > - View/create columns > - Bulk-create indexes > - Bulk-create foreign keys > > Child: > > - View stats > - View data > - Truncate > - Create indexes > - Create foreign keys > > > >> Apart from above we will have to do following: >> >>- Required switch control to specify whether it is a regular table or >>partitioned table. I have added it on General tab. Please refer >>Partition_Switch.png >>- Will have to add new tab "Partition" which will have one select2 >>control to define its Range partition or List partition. Refer >>Partition_Tab.png >> >> "Partitions"? > Is there a reason why 'Partition' needs to open in a new tab? If there's only one field, we should include it on the same page since the tabs don't dictate necessary steps in a sequential order. Users will be able to find what they need without navigating to another part of the dialog. Example: [image: tablepartition-1.png] [image: tablepartition-2.png] >>- Design following controls in *Partition* tab: >> - How to add columns in case of Range/List partition? LIST >> partition key supports only one column. For RANGE user can specify >> multiple >> columns. >> - How to specify expression, COLLATE while adding columns >> for partition. >> - We need subnode control so that user will add number of >> partition with there values of the main table. Need lot of R for >> this. >>- We will have to provide "Create partition", "Attach Partition" and " >>Detech partition" context menu options on Partitions collection node. >> >> OK. > > Thanks! This is a complex one :-( > > >> Let me know if I forgot something to add that we may need to >> handle/implement. >> >> On Thu, Apr 27, 2017 at 9:14 PM, Robert Eckhardt >> wrote: >> >>> The issues we consistently face: > >- The huge (often thousands sometimes tens of thousands) number of >partitions makes rendering all of the partitions painfully slow and >frequently not useful. > > Perhaps, though I doubt that number would be common in Postgres. The problem though, is that there are both stats and sub-objects (indexes and triggers for example) that are part of the child partitions, not the parent - and they may differ from partition to partition. >>> >>> Certainly there differences in Postgres and Greenplum
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi On Tue, May 2, 2017 at 2:46 PM, Akshay Joshiwrote: > Hi All > > To implement Declarative Partitioning in existing Table dialog > below changes should be implemented: > >1. *Icon: *As we have separate icon for view and materialised view, we >should have for partition table. I didn't find any in font awesome. > > They are really different object types though (even having their own collections), which isn't the case here. I'm not against having a slightly modified icon, but I don't think it's necessary. Note that the object icons come from pgAdmin III, and were custom designed for us. They aren't in font awesome etc. We'd need to tweak one of the existing ones. > >1. *Inheritance*: > - A partition cannot have any parents other than the partitioned > table it is a partition of, nor can a regular table inherit from a > partitioned table making the latter its parent. That means partitioned > tables and partitions do not participate in inheritance with regular > tables. > - When user creates regular table then Inherited from table(s) > control should not display partitioned table. >2. *Constraints*: > - Primary/Foreign/Unique/Exclusion constraints are not supported on > partitioned table. In that case respective controls should be disabled > for > partitioned table. > - We will have to check which constraints are applicable on > partitions(of partitioned table) still some R require. Can someone > help > me here. > - For regular tables in Foreign Key constraints tab References > control should not list partition tables. > - Check constraints : cannot add NO INHERIT constraint to > partitioned table, so that control is disabled for partition table. >3. *Advanced Tab*: > - Relation works with partition table theirs is an error if "With > indexes?" is set to Yes, so we need to disabled that for partition > table. > - "Has OIDs?" and "Unlogged?" works but not sure about "Fill > factor" and "Of type". >4. *Parameter Tab*: > - Gives error (unrecognized parameter "autovacuum_enabled") for all > parameters of Table Tab and working fine for "Toast Table" it's > working. > > Can you detail what operations someone would likely want (or need) to perform on the parent/child tables; e.g. Parent: - View stats - View data - Truncate - View/create columns - Bulk-create indexes - Bulk-create foreign keys Child: - View stats - View data - Truncate - Create indexes - Create foreign keys > Apart from above we will have to do following: > >- Required switch control to specify whether it is a regular table or >partitioned table. I have added it on General tab. Please refer >Partition_Switch.png >- Will have to add new tab "Partition" which will have one select2 >control to define its Range partition or List partition. Refer >Partition_Tab.png > > "Partitions"? > >- Design following controls in *Partition* tab: > - How to add columns in case of Range/List partition? LIST > partition key supports only one column. For RANGE user can specify > multiple > columns. > - How to specify expression, COLLATE while adding columns > for partition. > - We need subnode control so that user will add number of partition > with there values of the main table. Need lot of R for this. >- We will have to provide "Create partition", "Attach Partition" and " >Detech partition" context menu options on Partitions collection node. > > OK. Thanks! This is a complex one :-( > Let me know if I forgot something to add that we may need to > handle/implement. > > On Thu, Apr 27, 2017 at 9:14 PM, Robert Eckhardt > wrote: > >> The issues we consistently face: - The huge (often thousands sometimes tens of thousands) number of partitions makes rendering all of the partitions painfully slow and frequently not useful. Perhaps, though I doubt that number would be common in Postgres. The >>> problem though, is that there are both stats and sub-objects (indexes and >>> triggers for example) that are part of the child partitions, not the parent >>> - and they may differ from partition to partition. >>> >> >> Certainly there differences in Postgres and Greenplum and this might very >> well be one of those places. >> >> >>> I don't see that we have any choice but to display them so users can >>> work with them. >>> >> >> We don't want to hide them, I do think we want to make accessing them a >> useful experience. If we rephrase this statement as "How might we display >> partitioned tables so that users are able to work with and modify the >> pieces they need?", this opens us up to different opportunities in how we >> display them. >> >> Even with a simple case of 90 days of data partitioned by day, a drop >> down showing 90
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
> > The issues we consistently face: >> >>- The huge (often thousands sometimes tens of thousands) number of >>partitions makes rendering all of the partitions painfully slow and >>frequently not useful. >> >> Perhaps, though I doubt that number would be common in Postgres. The > problem though, is that there are both stats and sub-objects (indexes and > triggers for example) that are part of the child partitions, not the parent > - and they may differ from partition to partition. > Certainly there differences in Postgres and Greenplum and this might very well be one of those places. > I don't see that we have any choice but to display them so users can work > with them. > We don't want to hide them, I do think we want to make accessing them a useful experience. If we rephrase this statement as "How might we display partitioned tables so that users are able to work with and modify the pieces they need?", this opens us up to different opportunities in how we display them. Even with a simple case of 90 days of data partitioned by day, a drop down showing 90 tables that are all mostly the same is a little overwhelming. > >>- When end users are interested in looking at their partitions they >>frequently don't want all of them displayed mindlessly >> - They are looking at a subset of partitions >> - Partitions are typically grouped around their inheritance >> properties. >> >> How might you propose grouping them (based on the way they work in > Postgres)? > Honestly I'm not sure. We didn't really start thinking about this until the other day so we are starting to look into the pains that Greenplum customers have. Sharing that pain we discover back to the pgAdmin community and seeing if it makes sense from a Postgres perspective. After that I need to dive into the Postgres implementation. -- Rob
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Thu, Apr 27, 2017 at 3:18 PM, Robert Eckhardtwrote: > > > On Thu, Apr 27, 2017 at 7:01 AM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi Dave >> >> As per discussion I have changed the logic of showing partitioned table >> in browser tree. Attached is the screenshot. >> Let me know your thoughts. >> > > Greenplum has had declarative partitioning for quite some time, I haven't > spent much time diving into the Postgres implementation specifically, > however, we have had some pain and I would suggest a little bit of thought > behind this. > > The issues we consistently face: > >- The huge (often thousands sometimes tens of thousands) number of >partitions makes rendering all of the partitions painfully slow and >frequently not useful. > > Perhaps, though I doubt that number would be common in Postgres. The problem though, is that there are both stats and sub-objects (indexes and triggers for example) that are part of the child partitions, not the parent - and they may differ from partition to partition. I don't see that we have any choice but to display them so users can work with them. > >- When end users are interested in looking at their partitions they >frequently don't want all of them displayed mindlessly > - They are looking at a subset of partitions > - Partitions are typically grouped around their inheritance > properties. > > How might you propose grouping them (based on the way they work in Postgres)? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Thu, Apr 27, 2017 at 12:01 PM, Akshay Joshi < akshay.jo...@enterprisedb.com> wrote: > Hi Dave > > As per discussion I have changed the logic of showing partitioned table in > browser tree. Attached is the screenshot. > Let me know your thoughts. > That's pretty much what I had in mind, yes. There are certain object types that would need to be rendered under the partitions themselves instead of the parent though. > > > On Thu, Apr 27, 2017 at 1:44 PM, Dave Pagewrote: > >> >> >> On Wed, Apr 26, 2017 at 6:36 PM, Shirley Wang wrote: >> >>> Hello! >>> >>> On Wed, Apr 26, 2017 at 4:26 AM Dave Page wrote: >>> Hi [moving to the pgadmin-hackers mailing list as this a pgAdmin feature] On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshi < akshay.jo...@enterprisedb.com> wrote: > Hi Dave > > Murtuza and I started thinking about "How to add Declarative > Partitioning" support in pgAdmin4. We thought instead of showing Partition > Table under existing Tables collection, we should add new collection node > "Partition Tables". Showing table under the table node recursively will > require lots of code changes in table and it's child nodes (column, index, > trigger, etc..) which is more complex and error prone. > Perhaps, but from the user's perspective, there's no reason to list them separately - they are just tables with a different structure from others. We shouldn't confuse the user just because it's more convenient for us. I really think it should look like this: - Tables - t1 - Columns - Constraints - Partitions - p1 - Sub Objects (whatever they may be) ... - p2 ... - t2 ... >>> > > Below is the design that we can implement: > >- Create new "Partition Tables" collection node. User will be able >to create partition table by clicking "Create -> Partition Table" menu > that >we will add on collection node. We will share the dialog prototype >later once we will have complete understanding of it. > > Can you share a mock-up of the dialog? The Figma tool that Shirley shared looks like it'll be good for doing that - I can invite you to the team. >>> >- Once table is created user will be able to create partitions by >clicking "Create -> Partitions" menu will be added on each partitioned >table node. We will share the dialog prototype later once we will >have complete understanding of it. > > I would expect the user to be able to define the partitioning scheme when they create the table; e.g. on a new tab. It shouldn't be a two step process. > >- We will have to show sub nodes like (column, index, trigger, >constraints, etc..) on main table while some of the sub nodes won't > require >for partitions like (column and many more again require some more > knowledge >on partitioning). > > OK. > Apart from above we will have to figure out following: > >- How to remove partitions(table) from existing tables node as >value of relkind column is 'r' for partitions. >- Partitioning scheme to show in SQL pane for partitions. >- Some unknown issue/features of Declarative partitioning. > > OK. >>> >>> Seems like there are a couple of assumptions being made here: >>> - Users need to see partitioned tables when expanding parent table >>> >> >> If by "assumption" you mean "fact", then yes :-). Users need to be able >> to see and manipulate partitions. Whilst some sub-objects are defined on >> the parent table (e.g. the columns), others are defined on the individual >> partitions (e.g. triggers, indexes). >> >> >>> - Users need to view partitioned tables in context to their parent table >>> (Dave says yes, Akshay and Murtuza say no) >>> >> >> That's not what was said. Akshay and Murtuza were proposing a new >> collection node, e.g. >> >> - Schema >> - Functions >> - Partitioned Tables >> - Tables >> - Views >> >> I'm saying that that unnecessarily complicates things for the user. The >> fact that a table happens to use declarative partitioning, doesn't make it >> a different type of object as far as Postgres is concerned, nor should it >> for us. >> >> >>> - Users want to create a partitioned table through the browser (Akshay >>> and Murtuza say yes, Dave says no) >>> >> >> I didn't say that. I said it shouldn't be a two-part process. >> >> >>> >>> Plus some technical concerns: >>> - Making code changes in table is complex and error prone >>> - How to move partitions from one node to another >>> >>> I think the first assumption is important to validate or invalidate >>> before even thinking about how to
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Thu, Apr 27, 2017 at 7:01 AM, Akshay Joshiwrote: > Hi Dave > > As per discussion I have changed the logic of showing partitioned table in > browser tree. Attached is the screenshot. > Let me know your thoughts. > Greenplum has had declarative partitioning for quite some time, I haven't spent much time diving into the Postgres implementation specifically, however, we have had some pain and I would suggest a little bit of thought behind this. The issues we consistently face: - The huge (often thousands sometimes tens of thousands) number of partitions makes rendering all of the partitions painfully slow and frequently not useful. - When end users are interested in looking at their partitions they frequently don't want all of them displayed mindlessly - They are looking at a subset of partitions - Partitions are typically grouped around their inheritance properties. -- Rob
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Wed, Apr 26, 2017 at 6:36 PM, Shirley Wangwrote: > Hello! > > On Wed, Apr 26, 2017 at 4:26 AM Dave Page wrote: > >> Hi >> >> [moving to the pgadmin-hackers mailing list as this a pgAdmin feature] >> >> On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshi < >> akshay.jo...@enterprisedb.com> wrote: >> >>> Hi Dave >>> >>> Murtuza and I started thinking about "How to add Declarative >>> Partitioning" support in pgAdmin4. We thought instead of showing Partition >>> Table under existing Tables collection, we should add new collection node >>> "Partition Tables". Showing table under the table node recursively will >>> require lots of code changes in table and it's child nodes (column, index, >>> trigger, etc..) which is more complex and error prone. >>> >> >> Perhaps, but from the user's perspective, there's no reason to list them >> separately - they are just tables with a different structure from others. >> We shouldn't confuse the user just because it's more convenient for us. >> >> I really think it should look like this: >> >> - Tables >> - t1 >> - Columns >> - Constraints >> - Partitions >> - p1 >> - Sub Objects (whatever they may be) >> ... >> - p2 >> ... >> - t2 >> ... >> >> > >> >>> >>> Below is the design that we can implement: >>> >>>- Create new "Partition Tables" collection node. User will be able >>>to create partition table by clicking "Create -> Partition Table" menu >>> that >>>we will add on collection node. We will share the dialog prototype >>>later once we will have complete understanding of it. >>> >>> Can you share a mock-up of the dialog? The Figma tool that Shirley >> shared looks like it'll be good for doing that - I can invite you to the >> team. >> > >>>- Once table is created user will be able to create partitions by >>>clicking "Create -> Partitions" menu will be added on each partitioned >>>table node. We will share the dialog prototype later once we will >>>have complete understanding of it. >>> >>> I would expect the user to be able to define the partitioning scheme >> when they create the table; e.g. on a new tab. It shouldn't be a two step >> process. >> >>> >>>- We will have to show sub nodes like (column, index, trigger, >>>constraints, etc..) on main table while some of the sub nodes won't >>> require >>>for partitions like (column and many more again require some more >>> knowledge >>>on partitioning). >>> >>> OK. >> >> >>> Apart from above we will have to figure out following: >>> >>>- How to remove partitions(table) from existing tables node as value >>>of relkind column is 'r' for partitions. >>>- Partitioning scheme to show in SQL pane for partitions. >>>- Some unknown issue/features of Declarative partitioning. >>> >>> OK. >> > > Seems like there are a couple of assumptions being made here: > - Users need to see partitioned tables when expanding parent table > If by "assumption" you mean "fact", then yes :-). Users need to be able to see and manipulate partitions. Whilst some sub-objects are defined on the parent table (e.g. the columns), others are defined on the individual partitions (e.g. triggers, indexes). > - Users need to view partitioned tables in context to their parent table > (Dave says yes, Akshay and Murtuza say no) > That's not what was said. Akshay and Murtuza were proposing a new collection node, e.g. - Schema - Functions - Partitioned Tables - Tables - Views I'm saying that that unnecessarily complicates things for the user. The fact that a table happens to use declarative partitioning, doesn't make it a different type of object as far as Postgres is concerned, nor should it for us. > - Users want to create a partitioned table through the browser (Akshay and > Murtuza say yes, Dave says no) > I didn't say that. I said it shouldn't be a two-part process. > > Plus some technical concerns: > - Making code changes in table is complex and error prone > - How to move partitions from one node to another > > I think the first assumption is important to validate or invalidate before > even thinking about how to implement or addressing technical concerns. We > may come to learn that there are solutions that don't require a lot of > technical maneuvering, or perhaps learn there's no need for change at all. > > Akshay and Murtuza, I'm happy to work with you on doing some research > (interviews to discover user needs and pains, creating mockups, getting > feedback etc) and coming up with some solutions based on user feedback. > How would users come up with feedback, given that the feature doesn't exist in the field yet? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
On Wed, Apr 26, 2017 at 11:06 PM, Shirley Wangwrote: > Hello! > > On Wed, Apr 26, 2017 at 4:26 AM Dave Page wrote: > >> Hi >> >> [moving to the pgadmin-hackers mailing list as this a pgAdmin feature] >> >> On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshi < >> akshay.jo...@enterprisedb.com> wrote: >> >>> Hi Dave >>> >>> Murtuza and I started thinking about "How to add Declarative >>> Partitioning" support in pgAdmin4. We thought instead of showing Partition >>> Table under existing Tables collection, we should add new collection node >>> "Partition Tables". Showing table under the table node recursively will >>> require lots of code changes in table and it's child nodes (column, index, >>> trigger, etc..) which is more complex and error prone. >>> >> >> Perhaps, but from the user's perspective, there's no reason to list them >> separately - they are just tables with a different structure from others. >> We shouldn't confuse the user just because it's more convenient for us. >> >> I really think it should look like this: >> >> - Tables >> - t1 >> - Columns >> - Constraints >> - Partitions >> - p1 >> - Sub Objects (whatever they may be) >> ... >> - p2 >> ... >> - t2 >> ... >> >> > >> >>> >>> Below is the design that we can implement: >>> >>>- Create new "Partition Tables" collection node. User will be able >>>to create partition table by clicking "Create -> Partition Table" menu >>> that >>>we will add on collection node. We will share the dialog prototype >>>later once we will have complete understanding of it. >>> >>> Can you share a mock-up of the dialog? The Figma tool that Shirley >> shared looks like it'll be good for doing that - I can invite you to the >> team. >> > >>>- Once table is created user will be able to create partitions by >>>clicking "Create -> Partitions" menu will be added on each partitioned >>>table node. We will share the dialog prototype later once we will >>>have complete understanding of it. >>> >>> I would expect the user to be able to define the partitioning scheme >> when they create the table; e.g. on a new tab. It shouldn't be a two step >> process. >> >>> >>>- We will have to show sub nodes like (column, index, trigger, >>>constraints, etc..) on main table while some of the sub nodes won't >>> require >>>for partitions like (column and many more again require some more >>> knowledge >>>on partitioning). >>> >>> OK. >> >> >>> Apart from above we will have to figure out following: >>> >>>- How to remove partitions(table) from existing tables node as value >>>of relkind column is 'r' for partitions. >>>- Partitioning scheme to show in SQL pane for partitions. >>>- Some unknown issue/features of Declarative partitioning. >>> >>> OK. >> > > Seems like there are a couple of assumptions being made here: > - Users need to see partitioned tables when expanding parent table > - Users need to view partitioned tables in context to their parent table > (Dave says yes, Akshay and Murtuza say no) > - Users want to create a partitioned table through the browser (Akshay and > Murtuza say yes, Dave says no) > > Plus some technical concerns: > - Making code changes in table is complex and error prone > - How to move partitions from one node to another > > I think the first assumption is important to validate or invalidate before > even thinking about how to implement or addressing technical concerns. We > may come to learn that there are solutions that don't require a lot of > technical maneuvering, or perhaps learn there's no need for change at all. > > Akshay and Murtuza, I'm happy to work with you on doing some research > (interviews to discover user needs and pains, creating mockups, getting > feedback etc) and coming up with some solutions based on user feedback. > Sure, it would be great. We will require some R about which feature/controls(Inheritance, Constraints, Indexes, Triggers and may be more) works with partitioning and which we will have to disabled if user will create partition table. > > > -- *Akshay Joshi* *Principal Software Engineer * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hello! On Wed, Apr 26, 2017 at 4:26 AM Dave Pagewrote: > Hi > > [moving to the pgadmin-hackers mailing list as this a pgAdmin feature] > > On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi Dave >> >> Murtuza and I started thinking about "How to add Declarative >> Partitioning" support in pgAdmin4. We thought instead of showing Partition >> Table under existing Tables collection, we should add new collection node >> "Partition Tables". Showing table under the table node recursively will >> require lots of code changes in table and it's child nodes (column, index, >> trigger, etc..) which is more complex and error prone. >> > > Perhaps, but from the user's perspective, there's no reason to list them > separately - they are just tables with a different structure from others. > We shouldn't confuse the user just because it's more convenient for us. > > I really think it should look like this: > > - Tables > - t1 > - Columns > - Constraints > - Partitions > - p1 > - Sub Objects (whatever they may be) > ... > - p2 > ... > - t2 > ... > > > >> >> Below is the design that we can implement: >> >>- Create new "Partition Tables" collection node. User will be able to >>create partition table by clicking "Create -> Partition Table" menu that >> we >>will add on collection node. We will share the dialog prototype later >>once we will have complete understanding of it. >> >> Can you share a mock-up of the dialog? The Figma tool that Shirley shared > looks like it'll be good for doing that - I can invite you to the team. > >>- Once table is created user will be able to create partitions by >>clicking "Create -> Partitions" menu will be added on each partitioned >>table node. We will share the dialog prototype later once we will >>have complete understanding of it. >> >> I would expect the user to be able to define the partitioning scheme when > they create the table; e.g. on a new tab. It shouldn't be a two step > process. > >> >>- We will have to show sub nodes like (column, index, trigger, >>constraints, etc..) on main table while some of the sub nodes won't >> require >>for partitions like (column and many more again require some more >> knowledge >>on partitioning). >> >> OK. > > >> Apart from above we will have to figure out following: >> >>- How to remove partitions(table) from existing tables node as value >>of relkind column is 'r' for partitions. >>- Partitioning scheme to show in SQL pane for partitions. >>- Some unknown issue/features of Declarative partitioning. >> >> OK. > Seems like there are a couple of assumptions being made here: - Users need to see partitioned tables when expanding parent table - Users need to view partitioned tables in context to their parent table (Dave says yes, Akshay and Murtuza say no) - Users want to create a partitioned table through the browser (Akshay and Murtuza say yes, Dave says no) Plus some technical concerns: - Making code changes in table is complex and error prone - How to move partitions from one node to another I think the first assumption is important to validate or invalidate before even thinking about how to implement or addressing technical concerns. We may come to learn that there are solutions that don't require a lot of technical maneuvering, or perhaps learn there's no need for change at all. Akshay and Murtuza, I'm happy to work with you on doing some research (interviews to discover user needs and pains, creating mockups, getting feedback etc) and coming up with some solutions based on user feedback.
Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Hi [moving to the pgadmin-hackers mailing list as this a pgAdmin feature] On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshiwrote: > Hi Dave > > Murtuza and I started thinking about "How to add Declarative > Partitioning" support in pgAdmin4. We thought instead of showing Partition > Table under existing Tables collection, we should add new collection node > "Partition Tables". Showing table under the table node recursively will > require lots of code changes in table and it's child nodes (column, index, > trigger, etc..) which is more complex and error prone. > Perhaps, but from the user's perspective, there's no reason to list them separately - they are just tables with a different structure from others. We shouldn't confuse the user just because it's more convenient for us. I really think it should look like this: - Tables - t1 - Columns - Constraints - Partitions - p1 - Sub Objects (whatever they may be) ... - p2 ... - t2 ... > > Below is the design that we can implement: > >- Create new "Partition Tables" collection node. User will be able to >create partition table by clicking "Create -> Partition Table" menu that we >will add on collection node. We will share the dialog prototype later >once we will have complete understanding of it. > > Can you share a mock-up of the dialog? The Figma tool that Shirley shared looks like it'll be good for doing that - I can invite you to the team. > >- Once table is created user will be able to create partitions by >clicking "Create -> Partitions" menu will be added on each partitioned >table node. We will share the dialog prototype later once we will have >complete understanding of it. > > I would expect the user to be able to define the partitioning scheme when they create the table; e.g. on a new tab. It shouldn't be a two step process. > >- We will have to show sub nodes like (column, index, trigger, >constraints, etc..) on main table while some of the sub nodes won't require >for partitions like (column and many more again require some more knowledge >on partitioning). > > OK. > Apart from above we will have to figure out following: > >- How to remove partitions(table) from existing tables node as value >of relkind column is 'r' for partitions. >- Partitioning scheme to show in SQL pane for partitions. >- Some unknown issue/features of Declarative partitioning. > > OK. > The above implementation may take more time, so it might possible that we > may not be able to finish it by 14th May (deadline). > It would be nice to have it by then, but the true deadline will be a later beta (TBD, but probably beta 2 which is sufficiently far off). -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company