Thread: Regarding feature #3319

Hi Anil/Dave,

Why not use browser localStorage for saving this information? It persists when the browser closes and is based on the URL. It is safer to store at the user's machine than on our server. 

For Electron also it should work.

This will reduce load on the pgAdmin server.

On Wed, Aug 28, 2024 at 12:50 PM Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:

Hi Hackers,

We are going to store the below details

1. List of Query tools opened
2. List of connections present in each query tool
3. Each connection’s details that are required to make the connection once the application restarts
4. Active connection details of each query tool opened
5. Store the query that is written in the editor that may or may not be executed
6. Store the contents of the scratch pad
7. Store the file which is opened in the query tool with any unsaved changes

Some questions that I have are mentioned below,

1. For desktop mode the application is opened with some query tools and the user closes the application and on restarts the tabs will open as it is and will restore the workspace and connections on query tool tabs. But for server mode do we need to restore the query tool tabs and workspace? Because we see most of the desktop applications have this feature. 
2. I came across one issue reported by one user i.e https://github.com/pgadmin-org/pgadmin4/issues/6666, where pgAdmin crashed due to long SQL scripts that can be in Megabytes stored as query history.  We fixed it by giving MAX_QUERY_LENGTH  to 1000000, and checking if any query length is below the value then it gets stored in the database as query history else not. Should we go with storing the workspace and query tool details in SQLite DB or use a file system to store the details and retrieve the content accordingly?
   
   

Please let me know your suggestions on this.

Thanks

Anil

--

	Anil Sahoo Software Engineer www.enterprisedb.com Power to Postgres

On Tue, Aug 20, 2024 at 10:14 AM Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Anil,

There can be multiple query tools open with large files. Not entirely sure if storing in SQLite DB is a good idea.

External databases won't come in the picture as 99.99% users will not use external DB for desktop app. We're only doing this for desktop app.

And also consider the case where a SQL file is opened with some unsaved changes.

On Tue, Aug 20, 2024 at 9:11 AM Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:

Hi Aditya,

As we are already storing the query history in the pgAdmin 4's database file.

I am planning to store the information the same way. That can be an internal database file like SQLite or external database. 

Let me know if you all have any suggestions on this.

Thanks

Anil

--

	Anil Sahoo Software Engineer www.enterprisedb.com Power to Postgres

On Mon, Aug 19, 2024 at 11:40 AM Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Anil,

On Mon, Aug 12, 2024 at 3:02 PM Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:

Hi,

Yes, We will store the details that are needed to re-establish the connection.

How/Where are you planning to store the information? Query text could be large.

Regards
Anil

--

	Anil Sahoo Software Engineer www.enterprisedb.com Power to Postgres

On Mon, Aug 12, 2024 at 2:08 PM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Mon, 12 Aug 2024 at 06:50, Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:

Hi Hackers,

This feature #3319, demands the Workspace and the Query tool panel to be saved before exiting the application and on restart it will show earlier opened panels.

We are already saving the Browser layout, Query tool layout and the Object explorer tree state but to save the contents of panels we will initially start with the Query tool. The below implementation will be done,

1. Store the query tool panels and the list of connections present in each query tool panel and the active connection
2. Store the query that is written in the editor
3. Store the contents of scratch pad

The main reason that this has never been worked on is that there is no way to restore the state of a connection to what it was and be sure we've got it right. How do you propose to handle that? I assume in a similar way to the warnings we give if a connection has to be re-established? 

We will use debouncing to store the workspace data and all other data related to panels in the pgAdmin 4's configured database file. Through debouncing we will be able to call the API at certain intervals of user interaction, and it will update the stored data related to workspace and all other panels.

OK. 

--

Dave Page

pgAdmin: https://www.pgadmin.org

PostgreSQL: https://www.postgresql.org

EDB: https://www.enterprisedb.com

PGDay UK 2024, 11th September, London: https://2024.pgday.uk/

--

Thanks,

Aditya Toshniwal

pgAdmin Hacker | Sr. Software Architect | enterprisedb.com

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

--

Thanks,

Aditya Toshniwal

pgAdmin Hacker | Sr. Software Architect | enterprisedb.com

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

--

Thanks,

Aditya Toshniwal

pgAdmin Hacker | Sr. Staff SDE II | enterprisedb.com

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

Hi

On Thu, 16 Jan 2025 at 06:37, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Anil/Dave,

