Segmentation fault when running queries in sequence

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 

I am running postgresql through an SSH tunnel to process a collection of files on the server loading them into the database.

Specifically GTFS files

The process is as follows.

find ../Database\ management/SQL/Create\ tables/Landing -type f -name 'Bus *.sql'  | sort -n | while read -r f;
do     
echo "$f";
./Query\ to\ server "$f";
./Query\ to\ server "../Database management/SQL/Create tables/AllServices/Insert landing - bus.sql";
done

The 'Query to server' script initiates an SSL session with the following statement where postgresql_tcanalysis initiates an SSH tunnel to the database on the remote server which I have SSH connection to.

postgresql_tcanalysis < "$1"

There is no problem with the SSH connection and running individual queries.

The issue occurs when stepping through the queries.

The find statement results in 19 queries. It is necessary to have individual queries as the content of the GTFS files are not consistent with regard to fields.

Attached is the STDOUT messages showing the progress of the the process to segmentation fault.

Running the individual queries where the segfault occurs does not occur for the individual query. The segfault only occurs when doing the bulk process.

Also attached is the section from /var/log/postgresql/postgresql-15-main.log relating to the segfault occurence.

Any suggestions for identifying the cause appreciated.

Regards

Matt.



../Database management/SQL/Create tables/Landing/Bus tables_20200417.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
 ?column? 
----------
 Agency
(1 row)

DROP TABLE
CREATE TABLE
 load_tc_gtfs 
--------------
 
(1 row)

 ?column? 
----------
 Calendar
(1 row)

DROP TABLE
CREATE TABLE
 load_tc_gtfs 
--------------
 
(1 row)

    ?column?    
----------------
 Calendar_Dates
(1 row)

DROP TABLE
CREATE TABLE
 load_tc_gtfs 
--------------
 
(1 row)

 ?column? 
----------
 Routes
(1 row)

DROP TABLE
CREATE TABLE
 load_tc_gtfs 
--------------
 
(1 row)

 ?column? 
----------
 Stops
(1 row)

DROP TABLE
CREATE TABLE
 load_tc_gtfs 
--------------
 
(1 row)

  ?column?  
------------
 Stop_Times
(1 row)

DROP TABLE
CREATE TABLE
 load_tc_gtfs 
--------------
 
(1 row)

 ?column? 
----------
 Trips
(1 row)

DROP TABLE
CREATE TABLE
 load_tc_gtfs 
--------------
 
(1 row)

 ?column? 
----------
 Shapes
(1 row)

DROP TABLE
CREATE TABLE
 load_tc_gtfs 
--------------
 
(1 row)

Connection to localhost (::1) 9999 port [tcp/*] succeeded!
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
SSL SYSCALL error: EOF detected
connection to server was lost
../Database management/SQL/Create tables/Landing/Bus tables_20200701.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210219.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210610.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210823.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210930.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20211125.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20220120.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20230324.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20230717.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20200417.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20200701.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20210125.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20210408.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20210824.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20211125.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20220120.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20230324.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20230717.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  the database system is in recovery mode

2023-09-23 20:39:34.629 AEST [2667486] LOG:  server process (PID 2675514) was terminated by signal 11: Segmentation fault
2023-09-23 20:39:34.629 AEST [2667486] DETAIL:  Failed process was running: with stop_times as (select trip_id,stop_sequence from allservices.stop_times)
2023-09-23 20:39:34.629 AEST [2667486] LOG:  terminating any other active server processes
2023-09-23 20:39:34.638 AEST [2675518] nc_admin@nextcloud_db FATAL:  the database system is in recovery mode
2023-09-23 20:39:34.680 AEST [2675520] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:34.682 AEST [2667486] LOG:  all server processes terminated; reinitializing
2023-09-23 20:39:34.867 AEST [2675521] LOG:  database system was interrupted; last known up at 2023-09-23 20:35:56 AEST
2023-09-23 20:39:34.884 AEST [2675525] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:34.982 AEST [2675527] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.099 AEST [2675529] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.192 AEST [2675535] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.284 AEST [2675537] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.384 AEST [2675539] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.483 AEST [2675541] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.580 AEST [2675543] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.678 AEST [2675545] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.780 AEST [2675547] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.873 AEST [2675549] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:35.973 AEST [2675551] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:36.081 AEST [2675553] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:36.204 AEST [2675555] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:36.319 AEST [2675557] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:36.431 AEST [2675559] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:36.523 AEST [2675561] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:36.621 AEST [2675563] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:36.718 AEST [2675565] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:36.818 AEST [2675567] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:36.917 AEST [2675569] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.012 AEST [2675571] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.116 AEST [2675573] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.216 AEST [2675575] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.308 AEST [2675581] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.407 AEST [2675583] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.512 AEST [2675585] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.617 AEST [2675587] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.716 AEST [2675589] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.816 AEST [2675591] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:37.919 AEST [2675593] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:38.018 AEST [2675595] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:38.115 AEST [2675597] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:38.207 AEST [2675599] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:38.310 AEST [2675601] matthew@tcanalysis FATAL:  the database system is in recovery mode
2023-09-23 20:39:38.859 AEST [2675521] LOG:  database system was not properly shut down; automatic recovery in progress
2023-09-23 20:39:38.929 AEST [2675521] LOG:  redo starts at 1/A741D4E0
2023-09-23 20:39:39.067 AEST [2675521] LOG:  invalid record length at 1/A9F9F798: wanted 24, got 0
2023-09-23 20:39:39.067 AEST [2675521] LOG:  redo done at 1/A9F9F770 system usage: CPU: user: 0.08 s, system: 0.05 s, elapsed: 0.13 s
2023-09-23 20:39:39.414 AEST [2675522] LOG:  checkpoint starting: end-of-recovery immediate wait
2023-09-23 20:39:44.791 AEST [2675615] nc_admin@nextcloud_db FATAL:  the database system is not yet accepting connections
2023-09-23 20:39:44.791 AEST [2675615] nc_admin@nextcloud_db DETAIL:  Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.795 AEST [2675616] nc_admin@nextcloud_db FATAL:  the database system is not yet accepting connections
2023-09-23 20:39:44.795 AEST [2675616] nc_admin@nextcloud_db DETAIL:  Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.799 AEST [2675617] nc_admin@nextcloud_db FATAL:  the database system is not yet accepting connections
2023-09-23 20:39:44.799 AEST [2675617] nc_admin@nextcloud_db DETAIL:  Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.803 AEST [2675618] nc_admin@nextcloud_db FATAL:  the database system is not yet accepting connections
2023-09-23 20:39:44.803 AEST [2675618] nc_admin@nextcloud_db DETAIL:  Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.807 AEST [2675619] nc_admin@nextcloud_db FATAL:  the database system is not yet accepting connections
2023-09-23 20:39:44.807 AEST [2675619] nc_admin@nextcloud_db DETAIL:  Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.947 AEST [2675522] LOG:  checkpoint complete: wrote 7309 buffers (1.4%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.654 s, sync=4.146 s, total=5.600 s; sync files=73, longest=0.080 s, average=0.057 s; distance=44552 kB, estimate=44552 kB
2023-09-23 20:39:45.024 AEST [2667486] LOG:  database system is ready to accept connections
[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux