Re: Content assist / autocomplete hangs the server (latest pgAdmin version)

[Akshay Joshi]

On Mon, Aug 8, 2022 at 9:25 AM Aditya Toshniwal <
aditya.toshni...@enterprisedb.com> wrote:

> Akshay,
>
> We need to think on cancelling the autocomplete in the backend after a
> certain timeout.
>

Sure, will check.

>
> On Fri, Aug 5, 2022 at 7:08 PM Cherio  wrote:
>
>> Thanks Akshay, that's what I meant by "switched autocomplete to manual".
>>
>> That however doesn't really address the issue. I can still bring down the
>> pgAdmin backend (for everyone using it) by clicking Ctrl+Space on a
>> complex/rogue SQL.
>>
>> On Fri, Aug 5, 2022 at 4:35 AM Akshay Joshi <
>> akshay.jo...@enterprisedb.com> wrote:
>>
>>> Hi Cherio
>>>
>>> You can disable the automatic autocomplete option from "File ->
>>> Preferences -> Query Tool -> Auto completion -> Autocomplete on key press".
>>> We have also fixed an issue to stop autocomplete on arrow keys #7573
>>>  which will be available in
>>> the next release, meanwhile, you can install the snapshot build
>>> .
>>>
>>>
>>>
>>> On Wed, Aug 3, 2022 at 11:39 PM Cherio  wrote:
>>>
 I had to examine certain aspects of a query that looks like this:

 SELECT COUNT(*)
 FROM schema.table
 WHERE id IN (
 '1',
 '2',
 '3',
 
 '19998',
 '1',
 '2'
 )

 I pasted the query and autocomplete kicked in. For a minute it froze
 entirely. Then it sort of let me do things but everything was like in slow
 motion: the tree browser, the other SQL tabs - everything became slow as
 molasses. I logged onto the server and the "pgAdmin4.py" was
 keeping the CPU quite busy. It didn't recover for some time so I simply
 restarted the server and switched autocomplete to manual.

 Not knowing the design I may not be able to make a viable suggestion
 but maybe some sort of complexity counter (configurable or at least
 hard-coded at first) should be considered, which would hint to the
 autocomplete to stop trying, after it realizes the task may be too complex
 or takes too long to complete.

 Sure, a query like the one above should probably make use of
 a temporary table but it is beyond the point - there has to be a safeguard
 against an overloaded autocomplete. Without such a safeguard an
 ugly/invalid query or a user error could kill the server for all connected
 pgadmin clients.

>
>>>
>>> --
>>>
>>> 
>>>
>>> Akshay Joshi
>>>
>>> Principal Software Architect
>>>
>>> +91 9767888246
>>>
>>> www.enterprisedb.com
>>>
>>> 
>>> 
>>> 
>>> 
>>>
>>
>
> --
> Thanks,
> Aditya Toshniwal
> pgAdmin Hacker | Software Architect | *edbpostgres.com*
> 
> "Don't Complain about Heat, Plant a TREE"
>


-- 



Akshay Joshi

Principal Software Architect

+91 9767888246

www.enterprisedb.com

Re: Content assist / autocomplete hangs the server (latest pgAdmin version)

[Aditya Toshniwal]

Akshay,

We need to think on cancelling the autocomplete in the backend after a
certain timeout.

On Fri, Aug 5, 2022 at 7:08 PM Cherio  wrote:

