Обсуждение: 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.

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.

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.



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.

<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.

p { line-height: 115%; margin-bottom: 0.25cm; background: transparent }

body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Liberation Sans"; font-size:x-small }a.comment-indicator:hover + comment { background:#ffd; position:absolute; display:block; border:1px solid black; padding:0.5em; }a.comment-indicator { background:red; display:inline-block; border:1px solid black; width:0.5em; height:0.5em; }comment { display:none; }

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 Matt

Most 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 faults 

Regards

Abbas

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

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