New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Temp tables are lost following sql error #255

Closed
utopic-men opened this Issue Aug 17, 2016 · 13 comments

Comments

Projects
None yet
2 participants
@utopic-men

utopic-men commented Aug 17, 2016

When I create temp tables and I throw an SQL error (syntax, whatever reason...), Postico ask for reconnection and after my temp tables are lost (because of new session).

I love this tool but this behavior prevent me to use it when playing with temp tables.
pgAdmin works well.

Thank you for your work !

Version 1.0.9 (1491) (and previous)

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 17, 2016

Owner

This is odd -- an error shouldn't lead to a lost connection.

What server are you connecting to? Are you using any software like pg_pool or similar? Any extensions that may cause this?

Owner

jakob commented Aug 17, 2016

This is odd -- an error shouldn't lead to a lost connection.

What server are you connecting to? Are you using any software like pg_pool or similar? Any extensions that may cause this?

@utopic-men

This comment has been minimized.

Show comment
Hide comment
@utopic-men

utopic-men Aug 17, 2016

I'm connecting on localhost without special extensions.
My pg version : PostgreSQL 9.3.13 on x86_64-apple-darwin15.5.0, compiled by Apple LLVM version 7.3.0 (clang-703.0.31), 64-bit.

And I confirm : a syntax error lead to a disconnection... perhaps a pg setting ?...

utopic-men commented Aug 17, 2016

I'm connecting on localhost without special extensions.
My pg version : PostgreSQL 9.3.13 on x86_64-apple-darwin15.5.0, compiled by Apple LLVM version 7.3.0 (clang-703.0.31), 64-bit.

And I confirm : a syntax error lead to a disconnection... perhaps a pg setting ?...

@utopic-men

This comment has been minimized.

Show comment
Hide comment
@utopic-men

utopic-men Aug 17, 2016

This is not a pg setting because pgAdmin works... Other Java client works well too.

utopic-men commented Aug 17, 2016

This is not a pg setting because pgAdmin works... Other Java client works well too.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 17, 2016

Owner

It works for me. I've tested by executing the query select . against PostgreSQL 9.3.13. (Postgres.app) Postico does not lose the connection. I've tested this with Postico 1.0.9 running on OS X 10.11.6 and OS X 10.12.

Obviously there must be something different in our setups.

  1. What version of OS X are you using?
  2. What PostgreSQL distribution are you using? EnterpriseDB? Homebrew? MacPorts? Building from Source?
  3. Does select . also cause connection loss on your machine? If not, could you post the query that causes connection loss?

Losing the connection could happen when the backend crashes. Are you executing a very complex query that makes the backend crash? Is there anything in your server log?

By the way, 9.3.13 is not the current version. There have been a few security fixes in 9.3.14, so I'd recommend updating to the latest version.

Owner

jakob commented Aug 17, 2016

It works for me. I've tested by executing the query select . against PostgreSQL 9.3.13. (Postgres.app) Postico does not lose the connection. I've tested this with Postico 1.0.9 running on OS X 10.11.6 and OS X 10.12.

Obviously there must be something different in our setups.

  1. What version of OS X are you using?
  2. What PostgreSQL distribution are you using? EnterpriseDB? Homebrew? MacPorts? Building from Source?
  3. Does select . also cause connection loss on your machine? If not, could you post the query that causes connection loss?

Losing the connection could happen when the backend crashes. Are you executing a very complex query that makes the backend crash? Is there anything in your server log?

By the way, 9.3.13 is not the current version. There have been a few security fixes in 9.3.14, so I'd recommend updating to the latest version.

@utopic-men

This comment has been minimized.

Show comment
Hide comment
@utopic-men

utopic-men Aug 17, 2016

I use OSX 10.11.5 and Homebrew building from source.

SELECT . causes connection loss.

pgAdmin and other works.

How can I remove all Postico app and prefs to make a fresh install ?

utopic-men commented Aug 17, 2016

I use OSX 10.11.5 and Homebrew building from source.

SELECT . causes connection loss.

pgAdmin and other works.

How can I remove all Postico app and prefs to make a fresh install ?

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 17, 2016

Owner