> Thanks Akshay, that's what I meant by "switched autocomplete to manual".
>
> That however doesn't really address the issue. I can still bring down the
> pgAdmin backend (for everyone using it) by clicking Ctrl+Space on a
> complex/rogue SQL.
>
> On Fri, Aug 5, 2022 at 4:35 AM Akshay Joshi 
> wrote:
>
>> Hi Cherio
>>
>> You can disable the automatic autocomplete option from "File ->
>> Preferences -> Query Tool -> Auto completion -> Autocomplete on key press".
>> We have also fixed an issue to stop autocomplete on arrow keys #7573
>>  which will be available in
>> the next release, meanwhile, you can install the snapshot build
>> .
>>
>>
>>
>> On Wed, Aug 3, 2022 at 11:39 PM Cherio  wrote:
>>
>>> I had to examine certain aspects of a query that looks like this:
>>>
>>> SELECT COUNT(*)
>>> FROM schema.table
>>> WHERE id IN (
>>> '1',
>>> '2',
>>> '3',
>>> 
>>> '19998',
>>> '1',
>>> '2'
>>> )
>>>
>>> I pasted the query and autocomplete kicked in. For a minute it froze
>>> entirely. Then it sort of let me do things but everything was like in slow
>>> motion: the tree browser, the other SQL tabs - everything became slow as
>>> molasses. I logged onto the server and the "pgAdmin4.py" was
>>> keeping the CPU quite busy. It didn't recover for some time so I simply
>>> restarted the server and switched autocomplete to manual.
>>>
>>> Not knowing the design I may not be able to make a viable suggestion but
>>> maybe some sort of complexity counter (configurable or at least hard-coded
>>> at first) should be considered, which would hint to the autocomplete to
>>> stop trying, after it realizes the task may be too complex or takes too
>>> long to complete.
>>>
>>> Sure, a query like the one above should probably make use of a temporary
>>> table but it is beyond the point - there has to be a safeguard against an
>>> overloaded autocomplete. Without such a safeguard an ugly/invalid query or
>>> a user error could kill the server for all connected pgadmin clients.
>>>

>>
>> --
>>
>> 
>>
>> Akshay Joshi
>>
>> Principal Software Architect
>>
>> +91 9767888246
>>
>> www.enterprisedb.com
>>
>> 
>> 
>> 
>> 
>>
>

-- 
Thanks,
Aditya Toshniwal
pgAdmin Hacker | Software Architect | *edbpostgres.com*

"Don't Complain about Heat, Plant a TREE"

Re: Content assist / autocomplete hangs the server (latest pgAdmin version)

[Cherio]

Thanks Akshay, that's what I meant by "switched autocomplete to manual".

That however doesn't really address the issue. I can still bring down the
pgAdmin backend (for everyone using it) by clicking Ctrl+Space on a
complex/rogue SQL.

On Fri, Aug 5, 2022 at 4:35 AM Akshay Joshi 
wrote:

> Hi Cherio
>
> You can disable the automatic autocomplete option from "File ->
> Preferences -> Query Tool -> Auto completion -> Autocomplete on key press".
> We have also fixed an issue to stop autocomplete on arrow keys #7573
>  which will be available in
> the next release, meanwhile, you can install the snapshot build
> .
>
>
>
> On Wed, Aug 3, 2022 at 11:39 PM Cherio  wrote:
>
>> I had to examine certain aspects of a query that looks like this:
>>
>> SELECT COUNT(*)
>> FROM schema.table
>> WHERE id IN (
>> '1',
>> '2',
>> '3',
>> 
>> '19998',
>> '1',
>> '2'
>> )
>>
>> I pasted the query and autocomplete kicked in. For a minute it froze
>> entirely. Then it sort of let me do things but everything was like in slow
>> motion: the tree browser, the other SQL tabs - everything became slow as
>> molasses. I logged onto the server and the "pgAdmin4.py" was
>> keeping the CPU quite busy. It didn't recover for some time so I simply
>> restarted the server and switched autocomplete to manual.
>>
>> Not knowing the design I may not be able to make a viable suggestion but
>> maybe some sort of complexity counter (configurable or at least hard-coded
>> at first) should be considered, which would hint to the autocomplete to
>> stop trying, after it realizes the task may be too complex or takes too
>> long to complete.
>>
>> Sure, a query like the one above should probably make use of a temporary
>> table but it is beyond the point - there has to be a safeguard against an
>> overloaded autocomplete. Without such a safeguard an ugly/invalid query or
>> a user error could kill the server for all connected pgadmin clients.
>>
>>>
>
> --
>
> 
>
> Akshay Joshi
>
> Principal Software Architect
>
> +91 9767888246
>
> www.enterprisedb.com
>
> 
> 
> 
> 
>

