Oops..that was due to float4->text (rel.reltuples::text) datatype casting
in postgres.
Please find updated patch.
On Thu, Nov 2, 2017 at 2:31 AM, Dave Page <dp...@pgadmin.org> wrote:
> Hi
>
> I created a table with:
>
> create table bigun as select * from generate_series(0, 1000000)
>
> I then selected it in the treeview, and it showed 0 for estimated rows,
> and nothing at all for counted rows. I ran a vacuum full on the new table,
> and now it fails to display the properties. On the console I see:
>
> Exception in thread Thread-1618:
> Traceback (most recent call last):
> File
> "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/threading.py",
> line 810, in __bootstrap_inner
> self.run()
> File
> "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/threading.py",
> line 763, in run
> self.__target(*self.__args, **self.__kwargs)
> File "/System/Library/Frameworks/Python.framework/Versions/2.7/
> lib/python2.7/SocketServer.py", line 602, in process_request_thread
> self.handle_error(request, client_address)
> File "/System/Library/Frameworks/Python.framework/Versions/2.7/
> lib/python2.7/SocketServer.py", line 599, in process_request_thread
> self.finish_request(request, client_address)
> File "/System/Library/Frameworks/Python.framework/Versions/2.7/
> lib/python2.7/SocketServer.py", line 334, in finish_request
> self.RequestHandlerClass(request, client_address, self)
> File "/System/Library/Frameworks/Python.framework/Versions/2.7/
> lib/python2.7/SocketServer.py", line 655, in __init__
> self.handle()
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/serving.py",
> line 200, in handle
> rv = BaseHTTPRequestHandler.handle(self)
> File "/System/Library/Frameworks/Python.framework/Versions/2.7/
> lib/python2.7/BaseHTTPServer.py", line 340, in handle
> self.handle_one_request()
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/serving.py",
> line 235, in handle_one_request
> return self.run_wsgi()
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/serving.py",
> line 177, in run_wsgi
> execute(self.server.app)
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/serving.py",
> line 165, in execute
> application_iter = app(environ, start_response)
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
> line 2000, in __call__
> return self.wsgi_app(environ, start_response)
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
> line 1991, in wsgi_app
> response = self.make_response(self.handle_exception(e))
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
> line 1567, in handle_exception
> reraise(exc_type, exc_value, tb)
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
> line 1988, in wsgi_app
> response = self.full_dispatch_request()
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
> line 1641, in full_dispatch_request
> rv = self.handle_user_exception(e)
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
> line 1544, in handle_user_exception
> reraise(exc_type, exc_value, tb)
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
> line 1639, in full_dispatch_request
> rv = self.dispatch_request()
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
> line 1625, in dispatch_request
> return self.view_functions[rule.endpoint](**req.view_args)
> File
> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/views.py",
> line 84, in view
> return self.dispatch_request(*args, **kwargs)
> File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/utils.py", line
> 242, in dispatch_request
> return method(*args, **kwargs)
> File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_
> groups/servers/databases/schemas/tables/utils.py", line 150, in wrap
> return f(*args, **kwargs)
> File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_
> groups/servers/databases/schemas/tables/__init__.py", line 574, in
> properties
> estimated_row_count = int(res['rows'][0].get('reltuples', 0))
> ValueError: invalid literal for int() with base 10: '1e+06'
>
>
> On Wed, Nov 1, 2017 at 4:28 PM, Murtuza Zabuawala <murtuza.zabuawala@
> enterprisedb.com> wrote:
>
>> 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.zabuaw...@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
>>>
>>
>>
>
>
> --
> 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..1b24ee5 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,8 +565,42 @@ 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
+
+ # If estimated_row_count is zero then set the row count with same
+ elif not estimated_row_count:
+ res['rows'][0]['rows_cnt'] = estimated_row_count
+
return super(TableView, self).properties(
- gid, sid, did, scid, tid, res)
+ gid, sid, did, scid, tid, res
+ )
@BaseTableView.check_precondition
def types(self, gid, sid, did, scid, tid=None, clid=None):
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..4b26857 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::bigint, 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..55b5e48 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::bigint, 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..c76c805 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::bigint, 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..641afaf 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::bigint, 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..85b4cda 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::bigint, 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'
