Re: Query Tool Slow to Load

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 <dpage(at)pgadmin(dot)org> wrote:

> Hi
>
> On Mon, Aug 5, 2019 at 2:22 PM Avin Kavish <avinkavish(at)gmail(dot)com> 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 <dpage(at)pgadmin(dot)org> 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 <avinkavish(at)gmail(dot)com>
>>> 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
>