Subject: Re: backend crash on DELETE, reproducible locally
To: Alvaro Herrera <alvherre@2ndquadrant.com>,
 =?UTF-8?Q?Ond=c5=99ej_Bouda?= <bouda@edookit.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general@lists.postgresql.org,
 Karsten Hilbert <Karsten.Hilbert@gmx.net>
References: <20181106185255.776mstcyehnc63ty@alvherre.pgsql>
From: =?UTF-8?Q?Ond=c5=99ej_Bouda?= <obouda@email.cz>
Message-ID: <8e260d7c-13e8-b398-14dd-2e079ff7e833@email.cz>
Date: Tue, 6 Nov 2018 20:37:58 +0100
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <20181106185255.776mstcyehnc63ty@alvherre.pgsql>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: cs
Content-Transfer-Encoding: 8bit
Precedence: bulk

 > Hmm, this one smells like c203d6cf81b4 -- haven't seen any fixes for
 > that one.  Can you share more details on this?  I think the failing
 > update is on table with oid=557732818, but I might be wrong.

That's exactly the table, public.schedulecard.
We issue an UPDATE changing some of its columns. E.g.,

UPDATE public.schedulecard SET ext_ident=null, 
rotates=false,period_num=1,period_day=2 WHERE id=3817

lets the server crash. See the main log:

2018-11-06 17:29:40.031 CET [30202] LOG:  server process (PID 29879) was 
terminated by signal 11: Segmentation fault
2018-11-06 17:29:40.031 CET [30202] DETAIL:  Failed process was running: 
UPDATE public.schedulecard SET ext_ident=null, 
rotates=false,period_num=1,period_day=2 WHERE id=3817;
         select * from schedulecard where id = 3817
2018-11-06 17:29:40.031 CET [30202] LOG:  terminating any other active 
server processes

The query is reproducible - it always lets the server segfault. It 
crashes on multiple rows on that table -- actually, I haven't found any 
non-failing row yet.

I thought triggers should be suspected. However, even when all the three 
triggers have been disabled (ALTER TABLE DISABLE TRIGGER), the UPDATE 
crashed the server.

What else could I try?

Regards,
Ondřej Bouda


Dne 6.11.2018 v 19:52 Alvaro Herrera napsal(a):
> On 2018-Nov-06, Ondřej Bouda wrote:
> 
>> So we dumped and restored all our databases. After that, the crash on DELETE
>> never occurred (before, it was several times a day). However, the crash on
>> UPDATE still occurs on specific rows. We are quite certain no ALTER TABLE
>> statement was executed on the table after the restore.
>> There are two AFTER INSERT OR UPDATE constraint triggers and one BEFORE
>> INSERT OR UPDATE trigger on the table, all of which are implemented in
>> plpgsql. Multiple physical servers, on separate databases with identical
>> schema, crash on the same type of UPDATE query (different just in concrete
>> values to be updated). The same code worked perfectly on 10.x.
>>
>> See the attached backtrace below. Can we do something else to catch the bug?
>> Or can we hope for this bug to be already fixed and released in the upcoming
>> version?
> 
> Hmm, this one smells like c203d6cf81b4 -- haven't seen any fixes for
> that one.  Can you share more details on this?  I think the failing
> update is on table with oid=557732818, but I might be wrong.
>