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(at)hot(dot)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(dot)klaver(at)aklaver(dot)com