Refresh auto-completion data in background

[eliangcs]

If we have a lot of tables and views in the database, the program takes some time on startup. I wonder if we can load the auto-completion data in background so that users don't have to wait.

@amjith I'm still not very familiar with the code structure. Do you think if it is doable?

[amjith]

@eliangcs Yes this is a real concern and using background threads has been in my mind for a while now. dbcli/mycli#109.

Can you tell me how many tables/views are in your database for this to happen? We've tried to minimize the number of queries needed to populate the completions, but I'm wondering if the queries themselves are taking too long or if the data transfer of the huge list from server to the client is taking so long.

Either way a background thread should help.

@dbcli/pgcli-core Do you guys have any thoughts on how else to handle this situation?

[eliangcs]

@amjith In my case I have 500+ tables and 100+ views.

[j-bennet]

Doing this in the background sounds like the way to go. But before that, we should also verify we're only running those queries once on startup, and only pulling as much data as we need.

[eliangcs]

Tried using the code from dbcli/pgcli#345 in vcli, but didn't work. I think it's because vertica_python connection is not thread-safe. We'll need a separate connection for the refresh thread.

[amjith]

@eliangcs You're right using the same pgexecute object for completion and query is not the way to go. I've updated the PR dbcli/pgcli#345 to handle that case. But I'm still working on resolving the case where multiple completion refresh can be triggered concurrently which can stomp on the completion data-structures.

I'll keep you posted on how I resolve that in pgcli.

[eliangcs]

Implemented in #22.