public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: Dave Page <[email protected]>
Cc: Harshal Dhumal <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Cc: Robert Eckhardt <[email protected]>
Cc: Shirley Wang <[email protected]>
Cc: Ashesh Vashi <[email protected]>
Subject: Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Date: Sun, 25 Jun 2017 18:52:51 +0530
Message-ID: <CANxoLDdrzBZG8urt05qXDW06vVxaxdoWxszMp0S6Ymu3zcFmbQ@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxoze5yTR0Q4aHrnicUfRCRbHAVw5UJ-=Deng+Vb=KwGDUw@mail.gmail.com>
References: <CANxoLDcZND0pjXtrDKRip2xjddzjWiMgY2AMmrqqFE_Yu4+tHw@mail.gmail.com>
<CA+OCxowUuaNRX9jHmEVFpqT7JCbjn6vaxw+JJ6yrvVp69FZscg@mail.gmail.com>
<CAPG3WN5NY-Xsa_+6HUQ3NMU_n7jRgJ8L6rjHfyzSLSHS=zZC0Q@mail.gmail.com>
<CA+OCxoy1v+mq2P4ZL2v7mmyHmjwQmL=v8RR8CSRra_SV96nJDQ@mail.gmail.com>
<CANxoLDeBGRmq_kUUNNySXimzJO2Ebj0aQBdjNM+0JvP3_Yr9Dw@mail.gmail.com>
<CAAtBm9Ve2FX4_jY9tv11UqK2BhNoLn118aeT4y=TieSAovL+AA@mail.gmail.com>
<CA+OCxozkEdTmVUtJEBdHT97EbiUK_+cwW+rv21tuHyxSnN3HOg@mail.gmail.com>
<CAAtBm9UHyp+bkxcyYL+1qb9knps_cdh6N0tvwMy5uY-eVjWcPg@mail.gmail.com>
<CANxoLDdgp46uAZzda+cHBn16YibodXgtyH7O1hp39TKT=cv_ig@mail.gmail.com>
<CA+OCxowpGBLT1q2DzL9VpRG5So8zYssP9SAdd=3Mc6dk8_-p7A@mail.gmail.com>
<CANxoLDdP945GEfzeYaPjO41D4VoRN2kDMVhHZCOqCXWKegSEHw@mail.gmail.com>
<CA+OCxowCzLAFybtfnsay9NB0BFORP5yXiitruxh9tvMoADNKRQ@mail.gmail.com>
<CANxoLDcqudMZ5j-30EcFEL9KpQxyvrMWo0mVrWdg0p6_8e7peQ@mail.gmail.com>
<CA+OCxow5pXNkDxrL1dbWbheJjpSseefSdvUs5tiwx7k5o3vB7Q@mail.gmail.com>
<CANxoLDeNovspn8mm0XuYh+F2ShGotwRCAikU5JY9qF1GgFQ9rg@mail.gmail.com>
<CA+OCxowtH1WJpXA1MKSLrzx_qbKAA36GTEk1t5=3VAS8fegBiA@mail.gmail.com>
<CANxoLDeLHGvz0NxH_MM7dCe0muA8Sxe54V65b18iHTAESzt97g@mail.gmail.com>
<CANxoLDeuXKCqrdNwiBut5m7FKQwzRjbPrqR6wHf8qKqgLDnwgQ@mail.gmail.com>
<CAPG3WN4tiMGoFadBZ9KjB8NfNDVfvDnfUHhS=aya5A0o-jZ3Xw@mail.gmail.com>
<CANxoLDfN_RvNc0AsVCtrDC-03L53crHzE8JZjmxna3f08KWVqw@mail.gmail.com>
<CAPG3WN5QA88fNmY4jZZhBY+HUn2FAKecHuvyFjnq2x_vGu4_0w@mail.gmail.com>
<CANxoLDfjy6sWQVHy5m5Rj1R5_=x_XwPzz6Mndj3xXfnEYpU_zg@mail.gmail.com>
<CAPG3WN7haKwrQzrgVh7JSunGcP9_6wj=_q_C9J-yYgsZbhWmEw@mail.gmail.com>
<CANxoLDeZ-izo=RSaHRnFNaAAQjxhd9-x6stx5FyLYU2ZA3A3vA@mail.gmail.com>
<CAPG3WN6sKefWWYfg9A5=f-QOO9HAsg7krsuQ6FZwvojEuvSjCA@mail.gmail.com>
<CAAtBm9Xw0qpvqRUb87AoSDdu56iaS8TaoVym3KkBJGjOgLU8cA@mail.gmail.com>
<CANxoLDegWFzkbUi=8KSL-3cPb0masCjD1HwxaMDhV6fs2uOObw@mail.gmail.com>
<CAAtBm9VpHahO2pbPM_ATowUU-YLT--RwWHmvW1Q+BtUGiCetyA@mail.gmail.com>
<CANxoLDc53XkKDO=8FHG1i7KnvPCCiR2-1DjCTQoV9_K4Z11pRQ@mail.gmail.com>
<CA+OCxoyEAPAra-nkS4qPVYEk3hHyVfRN-FQFPRfjSPrshwhsUg@mail.gmail.com>
<CAPG3WN72DS8gQmrFR_nBObYaeMaxiqVuyjsVqHaZR1BT4LDqHg@mail.gmail.com>
<CA+OCxozRODSQ9mdLnJWq4cbgHthQ9EqE7AE80kLbi6YPHBQMYg@mail.gmail.com>
<CAAtBm9Ua5WMPnXRb87Dr3+FMeuaSWsHSgpYX8AB=TS+PF63pPw@mail.gmail.com>
<CA+OCxozEKKgCNL9ng7KegYYeFdTU6hy+TdQFBp80W=Ew4XDesg@mail.gmail.com>
<CAAtBm9V89ndB8ZqU0MPsAsUQ-RMEzbjaG2nFfMmFr1vtaY=v=g@mail.gmail.com>
<CANxoLDeC9e+=ESBzoCSQeg4zgxwTz5zGG8HwYs9JNr90x4a-tA@mail.gmail.com>
<CA+OCxoy3PV8iH8OrpH=yXWCR3GgHQ1v4tqiXpVMhD5Dg_fQhBQ@mail.gmail.com>
<CAG7mmow7a1fhhL1WoWZFUDCe4mro+C_Gt=VCrA4db80e2xf1Aw@mail.gmail.com>
<CA+OCxoxONjMu5BPgnFJsZApjPHC1owrNxkZOvUxLwp3nmN15=A@mail.gmail.com>
<CANxoLDcP71Fy-wG4ahw_ru-tKd0bigg-c+vqKxSHjnPeHKH4YQ@mail.gmail.com>
<CAPG3WN5vDsNnkQud-o08ebkUoXcKU1PPgEjNC-Xe7UrZgxGeQw@mail.gmail.com>
<CANxoLDck4-6uT8QsZWwc+VtWBbuG2HgUPsgSh6WhvV=r5zJBeQ@mail.gmail.com>
<CAPG3WN4imcBJ+OPpJtarhB_AqnDiHL-+upMSZaY=vafax+cc=Q@mail.gmail.com>
<CAFiP3vzws9ZjotnQVM+VX72+h38SgfhSGiEANURLeJw5gAfKhA@mail.gmail.com>
<CA+OCxoze5yTR0Q4aHrnicUfRCRbHAVw5UJ-=Deng+Vb=KwGDUw@mail.gmail.com>
List-Unsubscribe: <https://lists.postgresql.org/manage/>, <mailto:[email protected]>
Hi All
I have implemented test cases for partitioned table. Attached is the patch
file. Please review it and if it looks good then please commit it.
On Fri, Jun 23, 2017 at 8:54 PM, Dave Page <[email protected]> wrote:
> Thanks - Ashesh, can you review/commit this please? Additional reviews
> welcome as well of course.
>
> On Fri, Jun 23, 2017 at 2:25 PM, Harshal Dhumal <
> [email protected]> wrote:
>
>> Hi,
>>
>> Please find attached patch for partition support.
>>
>> This patch includes all the work done by Akashy and support for child
>> nodes (constraints, rules, index, triggers and partition itself ) under
>> partition node.
>>
>>
>> Thanks,
>>
>> --
>> *Harshal Dhumal*
>> *Sr. Software Engineer*
>>
>> EnterpriseDB India: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>> On Tue, Jun 20, 2017 at 12:16 AM, Shirley Wang <[email protected]> wrote:
>>
>>>
>>>
>>> On Mon, Jun 19, 2017 at 1:59 AM Akshay Joshi <
>>> [email protected]> wrote:
>>>
>>>> On Fri, Jun 16, 2017 at 11:16 PM, Shirley Wang <[email protected]>
>>>> wrote:
>>>>
>>>>> Looks good. I noticed people clicking back and forth to the columns
>>>>> tab to remember which columns they've created while filling out the
>>>>> Expressions column. It might be better to have a list of the columns and
>>>>> the datatype above the 'Partition Keys' subnode and have columns as a type
>>>>> field rather than a drop down.
>>>>>
>>>>
>>>> I think we should not duplicate that data as we already have all the
>>>> information on "Columns" tab and by providing drop down user can select
>>>> columns from there only.
>>>>
>>>>>
>>>>> Also, I think the fields someone sees after selecting the Key type
>>>>> needs to depend on what they select. Seeing both Column and Expressions
>>>>> type field might lead someone to think they need to fill out both fields.
>>>>>
>>>>
>>>> We can't, because user can select one column and provide an
>>>> expression as partition key in this case we will have to show both the
>>>> columns in subnode control. Anyways when user select columns I have
>>>> disabled the expression cell and if user selects expression column cell is
>>>> disabled.
>>>>
>>>
>>> Ah I see what you mean. What I meant was that the column would change
>>> depending on if someone selects Column or Expressions from the dropdown
>>> [image: expression.png]
>>> Can a user select more than one key type? The use case I can see where
>>> hiding 'Columns' or 'Expressions' would fail is if someone can create an
>>> expression key type and a column key type.
>>>
>>> Disabling a feature is one way to guide user behavior, but it doesn't
>>> provide enough context for someone to understand why it's disabled. It's
>>> better to only display what is absolutely necessary and hide fields that
>>> are unrelated to the workflow.
>>>
>>> Typically, disabling a UI element is useful when that disabled UI
>>> element also provides some context or value while disabled. In this case,
>>> I'm not sure it is.
>>>
>>> If hiding options isn't possible, providing some text in the fields
>>> (like N/A or --) would be helpful.
>>>
>>>
>>>
>>>>
>>>>> [image: coluns_partitioning.png]
>>>>> When is the 'In' column in the Partitions subnode enabled?
>>>>>
>>>>
>>>> In case of 'List' Partition.
>>>>
>>>
>>> It would improve the experience if the 'In' column was removed when a
>>> user selects 'Range' partitions then. And then if a user is creating a
>>> 'List' partition, 'From/To' should be hidden. In this case, 'From/To' and
>>> 'In' are dependent on that first drop down step, so seeing 'In' while on
>>> 'Range partitions' (and 'From/To' on 'List partitions') is not providing
>>> any value.
>>>
>>>
>>>>
>>>>> For the NoteControl on the bottom, what do 'Mode Control' or 'Attach
>>>>> Mode' refer to? And how can I tell the difference between 'Create Mode' and
>>>>> 'Edit Mode'?
>>>>>
>>>>
>>>> 'Mode control' is a switch control in subnode control that should be
>>>> "Mode switch control". 'Create Mode' is when user creates the new table by
>>>> clicking create-> table and 'Edit Mode' is when user open the properties
>>>> dialog for the existing table. In case of 'Edit Mode' there are two ways
>>>> user can create/attach partitions. In Attach mode we will identify and list
>>>> down the suitable tables to be attached.
>>>>
>>>
>>> I see. Describing these various states is great in case a user needs it.
>>> What are your thoughts on having it live in the documentation of pgAdmin4
>>> rather than in the dialog? This seems to be the established pattern for all
>>> other explanations.
>>>
>>>>
>>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
--
*Akshay Joshi*
*Principal Software Engineer *
*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
Attachments:
[image/png] expression.png (69.8K, 3-expression.png)
download | view image
[image/png] coluns_partitioning.png (78.6K, 4-coluns_partitioning.png)
download | view image
[application/octet-stream] Partition_Test_Case.patch (18.2K, 5-Partition_Test_Case.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/tests/test_column_get.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/tests/test_column_get.py
index 713d999..4adfbef 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/tests/test_column_get.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/tests/test_column_get.py
@@ -24,7 +24,7 @@ from . import utils as columns_utils
class ColumnGetTestCase(BaseTestGenerator):
"""This class will get column under table node."""
scenarios = [
- ('Fetch table Node URL', dict(url='/browser/column/obj/'))
+ ('Fetch columns under table node', dict(url='/browser/column/obj/'))
]
def setUp(self):
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_add.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_add.py
index 0703cba..9e4846c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_add.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_add.py
@@ -14,6 +14,7 @@ from pgadmin.browser.server_groups.servers.databases.schemas.tests import \
utils as schema_utils
from pgadmin.browser.server_groups.servers.databases.tests import utils as \
database_utils
+from pgadmin.browser.server_groups.servers.tests import utils as server_utils
from pgadmin.utils.route import BaseTestGenerator
from regression import parent_node_dict
from regression.python_test_utils import test_utils as utils
@@ -23,7 +24,19 @@ class TableAddTestCase(BaseTestGenerator):
""" This class will add new collation under schema node. """
scenarios = [
# Fetching default URL for table node.
- ('Fetch table Node URL', dict(url='/browser/table/obj/'))
+ ('Create Table', dict(url='/browser/table/obj/')),
+ ('Create Range partitioned table with 2 partitions',
+ dict(url='/browser/table/obj/',
+ server_min_version=100000,
+ partition_type='range'
+ )
+ ),
+ ('Create List partitioned table with 2 partitions',
+ dict(url='/browser/table/obj/',
+ server_min_version=100000,
+ partition_type='list'
+ )
+ )
]
def setUp(self):
@@ -43,6 +56,19 @@ class TableAddTestCase(BaseTestGenerator):
if not schema_response:
raise Exception("Could not find the schema to add a table.")
+ self.is_partition = False
+ if hasattr(self, 'server_min_version'):
+ server_con = server_utils.connect_server(self, self.server_id)
+ if not server_con["info"] == "Server connected.":
+ raise Exception("Could not connect to server to add "
+ "partitioned table.")
+ if server_con["data"]["version"] < self.server_min_version:
+ message = "Partitioned table are not supported by " \
+ "PPAS/PG 10.0 and below."
+ self.skipTest(message)
+ else:
+ self.is_partition = True
+
def runTest(self):
""" This function will add table under schema node. """
db_user = self.server["username"]
@@ -68,7 +94,7 @@ class TableAddTestCase(BaseTestGenerator):
"seclabels": []
},
{"name": "DOJ",
- "cltype": "date[]",
+ "cltype": "date",
"attacl": [],
"is_primary_key": False,
"attoptions": [],
@@ -76,7 +102,7 @@ class TableAddTestCase(BaseTestGenerator):
}
],
"exclude_constraint": [],
- "fillfactor": "11",
+ "fillfactor": "",
"hastoasttable": True,
"like_constraints": True,
"like_default_value": True,
@@ -166,6 +192,35 @@ class TableAddTestCase(BaseTestGenerator):
}
]
}
+
+ if self.is_partition:
+ data['partition_type'] = self.partition_type
+ data['is_partitioned'] = True
+ if self.partition_type == 'range':
+ data['partitions'] = \
+ [{'values_from': "'2010-01-01'",
+ 'values_to': "'2010-12-31'",
+ 'is_attach': False,
+ 'partition_name': 'emp_2010'
+ },
+ {'values_from': "'2011-01-01'",
+ 'values_to': "'2011-12-31'",
+ 'is_attach': False,
+ 'partition_name': 'emp_2011'
+ }]
+ else:
+ data['partitions'] = \
+ [{'values_in': "'2012-01-01', '2012-12-31'",
+ 'is_attach': False,
+ 'partition_name': 'emp_2012'
+ },
+ {'values_in': "'2013-01-01', '2013-12-31'",
+ 'is_attach': False,
+ 'partition_name': 'emp_2013'
+ }]
+ data['partition_keys'] = \
+ [{'key_type': 'column', 'pt_column': 'DOJ'}]
+
# Add table
response = self.tester.post(
self.url + str(utils.SERVER_GROUP) + '/' +
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_delete.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_delete.py
index 1b2f9f5..9749fed 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_delete.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_delete.py
@@ -23,7 +23,7 @@ class TableDeleteTestCase(BaseTestGenerator):
"""This class will delete new table under schema node."""
scenarios = [
# Fetching default URL for table node.
- ('Fetch table Node URL', dict(url='/browser/table/obj/'))
+ ('Delete Table', dict(url='/browser/table/obj/'))
]
def setUp(self):
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_put.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_put.py
index bfbf884..ef476d7 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_put.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_table_put.py
@@ -14,6 +14,7 @@ from pgadmin.browser.server_groups.servers.databases.schemas.tests import \
utils as schema_utils
from pgadmin.browser.server_groups.servers.databases.tests import utils as \
database_utils
+from pgadmin.browser.server_groups.servers.tests import utils as server_utils
from pgadmin.utils.route import BaseTestGenerator
from regression import parent_node_dict
from regression.python_test_utils import test_utils as utils
@@ -24,7 +25,49 @@ class TableUpdateTestCase(BaseTestGenerator):
"""This class will add new collation under schema node."""
scenarios = [
# Fetching default URL for table node.
- ('Fetch table Node URL', dict(url='/browser/table/obj/'))
+ ('Update Table', dict(url='/browser/table/obj/')),
+ ('Create partitions of existing range partitioned table',
+ dict(url='/browser/table/obj/',
+ server_min_version=100000,
+ partition_type='range',
+ mode='create'
+ )
+ ),
+ ('Create partitions of existing list partitioned table',
+ dict(url='/browser/table/obj/',
+ server_min_version=100000,
+ partition_type='list',
+ mode='create'
+ )
+ ),
+ ('Detach partition from existing range partitioned table',
+ dict(url='/browser/table/obj/',
+ server_min_version=100000,
+ partition_type='range',
+ mode='detach'
+ )
+ ),
+ ('Detach partition from existing list partitioned table',
+ dict(url='/browser/table/obj/',
+ server_min_version=100000,
+ partition_type='list',
+ mode='detach'
+ )
+ ),
+ ('Attach partition to existing range partitioned table',
+ dict(url='/browser/table/obj/',
+ server_min_version=100000,
+ partition_type='range',
+ mode='attach'
+ )
+ ),
+ ('Attach partition to existing list partitioned table',
+ dict(url='/browser/table/obj/',
+ server_min_version=100000,
+ partition_type='list',
+ mode='attach'
+ )
+ )
]
def setUp(self):
@@ -44,9 +87,31 @@ class TableUpdateTestCase(BaseTestGenerator):
if not schema_response:
raise Exception("Could not find the schema to add a table.")
self.table_name = "test_table_put_%s" % (str(uuid.uuid4())[1:6])
- self.table_id = tables_utils.create_table(self.server, self.db_name,
- self.schema_name,
- self.table_name)
+
+ self.is_partition = False
+ if hasattr(self, 'server_min_version'):
+ server_con = server_utils.connect_server(self, self.server_id)
+ if not server_con["info"] == "Server connected.":
+ raise Exception("Could not connect to server to add "
+ "partitioned table.")
+ if server_con["data"]["version"] < self.server_min_version:
+ message = "Partitioned table are not supported by " \
+ "PPAS/PG 10.0 and below."
+ self.skipTest(message)
+ else:
+ self.is_partition = True
+
+ self.table_id = tables_utils.create_table_for_partition(
+ self.server,
+ self.db_name,
+ self.schema_name,
+ self.table_name,
+ 'partitioned',
+ self.partition_type)
+ else:
+ self.table_id = tables_utils.create_table(self.server, self.db_name,
+ self.schema_name,
+ self.table_name)
def runTest(self):
"""This function will fetch added table under schema node."""
@@ -54,10 +119,18 @@ class TableUpdateTestCase(BaseTestGenerator):
self.table_id)
if not table_response:
raise Exception("Could not find the table to update.")
- data = {
- "description": "This is test comment for table",
- "id": self.table_id
- }
+
+ if self.is_partition:
+ data = {"id": self.table_id}
+ tables_utils.set_partition_data(
+ self.server, self.db_name, self.schema_name, self.table_name,
+ self.partition_type, data, self.mode)
+ else:
+ data = {
+ "description": "This is test comment for table",
+ "id": self.table_id
+ }
+
response = self.tester.put(
self.url + str(utils.SERVER_GROUP) + '/' +
str(self.server_id) + '/' + str(self.db_id) + '/' +
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/utils.py
index 456f020..0c7aede 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/utils.py
@@ -85,3 +85,153 @@ def verify_table(server, db_name, table_id):
except Exception:
traceback.print_exc(file=sys.stderr)
raise
+
+
+def create_table_for_partition(server, db_name, schema_name, table_name,
+ table_type, partition_type, partition_name=None):
+ """
+ This function creates partitioned/partition/regular table
+ under provided schema.
+
+ :param server: server details
+ :param db_name: database name
+ :param schema_name: schema name
+ :param table_name: table name
+ :param table_type: regular/partitioned/partition
+ :param partition_type: partition table type (range/list)
+ :param partition_name: Partition Name
+ :return table_id: table id
+ """
+ try:
+ connection = utils.get_db_connection(db_name,
+ server['username'],
+ server['db_password'],
+ server['host'],
+ server['port'])
+ old_isolation_level = connection.isolation_level
+ connection.set_isolation_level(0)
+ pg_cursor = connection.cursor()
+
+ query = ''
+ if table_type == 'partitioned':
+ if partition_type == 'range':
+ query = "CREATE TABLE %s.%s(country text, sales bigint, " \
+ "saledate date) PARTITION BY RANGE(saledate)" % \
+ (schema_name, table_name)
+ else:
+ query = "CREATE TABLE %s.%s(country text, sales bigint, " \
+ "saledate date) PARTITION BY LIST(saledate)" % \
+ (schema_name, table_name)
+ elif table_type == 'partition':
+ if partition_type == 'range':
+ query = "CREATE TABLE %s.%s PARTITION OF %s.%s " \
+ "FOR VALUES FROM ('2012-01-01') TO ('2012-12-31')" % \
+ (schema_name, partition_name, schema_name, table_name)
+ else:
+ query = "CREATE TABLE %s.%s PARTITION OF %s.%s " \
+ "FOR VALUES IN ('2013-01-01')" % \
+ (schema_name, partition_name, schema_name, table_name)
+
+ # To fetch OID table name is actually partition name
+ table_name = partition_name
+ elif table_type == 'regular':
+ query = "CREATE TABLE %s.%s(country text, sales bigint," \
+ "saledate date NOT NULL)" % (schema_name, table_name)
+
+ pg_cursor.execute(query)
+ connection.set_isolation_level(old_isolation_level)
+ connection.commit()
+ # Get 'oid' from newly created table
+ pg_cursor.execute("select oid from pg_class where relname='%s'" %
+ table_name)
+ table = pg_cursor.fetchone()
+ table_id = ''
+ if table:
+ table_id = table[0]
+ connection.close()
+ return table_id
+ except Exception:
+ traceback.print_exc(file=sys.stderr)
+ raise
+
+
+def set_partition_data(server, db_name, schema_name, table_name,
+ partition_type, data, mode):
+ """
+ This function is used to set the partitions data on the basis of
+ partition type and action.
+
+ :param server: server details
+ :param db_name: Database Name
+ :param schema_name: Schema Name
+ :param table_name: Table Name
+ :param partition_type: range or list
+ :param data: Data
+ :param mode: create/detach
+ :return:
+ """
+
+ data['partitions'] = dict()
+ if partition_type == 'range' and mode == 'create':
+ data['partitions'].update(
+ {'added': [{'values_from': "'2014-01-01'",
+ 'values_to': "'2014-12-31'",
+ 'is_attach': False,
+ 'partition_name': 'sale_2014'},
+ {'values_from': "'2015-01-01'",
+ 'values_to': "'2015-12-31'",
+ 'is_attach': False,
+ 'partition_name': 'sale_2015'
+ }]
+ }
+ )
+ elif partition_type == 'list' and mode == 'create':
+ data['partitions'].update(
+ {'added': [{'values_in': "'2016-01-01', '2016-12-31'",
+ 'is_attach': False,
+ 'partition_name': 'sale_2016'},
+ {'values_in': "'2017-01-01', '2017-12-31'",
+ 'is_attach': False,
+ 'partition_name': 'sale_2017'
+ }]
+ }
+ )
+ elif partition_type == 'range' and mode == 'detach':
+ partition_id = create_table_for_partition(server, db_name, schema_name,
+ table_name, 'partition',
+ partition_type, 'sale_2012')
+ data['partitions'].update(
+ {'deleted': [{'oid': partition_id}]
+ }
+ )
+ elif partition_type == 'list' and mode == 'detach':
+ partition_id = create_table_for_partition(server, db_name, schema_name,
+ table_name, 'partition',
+ partition_type, 'sale_2013')
+ data['partitions'].update(
+ {'deleted': [{'oid': partition_id}]
+ }
+ )
+ elif partition_type == 'range' and mode == 'attach':
+ partition_id = create_table_for_partition(server, db_name, schema_name,
+ 'attach_sale_2010', 'regular',
+ partition_type)
+ data['partitions'].update(
+ {'added': [{'values_from': "'2010-01-01'",
+ 'values_to': "'2010-12-31'",
+ 'is_attach': True,
+ 'partition_name': partition_id
+ }]
+ }
+ )
+ elif partition_type == 'list' and mode == 'attach':
+ partition_id = create_table_for_partition(server, db_name, schema_name,
+ 'attach_sale_2011', 'regular',
+ partition_type)
+ data['partitions'].update(
+ {'added': [{'values_in': "'2011-01-01'",
+ 'is_attach': True,
+ 'partition_name': partition_id
+ }]
+ }
+ )
view thread (77+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
In-Reply-To: <CANxoLDdrzBZG8urt05qXDW06vVxaxdoWxszMp0S6Ymu3zcFmbQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox