Обсуждение: Fix for a problem with auto reconnection
My colleagues and I recently stumbled over a quite perplexing issue with pgadmin3 (1.18.1), namely that the pgadmin query tool sometimes did not show values in the database which were definitely set. After further investigation it turned out that this happened when the values contained non-ascii characters and after the database connection had been reset by pgadmin (which happened sometimes when the database was restarted or there was a timeout in the firewall), and if that reset happened via the query tool window, not via the main window. The explanation for this behavior is obvious: The connection is reset with conn->Reset() in frm/frmQuery.cpp, but the Reset() method does not set the client side encoding for the connection to match the encoding of the database, as is done in DoConnect(). It also fails to do some other things that DoConnect() cares about, such as setting the DateStyle to ISO and setting the default role. This could cause additional problems. So my solution was to replace the call to conn->Reset() with a call to conn->Reconnect() which essentially does the same, but calls DoConnect() internally so that everything gets initialized. This fix works nicely for me. Another solution would be to add the initialization code in DoConnect() to the Reset() method as well. Here are the steps to reproduce the problem: * Open the SQL query tool and create a test table like this: create table t(a varchar , b varchar); insert into t values ('Wurst', 'Käse'); * Query the table with "select * from t". You should get "Wurst" and "Käse". * Kill the server process for the connection * Query the table again with "select * from t". You should get a Fatal error: connection lost. * Rerun the query. You should now get a dialog box asking "... attempt to reconnect ... ?" * Answer "Yes". You should get the message "Connection reset." * Rerun the query. Now you get the query result again, but this time only with the cell "Wurst"; the cell that should say "Käse" stays empty.
Ashesh, can you look at this please? Thanks for the report and investigation Christoph. On Thu, Nov 14, 2013 at 7:04 PM, Christoph Zwerschke <cito@online.de> wrote: > My colleagues and I recently stumbled over a quite perplexing issue with > pgadmin3 (1.18.1), namely that the pgadmin query tool sometimes did not show > values in the database which were definitely set. > > After further investigation it turned out that this happened when the values > contained non-ascii characters and after the database connection had been > reset by pgadmin (which happened sometimes when the database was restarted > or there was a timeout in the firewall), and if that reset happened via the > query tool window, not via the main window. > > The explanation for this behavior is obvious: The connection is reset with > conn->Reset() in frm/frmQuery.cpp, but the Reset() method does not set the > client side encoding for the connection to match the encoding of the > database, as is done in DoConnect(). It also fails to do some other things > that DoConnect() cares about, such as setting the DateStyle to ISO and > setting the default role. This could cause additional problems. > > So my solution was to replace the call to conn->Reset() with a call to > conn->Reconnect() which essentially does the same, but calls DoConnect() > internally so that everything gets initialized. > > This fix works nicely for me. Another solution would be to add the > initialization code in DoConnect() to the Reset() method as well. > > Here are the steps to reproduce the problem: > > * Open the SQL query tool and create a test table like this: > > create table t(a varchar , b varchar); > insert into t values ('Wurst', 'Käse'); > > * Query the table with "select * from t". > You should get "Wurst" and "Käse". > > * Kill the server process for the connection > > * Query the table again with "select * from t". > You should get a Fatal error: connection lost. > > * Rerun the query. > You should now get a dialog box asking > "... attempt to reconnect ... ?" > > * Answer "Yes". > You should get the message "Connection reset." > > * Rerun the query. > Now you get the query result again, > but this time only with the cell "Wurst"; > the cell that should say "Käse" stays empty. > > > -- > Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgadmin-hackers -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Sure - I will look into it..
On Fri, Nov 15, 2013 at 6:43 AM, Dave Page <dpage@pgadmin.org> wrote:
Ashesh, can you look at this please?
Thanks for the report and investigation Christoph.--
On Thu, Nov 14, 2013 at 7:04 PM, Christoph Zwerschke <cito@online.de> wrote:
> My colleagues and I recently stumbled over a quite perplexing issue with
> pgadmin3 (1.18.1), namely that the pgadmin query tool sometimes did not show
> values in the database which were definitely set.
>
> After further investigation it turned out that this happened when the values
> contained non-ascii characters and after the database connection had been
> reset by pgadmin (which happened sometimes when the database was restarted
> or there was a timeout in the firewall), and if that reset happened via the
> query tool window, not via the main window.
>
> The explanation for this behavior is obvious: The connection is reset with
> conn->Reset() in frm/frmQuery.cpp, but the Reset() method does not set the
> client side encoding for the connection to match the encoding of the
> database, as is done in DoConnect(). It also fails to do some other things
> that DoConnect() cares about, such as setting the DateStyle to ISO and
> setting the default role. This could cause additional problems.
>
> So my solution was to replace the call to conn->Reset() with a call to
> conn->Reconnect() which essentially does the same, but calls DoConnect()
> internally so that everything gets initialized.
>
> This fix works nicely for me. Another solution would be to add the
> initialization code in DoConnect() to the Reset() method as well.
>
> Here are the steps to reproduce the problem:
>
> * Open the SQL query tool and create a test table like this:
>
> create table t(a varchar , b varchar);
> insert into t values ('Wurst', 'Käse');
>
> * Query the table with "select * from t".
> You should get "Wurst" and "Käse".
>
> * Kill the server process for the connection
>
> * Query the table again with "select * from t".
> You should get a Fatal error: connection lost.
>
> * Rerun the query.
> You should now get a dialog box asking
> "... attempt to reconnect ... ?"
>
> * Answer "Yes".
> You should get the message "Connection reset."
>
> * Rerun the query.
> Now you get the query result again,
> but this time only with the cell "Wurst";
> the cell that should say "Käse" stays empty.
>
>
> --
> Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-hackers
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Thanks & Regards,
Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company
![](http://s.wisestamp.com/pixel.png?p=mozilla&v=2.0.3&t=1285751372076&u=4060523&e=777)
Am 15.11.2013 06:13, schrieb Ashesh Vashi: > Sure - I will look into it.. Thanks for looking into it. One more thing that I forgot to mention: The problem only appears if the database encoding is not already UNICODE, but, for instance, LATIN1 or WIN1252. This is unfortunately still a quite frequent setting here in Europe. So you need to create such a database to reproduce. If your template1 database is already UTF8, you need to create it from template0. -- Christoph
I posted this bug with suggested fix in 2013 (http://www.postgresql.org/message-id/52851ED9.1050003@online.de), but it seems nobody has looked into it until now. As far as I understand, there is no real bug-tracker for PgAdmin (which is unfortunate), so I'm re-raising the issue here. -- Christoph
Ashesh, did you get a chance to look at this? On Thu, May 29, 2014 at 8:05 PM, Christoph Zwerschke <cito@online.de> wrote: > I posted this bug with suggested fix in 2013 > (http://www.postgresql.org/message-id/52851ED9.1050003@online.de), but it > seems nobody has looked into it until now. As far as I understand, there is > no real bug-tracker for PgAdmin (which is unfortunate), so I'm re-raising > the issue here. > > > -- Christoph > > > -- > Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgadmin-hackers -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Sure.
I was lost a bit in other work...![](http://s.wisestamp.com/pixel.png?p=mozilla&v=2.0.3&t=1285751372076&u=4060523&e=777)
On Fri, May 30, 2014 at 1:41 PM, Dave Page <dpage@pgadmin.org> wrote:
Ashesh, did you get a chance to look at this?
On Thu, May 29, 2014 at 8:05 PM, Christoph Zwerschke <cito@online.de> wrote:
> I posted this bug with suggested fix in 2013
> (http://www.postgresql.org/message-id/52851ED9.1050003@online.de), but it
> seems nobody has looked into it until now. As far as I understand, there is
> no real bug-tracker for PgAdmin (which is unfortunate), so I'm re-raising
> the issue here.
>
>
> -- Christoph
>
>> --
> Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-hackers
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Dave,
PFA - moved the initialisation code in a separate function, and called that function from the "Reset" function too.
Please take a look at it.
![](http://s.wisestamp.com/pixel.png?p=mozilla&v=2.0.3&t=1285751372076&u=4060523&e=777)
On Tue, Jun 3, 2014 at 11:43 AM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
Will look into it today...Sure.I was lost a bit in other work...On Fri, May 30, 2014 at 1:41 PM, Dave Page <dpage@pgadmin.org> wrote:Ashesh, did you get a chance to look at this?
On Thu, May 29, 2014 at 8:05 PM, Christoph Zwerschke <cito@online.de> wrote:
> I posted this bug with suggested fix in 2013
> (http://www.postgresql.org/message-id/52851ED9.1050003@online.de), but it
> seems nobody has looked into it until now. As far as I understand, there is
> no real bug-tracker for PgAdmin (which is unfortunate), so I'm re-raising
> the issue here.
>
>
> -- Christoph
>
>> --
> Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-hackers
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Вложения
Thanks - applied.
On Tue, Jun 17, 2014 at 7:35 AM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
Dave,PFA - moved the initialisation code in a separate function, and called that function from the "Reset" function too.Please take a look at it.On Tue, Jun 3, 2014 at 11:43 AM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:Will look into it today...Sure.I was lost a bit in other work...On Fri, May 30, 2014 at 1:41 PM, Dave Page <dpage@pgadmin.org> wrote:Ashesh, did you get a chance to look at this?
On Thu, May 29, 2014 at 8:05 PM, Christoph Zwerschke <cito@online.de> wrote:
> I posted this bug with suggested fix in 2013
> (http://www.postgresql.org/message-id/52851ED9.1050003@online.de), but it
> seems nobody has looked into it until now. As far as I understand, there is
> no real bug-tracker for PgAdmin (which is unfortunate), so I'm re-raising
> the issue here.
>
>
> -- Christoph
>
>> --
> Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-hackers
--
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 for looking into this. The problem with the wrong encoding is now gone, but another problem appeared: When the connection is reestablished, I get an additional alert with an error message "Column not found in pgSet: "datlastsysoid". The problem seems to be that needColQuoting is not reset to false in the Reset() method as it is done in the Reconnect() method. Wenn I add the line "needColQuoting = false" before the call to Initialize() in the Reset() method, everything works as expected. -- Christoph Am 17.06.2014 08:35, schrieb Ashesh Vashi: > Dave, > > PFA - moved the initialisation code in a separate function, and called > that function from the "Reset" function too. > Please take a look at it. > > > -- > > Thanks & Regards, > > Ashesh Vashi
Ashesh - your thoughts on this? I'm not sure I see how resetting needColQuoting would prevent a datlastsysoid error, though I admit I haven't had a chance to dive into the code yet. On Sun, Jun 22, 2014 at 3:57 PM, Christoph Zwerschke <cito@online.de> wrote: > Thanks for looking into this. The problem with the wrong encoding is now > gone, but another problem appeared: > > When the connection is reestablished, I get an additional alert with an > error message "Column not found in pgSet: "datlastsysoid". > > The problem seems to be that needColQuoting is not reset to false in the > Reset() method as it is done in the Reconnect() method. > > Wenn I add the line "needColQuoting = false" before the call to Initialize() > in the Reset() method, everything works as expected. > > -- Christoph > > > Am 17.06.2014 08:35, schrieb Ashesh Vashi: > >> Dave, >> >> PFA - moved the initialisation code in a separate function, and called >> that function from the "Reset" function too. >> Please take a look at it. >> >> >> -- >> >> Thanks & Regards, >> >> Ashesh Vashi -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Am 27.06.2014 09:57, schrieb Dave Page: > Ashesh - your thoughts on this? I'm not sure I see how resetting > needColQuoting would prevent a datlastsysoid error, though I admit I > haven't had a chance to dive into the code yet. Some more detail: The error happens in line 285 of pgadmin/db/pgConn.cpp: if (set->ColNumber(wxT("\"datlastsysoid\"")) >= 0) needColQuoting = true; This assumes that needColQuoting was set to false before. Another solution might be to modify that check to something like this: if (!needColQuoting && set->ColNumber(wxT("\"datlastsysoid\"")) >= 0) needColQuoting = true; -- Christoph
On Fri, Jun 27, 2014 at 9:58 AM, Christoph Zwerschke <cito@online.de> wrote: > Am 27.06.2014 09:57, schrieb Dave Page: > >> Ashesh - your thoughts on this? I'm not sure I see how resetting >> needColQuoting would prevent a datlastsysoid error, though I admit I >> haven't had a chance to dive into the code yet. > > > Some more detail: > > The error happens in line 285 of pgadmin/db/pgConn.cpp: > > if (set->ColNumber(wxT("\"datlastsysoid\"")) >= 0) > needColQuoting = true; > > This assumes that needColQuoting was set to false before. Another solution > might be to modify that check to something like this: > > if (!needColQuoting && > set->ColNumber(wxT("\"datlastsysoid\"")) >= 0) > needColQuoting = true; Huh, that a kinda weird way of doing things. I think in the longer term we should consider refactoring the handling of that variable entirely. In any case, I've committed a fix now, doing what your first suggested (we do that elsewhere it seems). Thanks! -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company