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 1iXBpk-0004e9-6a for pgsql-bugs@arkaria.postgresql.org; Tue, 19 Nov 2019 22:18:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iXBok-0005uK-KM for pgsql-bugs@arkaria.postgresql.org; Tue, 19 Nov 2019 22:17:50 +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 1iXBok-0005uB-4Z for pgsql-bugs@lists.postgresql.org; Tue, 19 Nov 2019 22:17:50 +0000 Received: from mail-wm1-x343.google.com ([2a00:1450:4864:20::343]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iXBoc-000181-H9 for pgsql-bugs@lists.postgresql.org; Tue, 19 Nov 2019 22:17:48 +0000 Received: by mail-wm1-x343.google.com with SMTP id b17so5681021wmj.2 for ; Tue, 19 Nov 2019 14:17:42 -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=5H5cvuQbw5vCRDUgdHPmjUEVekUNVwXe0c//0HAyny0=; b=jA3h114EE8Jr7NKsPNKcjeCOe4bLjRJYCBx8BiDiD4jXy/bl5DnJyJFHCX60LD05Tc 1jaD2WMhfxwTJkhcnDmgNOdc+c5eDOu32U2mLgrRSTR0JOLuBj8oZysB1Mr+9irGXhLj w1d83+/tHrRUPrjBWIhLaPP1447ZY64j7ydgXTCF+vOTv3y/kgaWtez72EpI4o4Yn8+T /IfXd4PC5uaseYESR1OTFtAXyTA6CyEybm5LRo2tvlLkLmfy6VmWp1vUt1wFO76Dr9yB roYn5cu/7FjJeeY0h6MwDuH+xw1EiLKBy740egItETy3SL5fD7wFSojyIskEiPrnrSi2 kAIQ== 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=5H5cvuQbw5vCRDUgdHPmjUEVekUNVwXe0c//0HAyny0=; b=MD9yhKnI+utrEfyFR2FvixKAugLO/3fjIKUcpyUIlxIQNM+D8i2ZF547kDZ1DhzL0C p+uE7RYGVKNNmJbCRCahILB4vjba2ME1yMf1GtrX3N6dHEpBVLgrgsnLAZjzoZFntFW8 oriiSJer74AacCAj6VQHSv2LNzmvhw9pin5kG5gI//TgxhXFQ5ZQKM9oFiu0xaye1CAI tUB8J/7iv05ByZB/D2vws3sNdeXTUyyvUsB4NGDYijUmQ1vzbvVdJLFFKItEfS55sE8k EXWtxMNy09w3uYEAbjChuGJ/7cCMNY0WXVY2D7IbvAqLA6Ryt/iyIovGbtz5utMKKbbb zP5g== X-Gm-Message-State: APjAAAW39AsVnU/k/BKv0Hc6UDiLynQvblRwXQpfID79dqUnoI99f9nO 870ahMbw9DOFvZVmNyyJ3vMW0g== X-Google-Smtp-Source: APXvYqziuCRTcvz26OEqVBcJMBWfE/NQ5d0VBkFPI7ohBBJbJuNV0H7ofCvuzVvoaK7f5OtsJMyMdA== X-Received: by 2002:a1c:2e8f:: with SMTP id u137mr124659wmu.105.1574201860848; Tue, 19 Nov 2019 14:17:40 -0800 (PST) Received: from localhost (ip-86-49-253-92.net.upcbroadband.cz. [86.49.253.92]) by smtp.gmail.com with ESMTPSA id t134sm4701290wmt.24.2019.11.19.14.17.34 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 19 Nov 2019 14:17:34 -0800 (PST) Date: Tue, 19 Nov 2019 23:17:32 +0100 From: Tomas Vondra To: Andrey Salnikov Cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16125: Crash of PostgreSQL's wal sender during logical replication Message-ID: <20191119221732.pcqtxljac334zgb4@development> References: <16125-a78dafa032d53f7e@postgresql.org> <20191118205816.5wne6hxedrm7zd5p@development> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Wed, Nov 20, 2019 at 12:46:44AM +0300, Andrey Salnikov wrote: >Hi, > >> Can you create a core dump (see [1]), and print 'change' and 'txn' in >> frame #2? I wonder if some the other fields are bogus too (but it can't >> be entirely true ...), and if the transaction got serialized. >> >> You may need to install debug symbols first, BTW. > >I hope that I've understood how to extract correctly those data > >(gdb) frame 2 >#2 ReorderBufferCommit (rb=0x555713b7b788, xid=xid@entry=1667601527, commit_lsn=commit_lsn@entry=41707737345720, end_lsn=end_lsn@entry=41707737345792, commit_time=commit_time@entry=627188499326201, > origin_id=origin_id@entry=0, origin_lsn=0) at ./build/../src/backend/replication/logical/reorderbuffer.c:1585 >1585 ./build/../src/backend/replication/logical/reorderbuffer.c: No such file or directory. >(gdb) print change >$1 = (ReorderBufferChange *) 0x555713b9a108 >(gdb) print *change >$2 = {lsn = 41707737330608, action = REORDER_BUFFER_CHANGE_DELETE, origin_id = 0, data = {tp = {relnode = {spcNode = 1663, dbNode = 13018, relNode = 88964815}, clear_toast_afterwards = 1 '\001', oldtuple = 0x555713cd0d88, > newtuple = 0x0}, msg = {prefix = 0x32da0000067f , message_size = 4383932111, message = 0x555713cd0d88 "\330-\315\023WU"}, snapshot = 0x32da0000067f, > command_id = 1663, tuplecid = {node = {spcNode = 1663, dbNode = 13018, relNode = 88964815}, tid = {ip_blkid = {bi_hi = 1, bi_lo = 0}, ip_posid = 3464}, cmin = 21847, cmax = 0, combocid = 0}}, node = { > prev = 0x555713b98178, next = 0x555713b98178}} >(gdb) print txn >$3 = (ReorderBufferTXN *) 0x555713b982e8 >(gdb) print *txn >$6 = {xid = 1667601527, has_catalog_changes = 0 '\000', is_known_as_subxact = 0 '\000', toplevel_xid = 0, first_lsn = 41707730043960, final_lsn = 41707737345720, end_lsn = 41707737345792, restart_decoding_lsn = 0, > origin_id = 0, origin_lsn = 0, commit_time = 627188499326201, base_snapshot = 0x555713b90408, base_snapshot_lsn = 41707730047464, base_snapshot_node = {prev = 0x555713b7b7a0, next = 0x555713b7b7a0}, nentries = 0, > nentries_mem = 0, serialized = 0 '\000', changes = {head = {prev = 0x555713b98368, next = 0x555713b98368}}, tuplecids = {head = {prev = 0x555713b98378, next = 0x555713b98378}}, ntuplecids = 0, tuplecid_hash = 0x0, > toast_hash = 0x555714ad0fb8, subtxns = {head = {prev = 0x555713b985b0, next = 0x555713b984b8}}, nsubtxns = 4, ninvalidations = 0, invalidations = 0x0, node = {prev = 0x555713b7b790, next = 0x555713b7b790}} > Yep. thanks for this data. This seems to confirm Andres' suspicion that the change is actually a delete. It's not clear to me why we're doing this toast thing for DELETEs unconditionally. For the record, the change LSN is 25EE/D6DE33B0 and the transactions is between 25EE/D66F0438 and 25EE/D6DE6F00. > >> Do you see it on different PostgreSQL instances? Can you share the >> schema definition? > >Unfortunately I can't do it completely, but there are > List of installed extensions > Name | Version | Schema | Description >-----------+---------+------------+-------------------------------- > pgcrypto | 1.3 | public | cryptographic functions > pglogical | 2.2.1 | pglogical | PostgreSQL Logical Replication > plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language > >And each table in publication has next structure: >1 jsonb field, and few smallint, integer, bigint fields. No one table has any triggers or special constraints, only primary key and one or two btree indexes. > I think it'd be interesting to actually see the table structure - column types, primary key / replica identity. > >> Well, if you can create a reproducer, that'd be the best option, because >> then we can investigate locally instead of the ping-ping here. > >I will try to do it. > OK. > >> I'd also like to see the contents of the WAL, particularly for the XID >> triggering this issue. Please run pg_waldump and see how much data is >> there for XID 1667601527. It does commit at 25EE/D6DE6EB8, not sure >> where it starts. It may have subtransactions, so don't do just grep. > >Hm, it looks like I do not have wal with start of transaction, but I've found next records using txid and subxacts. I hope it will be useful. > >2514:rmgr: Transaction len (rec/tot): 38/ 38, tx: 0, lsn: 25EE/D66F0438, prev 25EE/D66F0410, desc: ASSIGNMENT xtop 1667601527: subxacts: 1667601528 >2518:rmgr: Heap len (rec/tot): 2063/ 2063, tx: 1667601530, lsn: 25EE/D66F11E8, prev 25EE/D66F0CB0, desc: INSERT off 4, blkref #0: rel 1663/13018/88964795 blk 3125 >2520:rmgr: Btree len (rec/tot): 64/ 64, tx: 1667601530, lsn: 25EE/D66F1A38, prev 25EE/D66F19F8, desc: INSERT_LEAF off 256, blkref #0: rel 1663/13018/88964797 blk 15840 >2522:rmgr: Heap len (rec/tot): 1341/ 9385, tx: 1667601530, lsn: 25EE/D66F2118, prev 25EE/D66F1A78, desc: INSERT off 7, blkref #0: rel 1663/13018/88964795 blk 3139 FPW >2524:rmgr: Btree len (rec/tot): 64/ 64, tx: 1667601530, lsn: 25EE/D66F54C8, prev 25EE/D66F45E0, desc: INSERT_LEAF off 257, blkref #0: rel 1663/13018/88964797 blk 15840 >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 >2527:rmgr: Btree len (rec/tot): 64/ 64, tx: 1667601530, lsn: 25EE/D66F6738, prev 25EE/D66F5B90, desc: INSERT_LEAF off 140, blkref #0: rel 1663/13018/88964798 blk 2058 >2530:rmgr: Btree len (rec/tot): 96/ 96, tx: 1667601530, lsn: 25EE/D66F7E40, prev 25EE/D66F7E10, desc: INSERT_LEAF off 28, blkref #0: rel 1663/13018/88964799 blk 5076 >2531:rmgr: Btree len (rec/tot): 80/ 80, tx: 1667601530, lsn: 25EE/D66F7EA0, prev 25EE/D66F7E40, desc: INSERT_LEAF off 88, blkref #0: rel 1663/13018/88964800 blk 3412 >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 >2533:rmgr: Heap len (rec/tot): 54/ 54, tx: 1667601530, lsn: 25EE/D66F7F28, prev 25EE/D66F7EF0, desc: DELETE off 4 , blkref #0: rel 1663/13018/88964795 blk 3125 >2534:rmgr: Heap len (rec/tot): 54/ 54, tx: 1667601530, lsn: 25EE/D66F7F60, prev 25EE/D66F7F28, desc: DELETE off 7 , blkref #0: rel 1663/13018/88964795 blk 3139 >56346:rmgr: Heap len (rec/tot): 378/ 8198, tx: 1667601537, lsn: 25EE/D6DE33B0, prev 25EE/D6DE1628, desc: DELETE off 6 KEYS_UPDATED , blkref #0: rel 1663/13018/88964815 blk 1938695 FPW 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. But this output seems a bit incomplete - there's an assignment record for subxact 1667601528, without any records. And there's no assignment for 1667601530. Can you simply post the whole WAL output for the transaction LSN range, i.e. something like pg_waldump -s 25EE/D66F0438 -e 25EE/D6DE6F00 ... Any idea what this transaction actually does, i.e. what commands it executes? Presumably it does some plpgsql with subtransactions? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services