Обсуждение: Segmentation fault when running queries in sequence
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.
Вложения
On 23 Sep 2023, at 2:53 PM, Matt Gibbins <matt_gibbins@fastmail.com.au> wrote:
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";
doneThe '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.
<messages.txt><postgresql-15-main-segfault.log>
Matt Gibbins <matt_gibbins@fastmail.com.au> writes: > Attached is the STDOUT messages showing the progress of the the process > to segmentation fault. Not a lot of information there. Can you get a stack trace from the crash? https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend Also, if you're not on the latest minor version of PG 15, first update and see if that resolves the problem. regards, tom lane
Matt Gibbins <matt_gibbins@fastmail.com.au> writes:Attached is the STDOUT messages showing the progress of the the process to segmentation fault.Not a lot of information there. Can you get a stack trace from the crash? https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend Also, if you're not on the latest minor version of PG 15, first update and see if that resolves the problem. regards, tom lane
Tom
Thank you.
This is the extent of information I am able to obtain at the moment.
FYI the system is Linux canberramjg.ddns.net 6.1.0-12-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.52-1 (2023-09-07) x86_64 GNU/Linux
The Debian version Debian GNU/Linux 12 (bookworm)
The postgresql version installed is 15.4-2.pgdg120+1 which is the latest from the distro.
Have enabled stack trace for postgresql. Noticed an interesting problem with running the stack trace.
Of note my process is driven by remote execution of SQL queries via SSH tunnel.
When I launch gdb for stack trace on the server the process hangs.
Regards
Matt.
Matt Gibbins <matt_gibbins@fastmail.com.au> writes: > When I launch gdb for stack trace on the server the process hangs. If you mean you're attaching gdb to the active server process, yes that would stop execution. You could just say "continue" and wait for the segfault to occur. regards, tom lane
He MattMost of time segment fault is because of broken index, reindex the table (or if you can whole db) it might help get rid of segment faultsRegardsAbbas
Abbas
Thanks for the tip.
Unfortunately reindexing the database did not resolve the issue.
Matt.
Matt Gibbins <matt_gibbins@fastmail.com.au> writes:When I launch gdb for stack trace on the server the process hangs.If you mean you're attaching gdb to the active server process, yes that would stop execution. You could just say "continue" and wait for the segfault to occur. regards, tom lane
Tom
Thanks for that
I launched gdb on the server with sudo gdp -p <port>.
Launched the client on the remote connection then executed continue in the gdb prompt on the server.
Initial response
Continuing. Program received signal SIGUSR1, User defined signal 1. |
I executed continue and the gdb reported '[Detaching after fork from child process 2790431]' for several sequences and the remote process then crashed.
No further information available other than 'Detaching after fork...'.
Regards
Matt.
Matt Gibbins <matt_gibbins@fastmail.com.au> writes: > I executed continue and the gdb reported '[Detaching after fork from > child process 2790431]' for several sequences and the remote process > then crashed. I think this means that you attached to the parent postmaster process, not to the per-session child process where the crash will occur. On the whole, attaching to a live server process is kind of the hard way to do this anyhow. I'd recommend enabling saving of core dump files and then gdb'ing the core file. That avoids needing to identify in advance the process that is going to crash. regards, tom lane
On 24/9/23 10:54, Tom Lane wrote: > Matt Gibbins <matt_gibbins@fastmail.com.au> writes: >> I executed continue and the gdb reported '[Detaching after fork from >> child process 2790431]' for several sequences and the remote process >> then crashed. > I think this means that you attached to the parent postmaster > process, not to the per-session child process where the crash > will occur. > > On the whole, attaching to a live server process is kind of the > hard way to do this anyhow. I'd recommend enabling saving of > core dump files and then gdb'ing the core file. That avoids > needing to identify in advance the process that is going to crash. > > regards, tom lane Tom Thank you for your patience. I have now created a core dump of the process. It is approximately 4G in size. Rather large for an email. As I am not familiar with core dump processing I require some further guidance. Matt.
Matt Gibbins <matt_gibbins@fastmail.com.au> writes: > I have now created a core dump of the process. It is approximately 4G in > size. > Rather large for an email. It would be of no value anyway except on a system exactly like yours. > As I am not familiar with core dump processing I require some further > guidance. The details are in the wiki page I pointed you to before. regards, tom lane