OK, so I've now installed PostgreSQL 9.3.13 from homebrew, and I still can't reproduce the issue. Here's what I see:
screen shot 2016-08-17 at 15 39 38

  1. Can you look into your PostgreSQL server log file if you see any messages? When the server disconnects, there should be a message in the server log. By default, homebrew puts the server log in the path /usr/local/var/postgres/server.log
  2. What (if any) error message does Postico show?
  3. If you open Console.app (in /Applications/Utilities), do you see any messages when the connection is lost?

Fresh install of Postico
I don't think afresh install of Postico will help, but you can try:

  • Postico favorites are stored in ~/Library/Containers/at.eggerapps.postico/Data/Library/Application Support/Postico.
  • Additional preferences are stored using the user defaults system; to reset defaults, use the defaults command in Terminal: defaults delete at.eggerapps.Postico
Owner

jakob commented Aug 17, 2016

OK, so I've now installed PostgreSQL 9.3.13 from homebrew, and I still can't reproduce the issue. Here's what I see:
screen shot 2016-08-17 at 15 39 38

  1. Can you look into your PostgreSQL server log file if you see any messages? When the server disconnects, there should be a message in the server log. By default, homebrew puts the server log in the path /usr/local/var/postgres/server.log
  2. What (if any) error message does Postico show?
  3. If you open Console.app (in /Applications/Utilities), do you see any messages when the connection is lost?

Fresh install of Postico
I don't think afresh install of Postico will help, but you can try:

  • Postico favorites are stored in ~/Library/Containers/at.eggerapps.postico/Data/Library/Application Support/Postico.
  • Additional preferences are stored using the user defaults system; to reset defaults, use the defaults command in Terminal: defaults delete at.eggerapps.Postico
@utopic-men

This comment has been minimized.

Show comment
Hide comment
@utopic-men

utopic-men Aug 17, 2016

I have the same screenshot and when I try another request Postico ask me to reconnect (all temp tables are lost; pg pid seems new 85867 -> 85870) :

2016-08-17 15:43:05 CEST [85867-1] postgres@mgt LOG:  instruction : select version()
2016-08-17 15:43:05 CEST [85867-2] postgres@mgt LOG:  instruction : show all
2016-08-17 15:43:05 CEST [85867-3] postgres@mgt ERREUR:  erreur de syntaxe sur ou près de « . » au caractère 8
2016-08-17 15:43:05 CEST [85867-4] postgres@mgt INSTRUCTION :  select .
2016-08-17 15:43:07 CEST [85870-1] postgres@mgt LOG:  instruction : select version()
2016-08-17 15:43:07 CEST [85870-2] postgres@mgt LOG:  instruction : show all
2016-08-17 15:43:07 CEST [85870-3] postgres@mgt ERREUR:  erreur de syntaxe sur ou près de « . » au caractère 8
2016-08-17 15:43:07 CEST [85870-4] postgres@mgt INSTRUCTION :  select .

No message on connection loss in Console.app.

I'll try a fresh install.

utopic-men commented Aug 17, 2016

I have the same screenshot and when I try another request Postico ask me to reconnect (all temp tables are lost; pg pid seems new 85867 -> 85870) :

2016-08-17 15:43:05 CEST [85867-1] postgres@mgt LOG:  instruction : select version()
2016-08-17 15:43:05 CEST [85867-2] postgres@mgt LOG:  instruction : show all
2016-08-17 15:43:05 CEST [85867-3] postgres@mgt ERREUR:  erreur de syntaxe sur ou près de « . » au caractère 8
2016-08-17 15:43:05 CEST [85867-4] postgres@mgt INSTRUCTION :  select .
2016-08-17 15:43:07 CEST [85870-1] postgres@mgt LOG:  instruction : select version()
2016-08-17 15:43:07 CEST [85870-2] postgres@mgt LOG:  instruction : show all
2016-08-17 15:43:07 CEST [85870-3] postgres@mgt ERREUR:  erreur de syntaxe sur ou près de « . » au caractère 8
2016-08-17 15:43:07 CEST [85870-4] postgres@mgt INSTRUCTION :  select .

No message on connection loss in Console.app.

I'll try a fresh install.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 17, 2016

Owner