Re: Content assist / autocomplete hangs the server (latest pgAdmin version)

[Akshay Joshi]

Hi Calle

On Fri, Aug 5, 2022 at 3:03 PM Calle Hedberg 
wrote:

> Hi,
>
> I would like to echo Cherio's suggestion here. I copy/paste and run very
> complex scripts every day, and after the recent changes I see the same:
> "Then it sort of let me do things but everything was like in slow motion:
> the tree browser, the other SQL tabs - everything became slow as molasses.
> I logged onto the server and the "pgAdmin4.py" was keeping the CPU quite
> busy. It didn't recover for some time so I simply restarted the server and
> switched autocomplete to manual."
>
> When things get really slow I find that I have to close pgadmin completely
> and re-open it, and if I had let us say 20 tabs open I have to re-create
> them - painful.
>
> Note that I really LIKE autocomplete to be on when writing NEW scripts, it
> can be a timesaver. But I cannot turn it off and turn it on again 30 times
> a day - the scripts I'm running are a mix of existing-run-as-is,
> existing-modify-run, and new-run. Autocomplete pops up after the semicolon
> I always put at the end of any script when it's finished, so if you could
> enable a switch to turn autocomplete off if a semicolon is present it would
> be helpful...  (I generally do NOT want auto-complete when just editing old
> scripts).
>
> One specific issue I've noticed is that whenever I paste a complex script,
> I have to wait for the "Loading" to disappear before executing, or else
> I get that "ERROR: execute cannot be used while an asynchronous query is
> underway" message. That's new, but I presume it is doing some kind of
> script checking and it usually is quick, so that's OK.
>

We have fixed an issue to stop autocomplete on arrow keys/meta keys
#7573  which will be
available in the next release, meanwhile, you can install the snapshot build
. Can you
please test and let us know whether it is better/worse than the existing
one? You can press the *ESC* key to delete the 'Loading...' option.

>
> NOTE, though, that the hourglass icon to the left of the URL does not
> change to "this session is idle.." etc, it still says "a command is
> currently in progress" until you have actually executed something
>
> [image: Screenshot latest pgAdmin.png]
>
> Best regards
> Calle Hedberg
>
> On Fri, 5 Aug 2022 at 10:35, Akshay Joshi 
> wrote:
>
>> Hi Cherio
>>
>> You can disable the automatic autocomplete option from "File ->
>> Preferences -> Query Tool -> Auto completion -> Autocomplete on key press".
>> We have also fixed an issue to stop autocomplete on arrow keys #7573
>>  which will be available in
>> the next release, meanwhile, you can install the snapshot build
>> .
>>
>>
>>
>> On Wed, Aug 3, 2022 at 11:39 PM Cherio  wrote:
>>
>>> I had to examine certain aspects of a query that looks like this:
>>>
>>> SELECT COUNT(*)
>>> FROM schema.table
>>> WHERE id IN (
>>> '1',
>>> '2',
>>> '3',
>>> 
>>> '19998',
>>> '1',
>>> '2'
>>> )
>>>
>>> I pasted the query and autocomplete kicked in. For a minute it froze
>>> entirely. Then it sort of let me do things but everything was like in slow
>>> motion: the tree browser, the other SQL tabs - everything became slow as
>>> molasses. I logged onto the server and the "pgAdmin4.py" was
>>> keeping the CPU quite busy. It didn't recover for some time so I simply
>>> restarted the server and switched autocomplete to manual.
>>>
>>> Not knowing the design I may not be able to make a viable suggestion but
>>> maybe some sort of complexity counter (configurable or at least hard-coded
>>> at first) should be considered, which would hint to the autocomplete to
>>> stop trying, after it realizes the task may be too complex or takes too
>>> long to complete.
>>>
>>> Sure, a query like the one above should probably make use of a temporary
>>> table but it is beyond the point - there has to be a safeguard against an
>>> overloaded autocomplete. Without such a safeguard an ugly/invalid query or
>>> a user error could kill the server for all connected pgadmin clients.
>>>

