Re: Query Tool Slow to Load

Hi Avin,

I create a config_local.py and try changing the below params. This will
avoid a clash between the dev environment and installed pgAdmin. Change the
DATA_DIR to one where you have permissions.

import os
import logging

DATA_DIR = '/Users/xyz/.pgadmin_dev'
LOG_FILE = os.path.join(DATA_DIR, 'pgadmin4.log')
SQLITE_PATH = os.path.join(DATA_DIR, 'pgadmin4.db')
SESSION_DB_PATH = os.path.join(DATA_DIR, 'sessions')
STORAGE_DIR = os.path.join(DATA_DIR, 'storage')
TEST_SQLITE_PATH = os.path.join(DATA_DIR, 'test_pgadmin4.db')
SESSION_COOKIE_NAME = 'pga4_session_schema_dev'


On Tue, Aug 6, 2019 at 9:42 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.
>
>   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,
>
> *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?
>
> 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
>>
>

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