Hi Dave, Please find updated patch with suggested changes.
On Wed, Nov 1, 2017 at 8:42 PM, Dave Page <dp...@pgadmin.org> wrote: > > > On Wed, Nov 1, 2017 at 2:40 PM, Murtuza Zabuawala <murtuza.zabuawala@ > enterprisedb.com> wrote: > >> In my opinion, displaying 2000+ instead of actual value doesn't looks >> good. >> >> Let say my threshold is set to 5000 and I have 1million rows, displaying >> 5000+ instead of actual value doesn't looks good. >> OR >> What we can do is, we can hide the field if it is empty/or less than >> threshold. >> > > I don't think it would necessarily look bad - after all, the estimated row > count will be displayed right above it. > > >> >> Thoughts? >> >> >> On Wed, Nov 1, 2017 at 7:55 PM, Dave Page <dp...@pgadmin.org> wrote: >> >>> >>> >>> On Wed, Nov 1, 2017 at 2:25 PM, Harshal Dhumal < >>> harshal.dhu...@enterprisedb.com> wrote: >>> >>>> Hi Murtuza, >>>> >>>> I think for tables having row count more than preference count it >>>> should show preference count with plus '+' sign instead empty. >>>> eg. 2000+ (for table with rows more than 2000 and for preference count >>>> of 2000.) >>>> >>> >>> That's a nice idea. >>> >>> >>>> >>>> >>>> [image: Inline image 1] >>>> >>>> >>>> >>>> -- >>>> *Harshal Dhumal* >>>> *Sr. Software Engineer* >>>> >>>> EnterpriseDB India: http://www.enterprisedb.com >>>> The Enterprise PostgreSQL Company >>>> >>>> On Wed, Nov 1, 2017 at 7:38 PM, Murtuza Zabuawala < >>>> murtuza.zabuaw...@enterprisedb.com> wrote: >>>> >>>>> Hi Dave, >>>>> >>>>> Please find updated patch. >>>>> >>>>> -- Murtuza >>>>> >>>>> >>>>> On Wed, Nov 1, 2017 at 6:50 PM, Murtuza Zabuawala < >>>>> murtuza.zabuaw...@enterprisedb.com> wrote: >>>>> >>>>>> Thanks Dave, I'll check the implementation in pgAdmin3 & send new >>>>>> patch with suggested changes. >>>>>> >>>>>> >>>>>> On Wed, Nov 1, 2017 at 6:41 PM, Dave Page <dp...@pgadmin.org> wrote: >>>>>> >>>>>>> Hi >>>>>>> >>>>>>> On Wed, Nov 1, 2017 at 11:58 AM, Murtuza Zabuawala < >>>>>>> murtuza.zabuaw...@enterprisedb.com> wrote: >>>>>>> >>>>>>>> Hi, >>>>>>>> >>>>>>>> PFA patch to fix the issue where "Rows (estimated)" field was not >>>>>>>> displaying long number properly. >>>>>>>> RM#2386 >>>>>>>> >>>>>>>> I have also added the code to count actual rows in a table [ "Rows >>>>>>>> (counted)" filed ] which was missing. >>>>>>>> >>>>>>> >>>>>>> Oops. Well the idea is good, but unfortunately we want to be careful >>>>>>> about when we count the rows, as we could have just clicked on a 10B row >>>>>>> table which would take a while. To avoid that, pgAdmin III would only >>>>>>> count >>>>>>> the rows in the table if the estimated number of rows was below a >>>>>>> specific >>>>>>> value (see File -> Preferences -> Browser -> Properties -> "Count rows >>>>>>> if >>>>>>> estimated less than"). >>>>>>> >>>>>>> I think we need to do the same here - add a preferences option, and >>>>>>> only count if estimated is greater than the specified value (the default >>>>>>> for which was 2000 in pgAdmin III I believe). >>>>>>> >>>>>>> -- >>>>>>> Dave Page >>>>>>> Blog: http://pgsnake.blogspot.com >>>>>>> Twitter: @pgsnake >>>>>>> >>>>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>>>> The Enterprise PostgreSQL Company >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>> >>> >>> -- >>> Dave Page >>> Blog: http://pgsnake.blogspot.com >>> Twitter: @pgsnake >>> >>> EnterpriseDB UK: http://www.enterprisedb.com >>> The Enterprise PostgreSQL Company >>> >> >> > > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
diff --git a/web/pgadmin/browser/__init__.py b/web/pgadmin/browser/__init__.py index f5b88a9..a70a751 100644 --- a/web/pgadmin/browser/__init__.py +++ b/web/pgadmin/browser/__init__.py @@ -195,6 +195,11 @@ class BrowserModule(PgAdminModule): gettext("Show system objects?"), 'boolean', False, category_label=gettext('Display') ) + self.table_row_count_threshold = self.preference.register( + 'properties', 'table_row_count_threshold', + gettext("Count rows if estimated less than"), 'integer', 2000, + category_label=gettext('Properties') + ) def get_exposed_url_endpoints(self): """ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py index e22e54c..5a93351 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py @@ -21,6 +21,7 @@ from pgadmin.browser.server_groups.servers.utils import parse_priv_to_db from pgadmin.utils.ajax import make_json_response, internal_server_error, \ make_response as ajax_response, gone from .utils import BaseTableView +from pgadmin.utils.preferences import Preferences class TableModule(SchemaChildModule): @@ -564,6 +565,32 @@ class TableView(BaseTableView, DataTypeReader, VacuumSettings): if len(res['rows']) == 0: return gone(gettext("The specified table could not be found.")) + # We will check the threshold set by user before executing + # the query because that can cause performance issues + # with large result set + pref = Preferences.module('browser') + table_row_count_pref = pref.preference('table_row_count_threshold') + table_row_count_threshold = table_row_count_pref.get() + estimated_row_count = int(res['rows'][0].get('reltuples', 0)) + + # If estimated rows are greater than threshold then + if estimated_row_count and \ + estimated_row_count > table_row_count_threshold: + res['rows'][0]['rows_cnt'] = str(table_row_count_threshold) + '+' + # If estimated rows is lower than threshold then calculate the count + elif estimated_row_count and \ + table_row_count_threshold >= estimated_row_count: + SQL = render_template( + "/".join( + [self.table_template_path, 'get_table_row_count.sql'] + ), data=res['rows'][0] + ) + status, count = self.conn.execute_scalar(SQL) + if not status: + return internal_server_error(errormsg=count) + + res['rows'][0]['rows_cnt'] = count + return super(TableView, self).properties( gid, sid, did, scid, tid, res) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/properties.sql index 6a6d44a..fb9c72e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/properties.sql @@ -7,7 +7,7 @@ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS r (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as parent_schema, nsp.nspname as schema, pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, rel.relispartition, - rel.relhassubclass, rel.reltuples, des.description, con.conname, con.conkey, + rel.relhassubclass, rel.reltuples::text, des.description, con.conname, con.conkey, EXISTS(select 1 FROM pg_trigger JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger' JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion' diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/10_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/10_plus/properties.sql index bc1a1cc..b0f7f72 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/10_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/10_plus/properties.sql @@ -7,7 +7,7 @@ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS r (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as schema, pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, rel.relkind, (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned, - rel.relhassubclass, rel.reltuples, des.description, con.conname, con.conkey, + rel.relhassubclass, rel.reltuples::text, des.description, con.conname, con.conkey, EXISTS(select 1 FROM pg_trigger JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger' JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion' diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql index 5ef8b12..c8602e0 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql @@ -6,7 +6,7 @@ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS r END) as spcname, (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as schema, pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, - rel.relhassubclass, rel.reltuples, des.description, con.conname, con.conkey, + rel.relhassubclass, rel.reltuples::text, des.description, con.conname, con.conkey, EXISTS(select 1 FROM pg_trigger JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger' JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion' diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/get_table_row_count.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/get_table_row_count.sql new file mode 100644 index 0000000..8788187 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/get_table_row_count.sql @@ -0,0 +1 @@ +SELECT COUNT(*)::text FROM {{ conn|qtIdent(data.schema, data.name) }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql index 60f2927..67acdc3 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql @@ -9,7 +9,7 @@ FROM ( END) as spcname, (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as schema, pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, - rel.relhassubclass, rel.reltuples, des.description, con.conname, con.conkey, + rel.relhassubclass, rel.reltuples::text, des.description, con.conname, con.conkey, EXISTS(select 1 FROM pg_trigger JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger' JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion' diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/properties.sql index 829507a..ca8e6bc 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/properties.sql @@ -9,7 +9,7 @@ FROM ( END) as spcname, (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as schema, pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, - rel.relhassubclass, rel.reltuples, des.description, con.conname, con.conkey, + rel.relhassubclass, rel.reltuples::text, des.description, con.conname, con.conkey, EXISTS(select 1 FROM pg_trigger JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger' JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'