Maybe the issue is with the french localisation of the error message. I'll try setting my PostgreSQL server to french, maybe that triggers the error.

Owner

jakob commented Aug 17, 2016

Maybe the issue is with the french localisation of the error message. I'll try setting my PostgreSQL server to french, maybe that triggers the error.

@utopic-men

This comment has been minimized.

Show comment
Hide comment
@utopic-men

utopic-men Aug 17, 2016

Fresh install doesn't help...

utopic-men commented Aug 17, 2016

Fresh install doesn't help...

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 18, 2016

Owner

OK, I can reproduce the problem now, the problem is caused by the french error messages. I'll investigate why Postico loses the connection; there is probably an encoding problem somewhere.

Did you really install PostgreSQL with homebrew? I had to manually edit the PostgreSQL formula in order to enable french error messages because by default --enable-nls is disabled.

As a workaround, you can configure your server to emit English error messages by editing the following line in postgresql.conf:

lc_messages = 'en_US'           # locale for system error message

Anyway, thanks for your patience! I'll let you know as soon as I've fixed this issue.

Owner

jakob commented Aug 18, 2016

OK, I can reproduce the problem now, the problem is caused by the french error messages. I'll investigate why Postico loses the connection; there is probably an encoding problem somewhere.

Did you really install PostgreSQL with homebrew? I had to manually edit the PostgreSQL formula in order to enable french error messages because by default --enable-nls is disabled.

As a workaround, you can configure your server to emit English error messages by editing the following line in postgresql.conf:

lc_messages = 'en_US'           # locale for system error message

Anyway, thanks for your patience! I'll let you know as soon as I've fixed this issue.

@utopic-men

This comment has been minimized.

Show comment
Hide comment
@utopic-men

utopic-men Aug 19, 2016

Yes I'm sure concerning homebrew.
Perhaps it's because I'm on a french macbook...
In Postico setting set lc_messages = 'en_US' solves the problem.
As you suggested, I'll put it in the conf file.

Anyway, thank you for the workaround !
Feel free to close this ticket.

PS : is the localisation linked to the "null" tooltip I have over a column header instead of the data type ?

utopic-men commented Aug 19, 2016

Yes I'm sure concerning homebrew.
Perhaps it's because I'm on a french macbook...
In Postico setting set lc_messages = 'en_US' solves the problem.
As you suggested, I'll put it in the conf file.

Anyway, thank you for the workaround !
Feel free to close this ticket.

PS : is the localisation linked to the "null" tooltip I have over a column header instead of the data type ?

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 26, 2016

Owner

The "null" tooltip is unrelated, and is a separate bug that I should probably fix :)

I found the reason for this problem. In case you are interested in the details: Postico checked the PG_DIAG_SEVERITY value for errors to see if the connection can be reused (ERROR means yes, FATAL or PANIC means no). Unfortunately this value is localized; a server with french error messages will return ERREUR instead of ERROR. This confused Postico, and led it to believe that the connection is lost. I believe that this check is not actually necessary anymore, so I removed it.

The next version of Postico should work as expected with localized error messages. You can download the prerelease version here:
https://eggerapps-downloads.s3-eu-west-1.amazonaws.com/postico-1496.zip

Owner

jakob commented Aug 26, 2016

The "null" tooltip is unrelated, and is a separate bug that I should probably fix :)

I found the reason for this problem. In case you are interested in the details: Postico checked the PG_DIAG_SEVERITY value for errors to see if the connection can be reused (ERROR means yes, FATAL or PANIC means no). Unfortunately this value is localized; a server with french error messages will return ERREUR instead of ERROR. This confused Postico, and led it to believe that the connection is lost. I believe that this check is not actually necessary anymore, so I removed it.

The next version of Postico should work as expected with localized error messages. You can download the prerelease version here:
https://eggerapps-downloads.s3-eu-west-1.amazonaws.com/postico-1496.zip

@jakob jakob added the done label Aug 26, 2016

@utopic-men

This comment has been minimized.

Show comment
Hide comment
@utopic-men

utopic-men Aug 29, 2016

I close the ticket.

Thank you !

utopic-men commented Aug 29, 2016

I close the ticket.

Thank you !

@utopic-men utopic-men closed this Aug 29, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment