Обсуждение: How to fix 0xC0000005 exception in Postgres 9.0

Hi!

Every time when user tries to log on from same virtual computer where 
Postgreql resides, Postgres terminates with 0xC0000005 exception. Log is 
below.

About 65 users are accessing this server over VPN. They can use server 
normally.
Logging from localhost using pgadmin 4 with user postgres also works.

Only any attempt to log on from localhost with user dbadmin from psqlODBC 
client causes this exception.

It has worked normally for many years but now suddenly stopped working for 
localhost.

How to fix this ?

Server:

PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit

OS:

Windows server 2008 R2
Version 6.1 Build 7601

Andrus.

Postgres log:

2020-02-20 15:44:51 EET   LOG:  server process (PID 3788) was terminated by 
exception 0xC0000005
2020-02-20 15:44:51 EET   HINT:  See C include file "ntstatus.h" for a 
description of the hexadecimal value.
2020-02-20 15:44:51 EET   LOG:  terminating any other active server 
processes
2020-02-20 15:44:51 EET andrus mydatabase WARNING:  terminating connection 
because of crash of another server process
2020-02-20 15:44:51 EET andrus mydatabase DETAIL:  The postmaster has 
commanded this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted 
shared memory.
2020-02-20 15:44:51 EET andrus mydatabase HINT:  In a moment you should be 
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET kati mydatabase WARNING:  terminating connection 
because of crash of another server process
2020-02-20 15:44:51 EET kati mydatabase DETAIL:  The postmaster has 
commanded this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted 
shared memory.
2020-02-20 15:44:51 EET kati mydatabase HINT:  In a moment you should be 
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET julia mydatabase WARNING:  terminating connection 
because of crash of another server process
2020-02-20 15:44:51 EET julia mydatabase DETAIL:  The postmaster has 
commanded this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted 
shared memory.
2020-02-20 15:44:51 EET julia mydatabase HINT:  In a moment you should be 
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET reinpuu mydatabase WARNING:  terminating connection 
because of crash of another server process
2020-02-20 15:44:51 EET reinpuu mydatabase DETAIL:  The postmaster has 
commanded this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted 
shared memory.
2020-02-20 15:44:51 EET reinpuu mydatabase HINT:  In a moment you should be 
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET kokka mydatabase WARNING:  terminating connection 
because of crash of another server process
2020-02-20 15:44:51 EET kokka mydatabase DETAIL:  The postmaster has 
commanded this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted 
shared memory.
2020-02-20 15:44:51 EET kokka mydatabase HINT:  In a moment you should be 
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET myll mydatabase WARNING:  terminating connection 
because of crash of another server process
2020-02-20 15:44:51 EET myll mydatabase DETAIL:  The postmaster has 
commanded this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted 
shared memory.
2020-02-20 15:44:51 EET myll mydatabase HINT:  In a moment you should be 
able to reconnect to the database and repeat your command.
2020-02-20 15:44:51 EET   WARNING:  terminating connection because of crash 
of another server process
2020-02-20 15:44:51 EET   DETAIL:  The postmaster has commanded this server 
process to roll back the current transaction and exit, because another 
server process exited abnormally and possibly corrupted shared memory.
2020-02-20 15:44:51 EET   HINT:  In a moment you should be able to reconnect 
to the database and repeat your command.
2020-02-20 15:44:51 EET dbadmin mydatabase FATAL:  the database system is in 
recovery mode
2020-02-20 15:44:51 EET   LOG:  all server processes terminated; 
reinitializing
2020-02-20 15:45:01 EET   FATAL:  pre-existing shared memory block is still 
in use
2020-02-20 15:45:01 EET   HINT:  Check if there are any old server processes 
still running, and terminate them. 

On 2/20/20 12:35 PM, Andrus wrote:
> Hi!
> 
> Every time when user tries to log on from same virtual computer where 
> Postgreql resides, Postgres terminates with 0xC0000005 exception. Log is 
> below.
> 
> About 65 users are accessing this server over VPN. They can use server 
> normally.
> Logging from localhost using pgadmin 4 with user postgres also works.
> 
> Only any attempt to log on from localhost with user dbadmin from 
> psqlODBC client causes this exception.
> 
> It has worked normally for many years but now suddenly stopped working 
> for localhost.
> 
> How to fix this ?

Upgrade to a version of Postgres that is not 5 years past EOL?

> 
> Server:
> 
> PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit
> 
> OS:
> 
> Windows server 2008 R2
> Version 6.1 Build 7601
> 
> Andrus.
> 
> Postgres log:
> 
> 2020-02-20 15:44:51 EET   LOG:  server process (PID 3788) was terminated 
> by exception 0xC0000005
> 2020-02-20 15:44:51 EET   HINT:  See C include file "ntstatus.h" for a 
> description of the hexadecimal value.
> 2020-02-20 15:44:51 EET   LOG:  terminating any other active server 
> processes
> 2020-02-20 15:44:51 EET andrus mydatabase WARNING:  terminating 
> connection because of crash of another server process
> 2020-02-20 15:44:51 EET andrus mydatabase DETAIL:  The postmaster has 
> commanded this server process to roll back the current transaction and 
> exit, because another server process exited abnormally and possibly 
> corrupted shared memory.
> 2020-02-20 15:44:51 EET andrus mydatabase HINT:  In a moment you should 
> be able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET kati mydatabase WARNING:  terminating connection 
> because of crash of another server process
> 2020-02-20 15:44:51 EET kati mydatabase DETAIL:  The postmaster has 
> commanded this server process to roll back the current transaction and 
> exit, because another server process exited abnormally and possibly 
> corrupted shared memory.
> 2020-02-20 15:44:51 EET kati mydatabase HINT:  In a moment you should be 
> able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET julia mydatabase WARNING:  terminating 
> connection because of crash of another server process
> 2020-02-20 15:44:51 EET julia mydatabase DETAIL:  The postmaster has 
> commanded this server process to roll back the current transaction and 
> exit, because another server process exited abnormally and possibly 
> corrupted shared memory.
> 2020-02-20 15:44:51 EET julia mydatabase HINT:  In a moment you should 
> be able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET reinpuu mydatabase WARNING:  terminating 
> connection because of crash of another server process
> 2020-02-20 15:44:51 EET reinpuu mydatabase DETAIL:  The postmaster has 
> commanded this server process to roll back the current transaction and 
> exit, because another server process exited abnormally and possibly 
> corrupted shared memory.
> 2020-02-20 15:44:51 EET reinpuu mydatabase HINT:  In a moment you should 
> be able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET kokka mydatabase WARNING:  terminating 
> connection because of crash of another server process
> 2020-02-20 15:44:51 EET kokka mydatabase DETAIL:  The postmaster has 
> commanded this server process to roll back the current transaction and 
> exit, because another server process exited abnormally and possibly 
> corrupted shared memory.
> 2020-02-20 15:44:51 EET kokka mydatabase HINT:  In a moment you should 
> be able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET myll mydatabase WARNING:  terminating connection 
> because of crash of another server process
> 2020-02-20 15:44:51 EET myll mydatabase DETAIL:  The postmaster has 
> commanded this server process to roll back the current transaction and 
> exit, because another server process exited abnormally and possibly 
> corrupted shared memory.
> 2020-02-20 15:44:51 EET myll mydatabase HINT:  In a moment you should be 
> able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET   WARNING:  terminating connection because of 
> crash of another server process
> 2020-02-20 15:44:51 EET   DETAIL:  The postmaster has commanded this 
> server process to roll back the current transaction and exit, because 
> another server process exited abnormally and possibly corrupted shared 
> memory.
> 2020-02-20 15:44:51 EET   HINT:  In a moment you should be able to 
> reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET dbadmin mydatabase FATAL:  the database system 
> is in recovery mode
> 2020-02-20 15:44:51 EET   LOG:  all server processes terminated; 
> reinitializing
> 2020-02-20 15:45:01 EET   FATAL:  pre-existing shared memory block is 
> still in use
> 2020-02-20 15:45:01 EET   HINT:  Check if there are any old server 
> processes still running, and terminate them.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

>Given this is localhost connection,  start looking at firewall or AV.

Windows firewall is turned off. It does not affect to local connections in 
same computer.
I turned windows antivirus off but problem persists.

Andrus.

Hi!

>Upgrade to a version of Postgres that is not 5 years past EOL?

Not possible at moment because requires lot of testing not to break existing 
applications.
Planned in future.

Andrus.

On 2/20/20 1:27 PM, Andrus wrote:
> Hi!
> 
>> Upgrade to a version of Postgres that is not 5 years past EOL?
> 
> Not possible at moment because requires lot of testing not to break 
> existing applications.
> Planned in future.

Alright.

Then what changes were done recently, in particular ODBC-related.

> 
> Andrus.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/20/20 2:29 PM, Andrus wrote:
> Hi!
>  >Then what changes were done recently, in particular ODBC-related.
> Same psqlODBC driver 09.00.0101 has worked 10 years.
> Previously I logged into this server long time ago and then it worked.
> Windows update history shows lot of updates all called
> Security Intelligence Update for Microsoft Endpoint Protection - KB 2461484
> with different version numbers.

So did the issue show up after the above updates?

> image
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2020-Feb-20, Andrus wrote:

> > Upgrade to a version of Postgres that is not 5 years past EOL?
> 
> Not possible at moment because requires lot of testing not to break existing
> applications.
> Planned in future.

