public inbox for [email protected]
help / color / mirror / Atom feedFrom: Aditya Toshniwal <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin][RM4988] Refactoring of all the SQL files used by pgAdmin4
Date: Mon, 16 Dec 2019 17:39:37 +0530
Message-ID: <CAM9w-_mgjt9ju6057rQi87XpaA5CXSnwkYa=pgLf7wvW4nCs-g@mail.gmail.com> (raw)
Hi Hackers,
Attached patch which is related to RM4988, reduces the number of hits
to edit_mode_types.sql
(an expensive one).
Currently, this is query is executed for every column one by one when table
properties dialog is opened. I've added edit_mode_types_bulk.sql, which
will fetch the edit types for all the distinct column types used in the
table in a single DB trip and use that data for each column.
Also, while generating RE-SQL for columns, edit_mode_types.sql will not be
executed.
This will definitely reduce time.
Kindly review.
--
Thanks and Regards,
Aditya Toshniwal
Sr. Software Engineer | EnterpriseDB India | Pune
"Don't Complain about Heat, Plant a TREE"
Attachments:
[application/octet-stream] RM4988.edit_types.patch (5.8K, 3-RM4988.edit_types.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py
index ee082810b..c0c40e417 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py
@@ -717,7 +717,10 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
column_utils.type_formatter(data['cltype'])
# We will add table & schema as well
- data = column_utils.column_formatter(self.conn, tid, clid, data)
+ # Passing edit_types_list param so that it does not fetch
+ # edit types. It is not required here.
+ data = column_utils.column_formatter(self.conn, tid, clid,
+ data, [])
SQL, name = self.get_sql(scid, tid, None, data, is_sql=True)
if not isinstance(SQL, (str, unicode)):
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py
index bad938142..15a3143c6 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py
@@ -64,7 +64,8 @@ def get_parent(conn, tid, template_path=None):
@get_template_path
-def column_formatter(conn, tid, clid, data, template_path=None):
+def column_formatter(conn, tid, clid, data, edit_types_list=None,
+ template_path=None):
"""
This function will return formatted output of query result
as per client model format for column node
@@ -144,25 +145,17 @@ def column_formatter(conn, tid, clid, data, template_path=None):
# we will send filtered types related to current type
type_id = data['atttypid']
- SQL = render_template("/".join([template_path, 'is_referenced.sql']),
- tid=tid, clid=clid)
-
- status, is_reference = conn.execute_scalar(SQL)
-
- edit_types_list = list()
- # We will need present type in edit mode
- edit_types_list.append(data['cltype'])
-
- if int(is_reference) == 0:
+ if edit_types_list is None:
+ edit_types_list = []
SQL = render_template("/".join([template_path,
'edit_mode_types.sql']),
type_id=type_id)
status, rset = conn.execute_2darray(SQL)
+ edit_types_list = [row['typname'] for row in rset['rows']]
- for row in rset['rows']:
- edit_types_list.append(row['typname'])
-
- data['edit_types'] = edit_types_list
+ # We will need present type in edit mode
+ edit_types_list.append(data['cltype'])
+ data['edit_types_list'] = edit_types_list
data['cltype'] = DataTypeReader.parse_type_name(data['cltype'])
@@ -191,9 +184,10 @@ def get_formatted_columns(conn, tid, data, other_columns,
raise Exception(res)
all_columns = res['rows']
-
+ edit_types = {}
# Add inherited from details from other columns - type, table
for col in all_columns:
+ edit_types[col['atttypid']] = []
for other_col in other_columns:
if col['name'] == other_col['name']:
col['inheritedfrom' + table_or_type] = \
@@ -202,8 +196,17 @@ def get_formatted_columns(conn, tid, data, other_columns,
data['columns'] = all_columns
if 'columns' in data and len(data['columns']) > 0:
+ SQL = render_template("/".join([template_path,
+ 'edit_mode_types_bulk.sql']),
+ type_ids=",".join(map(lambda x: str(x),
+ edit_types.keys())))
+ status, res = conn.execute_2darray(SQL)
+ for row in res['rows']:
+ edit_types[row['main_oid']] = row['edit_types']
+
for column in data['columns']:
- column_formatter(conn, tid, column['attnum'], column)
+ column_formatter(conn, tid, column['attnum'], column,
+ edit_types[col['atttypid']])
return data
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/edit_mode_types_bulk.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/edit_mode_types_bulk.sql
new file mode 100644
index 000000000..2ff192156
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/edit_mode_types_bulk.sql
@@ -0,0 +1,13 @@
+SELECT t.main_oid, ARRAY_AGG(t.typname) as edit_types
+FROM
+(SELECT pc.castsource as main_oid, format_type(tt.oid,NULL) AS typname
+FROM pg_type tt
+ JOIN pg_cast pc ON tt.oid=pc.casttarget
+ WHERE pc.castsource in ({{type_ids}})
+ AND pc.castcontext IN ('i', 'a')
+UNION
+SELECT tt.typbasetype as main_oid, format_type(tt.oid,NULL) AS typname
+FROM pg_type tt
+WHERE tt.typbasetype IN ({{type_ids}})
+) t
+GROUP BY t.main_oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/is_referenced.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/is_referenced.sql
deleted file mode 100644
index 52a4ffa86..000000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/is_referenced.sql
+++ /dev/null
@@ -1,6 +0,0 @@
-SELECT COUNT(1)
-FROM pg_depend dep
- JOIN pg_class cl ON dep.classid=cl.oid AND relname='pg_rewrite'
- WHERE refobjid= {{tid}}::oid
- AND classid='pg_class'::regclass
- AND refobjsubid= {{clid|qtLiteral}};
view thread (4+ 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]
Subject: Re: [pgAdmin][RM4988] Refactoring of all the SQL files used by pgAdmin4
In-Reply-To: <CAM9w-_mgjt9ju6057rQi87XpaA5CXSnwkYa=pgLf7wvW4nCs-g@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