Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1qRQZJ-001gGH-Ul
	for pgsql-bugs@arkaria.postgresql.org; Thu, 03 Aug 2023 05:08:14 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1qRQZH-00A207-VP
	for pgsql-bugs@arkaria.postgresql.org; Thu, 03 Aug 2023 05:08:12 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <justin@tocci.org>)
	id 1qRQZH-00A1wW-Le
	for pgsql-bugs@lists.postgresql.org; Thu, 03 Aug 2023 05:08:12 +0000
Received: from smtp.wfprod.com ([76.255.199.129])
	by magus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <justin@tocci.org>)
	id 1qRQZF-000Gps-3N
	for pgsql-bugs@lists.postgresql.org; Thu, 03 Aug 2023 05:08:11 +0000
Received: from [192.168.34.215] (unknown [76.255.195.161])
	by smtp.wfprod.com (Postfix) with ESMTPSA id 9D17EE2DBE;
	Thu,  3 Aug 2023 00:08:06 -0500 (CDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tocci.org; s=wfprod;
	t=1691039286; bh=JOtM0wrFDB/PYO/L8wWNnI6s1SzE74/5PhXbI9ePj9I=;
	h=Date:Subject:To:References:From:In-Reply-To;
	b=oNzhc36nuKJCfKXSn027v7mQQPW+PQ9xJIhKpjGI/DGmglGIAPy8RqyhzT8HZXwnK
	 zuHgOZaXJ5EvdRgg3auVxvgbLUIGDQopg5vCKJCpglzS7LXLMIBrFwZpkArajOW40h
	 vatqWZGJhlSZqQFt0UcMgffuytQQ5i04+xY2cHLQ=
Message-ID: <eacbf457-35b4-4bbe-5aec-423dfbc9c139@tocci.org>
Date: Thu, 3 Aug 2023 00:08:08 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
 Thunderbird/102.14.0
Subject: Re: BUG #18047: ODBC to PG long transaction causes PANIC
To: pgsql-bugs@lists.postgresql.org,
 PG Bug reporting form <noreply@postgresql.org>
References: <18047-02a64967c9174466@postgresql.org>
Content-Language: en-US
From: justin tocci <justin@tocci.org>
In-Reply-To: <18047-02a64967c9174466@postgresql.org>
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/eacbf457-35b4-4bbe-5aec-423dfbc9c139%40tocci.org>
Precedence: bulk

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    found it! had to set security.bsd.hardlink_check_gid=0 to give
    postgres the ability to create hard links.<br>
    please disregard.<br>
    <pre class="moz-signature" cols="72">Justin Tocci
817-503-9545</pre>
    <div class="moz-cite-prefix">On 8/2/2023 8:28 PM, PG Bug reporting
      form wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:18047-02a64967c9174466@postgresql.org">
      <pre class="moz-quote-pre" wrap="">The following bug has been logged on the website:

Bug reference:      18047
Logged by:          Justin Tocci
Email address:      <a class="moz-txt-link-abbreviated" href="mailto:justin@tocci.org">justin@tocci.org</a>
PostgreSQL version: 15.3
Operating system:   FreeBSD
Description:        

on the server:
everything going nicely:
Aug  2 19:41:12 bvc postgres[20276]: [7-24]
Aug  2 19:41:12 bvc postgres[20276]: [7-25]
Aug  2 19:41:12 bvc postgres[20276]: [7-26]
Then the server crashes....
Aug  2 19:41:12 bvc envelope.exe[47831]: FILE:
common_websocket.c:408:WS_sendFrame_step2() ERROR  === CERR: 35 (Resource
temporarily unavailable) === write() failed
...
Aug  2 19:41:12 bvc envelope.exe[47831]: FILE:
common_websocket.c:435:WS_sendFrame_step2() ERROR  === CERR: 35 (Resource
temporarily unavailable) === should never get to EAGAIN with libev
Aug  2 19:41:12 bvc postgres[10161]: [5-1] 2023-08-02 19:41:12.502 CDT
[10161] PANIC:  could not open file "pg_wal/000000010000000000000006": No
such file or directory
Aug  2 19:41:12 bvc envelope.exe[47831]: FILE:
common_client.c:326:client_notify_cb() ERROR  === Lost postgresql
connection: server closed the connection unexpectedly    This probably means
the server terminated abnormally    before or while processing the
request.
Aug  2 19:41:12 bvc syslogd: last message repeated 3 times
Aug  2 19:41:12 bvc postgres[13049]: [7-1] 2023-08-02 19:41:12.627 CDT
[13049] FATAL:  the database system is in recovery mode

ok, we got to recovery mode...

Aug  2 19:41:12 bvc envelope.exe[47831]: FILE:
common_client.c:241:cnxn_reset_cb() ERROR  === Connect failed: connection to
server on socket "/tmp/.s.PGSQL.5434" failed: FATAL:  the database system is
in recovery mode
Aug  2 19:41:12 bvc postgres[13568]: [7-1] 2023-08-02 19:41:12.628 CDT
[13568] FATAL:  the database system is in recovery mode
Aug  2 19:41:12 bvc postgres[13623]: [7-1] 2023-08-02 19:41:12.628 CDT
[13623] FATAL:  the database system is in recovery mode
Aug  2 19:41:12 bvc envelope.exe[47831]: FILE:
common_client.c:241:cnxn_reset_cb() ERROR  === Connect failed: connection to
server on socket "/tmp/.s.PGSQL.5434" failed: FATAL:  the database system is
in recovery mode
Aug  2 19:41:12 bvc syslogd: last message repeated 1 times
Aug  2 19:41:12 bvc postgres[15492]: [8-1] 2023-08-02 19:41:12.638 CDT
[15492] FATAL:  the database system is in recovery mode
Aug  2 19:41:12 bvc envelope.exe[47831]: FILE:
common_client.c:241:cnxn_reset_cb() ERROR  === Connect failed: connection to
server on socket "/tmp/.s.PGSQL.5434" failed: FATAL:  the database system is
in recovery mode
Aug  2 19:41:12 bvc postgres[14032]: [15-1] 2023-08-02 19:41:12.838 CDT
[14032] PANIC:  could not open file "pg_wal/000000010000000000000006": No
such file or directory

oh no! a PANIC! apparently we could not recover.

from the client i get nothing useful:
FATAL
common_client.c:cnxn_cb: FATAL
common_client.c:client_notify_cb: PQresetStart failed
cannot start connect: connection to server on socket "/tmp/.s.PGSQL.5434"
failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
last message repeated 1 times

quickest way to recover is to su - postgres:
pg_resetwal -f /opt/env.d/5434/data
exit
service postgresql restart 5434

and we are back in business.

I suspect any long script will be a problem but I happen to have the schema
that crashes the server for me. It's here: <a class="moz-txt-link-freetext" href="https://pastebin.com/Gr6gvPHa">https://pastebin.com/Gr6gvPHa</a> 
but it is long. it's a sage 100 table schema. I would say every one to four
times i run this script it crashes the server. We have tried this script
various ways and the result is the same but I am happy to take suggestions.


fourth run with: psql -p 5434 -1f sage.sql crashes and gave me the same
output as above.

Aug  2 20:10:12 bvc postgres[99759]: [5-1] 2023-08-02 20:10:12.687 CDT
[99759] ERROR:  syntax error at or near "dsafasdfds" at character 1
Aug  2 20:10:12 bvc postgres[99759]: [5-2] 2023-08-02 20:10:12.687 CDT
[99759] STATEMENT:  dsafasdfds
Aug  2 20:10:13 bvc postgres[20]: [5-1] 2023-08-02 20:10:13.981 CDT [20]
ERROR:  syntax error at or near "dsafasdfds" at character 1
Aug  2 20:10:13 bvc postgres[20]: [5-2] 2023-08-02 20:10:13.981 CDT [20]
STATEMENT:  dsafasdfds
Aug  2 20:10:14 bvc postgres[710]: [5-1] 2023-08-02 20:10:14.823 CDT [710]
ERROR:  syntax error at or near "dsafasdfds" at character 1
Aug  2 20:10:14 bvc postgres[710]: [5-2] 2023-08-02 20:10:14.823 CDT [710]
STATEMENT:  dsafasdfds
Aug  2 20:10:15 bvc postgres[88314]: [5-1] 2023-08-02 20:10:15.040 CDT
[88314] PANIC:  could not open file "pg_wal/000000010000000000000008": No
such file or directory

</pre>
    </blockquote>
    <br>
  </body>
</html>