Applications are already broken, since they can't connect, right?  Seems
like a great time to test them with an upgraded server.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2/20/20 2:42 PM, Alvaro Herrera wrote:
> On 2020-Feb-20, Andrus wrote:
> 
>>> Upgrade to a version of Postgres that is not 5 years past EOL?
>>
>> Not possible at moment because requires lot of testing not to break existing
>> applications.
>> Planned in future.
> 
> Applications are already broken, since they can't connect, right?  Seems
> like a great time to test them with an upgraded server.
> 

 From here:

https://www.postgresql.org/message-id/B75CDDBB971F43D0864066636BBAA24B%40dell2

the breakage seems restricted to ODBC connection to localhost with user 
dbadmin(other users maybe?). The driver is psqlODBC driver 09.00.0101, 
which I believe is pre-libpq, so libpq connections seem to work.



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/20/20 4:35 PM, Adrian Klaver wrote:
> On 2/20/20 2:29 PM, Andrus wrote:
>> Hi!
>>  >Then what changes were done recently, in particular ODBC-related.
>> Same psqlODBC driver 09.00.0101 has worked 10 years.
>> Previously I logged into this server long time ago and then it worked.
>> Windows update history shows lot of updates all called
>> Security Intelligence Update for Microsoft Endpoint Protection - KB 2461484
>> with different version numbers.
>
> So did the issue show up after the above updates?

What is ntstatus.h?  Would looking there for 0xC0000005 give a clue to the 
problem (and thus solution)?

>
>> image
>> Andrus.
>
>

-- 
Angular momentum makes the world go 'round.

On 2/20/20 2:42 PM, Alvaro Herrera wrote:
> On 2020-Feb-20, Andrus wrote:
> 
>>> Upgrade to a version of Postgres that is not 5 years past EOL?
>>
>> Not possible at moment because requires lot of testing not to break existing
>> applications.
>> Planned in future.
> 
> Applications are already broken, since they can't connect, right?  Seems
> like a great time to test them with an upgraded server.
> 

Realized I should have been clearer. By pre-libpq I meant this:

https://odbc.postgresql.org/docs/release.html

psqlODBC 09.05.0100 Release
Changes:

     Use libpq for all communication with the server
     Previously, libpq was only used for authentication. Using it for 
all communication lets us remove a lot of duplicated code. libpq is now 
required for building or using libpq.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/20/20 2:48 PM, Ron wrote:
> On 2/20/20 4:35 PM, Adrian Klaver wrote:
>> On 2/20/20 2:29 PM, Andrus wrote:
>>> Hi!
>>>  >Then what changes were done recently, in particular ODBC-related.
>>> Same psqlODBC driver 09.00.0101 has worked 10 years.
>>> Previously I logged into this server long time ago and then it worked.
>>> Windows update history shows lot of updates all called
>>> Security Intelligence Update for Microsoft Endpoint Protection - KB 
>>> 2461484
>>> with different version numbers.
>>
>> So did the issue show up after the above updates?
> 
> What is ntstatus.h?  Would looking there for 0xC0000005 give a clue to 
> the problem (and thus solution)?

https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-erref/596a1078-e883-4972-9bbc-49e60bebca55

Good luck on gaining any useful information:)

> 
>>
>>> image
>>> Andrus.
>>
>>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/20/20 2:48 PM, Ron wrote:

On 2/20/20 4:35 PM, Adrian Klaver wrote:

On 2/20/20 2:29 PM, Andrus wrote:

Hi!
 >Then what changes were done recently, in particular ODBC-related.
Same psqlODBC driver 09.00.0101 has worked 10 years.
Previously I logged into this server long time ago and then it worked.
Windows update history shows lot of updates all called
Security Intelligence Update for Microsoft Endpoint Protection - KB 2461484
with different version numbers.

So did the issue show up after the above updates?

What is ntstatus.h?  Would looking there for 0xC0000005 give a clue to the problem (and thus solution)?

https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-erref/596a1078-e883-4972-9bbc-49e60bebca55

Good luck on gaining any useful information:)

Hi!

>Realized I should have been clearer. By pre-libpq I meant this:
>https://odbc.postgresql.org/docs/release.html
>psqlODBC 09.05.0100 Release
>Changes:
>     Use libpq for all communication with the server
>     Previously, libpq was only used for authentication. Using it for all 
> communication lets us remove a lot of duplicated code. libpq is now 
> required for building or using libpq.

I upgraded psqlodbc driver to 12.1 version but problem persists.
After server is manually started, application works.

I added log_statement = 'all' . Log before crash is:

