Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ia5yL-0003gZ-AN for pgsql-bugs@arkaria.postgresql.org; Wed, 27 Nov 2019 22:39:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ia5yJ-0005dr-Kx for pgsql-bugs@arkaria.postgresql.org; Wed, 27 Nov 2019 22:39:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ia5yJ-0005dd-3x for pgsql-bugs@lists.postgresql.org; Wed, 27 Nov 2019 22:39:43 +0000 Received: from mail-wm1-x344.google.com ([2a00:1450:4864:20::344]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ia5yF-0001iE-Jk for pgsql-bugs@lists.postgresql.org; Wed, 27 Nov 2019 22:39:41 +0000 Received: by mail-wm1-x344.google.com with SMTP id s14so2779707wmh.4 for ; Wed, 27 Nov 2019 14:39:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=GSWcH6anOgwg758eBbHCH9dXJZUbbLLtpCxoB6w3D3o=; b=SB2GNJgg5QeOzEqMkHBrf6+/KDyc6jU5/G19/i0ehQ5kiYohs06QtaNKirOvHrUi0A ERdole85E7V6d9m6wEZxYhmXoReRglw/W84gI0UZq85tlTbJwCzD56RE2C/Jus5SHowj Kckn+r8mqtTbMnSua1ERMFYuqPeNWo7Luq22qZ3v1jIgaiFuD93zyZrF2P45Pr6pEgab wTrggv+X0XHDL9Rf/PdujNN0Kc6XzMRNn0LbuoovzlOxM5/dkaW3OhHGeXFAceNE4udt v5DyZ/mIE6nOFxomhkGf9GO3mytVFSGN6jF+H4/5mOdSlTxgkdh4OQgaGevhScuPZYce wSFA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=GSWcH6anOgwg758eBbHCH9dXJZUbbLLtpCxoB6w3D3o=; b=H3vkGTrHceL9DV8W2aBp3jb2k19QYFEiWeN1mSKaZtpoIOkTUJv1KrM/DwFnGUT7hU YzYC9G68H/s71PYTCFpw8uS2jMfmsBrROdWgeyg4Kmd9Ghhpz13zsp3OEGQ6l5X3F+fb QVsAZ+klxEXAnKuoUe4iDPw852fkvH50YV8DSNnvY99E3KcaocWxQxj1aYqQyRYm9U8A D/8tePakdp5qKH7Uj18R7qzZHHux18Au0cdbvMdPA/sb+qQO1EqTzuGdZYCoLE2vPVbA l7fqwr4iLBkuofhE5vVjQKxpl0C/KeRV+RgDhA8PNfpOKgHQlYs+mZKPzxCif8mE5SYA mAig== X-Gm-Message-State: APjAAAVbQQ03ay0exR+eVRulM5q/s+BH5pm6rp6E2USGkqHw/re7m1ug HMPROuBqB+CDLWJ09MPm64rEaw== X-Google-Smtp-Source: APXvYqwcXz23GKCUsnDwQqidQH8DGShbqOD1NtjFbA4G7frHhdxlXQn5bButomtDkyvxxlSCDQCReQ== X-Received: by 2002:a05:600c:22d1:: with SMTP id 17mr6884846wmg.31.1574894376553; Wed, 27 Nov 2019 14:39:36 -0800 (PST) Received: from localhost (ip-86-49-253-92.net.upcbroadband.cz. [86.49.253.92]) by smtp.gmail.com with ESMTPSA id x6sm21267348wrw.34.2019.11.27.14.39.34 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 27 Nov 2019 14:39:35 -0800 (PST) Date: Wed, 27 Nov 2019 23:39:33 +0100 From: Tomas Vondra To: Alvaro Herrera Cc: Andres Freund , Andrey Salnikov , pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16125: Crash of PostgreSQL's wal sender during logical replication Message-ID: <20191127223933.ohe3wtdsssvgxdh5@development> References: <20191119232849.qhtuhq4udrqrz4he@alap3.anarazel.de> <20191127175935.GA20945@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: <20191127175935.GA20945@alvherre.pgsql> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Wed, Nov 27, 2019 at 02:59:35PM -0300, Alvaro Herrera wrote: >On 2019-Nov-19, Andres Freund wrote: > >> > > 2525:rmgr: Heap len (rec/tot): 1671/ 1671, tx: 1667601530, lsn: 25EE/D66F5508, prev 25EE/D66F54C8, desc: INSERT off 2, blkref #0: rel 1663/13018/88964792 blk 18475 >> > > 2532:rmgr: Heap len (rec/tot): 54/ 54, tx: 1667601530, lsn: 25EE/D66F7EF0, prev 25EE/D66F7EA0, desc: DELETE off 2 , blkref #0: rel 1663/13018/88964792 blk 18475 > >> > So, this DELETE change triggers the issue. If I understand it correctly, >> > that means the transaction locked the tuple and then deleted it. But I >> > don't see any record about the lock. >> >> Hm. I don't think it necessarily means that. compute_new_xmax_infomask(), when >> called from heap_delete(), will e.g. set HEAP_KEYS_UPDATED when >> >> if (old_infomask & HEAP_XMAX_INVALID) >> >> is true. Which is the most common case. I actually find it *more* >> curious to *not* see that for a delete. I've not traced fully through >> it, yet. >> >> >> Alvaro, it's not clear to me whether the the multixact codepaths in >> compute_new_xmax_infomask() actually work as expected for deletes. > >So, what's really happening here is heap_insert(speculative) followed by >heap_abort_speculative(). That seems to explain the above WAL sequence. >I *think* the speculative insertion code in reorderbuffer simply delays >the tuple insertion until it is confirmed; so this sequence would result >in nothing at all happening at ReorderBufferCommit. Unless something >has messed up the state somehow. > Hmm, so how would that transaction look like? My reading of the WAL (based on the available relfilenode info) is this: Transaction 25EE/D66F0438 ASSIGNMENT xtop 1667601527: subxacts: 1667601528 -> seems like a savepoint, probably Heap 25EE/D66F11E8 INSERT off 4, blkref #0: rel 1663/13018/88964795 blk 3125 Heap 25EE/D66F2118 INSERT off 7, blkref #0: rel 1663/13018/88964795 blk 3139 FPW Btree 25EE/D66F54C8 INSERT_LEAF off 257, blkref #0: rel 1663/13018/88964797 blk 15840 Heap 25EE/D66F5508 INSERT off 2, blkref #0: rel 1663/13018/88964792 blk 18475 Btree 25EE/D66F6738 INSERT_LEAF off 140, blkref #0: rel 1663/13018/88964798 blk 2058 Btree 25EE/D66F7E40 INSERT_LEAF off 28, blkref #0: rel 1663/13018/88964799 blk 5076 Btree 25EE/D66F7EA0 INSERT_LEAF off 88, blkref #0: rel 1663/13018/88964800 blk 3412 -> insert into TOAST table (88964795) + TOAST idx (probably 88964797) -> then an insert into table2 (88964792), and it's indexes Heap 25EE/D66F7EF0 DELETE off 2 , blkref #0: rel 1663/13018/88964792 blk 18475 Heap 25EE/D66F7F28 DELETE off 4 , blkref #0: rel 1663/13018/88964795 blk 3125 Heap 25EE/D66F7F60 DELETE off 7 , blkref #0: rel 1663/13018/88964795 blk 3139 -> now we delete the record from table2 and also it's TOAST table (how do we trigger that?) Heap 25EE/D6DE33B0 DELETE off 6 KEYS_UPDATED , blkref #0: rel 1663/13018/88964815 blk 1938695 FPW -> delete from table1 (88964815), which we haven't touched in this xact until now (this is the crash LSN) Heap 25EE/D6DE53D0 LOCK off 5: xid 1667601538: flags 0 LOCK_ONLY EXCL_LOCK , blkref #0: rel 1663/13018/88964792 blk 18460 Heap 25EE/D6DE5408 UPDATE off 5 xmax 1667601538 ; new off 3 xmax 0, blkref #0: rel 1663/13018/88964792 blk 18499, blkref #1: rel 1663/13018/88964792 blk 18460 Btree 25EE/D6DE6DC8 INSERT_LEAF off 140, blkref #0: rel 1663/13018/88964798 blk 2058 Btree 25EE/D6DE6E08 INSERT_LEAF off 28, blkref #0: rel 1663/13018/88964799 blk 5076 Btree 25EE/D6DE6E68 INSERT_LEAF off 88, blkref #0: rel 1663/13018/88964800 blk 3412 Transaction 25EE/D6DE6EB8 COMMIT 2019-11-16 03:01:39.326201 UTC; subxacts: 1667601528 1667601530 1667601537 1667601538 -> seems like a lock of a record in table2, along with update of indexes I can more or less simulate this by begin; savepoint s; insert into table2 values (1, ... jsonb ..., NULL, 1, 1, 'x', 1, 1, 1, 1, 1, 1) on conflict (column1) do update set column14 = 1; delete from table2 where column1 = 1; update table2 set column14 = 2 where column1 = 1; commit; Which however does not explain the three deletes from table2 + TOAST. But, maybe that's not the issue, because the decoding fails on the next record, which is for table1. FWIW attached is a SQL script creating tables with the same structure (without the dropped columns, but I think those are irrelevant). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services