Re: How to fix 0xC0000005 exception in Postgres 9.0

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