2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: SELECT 
drop_table('temptulemus')
2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: create temp table 
temptulemus as SELECT * FROM andmetp  ;select * from temptulemus limit 0
2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: select n.nspname, 
c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, 
a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, 
pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype 
else 0 end, t.typtypmod, c.relhasoids, '', c.relhassubclass from 
(((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = 
c.relnamespace and c.oid = 28203181) inner join pg_catalog.pg_attribute a on 
(not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join 
pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on 
a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by 
n.nspname, c.relname, attnum
2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: select COUNT(*)::int 
from temptulemus
2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: ;SELECT * FROM 
temptulemus offset 0 limit 900
2020-02-21 18:46:40 EET   LOG:  server process (PID 6000) was terminated by 
exception 0xC0000005
2020-02-21 18:46:40 EET   HINT:  See C include file "ntstatus.h" for a 
description of the hexadecimal value.
2020-02-21 18:46:40 EET   LOG:  terminating any other active server 
processes

So command which causes crash is

SELECT * FROM temptulemus offset 0 limit 900

As shown in lines before this is data from andmetp table. This table 
contains text type column. This column may contain data like <script>
Maybe this causes postgres crash.  Or maybe some unicode character in table 
cause crash. This table does not contain binary data.
Database encoding is UTF-8.

There is also other server in Linux

PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 
4.9.2, 64-bit

which drops connection when reading the same table. Itlooks like in this 
case linux re-starts process automatically so application starts.
Table contents are similar in both servers.

Andrus. 

Hi!

>forgot to say publish the Linux logs it may have more details what is going 
>on

Linux server has older application client which replicates all data one from 
andmetp table using select * from andmetp
I tried several times. Linux log contains

2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
2020-02-21 16:18:50 EET mydbadmin mydb FATAL:  connection to client lost
2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
2020-02-21 17:19:08 EET mydbadmin mydb LOG:  could not send data to client: 
Connection reset by peer
2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
2020-02-21 17:19:08 EET mydbadmin mydb FATAL:  connection to client lost
2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp

It looks like only connection was terminated.  “dmesg -T” does not show 
postgres crash. Maybe postgres main process is not killed in Linux.

> is this happening from any client or just a specific client running ODBC?

This happene if Windows 2008 server if logged in same same server from RDP 
client and in my development windows 10 workstation which logs to linux 
server over VPN

>are the clients running AV if so are the AV versions the same?

In windows 2008 server antivirus was turned off like I wrote.  In my devel 
workstation I use Microsoft antivirus coming with windows 10.

>Given this is killing a Linux server,  sounds like ODBC is sending  back 
>garabage data to the server crashing it.

I can try publish odbc log if this helps.

Application replicateds some tables at startup to local disk.
For tables with 5000 or more rows odbc connection hangs if there is big TCP 
packet loss.
So we changed  replication command

select * from mytable

to commands

select drop_table(‘temptulemus’);
create temp table  temptulemus as select * from mytable;

select * from mytable offset 0 limit 900;
select * from mytable offset 900 limit 900;
select * from mytable offset 1800 limit 900;
...

etc.

In this case data was also retrieved on poor connections.
Maybe there is some better solution for this.

>There are several settings in OBDC, to change how text columns are 
>processed, play around with those settings see if that helps it
>https://odbc.postgresql.org/docs/config.html

Application needs certain settings. Maybe it is better try to upgrade to 
Postgres 12.2 first.

>As you have it narrowed down to a table,  try querying only a few records 
>at a time to see if you can identify the specific Record(s) that may be the 
>issue.
>SELECT * FROM temptulemus where temptulemus.unique_id >1  offset 0 limit 
>100
>and try querying the columns that do not contain the suspect data that 
>could be causing this

andmetp table contains 584 record in linux server. I tried script to read 
data from this table every time one more row

for i=1 to 600
? i
StartTextMerge()
TEXT TEXTMERGE NOSHOW
select * from andmetp limit <<tran(i)>>
ENDTEXT
IF !TExec()
  RETURN .f.
  ENDIF
endfor

this worked without error.

Andrus. 

Hi!

>Yes publish the ODBC logs

I send psqlodbc log from windows server to you.

I added and removed Debug=1 parameter from odbc conncetion string, ran 
application as administrator.
Other errors

2020-02-21 21:27:30 EET  ERROR:  invalid memory alloc request size 
4294967293
2020-02-21 21:27:30 EET STATEMENT:  ;SELECT * FROM temptulemus offset 0 
limit 900

and

2020-02-21 21:25:37 EET ERROR:  could not open relation with OID 538386464
2020-02-21 21:25:37 EET STATEMENT:  ;SELECT * FROM temptulemus offset 0 
limit 900

also occured.

> Hello FOXPRO CODE
Yes.
Starting at psqlODBC 09.05.0100 when it uses libpq for all FoxPro does not 
show error message details anymore.
I posted it it

https://stackoverflow.com/questions/54978713/how-to-get-psqlodbc-error-details-in-visual-foxpro

Maybe anybody has some idea how to get postgres error message details using 
new psqlodbc drivers.
I looked into odbc description and havent found how error message details 
are returned.

Andrus. 

On 2/21/20 10:24 AM, Andrus wrote:
> Hi!
> 
>> forgot to say publish the Linux logs it may have more details what is 
>> going on
> 
> Linux server has older application client which replicates all data one 
> from andmetp table using select * from andmetp
> I tried several times. Linux log contains
> 
> 2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
> 2020-02-21 16:18:50 EET mydbadmin mydb FATAL:  connection to client lost
> 2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
> 2020-02-21 17:19:08 EET mydbadmin mydb LOG:  could not send data to 
> client: Connection reset by peer
> 2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
> 2020-02-21 17:19:08 EET mydbadmin mydb FATAL:  connection to client lost
> 2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
> 
> It looks like only connection was terminated.  “dmesg -T” does not show 
> postgres crash. Maybe postgres main process is not killed in Linux.
> 
>> is this happening from any client or just a specific client running ODBC?
> 
> This happene if Windows 2008 server if logged in same same server from 
> RDP client and in my development windows 10 workstation which logs to 
> linux server over VPN
> 
>> are the clients running AV if so are the AV versions the same?
> 
> In windows 2008 server antivirus was turned off like I wrote.  In my 
> devel workstation I use Microsoft antivirus coming with windows 10.
> 
>> Given this is killing a Linux server,  sounds like ODBC is sending  
>> back garabage data to the server crashing it.
> 
> I can try publish odbc log if this helps.
> 
> Application replicateds some tables at startup to local disk.
> For tables with 5000 or more rows odbc connection hangs if there is big 
> TCP packet loss.
> So we changed  replication command
> 
> select * from mytable
> 
> to commands
> 
> select drop_table(‘temptulemus’);
> create temp table  tempestuous as select * from mytable;
> 
> select * from mytable offset 0 limit 900;
> select * from mytable offset 900 limit 900;
> select * from mytable offset 1800 limit 900;
> ...
> 
> etc.
> 
> In this case data was also retrieved on poor connections.
> Maybe there is some better solution for this.
> 
>> There are several settings in OBDC, to change how text columns are 
>> processed, play around with those settings see if that helps it
>> https://odbc.postgresql.org/docs/config.html
> 
> Application needs certain settings. Maybe it is better try to upgrade to 
> Postgres 12.2 first.
> 
>> As you have it narrowed down to a table,  try querying only a few 
>> records at a time to see if you can identify the specific Record(s) 
>> that may be the issue.
>> SELECT * FROM temptulemus where temptulemus.unique_id >1  offset 0 
>> limit 100
>> and try querying the columns that do not contain the suspect data that 
>> could be causing this
> 
> andmetp table contains 584 record in linux server. I tried script to 
> read data from this table every time one more row


In psql what does \d tempestuous show?

What relation is andmetp to tempestuous?

I thought you said you had problem with same table in Linux and Windows 
servers?

 From above what does the drop_table() function do in?:

select drop_table(‘temptulemus’);

> 
> for i=1 to 600
> ? i
> StartTextMerge()
> TEXT TEXTMERGE NOSHOW
> select * from andmetp limit <<tran(i)>>
> ENDTEXT
> IF !TExec()
>   RETURN .f.
>   ENDIF
> endfor
> 
> this worked without error.
> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

>In psql what does \d tempestuous show?
>What relation is andmetp to tempestuous?

I'm sorry, this was typo. Correct commands issued from application are

select drop_table('temptulemus');
create temp table  temptulemus as select * from andmetp;
select * from temptulemus  offset 0 limit 900;

>I thought you said you had problem with same table in Linux and Windows 
>servers?

Yes.

> From above what does the drop_table() function do in?:
>select drop_table(‘temptulemus’);

drop_table is defined as

CREATE OR REPLACE FUNCTION drop_table(TEXT)
  RETURNS VOID STRICT LANGUAGE plpgsql AS $$
    BEGIN
    EXECUTE 'DROP TABLE ' || $1;
    EXCEPTION WHEN UNDEFINED_TABLE THEN
    RETURN;
    END;
    $$;


Andrus. 

On 2/21/20 12:50 PM, Andrus wrote:
> Hi!
> 
>> In psql what does \d tempestuous show?
>> What relation is andmetp to tempestuous?
> 
> I'm sorry, this was typo. Correct commands issued from application are
> 
> select drop_table('temptulemus');
> create temp table  temptulemus as select * from andmetp;
> select * from temptulemus  offset 0 limit 900;

Alright so what does:

\d temptulemus

and/or

\d andmetp

show?

> 
>> I thought you said you had problem with same table in Linux and 
>> Windows servers?
> 
> Yes.
> 
>> From above what does the drop_table() function do in?:
>> select drop_table(‘temptulemus’);
> 
> drop_table is defined as
> 
> CREATE OR REPLACE FUNCTION drop_table(TEXT)
>   RETURNS VOID STRICT LANGUAGE plpgsql AS $$
>     BEGIN
>     EXECUTE 'DROP TABLE ' || $1;
>     EXCEPTION WHEN UNDEFINED_TABLE THEN
>     RETURN;
>     END;
>     $$;
> 
> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

>Alright so what does:
>\d temptulemus

Did not find any relation named "temptulemus".

>and/or
>\d andmetp

                   Table "public.andmetp"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
andmetp    | character(25) |           | not null |
klass      | character(1)  |           |          |
andmeklass | character(10) |           |          |
otsbaas    | character(8)  |           |          |
kiirpref   | character(80) |           |          |
kiirnimi   | character(10) |           |          |
inlahte    | character(10) |           |          |
vaateindks | character(10) |           |          |
tyhjakeeld | ebool         |           |          |
whenting   | character(50) |           |          |
kiirindks  | character(10) |           |          |
validting  | text          |           |          |
valivaljad | character(10) |           |          |
valivali2  | character(10) |           |          |
userlang   | ebool         |           |          |
valifilter | character(80) |           |          |
paring     | character(60) |           |          |
allrows    | ebool         |           |          |
html       | text          |           |          |
specialwhe | text          |           |          |
pakuvalik  | text          |           |          |
klikkprots | text          |           |          |
valivali3  | character(10) |           |          |
Indexes:
    "andmetp_pkey" PRIMARY KEY, btree (andmetp)
Referenced by:
    TABLE "desktop" CONSTRAINT "desktop_alamklass_fkey" FOREIGN KEY 
(alamklass)
REFERENCES andmetp(andmetp) ON UPDATE CASCADE DEFERRABLE
Triggers:
    andmetp_trig BEFORE INSERT OR DELETE OR UPDATE ON andmetp FOR EACH 
STATEMENT
EXECUTE PROCEDURE setlastchange()

Andrus.

On 2/21/20 2:02 PM, Andrus wrote:
Please reply to list also.
Ccing list.

To me the relevant part of the log is below. Not sure what it means though:

[3604-0.187] execute.c[Exec_with_parameters_resolved]421: copying 
statement params: trans_status=1, len=45, stmt=';SELECT * FROM 
temptulemus offset 0 limit 900'
[3604-0.187] convert.c[inner_process_tokens]3909: token_len=6 status=1 
token=SELECT
[3604-0.187] convert.c[inner_process_tokens]3909: token_len=1 status=0 
token=*
[3604-0.187] convert.c[inner_process_tokens]3909: token_len=4 status=1 
token=FROM
[3604-0.187] convert.c[inner_process_tokens]3909: token_len=11 status=1 
token=temptulemus
[3604-0.187] convert.c[inner_process_tokens]3909: token_len=6 status=1 
token=offset
[3604-0.187] convert.c[inner_process_tokens]3909: token_len=1 status=1 
token=0
[3604-0.187] convert.c[inner_process_tokens]3909: token_len=5 status=1 
token=limit
[3604-0.187] execute.c[Exec_with_parameters_resolved]444: 
stmt_with_params = ';SELECT * FROM temptulemus offset 0 limit 900'
[3604-0.187] execute.c[Exec_with_parameters_resolved]449: about to begin 
SC_execute
[3604-0.187]statement.[SC_execute]2037:       it's NOT a select 
statement: stmt=005FE040
[3604-0.187]connection[CC_send_query_append]1769: conn=0061FBE8, 
query=';SELECT * FROM temptulemus offset 0 limit 900'
[3604-0.187]connection[CC_send_query_append]1849: query_len=45
[3604-0.187]connection[CC_send_query_append]1892: [QLOG]PQsendQuery: 
005FDB80 ';SELECT * FROM temptulemus offset 0 limit 900'
[3604-0.187] qresult.c[QR_Constructor]173: entering
[3604-0.187] qresult.c[QR_Constructor]242: leaving
[3604-0.187]connection[handle_pgres_error]870: [QLOG]    ((null)) (null) 
'(null)'
[3604-0.187]connection[handle_pgres_error]902: error message=server 
closed the connection unexpectedly
    This probably means the server terminated abnormally


> Hi!
> odbc log file is attached.
> Error is here in this file :
> [3604-0.187]connection[handle_pgres_error]870: [QLOG] ((null)) (null) 
> '(null)'
> [3604-0.187]connection[handle_pgres_error]902: error message=server 
> closed the connection unexpectedly
>      This probably means the server terminated abnormally
>      before or while processing the request.
> (137)
> Andrus.
> *From:* Justin
> *Sent:* Friday, February 21, 2020 8:44 PM
> *To:* Andrus
> *Cc:* Alvaro Herrera ; Adrian Klaver ; pgsql-general
> *Subject:* Re: How to fix 0xC0000005 exception in Postgres 9.0
> Yes publish the ODBC logs
> seeing the Linux logs tells us the ODBC client crashing NOT the client 
> process.  if the linux  postgrsql client process crashes it typically 
> will cause the postgresql postmaster  to panic and restart as it has to 
> assume it corrupt share memory.
> On Fri, Feb 21, 2020 at 1:25 PM Andrus <kobruleht2@hot.ee> wrote:
> 
>     Hi!
> 
>      >forgot to say publish the Linux logs it may have more details what
>     is going
>      >on
> 
>     Linux server has older application client which replicates all data
>     one from
>     andmetp table using select * from andmetp
>     I tried several times. Linux log contains
> 
>     2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
> 
> This is a big hint here that is started to spool the data back to the 
> client and the client crashed...
> 
>     2020-02-21 16:18:50 EET mydbadmin mydb FATAL:  connection to client lost
>     2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
>     2020-02-21 17:19:08 EET mydbadmin mydb LOG:  could not send data to
>     client:
>     Connection reset by peer
> 
> this tells us the client dropped out not the server..
> 
>     2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
>     2020-02-21 17:19:08 EET mydbadmin mydb FATAL:  connection to client lost
>     2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
> 
>     It looks like only connection was terminated. “dmesg -T” does not show
>     postgres crash. Maybe postgres main process is not killed in Linux.
> 
>      > is this happening from any client or just a specific client
>     running ODBC?
> 
>     This happene if Windows 2008 server if logged in same same server
>     from RDP
> 
> RDP should not be causing an issue
> 
>     client and in my development windows 10 workstation which logs to linux
>     server over VPN
> 
> This being run over VPN does add some complexity to this as it can cause 
> corrupt data packets
> 
> 
>      >are the clients running AV if so are the AV versions the same?
> 
>     In windows 2008 server antivirus was turned off like I wrote.  In my
>     devel
>     workstation I use Microsoft antivirus coming with windows 10.
> 
>      >Given this is killing a Linux server,  sounds like ODBC is
>     sending  back
>      >garabage data to the server crashing it.
> 
>     I can try publish odbc log if this helps.
> 
> Yes that was going to be next suggestion turn the log to full, run the 
> command see what happens
> 
> 
>     Application replicateds some tables at startup to local disk.
>     For tables with 5000 or more rows odbc connection hangs if there is
>     big TCP
>     packet loss.
>     So we changed  replication command
> 
> This is a hint something is wrong with the Network,  TCP packet lose  
> should just request packet again.
> 
>     select * from mytable
> 
>     to commands
> 
>     select drop_table(‘temptulemus’);
>     create temp table  temptulemus as select * from mytable;
> 
>     select * from mytable offset 0 limit 900;
>     select * from mytable offset 900 limit 900;
>     select * from mytable offset 1800 limit 900;
>     ...
> 
>     etc.
> 
>     In this case data was also retrieved on poor connections.
>     Maybe there is some better solution for this.
> 
> If the connections are bad enough and starts corrupting data it will 
> cause all kinds of weird issues.
> 
> 
>      >There are several settings in OBDC, to change how text columns are
>      >processed, play around with those settings see if that helps it
>      >https://odbc.postgresql.org/docs/config.html
> 
>     Application needs certain settings. Maybe it is better try to
>     upgrade to
>     Postgres 12.2 first.
> 
>      >As you have it narrowed down to a table,  try querying only a few
>     records
>      >at a time to see if you can identify the specific Record(s) that
>     may be the
>      >issue.
>      >SELECT * FROM temptulemus where temptulemus.unique_id >1  offset 0
>     limit
>      >100
>      >and try querying the columns that do not contain the suspect data
>     that
>      >could be causing this
> 
>     andmetp table contains 584 record in linux server. I tried script to
>     read
>     data from this table every time one more row
> 
> Hello FOXPRO CODE
> 
>     for i=1 to 600
>     ? i
>     StartTextMerge()
>     TEXT TEXTMERGE NOSHOW
>     select * from andmetp limit <<tran(i)>>
>     ENDTEXT
>     IF !TExec()
>     RETURN .f.
>        ENDIF
>     endfor
> 
>     this worked without error.
> 
> This is shaping up more and more like Networking Problem corrupting 
> data,  bad router or port
> 
> 
>     Andrus.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

>To me the relevant part of the log is below. Not sure what it means though:
>[3604-0.187] execute.c[Exec_with_parameters_resolved]444:
>stmt_with_params = ';SELECT * FROM temptulemus offset 0 limit 900'
>[3604-0.187] execute.c[Exec_with_parameters_resolved]449: about to begin
>SC_execute
>[3604-0.187]statement.[SC_execute]2037:       it's NOT a select
>statement: stmt=005FE040

Maybe issue occurs when ODBC client sends command to odbc driver which 
contains semicolon as first character before SELECT and returned data
contains certain characters and server or client has certain configuration.
Maybe I will change application not to send semicolon before SELECT 
statement.

Andrus. 

On 2/21/20 11:43 PM, Andrus wrote:
> Hi!
> 
>> To me the relevant part of the log is below. Not sure what it means 
>> though:
>> [3604-0.187] execute.c[Exec_with_parameters_resolved]444:
>> stmt_with_params = ';SELECT * FROM temptulemus offset 0 limit 900'
>> [3604-0.187] execute.c[Exec_with_parameters_resolved]449: about to begin
>> SC_execute
>> [3604-0.187]statement.[SC_execute]2037:       it's NOT a select
>> statement: stmt=005FE040
> 
> Maybe issue occurs when ODBC client sends command to odbc driver which 
> contains semicolon as first character before SELECT and returned data

I have no idea. For psqlODBC issues I would suggest asking here:

https://www.postgresql.org/list/pgsql-odbc/

Chances are better that there will be someone there that could answer 
you questions.

> contains certain characters and server or client has certain configuration.
> Maybe I will change application not to send semicolon before SELECT 
> statement.
> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

>I have no idea.

I changed application not to send ; before select.
This solves issues in both servers.

So using ; as first character before select in ODBC command like

;SELECT * from mytable

Causes C5 is Postgres 9.0 and ODBC client hangup with "connection reset by
peer" message in log file in Postgres 9.6
It was great help and especially great ODBC log analyzing in this list,
thanks.

>For psqlODBC issues I would suggest asking here:
>https://www.postgresql.org/list/pgsql-odbc/
>Chances are better that there will be someone there that could answer you
>questions.

I posted error message details issue long time ago in this pgsql-odbc list 
but havent got solution.
I posted it as separate message here and in

https://stackoverflow.com/questions/60357505/how-to-fix-psqlodbc-driver-regression-to-get-error-message-details

Andrus. 

On 2/22/20 2:39 PM, Andrus wrote:
> Hi!
> 
>> I have no idea.
> 
> I changed application not to send ; before select.
> This solves issues in both servers.
> 
> So using ; as first character before select in ODBC command like
> 
> ;SELECT * from mytable
> 
> Causes C5 is Postgres 9.0 and ODBC client hangup with "connection reset by
> peer" message in log file in Postgres 9.6
> It was great help and especially great ODBC log analyzing in this list,
> thanks.
> 
>> For psqlODBC issues I would suggest asking here:
>> https://www.postgresql.org/list/pgsql-odbc/
>> Chances are better that there will be someone there that could answer you
>> questions.
> 
> I posted error message details issue long time ago in this pgsql-odbc 
> list but havent got solution.
> I posted it as separate message here and in

This is a different issue and involves a product VFP that is EOL 5-10 
years depending on support package. I'm going to say the hand writing is 
on the wall and it is time to upgrade software.


> 
> https://stackoverflow.com/questions/60357505/how-to-fix-psqlodbc-driver-regression-to-get-error-message-details 
> 
> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/22/20 2:39 PM, Andrus wrote:

This is a different issue and involves a product VFP that is EOL 5-10 years depending on support package. I'm going to say the hand writing is on the wall and it is time to upgrade software.

 On 2/22/20 5:12 PM, Adrian Klaver wrote:

On 2/22/20 2:39 PM, Andrus wrote:

[snip]

This is a different issue and involves a product VFP that is EOL 5-10 years depending on support package. I'm going to say the hand writing is on the wall and it is time to upgrade software.

I don't know where you work, but where I work, old programs where the source code disappeared ages ago, but have worked reliably for 15+ years is distressingly common.

Breaking userland backwards compatibility is a mortal sin, and one of the reasons that MS software is so popular is that they work so hard to not break userland backwards compatibility.

--

Greetings,

* Ron (ronljohnsonjr@gmail.com) wrote:
> Breaking userland backwards compatibility is a mortal sin, and one of the
> reasons that MS software is so popular is that they work so hard to *not*
> break userland backwards compatibility.

It's also a reason why it's a mess and not everyone is terribly thrilled
to have to deal with it.

Changes that break things on users should be considered and weighed, and
that's what the discussion here should be driving towards.  Neither
"never break anything ever" or "break everything every release" is
tenable.

Seems we've managed to do a pretty good job over time, based on what
I've heard and our popularity, and I dare say it'd be best if we
continued on in much the way we have these past decades.

Thanks,

Stephen

On 2/22/20 3:28 PM, Ron wrote:
> On 2/22/20 5:12 PM, Adrian Klaver wrote:
>> On 2/22/20 2:39 PM, Andrus wrote:
> [snip]
>> This is a different issue and involves a product VFP that is EOL 5-10 
>> years depending on support package. I'm going to say the hand writing 
>> is on the wall and it is time to upgrade software.
> 
> I don't know where you work, but where I work, old programs where the 
> source code disappeared ages ago, but have worked reliably for 15+ years 
> is distressingly common.

I see the same thing. In the end it comes down to whether you want to 
pay the price one workaround at a time or by moving forward to something 
you don't have to cross your fingers everytime you start it.

> 
> Breaking userland backwards compatibility is a mortal sin, and one of 
> the reasons that MS software is so popular is that they work so hard to 
> *not* break userland backwards compatibility.

That is not true. I have a programs that have to run on old versions of 
Windows as they no longer run reliably or at all on newer Windows 
versions and the client does not want to upgrade/change the program. And 
no, compatibility mode is not the answer.

> 
> -- 
> Angular momentum makes the world go 'round.


-- 
Adrian Klaver
adrian.klaver@aklaver.com