Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

[Dave Page]

On Wed, Jun 14, 2017 at 1:06 PM, Ashesh Vashi  wrote:

> 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

[Dave Page]

On Tue, Jun 13, 2017 at 2:59 PM, Akshay Joshi  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?


>
>- *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

[Robert Eckhardt]

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 Joshi  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)
>
>
>- *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

[Robert Eckhardt]

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)
>>- 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

[Dave Page]

On Mon, Jun 5, 2017 at 5:17 PM, Robert Eckhardt 
wrote:

> 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

[Robert Eckhardt]

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 Eckhardt 
wrote:

> 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

[Robert Eckhardt]

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 ('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

[Dave Page]

Hi

On Mon, Jun 5, 2017 at 4:34 PM, Shirley Wang  wrote:

> 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

[Shirley Wang]

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 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.

Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

[Dave Page]

On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi 
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.
>>
>> 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

[Robert Eckhardt]

On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi  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

Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

[Akshay Joshi]

Hi Shirley

On Tue, May 23, 2017 at 7:39 PM, Shirley Wang  wrote:

> 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

[Robert Eckhardt]

On Tue, May 23, 2017 at 10:09 AM, Shirley Wang  wrote:

>
> 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

[Dave Page]

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

[Akshay Joshi]

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:

   { 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

[Anne Thomas]

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 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:

   { 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

[Dave Page]

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
>> 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

[Dave Page]

Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi  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.

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

[Akshay Joshi]

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.


>
>
> 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

[Dave Page]

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 Joshi 
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 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

[Akshay Joshi]

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 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

[Akshay Joshi]

Hi Shirley

On Wed, May 3, 2017 at 3:31 AM, Shirley Wang  wrote:

>
>
> 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

[Shirley Wang]

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.

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

[Dave Page]

Hi

On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi 
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 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

[Robert Eckhardt]

>
> 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

[Dave Page]

On Thu, Apr 27, 2017 at 3:18 PM, Robert Eckhardt 
wrote:

>
>
> 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

[Dave Page]

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 Page  wrote:
>
>>
>>
>> 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

[Robert Eckhardt]

On Thu, Apr 27, 2017 at 7:01 AM, Akshay Joshi  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.
   - 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

[Dave Page]

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 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

[Akshay Joshi]

On Wed, Apr 26, 2017 at 11:06 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
> - 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

[Shirley Wang]

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.

Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

[Dave Page]

Hi

[moving to the pgadmin-hackers mailing list as this a pgAdmin feature]

On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshi  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.


> 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