Home > mailing lists

Thread: Query Tool Slow to Load

Query Tool Slow to Load

From

Avin Kavish

Date:

05 August 2019, 11:55:47

To Dave and maintainers,

Here's when the click event handler triggers,

Here is the first loading screen,

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,

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.

Attachment

Re: Query Tool Slow to Load

From

Dave Page

Date:

05 August 2019, 12:00:50

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@gmail.com> wrote:

To Dave and maintainers,

Here's when the click event handler triggers,

Here is the first loading screen,

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,

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

Attachment

Re: Query Tool Slow to Load

From

Avin Kavish

Date:

05 August 2019, 13:22:07

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. 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.

On Mon, Aug 5, 2019 at 5:31 PM Dave Page <dpage@pgadmin.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@gmail.com> wrote:
To Dave and maintainers,

Here's when the click event handler triggers,

Here is the first loading screen,

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,

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

Attachment

Re: Query Tool Slow to Load

From

Dave Page

Date:

05 August 2019, 13:42:01

On Mon, Aug 5, 2019 at 2:22 PM Avin Kavish <avinkavish@gmail.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@pgadmin.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@gmail.com> wrote:
To Dave and maintainers,

Here's when the click event handler triggers,

Here is the first loading screen,

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,

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

Attachment

Re: Query Tool Slow to Load

From

Avin Kavish

Date:

06 August 2019, 04:11:46

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@pgadmin.org> wrote:

Hi

On Mon, Aug 5, 2019 at 2:22 PM Avin Kavish <avinkavish@gmail.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@pgadmin.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@gmail.com> wrote:
To Dave and maintainers,

Here's when the click event handler triggers,

Here is the first loading screen,

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,

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

Attachment

Re: Query Tool Slow to Load

From

Aditya Toshniwal

Date:

06 August 2019, 05:38:33

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 <avinkavish@gmail.com> 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 <dpage@pgadmin.org> wrote:
Hi

On Mon, Aug 5, 2019 at 2:22 PM Avin Kavish <avinkavish@gmail.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@pgadmin.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@gmail.com> wrote:
To Dave and maintainers,

Here's when the click event handler triggers,

Here is the first loading screen,

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,

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"

Attachment

Re: Query Tool Slow to Load

From

Dave Page

Date:

06 August 2019, 08:12:03

On Tue, Aug 6, 2019 at 5:11 AM Avin Kavish <avinkavish@gmail.com> 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 <dpage@pgadmin.org> wrote:
Hi

On Mon, Aug 5, 2019 at 2:22 PM Avin Kavish <avinkavish@gmail.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@pgadmin.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@gmail.com> wrote:
To Dave and maintainers,

Here's when the click event handler triggers,

Here is the first loading screen,

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,

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

Attachment

Re: Query Tool Slow to Load

From

Aditya Toshniwal

Date:

14 August 2019, 05:08:22

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 <dpage@pgadmin.org> wrote:

Hi

On Tue, Aug 6, 2019 at 5:11 AM Avin Kavish <avinkavish@gmail.com> 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 <dpage@pgadmin.org> wrote:
Hi

On Mon, Aug 5, 2019 at 2:22 PM Avin Kavish <avinkavish@gmail.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@pgadmin.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@gmail.com> wrote:
To Dave and maintainers,

Here's when the click event handler triggers,

Here is the first loading screen,

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,

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"