Re: PG10 Segfault 11 on default Ubuntu 16.04 installs

Hi

The segfaults thrown when I run my application on PG10 got worse. I have
found more segfaults even when max_parallel_workers_per_gather is left
default.

I have been able to create a 6Mb PG10 dump that I can be imported into a
vigin PG10 install on Ubuntu 16.04 and I have a query that segfaults
PG10 100% (on my mashines at least).

The dataset has been wiped of sensitive data, but it still should not go
public. I will send a password by email to all PG-hackers interested.

https://oc.empirica-systeme.de/index.php/s/0XLKObTrUjRlCV7

The dump contains a
* a table with lots of columns called "basedata", 56k rows
* a mat view created as select * from basedata called "mv", 56k rows
* Lots of btree indexes on most of the mv-colums

I do the following on my laptop running latest Ubuntu 16.04 with the
PG-APT-Repository:

#!PURGING ALL PG STUFF HERE TO GET A CLEAN START!!
sudo apt-get purge postgresql-9.6 postgresql-10
postgresql-10-postgis-2.4-scripts postgresql-10-postgis-2.4
sudo rm /etc/postgresql -rf

# Installing 10.0-1.pgdg16.04+1
sudo apt install postgresql-10
sudo su postgres
dropdb analyst
createdb analyst

SELECT count(1) FROM mv WHERE
((nachfrageart IN (1)) AND (oadr_gkz IN (6611000) OR oadr_kkz IN
(3152,5111,5113,5158,5162,5314,5315,5362,5378,5382,5515,5711,6411,6412,6413,6414,6431,6432,6433,6434,6435,6436,6438,6439,6440,6531,6532,6534,6535,6631,6632,6633,6634,6635,6636,7315,8125,8215,8221,8222,9663))
AND (objekttyp_grob IN (1,2)) AND (startdate>='2012-01-01' OR enddate IS
NULL OR enddate>='2012-01-01'))
OR
((nachfrageart IN (0)) AND (nutzungsart IN (0)) AND (oadr_gkz IN
(6611000,8121000,8212000) OR oadr_kkz IN
(3152,5111,5113,5158,5162,5314,5315,5362,5378,5382,5515,5711,6411,6412,6413,6414,6431,6432,6433,6434,6435,6436,6438,6439,6440,6531,6532,6534,6631,6633,7315,8125,8221,8222,9663))
AND (objekttyp_grob IN (1,2,3)) AND (startdate>='2012-01-01' OR enddate
IS NULL OR enddate>='2012-01-01'));

100% of the times segfault when running above query. Tested on thrwew
Ubuntu 16.04 servers and one Ubuntu 16.04 Desktop.

Where data comes from: The data is created on a PG9.6 mashine daily,
dumped, and imported into PG10. The whole dataflow is stable with PG9.6.
I have seen the problem with every fresh dataset.

I hope this finally makes the bug reproducable to you. If it does not
segfault on your mashine, please try to increase
max_parallel_workers_per_gather to 5.

I am very sorry that I didn't test PG10 earlier when it was beta. I
guess the current bughunt makes it more likely that I will test PG11
beta with my application. Promised!

Greetings,
Steve

Am 25.10.2017 um 22:41 schrieb Tom Lane:
> Stefan Tzeggai <tzeggai(at)empirica-systeme(dot)de> writes:
>> I also tried to get a sensible stack trace. I attached 9 gdb to all
>> postgres-pids and when I triggered the crash, two of the gdb had some
>> output and produced something on 'bt'. Attached..
>
> Those look like normal operation --- SIGUSR1 isn't a crash condition,
> it's what PG normally uses to wake up a sleeping process. If you
> want to attach gdb before provoking the crash, you need to tell it
> to ignore SIGUSR1 (I think "handle SIGUSR1 pass nostop noprint"
> is the right incantation).
>
> It might be easier to enable core files ("ulimit -c unlimited" before
> starting the postmaster) and then gdb the core files.
>
>> If I would be able to dump the relevant data from my db and I would be
>> able to reproduce the crash with it on a fresh PG10 install - Would
>> anyone have time to look at it? I guess its would no more than 50Mb...
>
> Sure, I or somebody else would look at it.
>
> regards, tom lane
>
>

--
Stefan Tzeggai