Slow autocomplete can block the whole Query-Tool (RM #7496)

[dpage]

Issue migrated from Redmine: https://redmine.postgresql.org/issues/7496
Originally created by Abdelhady Muhammad at 2022-06-22 12:00:33 UTC.

pgAdmin details:

Version: 6.10
Application Mode: Server
Browser: Chrome 102.0.0.0
Operating System: Linux-5.10.104-linuxkit-x86_64-with (because it is a dockerized version, but in fact, my main OS is MacOS Montery v12.3.1)
Log File: /dev/null

First thanks for introducing the autocomplete feature,

but away from the fact that it is very slow (may reach 2 sec or even more), it can block the whole Query-Tool, and make it useless,

Steps:

1. Specify the schemas first, using set search_path=public,'company#1234' (where I have a lot of company schemas)
2. Try to auto-complete a table name from the company schema
3. most of the time it just times out, and doesn't show any auto-complete results
4. at this point, when I try to run any other query in this same Query-Tool, it just gives me the error ERROR: execute cannot be used while an asynchronous query is underway .. where I can't run any other query inside this Query-Tool

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/7496#note-1
Originally created by Abdelhady Muhammad at 2022-06-22 12:18:45 UTC.

pgAdmin details:

Version: 6.10
Application Mode: Server
Browser: Chrome 102.0.0.0
Operating System: Linux-5.10.104-linuxkit-x86_64-with (because it is a dockerized version, but in fact, my main OS is MacOS Montery v12.3.1)
Log File: /dev/null

bug description:
First thanks for introducing the autocomplete feature,

but away from the fact that it is very slow (may reach 2 sec or even more), it can block the whole Query-Tool, and make it useless,

Steps:

Specify the schemas first, using @set search_path=public,'company#1234'@ (where I have a lot of company schemas)

Try to auto-complete a table name from the company schema

most of the time it just times out, and doesn't show any auto-complete results

at this point, when I try to run any other query in this same Query-Tool, it just gives me the error @error: execute cannot be used while an asynchronous query is underway@ .. where I can't run any other query inside this Query-Tool

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/7496#note-2
Originally created by Nikhil Mohite at 2022-06-24 11:18:27 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Sprint changed		Ready

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/7496#note-3
Originally created by Nikhil Mohite at 2022-06-24 11:18:59 UTC.

We can cache catalog information on the first load.

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/7496#note-4
Originally created by Luca Zambarda at 2022-08-10 08:48:04 UTC.

Nikhil Mohite wrote:

We can cache catalog information on the first load.

This would help a lot as currently I can see a degradation in the performance if autocomplete is on.
Could the cache also be refreshed after each query?

[Abdelhady]

I'm the original creator of the bug,

By the way, I made this comment just to re-write my bug in a redmin friendly style .. no need for it here in GitHub, since the original bug was written with a proper markdown ... we can just delete this mentioned comment

[yogeshmahajan-1903]

@Abdelhady

Could you please test this with latest version 7.6?

[Abdelhady]

Thanks for the followup, I do appreciate your work in this amazing tool,

I've just download v7.6 for mac M1 .. and as a disclaimer, I'm testing with a totally different DB than the one I submitted the bug with .. current DB has about a 100 tables distributed over 5 schemas,

I'm not seeing the error mentioned in the bug any more, and Query Tool was available the whole time with no blocking ..

But, the auto-complete rarely gives me any result now .. most of the time it just shows loading... .. and when it rarely shows me a list of options, I start typing forward to choose what I need, but the list doesn't get updated based on what I'm typing.

I think there isn't any type of caching in this feature, right?