Why not use browser localStorage for saving this information? It persists when the browser closes and is based on the URL. It is safer to store at the user's machine than on our server. 

For Electron also it should work.

This will reduce load on the pgAdmin server.

Because it stores it at the users machine and not on the pgAdmin server, and thus state cannot be restored if the user is on a different machine. I think that's a compelling feature.

That said, I think this is largely irrelevant until the fundamental problem is solved. e.g. how do we restore the state of the session (spoiler: it's almost certainly not possible, unless we can figure out all the session-changing side effects of every query, stored procedure/function etc. that may have been directly or indirectly called).

Or, we make a decision not to bother with that, and to give the user suitable warnings such as we do when we perform a reconnect.

 

On Wed, Aug 28, 2024 at 12:50 PM Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:

Hi Hackers,

We are going to store the below details

1. List of Query tools opened
2. List of connections present in each query tool
3. Each connection’s details that are required to make the connection once the application restarts
4. Active connection details of each query tool opened
5. Store the query that is written in the editor that may or may not be executed
6. Store the contents of the scratch pad
7. Store the file which is opened in the query tool with any unsaved changes

Some questions that I have are mentioned below,

1. For desktop mode the application is opened with some query tools and the user closes the application and on restarts the tabs will open as it is and will restore the workspace and connections on query tool tabs. But for server mode do we need to restore the query tool tabs and workspace? Because we see most of the desktop applications have this feature. 
2. I came across one issue reported by one user i.e https://github.com/pgadmin-org/pgadmin4/issues/6666, where pgAdmin crashed due to long SQL scripts that can be in Megabytes stored as query history.  We fixed it by giving MAX_QUERY_LENGTH  to 1000000, and checking if any query length is below the value then it gets stored in the database as query history else not. Should we go with storing the workspace and query tool details in SQLite DB or use a file system to store the details and retrieve the content accordingly?
   
   

Please let me know your suggestions on this.

Thanks

Anil

--

	Anil Sahoo Software Engineer www.enterprisedb.com Power to Postgres

On Tue, Aug 20, 2024 at 10:14 AM Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Anil,

There can be multiple query tools open with large files. Not entirely sure if storing in SQLite DB is a good idea.

External databases won't come in the picture as 99.99% users will not use external DB for desktop app. We're only doing this for desktop app.

And also consider the case where a SQL file is opened with some unsaved changes.

On Tue, Aug 20, 2024 at 9:11 AM Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:

Hi Aditya,

As we are already storing the query history in the pgAdmin 4's database file.

I am planning to store the information the same way. That can be an internal database file like SQLite or external database. 

Let me know if you all have any suggestions on this.

Thanks

Anil

--

	Anil Sahoo Software Engineer www.enterprisedb.com Power to Postgres

On Mon, Aug 19, 2024 at 11:40 AM Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Anil,

On Mon, Aug 12, 2024 at 3:02 PM Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:

Hi,

Yes, We will store the details that are needed to re-establish the connection.

How/Where are you planning to store the information? Query text could be large.

Regards
Anil

--

	Anil Sahoo Software Engineer www.enterprisedb.com Power to Postgres

On Mon, Aug 12, 2024 at 2:08 PM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Mon, 12 Aug 2024 at 06:50, Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:

Hi Hackers,

This feature #3319, demands the Workspace and the Query tool panel to be saved before exiting the application and on restart it will show earlier opened panels.

We are already saving the Browser layout, Query tool layout and the Object explorer tree state but to save the contents of panels we will initially start with the Query tool. The below implementation will be done,

1. Store the query tool panels and the list of connections present in each query tool panel and the active connection
2. Store the query that is written in the editor
3. Store the contents of scratch pad

The main reason that this has never been worked on is that there is no way to restore the state of a connection to what it was and be sure we've got it right. How do you propose to handle that? I assume in a similar way to the warnings we give if a connection has to be re-established? 

We will use debouncing to store the workspace data and all other data related to panels in the pgAdmin 4's configured database file. Through debouncing we will be able to call the API at certain intervals of user interaction, and it will update the stored data related to workspace and all other panels.

OK. 

--

Dave Page

pgAdmin: https://www.pgadmin.org

PostgreSQL: https://www.postgresql.org

EDB: https://www.enterprisedb.com

PGDay UK 2024, 11th September, London: https://2024.pgday.uk/

--

Thanks,

Aditya Toshniwal

pgAdmin Hacker | Sr. Software Architect | enterprisedb.com

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

--

Thanks,

Aditya Toshniwal

pgAdmin Hacker | Sr. Software Architect | enterprisedb.com

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

--

Thanks,

Aditya Toshniwal

pgAdmin Hacker | Sr. Staff SDE II | enterprisedb.com

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

--

Dave Page

pgAdmin: https://www.pgadmin.org

PostgreSQL: https://www.postgresql.org

pgEdge: https://www.pgedge.com

Hi,

On Wed, Feb 19, 2025 at 5:12 PM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, 16 Jan 2025 at 06:37, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Anil/Dave,

Why not use browser localStorage for saving this information? It persists when the browser closes and is based on the URL. It is safer to store at the user's machine than on our server. 

For Electron also it should work.

This will reduce load on the pgAdmin server.

Because it stores it at the users machine and not on the pgAdmin server, and thus state cannot be restored if the user is on a different machine. I think that's a compelling feature.

That said, I think this is largely irrelevant until the fundamental problem is solved. e.g. how do we restore the state of the session (spoiler: it's almost certainly not possible, unless we can figure out all the session-changing side effects of every query, stored procedure/function etc. that may have been directly or indirectly called).

Or, we make a decision not to bother with that, and to give the user suitable warnings such as we do when we perform a reconnect.

If I understand correctly, Users are complaining about losing unsaved data in the query tool and not about data output or session state. Hence just reopening the query tool with only data should be suffice.

If I understand correctly, Users are complaining about losing unsaved data in the query tool and not about data output or session state. Hence just reopening the query tool with only data should be suffice.

I'm sure that will suffice for 95%+ of users. The ones I'm concerned about are those who (for example) have done SET search_path = ... and then performed some destructive operation that worked as expected because of the earlier SET, but might cause data loss or unexpected consequences if run without the SET.

Granted, that class of issues is likely to affect only a small number of users in reality, but the consequences could easily be data loss.

 

Hi Dave,

On Wed, Feb 19, 2025 at 6:55 PM Dave Page <dpage@pgadmin.org> wrote:

On Wed, 19 Feb 2025 at 12:24, Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi,

On Wed, Feb 19, 2025 at 5:12 PM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, 16 Jan 2025 at 06:37, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Anil/Dave,

Why not use browser localStorage for saving this information? It persists when the browser closes and is based on the URL. It is safer to store at the user's machine than on our server. 

For Electron also it should work.

This will reduce load on the pgAdmin server.

Because it stores it at the users machine and not on the pgAdmin server, and thus state cannot be restored if the user is on a different machine. I think that's a compelling feature.

That said, I think this is largely irrelevant until the fundamental problem is solved. e.g. how do we restore the state of the session (spoiler: it's almost certainly not possible, unless we can figure out all the session-changing side effects of every query, stored procedure/function etc. that may have been directly or indirectly called).

Or, we make a decision not to bother with that, and to give the user suitable warnings such as we do when we perform a reconnect.

If I understand correctly, Users are complaining about losing unsaved data in the query tool and not about data output or session state. Hence just reopening the query tool with only data should be suffice.

I'm sure that will suffice for 95%+ of users. The ones I'm concerned about are those who (for example) have done SET search_path = ... and then performed some destructive operation that worked as expected because of the earlier SET, but might cause data loss or unexpected consequences if run without the SET.

Granted, that class of issues is likely to affect only a small number of users in reality, but the consequences could easily be data loss.

It may be compelling to restore your workspace on any browser in the world but is it worth it? I mean think about the overhead it will put on the pgAdmin server. Plus the storage it will require on the server side. Already people are asking to make pgAdmin storage free by putting sessions in db instead of file based and so on. Also, the information cannot be saved as is, it needs to be encrypted (more overhead).

On a slower internet, restoring might take a long time. We'll have an advantage of storing on the client side and it will cover most of the users with good performance.

On Thu, 20 Feb 2025 at 03:52, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Dave,

On Wed, Feb 19, 2025 at 6:55 PM Dave Page <dpage@pgadmin.org> wrote:

On Wed, 19 Feb 2025 at 12:24, Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi,

On Wed, Feb 19, 2025 at 5:12 PM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, 16 Jan 2025 at 06:37, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Anil/Dave,

Why not use browser localStorage for saving this information? It persists when the browser closes and is based on the URL. It is safer to store at the user's machine than on our server. 

For Electron also it should work.

This will reduce load on the pgAdmin server.

Because it stores it at the users machine and not on the pgAdmin server, and thus state cannot be restored if the user is on a different machine. I think that's a compelling feature.

That said, I think this is largely irrelevant until the fundamental problem is solved. e.g. how do we restore the state of the session (spoiler: it's almost certainly not possible, unless we can figure out all the session-changing side effects of every query, stored procedure/function etc. that may have been directly or indirectly called).

Or, we make a decision not to bother with that, and to give the user suitable warnings such as we do when we perform a reconnect.

If I understand correctly, Users are complaining about losing unsaved data in the query tool and not about data output or session state. Hence just reopening the query tool with only data should be suffice.

I'm sure that will suffice for 95%+ of users. The ones I'm concerned about are those who (for example) have done SET search_path = ... and then performed some destructive operation that worked as expected because of the earlier SET, but might cause data loss or unexpected consequences if run without the SET.

Granted, that class of issues is likely to affect only a small number of users in reality, but the consequences could easily be data loss.

It may be compelling to restore your workspace on any browser in the world but is it worth it? I mean think about the overhead it will put on the pgAdmin server. Plus the storage it will require on the server side. Already people are asking to make pgAdmin storage free by putting sessions in db instead of file based and so on. Also, the information cannot be saved as is, it needs to be encrypted (more overhead).

On a slower internet, restoring might take a long time. We'll have an advantage of storing on the client side and it will cover most of the users with good performance.

How big do you think the average SQL query/script is? Even in outlier cases where they might run into a megabyte or two, that's still trivial compared to what people download browsing Facebook on their phone for example.

 

--

Dave Page

pgAdmin: https://www.pgadmin.org

PostgreSQL: https://www.postgresql.org

pgEdge: https://www.pgedge.com

Hi Dave,

On Thu, Feb 20, 2025 at 3:22 PM Dave Page <dpage@pgadmin.org> wrote:

On Thu, 20 Feb 2025 at 03:52, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Dave,

On Wed, Feb 19, 2025 at 6:55 PM Dave Page <dpage@pgadmin.org> wrote:

On Wed, 19 Feb 2025 at 12:24, Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi,

On Wed, Feb 19, 2025 at 5:12 PM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, 16 Jan 2025 at 06:37, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Anil/Dave,

Why not use browser localStorage for saving this information? It persists when the browser closes and is based on the URL. It is safer to store at the user's machine than on our server. 

For Electron also it should work.

This will reduce load on the pgAdmin server.

Because it stores it at the users machine and not on the pgAdmin server, and thus state cannot be restored if the user is on a different machine. I think that's a compelling feature.

That said, I think this is largely irrelevant until the fundamental problem is solved. e.g. how do we restore the state of the session (spoiler: it's almost certainly not possible, unless we can figure out all the session-changing side effects of every query, stored procedure/function etc. that may have been directly or indirectly called).

Or, we make a decision not to bother with that, and to give the user suitable warnings such as we do when we perform a reconnect.

If I understand correctly, Users are complaining about losing unsaved data in the query tool and not about data output or session state. Hence just reopening the query tool with only data should be suffice.

I'm sure that will suffice for 95%+ of users. The ones I'm concerned about are those who (for example) have done SET search_path = ... and then performed some destructive operation that worked as expected because of the earlier SET, but might cause data loss or unexpected consequences if run without the SET.

Granted, that class of issues is likely to affect only a small number of users in reality, but the consequences could easily be data loss.

It may be compelling to restore your workspace on any browser in the world but is it worth it? I mean think about the overhead it will put on the pgAdmin server. Plus the storage it will require on the server side. Already people are asking to make pgAdmin storage free by putting sessions in db instead of file based and so on. Also, the information cannot be saved as is, it needs to be encrypted (more overhead).

On a slower internet, restoring might take a long time. We'll have an advantage of storing on the client side and it will cover most of the users with good performance.

How big do you think the average SQL query/script is? Even in outlier cases where they might run into a megabyte or two, that's still trivial compared to what people download browsing Facebook on their phone for example.

In server mode, the user count may go up. The number of open SQL editor tabs may go up. And there is encryption/decryption that is needed as well. Users changing a browser is also less likely. The feature is most useful for desktop users.