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 ) id 1rzknN-006ST9-AX for pgsql-novice@arkaria.postgresql.org; Wed, 24 Apr 2024 22:08:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rzknL-000dx6-Re for pgsql-novice@arkaria.postgresql.org; Wed, 24 Apr 2024 22:08:51 +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 ) id 1rzknK-000dwy-Ue for pgsql-novice@lists.postgresql.org; Wed, 24 Apr 2024 22:08:51 +0000 Received: from resqmta-c2p-570503.sys.comcast.net ([2001:558:fd00:56::5]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzknG-002mQr-Jx for pgsql-novice@lists.postgresql.org; Wed, 24 Apr 2024 22:08:49 +0000 Received: from resomta-c2p-555954.sys.comcast.net ([96.102.18.234]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by resqmta-c2p-570503.sys.comcast.net with ESMTPS id zjhdrRkatbwk3zkn7rMxMk; Wed, 24 Apr 2024 22:08:37 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1713996517; bh=TPvLySJB35e2FpgH9NW7naZGqGjYJsx1f1hQScjNujU=; h=Received:Received:Date:From:To:Message-ID:Subject:MIME-Version: Content-Type:Xfinity-Spam-Result; b=OZDmulmikCHeI+hTRuY+74pLV0HvTLG7TmlvKFxf/2Qgkl8IqkgNq3jMwnv3y+qRX mpmZFFKWPClS1Cb7pMeLk3CoVvfwVLuJwufcULy6HdQowzVmMRpTJW+lipky4cmt7M Xd5CS3KAzpgfTCxsOEfjN8wVLhTYAaToxtKtp0uqLDgAb5X66XwjhKwrAnxVj0J5CL jqGAvegQxJpQdGCwmEGzl/4u+RWm+mMEp029RrW8N6Mv0nE0Ao/W0pVgT8mLkp41xv iQHXc8UCeJoDcDXZ2ZzDtvnfQJN3SlVyrCHQeHGCHXe+cIUTxAIA56OWhLZcHbDMKm Ip5XXS9EBZ2mA== Received: from oxappi-hoa-47o.email.comcast.net ([96.116.227.22]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by resomta-c2p-555954.sys.comcast.net with ESMTPS id zkn6rMKy9TpgQzkn6rfWPN; Wed, 24 Apr 2024 22:08:36 +0000 Date: Wed, 24 Apr 2024 15:08:36 -0700 (PDT) From: TIM CHILD To: pgsql-novice@lists.postgresql.org Message-ID: <1373508019.2230010.1713996516114@connect.xfinity.com> In-Reply-To: References: Subject: Re: Hello, novice Postgres user, seg fault investigation MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2230009_891574826.1713996516067" X-Priority: 3 Importance: Normal X-Mailer: Open-Xchange Mailer v7.10.6-Rev55 X-Originating-IP: ::ffff:24.6.235.244 X-Originating-Client: open-xchange-appsuite X-CMAE-Envelope: MS4xfHHJzvXxhiYU0lkjxvifH154aekcJx3zcmHLnXjnnds9gpl3xdQGAlps3CX723UCJW5VERydfP7l/+VVay0r9vYhroj7iufU1ZXNQkdnlPc55QZ3OShT nRAHGyrH++hZlX/s2Bf56mgMmcXzDpXr+eK7dxpOBFmIFm778VAXKBGzaAbJvn86bYeio7HD0yN0K/sAAN5LYriRrS2UKclJPudvRe1N1+wdbIPBN5S8WHXR List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2230009_891574826.1713996516067 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit James, One of the effects of VACUUM is to update table statistics. This provides information that allows query planner to better optimize your queries. So assuming there is residual data already in the table before writing to it, the VACUUM that updated statistics may of changed your query plans. While the possibility of changed query plans in no way explains the seg fault. It may explain the change in behavior the pre/post VACUUM. In general without seeing more information regarding table structure, queries, table indices, query plans (pre/post) my answer is pure speculation. It would also be beneficial to know what platform and version of Postgres, PostgREST and PostGIS you are running? -Tim > On 04/24/2024 2:26 PM PDT James Creasy wrote: > > > Hi, > > Trying to figure out which list to post which types of questions to. New to Postgres and the community so I thought I'd post an introduction. > > We're also using the PostGIS extension for 3D data, PostgREST for the API, socketio for push notifications. Deployed on AWS via Docker. We analyze 3D data with incremental changes over year+ long time periods. > > Now the interesting part, we're getting a seg fault which goes away when we run VACUUM on the table before writing to it, which is perplexing as the table can be newly created and contains a few hundred rows. How could the db get into a fatal state so quickly? > > We are setting up Postgres for debugging and see where we go from there. Looks like gdb and valgrind are still the tools of choice. Lots to learn, and that's where we are currently. > > James > Bay Area, U.S.A > ------=_Part_2230009_891574826.1713996516067 MIME-Version: 1.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
James,
 
One of the effects of VACUUM is to update  table statistics. This provides information that  allows  query planner to better optimize your queries.  So assuming there is residual data already in the table before writing to it,  the  VACUUM that updated statistics may of changed your query plans. 
 
While the possibility of  changed query plans in no way explains the seg fault.  It may explain the change in behavior   the pre/post VACUUM.
 
In general without seeing more information regarding table structure, queries, table  indices,  query plans (pre/post) my answer is pure speculation.  It would also be beneficial to know what platform and version of Postgres, PostgREST and PostGIS you are running?
 
-Tim
On 04/24/2024 2:26 PM PDT James Creasy <james@buildtrue.io> wrote:
 
 
Hi,
 
Trying to figure out which list to post which types of questions to. New to Postgres and the community so I thought I'd post an introduction. 
 
We're also using the PostGIS extension for 3D data, PostgREST for the API, socketio for push notifications. Deployed on AWS via Docker. We analyze 3D data with incremental changes over year+ long time periods.  
 
Now the interesting part, we're getting a seg fault which goes away when we run VACUUM on the table before writing to it, which is perplexing as the table can be newly created and contains a few hundred rows. How could the db get into a fatal state so quickly? 
 
We are setting up Postgres for debugging and see where we go from there. Looks like gdb and valgrind are still the tools of choice. Lots to learn, and that's where we are currently. 
 
James 
Bay Area, U.S.A
------=_Part_2230009_891574826.1713996516067--