>>
>> --
>>
>> 
>>
>> Akshay Joshi
>>
>> Principal Software Architect
>>
>> +91 9767888246
>>
>> www.enterprisedb.com
>>
>> 
>> 
>> 
>> 
>>
>
>
> --
>
> *Carl-Anders (Calle) Hedberg*
>
> HISP
>
> Researcher & Technical Specialist
>
> Health Information Systems Programme – South Africa
>
> Cell:+47 41461011 (Norway)
>
> Iridium SatPhone: +8816-315-19119 (usually OFF)
>
> E-mail1: ca...@hisp.org
>
> E-mail2: calle.hedb...@gmail.com
>
> Skype:  calle_hedberg
>


-- 



Akshay Joshi

Principal Software Architect

+91 9767888246

Re: Content assist / autocomplete hangs the server (latest pgAdmin version)

[Calle Hedberg]

Hi,

I would like to echo Cherio's suggestion here. I copy/paste and run very
complex scripts every day, and after the recent changes I see the same:
"Then it sort of let me do things but everything was like in slow motion:
the tree browser, the other SQL tabs - everything became slow as molasses.
I logged onto the server and the "pgAdmin4.py" was keeping the CPU quite
busy. It didn't recover for some time so I simply restarted the server and
switched autocomplete to manual."

When things get really slow I find that I have to close pgadmin completely
and re-open it, and if I had let us say 20 tabs open I have to re-create
them - painful.

Note that I really LIKE autocomplete to be on when writing NEW scripts, it
can be a timesaver. But I cannot turn it off and turn it on again 30 times
a day - the scripts I'm running are a mix of existing-run-as-is,
existing-modify-run, and new-run. Autocomplete pops up after the semicolon
I always put at the end of any script when it's finished, so if you could
enable a switch to turn autocomplete off if a semicolon is present it would
be helpful...  (I generally do NOT want auto-complete when just editing old
scripts).

One specific issue I've noticed is that whenever I paste a complex script,
I have to wait for the "Loading" to disappear before executing, or else
I get that "ERROR: execute cannot be used while an asynchronous query is
underway" message. That's new, but I presume it is doing some kind of
script checking and it usually is quick, so that's OK.

NOTE, though, that the hourglass icon to the left of the URL does not
change to "this session is idle.." etc, it still says "a command is
currently in progress" until you have actually executed something

[image: Screenshot latest pgAdmin.png]

Best regards
Calle Hedberg

On Fri, 5 Aug 2022 at 10:35, Akshay Joshi 
wrote:

> Hi Cherio
>
> You can disable the automatic autocomplete option from "File ->
> Preferences -> Query Tool -> Auto completion -> Autocomplete on key press".
> We have also fixed an issue to stop autocomplete on arrow keys #7573
>  which will be available in
> the next release, meanwhile, you can install the snapshot build
> .
>
>
>
> On Wed, Aug 3, 2022 at 11:39 PM Cherio  wrote:
>
>> I had to examine certain aspects of a query that looks like this:
>>
>> SELECT COUNT(*)
>> FROM schema.table
>> WHERE id IN (
>> '1',
>> '2',
>> '3',
>> 
>> '19998',
>> '1',
>> '2'
>> )
>>
>> I pasted the query and autocomplete kicked in. For a minute it froze
>> entirely. Then it sort of let me do things but everything was like in slow
>> motion: the tree browser, the other SQL tabs - everything became slow as
>> molasses. I logged onto the server and the "pgAdmin4.py" was
>> keeping the CPU quite busy. It didn't recover for some time so I simply
>> restarted the server and switched autocomplete to manual.
>>
>> Not knowing the design I may not be able to make a viable suggestion but
>> maybe some sort of complexity counter (configurable or at least hard-coded
>> at first) should be considered, which would hint to the autocomplete to
>> stop trying, after it realizes the task may be too complex or takes too
>> long to complete.
>>
>> Sure, a query like the one above should probably make use of a temporary
>> table but it is beyond the point - there has to be a safeguard against an
>> overloaded autocomplete. Without such a safeguard an ugly/invalid query or
>> a user error could kill the server for all connected pgadmin clients.
>>
>>>
>
> --
>
> 
>
> Akshay Joshi
>
> Principal Software Architect
>
> +91 9767888246
>
> www.enterprisedb.com
>
> 
> 
> 
> 
>


