Re: Query Tool Slow to Load

Hi Avin,

Are you able to work on this ? Kindly let us know so that someone else can
pick it up.
We should try to implement this before the next release ( tentatively
around 22nd Aug ).

On Tue, Aug 6, 2019 at 1:42 PM Dave Page <[email protected]> wrote:

> Hi
>
> On Tue, Aug 6, 2019 at 5:11 AM Avin Kavish <[email protected]> wrote:
>
>> I'm having trouble with the dev env. So I'm trying to develop in server
>> mode because I don't have any QT sdks installed. Once I enter the username,
>> password on first launch, I get this error.
>>
>
> It's usually easier to dev/test with the Python code in Desktop mode, even
> without using the desktop runtime. Just set SERVER_MODE=False in
> config_local.py, then run pgAdmin4.py from PyCharm or the command line.
> It'll detect that it's not running under the runtime and operate in desktop
> mode, but on port 5050 (by default - you can change that in config_local.py
> too).
>
>
>>
>>   File "./web/pgAdmin4.py", line 97, in <module>
>>>     app = create_app()
>>>   File "/home/avin/code/pgadmin4/web/pgadmin/__init__.py", line 330, in
>>> create_app
>>>     db_upgrade(app)
>>>   File "/home/avin/code/pgadmin4/web/pgadmin/setup/db_upgrade.py", line
>>> 25, in db_upgrade
>>>     flask_migrate.upgrade(migration_folder)
>>>   File "/usr/lib/python3/dist-packages/flask_migrate/__init__.py", line
>>> 259, in upgrade
>>>     command.upgrade(config, revision, sql=sql, tag=tag)
>>>  < --- shortened stack trace -->
>>>   File
>>> "/home/avin/code/pgadmin4/web/pgadmin/setup/../../migrations/env.py", line
>>> 94, in <module>
>>>     run_migrations_online()
>>>   File
>>> "/home/avin/code/pgadmin4/web/pgadmin/setup/../../migrations/env.py", line
>>> 87, in run_migrations_online
>>>     context.run_migrations()
>>>   File "<string>", line 8, in run_migrations
>>>   File "/usr/lib/python3/dist-packages/alembic/runtime/environment.py",
>>> line 836, in run_migrations
>>>     self.get_context().run_migrations(**kw)
>>>   File "/usr/lib/python3/dist-packages/alembic/runtime/migration.py",
>>> line 330, in run_migrations
>>>     step.migration_fn(**kw)
>>>   File
>>> "/home/avin/code/pgadmin4/web/migrations/versions/fdc58d9bd449_.py", line
>>> 48, in upgrade
>>>     sa.PrimaryKeyConstraint('name')
>>>   File "<string>", line 8, in create_table
>>>   File "<string>", line 3, in create_table
>>>  < --- shortened stack trace -->
>>> *sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table
>>> version already exists [SQL: '\nCREATE TABLE version (\n\tname VARCHAR(32)
>>> NOT NULL, \n\tvalue INTEGER NOT NULL, \n\tPRIMARY KEY (name)\n)\n\n']*
>>
>>
>> So I delete the database and  try again. Then I get this,
>>
>
> Deleting the database is the right call. That can happen if you kill the
> server when it's halfway through setting up the database.
>
>
>>
>> *sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to
>>> open database file*
>>
>>
>> But the database and the logs do get created. So I try again, and then I
>> get the first error again. I'm stuck in bit of a catch 22 scenario here and
>> don't know what to do. Any ideas hackers?
>>
>
> My guess would be that because you're running the code in server mode,
> it's probably trying to create/access the database under /var or similar,
> and SELinux is then silently stopping you having the access you expect.
> Switching to desktop mode, or moving the data directory in the config
> should fix that. Another quick check to see if that's the issue would be to
> do "sudo setenforce 0" to disable SELinux temporarily.
>
>
>>
>> On Mon, Aug 5, 2019 at 7:12 PM Dave Page <[email protected]> wrote:
>>
>>> Hi
>>>
>>> On Mon, Aug 5, 2019 at 2:22 PM Avin Kavish <[email protected]> wrote:
>>>
>>>> Yeah, I'll give it a go. If you don't hear back from me in a week, it
>>>> probably means I'm lost in the void between two AMD modules.
>>>>
>>>
>>> Cool, thanks.
>>>
>>>
>>>> Is there a WebStorm or VSCode extension that will help me cross that
>>>> gap? ctrl + click to go to definition doesn't seem to work.
>>>>
>>>
>>> No idea. I don't know if any of the devs use those IDEs (I think most of
>>> us probably use the free version of PyCharm). Maybe it's time we invested
>>> in the full version of PyCharm...
>>>
>>> Anyone else know?
>>>
>>>
>>>>
>>>> On Mon, Aug 5, 2019 at 5:31 PM Dave Page <[email protected]> wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> Yes - we were talking about that last week as it happens:
>>>>> https://redmine.postgresql.org/issues/4553
>>>>>
>>>>> Is this something you're interested to work on?
>>>>>
>>>>> On Mon, Aug 5, 2019 at 12:56 PM Avin Kavish <[email protected]>
>>>>> wrote:
>>>>>
>>>>>> To Dave and maintainers,
>>>>>>
>>>>>> Here's when the click event handler triggers,
>>>>>> [image: Query open.jpg]
>>>>>> Here is the first loading screen,
>>>>>> [image: query loading.jpg]
>>>>>> It takes  3.8 - 1.5 = 2.3s for the loading screen to show up.
>>>>>>
>>>>>>
>>>>>> Here's when the query tool is ready to use,
>>>>>> [image: Query close.jpg]
>>>>>>
>>>>>> It takes 4.7s - 1.5s = 3.2s total for the query tool to be ready
>>>>>> since click,  0.9s for UI instantiation.
>>>>>>
>>>>>> So, I looked at what is causing the delay, there is an ajax request
>>>>>> to initialize_datagrid that takes 2s to complete. It's primary job is to
>>>>>> return a transaction id under the key `gridTransId` after making the
>>>>>> connection to the database. Obviously, dns, ssl, and password
>>>>>> authentication to a remote database is going to incur delays. There is
>>>>>> nothing that can be done to prevent that. BUT, there is no need to delay
>>>>>> the loading of the query editor till the transaction id is received.
>>>>>>
>>>>>> this.on('pgadmin-datagrid:transaction:created', function(trans_obj) {
>>>>>> this.launch_grid(trans_obj);
>>>>>> });
>>>>>>
>>>>>> ^ This is where the delay happens. I suggest launching the grid
>>>>>> instantly behind the $spinner_el and remove the $spinner_el when the
>>>>>> transaction id is recieved and set via self.
>>>>>>
>>>>>> There are two primary hindrances to doing this, one being the
>>>>>> transaction id being used as a url param in new tab mode,
>>>>>>
>>>>>> var url_params = {
>>>>>> 'trans_id': trans_obj.gridTransId, // <<---- HERE
>>>>>> 'is_query_tool': trans_obj.is_query_tool,
>>>>>> 'editor_title': titileForURLObj.title,
>>>>>> },
>>>>>> baseUrl = url_for('datagrid.panel', url_params) +
>>>>>> '?' + 'query_url=' + encodeURI(trans_obj.sURL) +
>>>>>> '&server_type=' + encodeURIComponent(trans_obj.server_type) +
>>>>>> '&server_ver=' + trans_obj.serverVersion+
>>>>>> '&fslashes=' + titileForURLObj.slashLocations;
>>>>>>
>>>>>> The other being this close handler,
>>>>>>
>>>>>> queryToolPanel.on(wcDocker.EVENT.CLOSED, function() {
>>>>>> $.ajax({
>>>>>> url: url_for('datagrid.close', {'trans_id': trans_obj.gridTransId}), //
>>>>>> <<---- and HERE
>>>>>> method: 'DELETE',
>>>>>> });
>>>>>> });
>>>>>>
>>>>>> Looking at the python server, this id is nothing but a random number
>>>>>> between 1 and 9999999,
>>>>>> # Create a unique id for the transaction
>>>>>> trans_id = str(random.randint(1, 9999999))
>>>>>>
>>>>>> So instead of generating this id once the connection to the database
>>>>>> has been established, I suggest generating a random number in javascript
>>>>>> and POSTing it to the backend to be associated with the newly created
>>>>>> connection asynchronously while the query tool loads in the background. The
>>>>>> spinning indicator can be removed once the server sends an OK response once
>>>>>> the connection is established and associated with the number POSTed.
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> 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
>


-- 
Thanks and Regards,
Aditya Toshniwal
Software Engineer | EnterpriseDB India | Pune
"Don't Complain about Heat, Plant a TREE"