Subject: Re: backend crash on DELETE, reproducible locally
To: pgsql-general@lists.postgresql.org
References: <20181101160007.GJ2954@hermes.hilbert.loc>
 <20181101152723.zq2dtcgntmcmr4br@alvherre.pgsql>
 <18072.1541087036@sss.pgh.pa.us> <32192.1541217418@sss.pgh.pa.us>
 <4kp.XAMr.3k5V4ORoB1t.1RtVIt@seznam.cz>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <ff3b1482-55f5-48c7-26b9-8006b4524221@gmail.com>
Date: Sat, 3 Nov 2018 15:06:02 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <4kp.XAMr.3k5V4ORoB1t.1RtVIt@seznam.cz>
Content-Type: multipart/alternative;
 boundary="------------E5C29B0472EF72867B9286B9"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------E5C29B0472EF72867B9286B9
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 11/03/2018 02:19 PM, obouda@email.cz wrote:
> Hello,
>
> we reached the exactly same problem after upgrading to PostgreSQL 11 - the 
> server crashed on a DELETE statement with a trigger.We also observed an 
> AFTER DELETE trigger receiving NULL values in OLD. Now I see the problem 
> seems to be solved (theoretically). Unfortunately, we are not able to 
> build the server with the patch, so we cannot confirm that. However, when 
> we just copied the database (within the same server), the same DELETE 
> executed on the copy with no problems.
>
> I would like to ask, however: could the same problemarise from an UPDATE 
> statement (also on a table with an AFTER trigger), or would that be 
> another bug (technical details below)?
> As the bug causes our production servers segfault several times a day, 
> we'd like to solve the problem as soon as possible. Do I understand it 
> correctly that if we dump and restore the database, the bug should not 
> occur (until the next ALTER TABLE ADD COLUMN - which we'll avoid until the 
> patch is released)?

You can dump that single table, truncate the table (presuming no FKs) and 
then restore it.

-- 
Angular momentum makes the world go 'round.

--------------E5C29B0472EF72867B9286B9
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 11/03/2018 02:19 PM, <a class="moz-txt-link-abbreviated" href="mailto:obouda@email.cz">obouda@email.cz</a> wrote:<br>
    <blockquote type="cite"
      cite="mid:4kp.XAMr.3k5V4ORoB1t.1RtVIt@seznam.cz">
      <meta http-equiv="content-type" content="text/html; charset=utf-8">
      Hello,
      <div><br>
      </div>
      <div>we reached the exactly same problem after upgrading to
        PostgreSQL 11 - the server crashed on a DELETE statement with a
        trigger.<span style="background-color:transparent"> </span><span
          style="background-color:transparent">We also observed an AFTER
          DELETE trigger receiving NULL values in OLD.</span><span
          style="background-color:transparent"> Now I see the problem
          seems to be solved (theoretically). Unfortunately, we are not
          able to build the server with the patch, so we cannot confirm
          that. However, when we just copied the database (within the
          same server), the same DELETE executed on the copy with no
          problems.</span></div>
      <div><br>
      </div>
      <div>I would like to ask, however: could <span
          style="background-color:transparent">the same problem</span><span
          style="background-color:transparent"> </span><span
          style="background-color:transparent">arise from an UPDATE
          statement (also on a table with an AFTER trigger), or would
          that be another bug (technical details below)?</span></div>
      <div>As the bug causes our production servers segfault several
        times a day, we'd like to solve the problem as soon as possible.
        Do I understand it correctly that if we dump and restore the
        database, the bug should not occur (until the next ALTER TABLE
        ADD COLUMN - which we'll avoid until the patch is released)?</div>
    </blockquote>
    <br>
    You can dump that single table, truncate the table (presuming no
    FKs) and then restore it.<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------E5C29B0472EF72867B9286B9--