-- 

*Carl-Anders (Calle) Hedberg*

HISP

Researcher & Technical Specialist

Health Information Systems Programme – South Africa

Cell:+47 41461011 (Norway)

Iridium SatPhone: +8816-315-19119 (usually OFF)

E-mail1: ca...@hisp.org

E-mail2: calle.hedb...@gmail.com

Skype:  calle_hedberg

Re: Content assist / autocomplete hangs the server (latest pgAdmin version)

[Akshay Joshi]

Hi Cherio

You can disable the automatic autocomplete option from "File -> Preferences
-> Query Tool -> Auto completion -> Autocomplete on key press". We have
also fixed an issue to stop autocomplete on arrow keys #7573
 which will be available in the
next release, meanwhile, you can install the snapshot build
.



On Wed, Aug 3, 2022 at 11:39 PM Cherio  wrote:

> I had to examine certain aspects of a query that looks like this:
>
> SELECT COUNT(*)
> FROM schema.table
> WHERE id IN (
> '1',
> '2',
> '3',
> 
> '19998',
> '1',
> '2'
> )
>
> I pasted the query and autocomplete kicked in. For a minute it froze
> entirely. Then it sort of let me do things but everything was like in slow
> motion: the tree browser, the other SQL tabs - everything became slow as
> molasses. I logged onto the server and the "pgAdmin4.py" was
> keeping the CPU quite busy. It didn't recover for some time so I simply
> restarted the server and switched autocomplete to manual.
>
> Not knowing the design I may not be able to make a viable suggestion but
> maybe some sort of complexity counter (configurable or at least hard-coded
> at first) should be considered, which would hint to the autocomplete to
> stop trying, after it realizes the task may be too complex or takes too
> long to complete.
>
> Sure, a query like the one above should probably make use of a temporary
> table but it is beyond the point - there has to be a safeguard against an
> overloaded autocomplete. Without such a safeguard an ugly/invalid query or
> a user error could kill the server for all connected pgadmin clients.
>
>>

-- 



Akshay Joshi

Principal Software Architect

+91 9767888246

www.enterprisedb.com

Content assist / autocomplete hangs the server (latest pgAdmin version)

[Cherio]

I had to examine certain aspects of a query that looks like this:

SELECT COUNT(*)
FROM schema.table
WHERE id IN (
'1',
'2',
'3',

'19998',
'1',
'2'
)

I pasted the query and autocomplete kicked in. For a minute it froze
entirely. Then it sort of let me do things but everything was like in slow
motion: the tree browser, the other SQL tabs - everything became slow as
molasses. I logged onto the server and the "pgAdmin4.py" was
keeping the CPU quite busy. It didn't recover for some time so I simply
restarted the server and switched autocomplete to manual.

Not knowing the design I may not be able to make a viable suggestion but
maybe some sort of complexity counter (configurable or at least hard-coded
at first) should be considered, which would hint to the autocomplete to
stop trying, after it realizes the task may be too complex or takes too
long to complete.

Sure, a query like the one above should probably make use of a temporary
table but it is beyond the point - there has to be a safeguard against an
overloaded autocomplete. Without such a safeguard an ugly/invalid query or
a user error could kill the server for all connected pgadmin clients.

>