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 1iZhnj-0007uq-UL for pgsql-bugs@arkaria.postgresql.org; Tue, 26 Nov 2019 20:51:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iZhng-00048p-Nk for pgsql-bugs@arkaria.postgresql.org; Tue, 26 Nov 2019 20:51:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iZhng-00048i-EL for pgsql-bugs@lists.postgresql.org; Tue, 26 Nov 2019 20:51:08 +0000 Received: from mail-wm1-x344.google.com ([2a00:1450:4864:20::344]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iZhnb-0002XU-IV for pgsql-bugs@lists.postgresql.org; Tue, 26 Nov 2019 20:51:06 +0000 Received: by mail-wm1-x344.google.com with SMTP id g206so4732926wme.1 for ; Tue, 26 Nov 2019 12:51:03 -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:content-transfer-encoding:in-reply-to; bh=3oDJOT+hFQaaHp9yFjvDbOc4OXDwJjdRApGmJ7U3QFY=; b=Z7vPqe+R/3Ymw7l7zSAimMWnbvUQjrkMPVxPOW9UQK50dRTFyD9oLP2/WQJCY/dbrk eEtUa3/k6hfFTOxdPlxb/5XMDIEwcd3IvRmrDh/HqrFf8Z3HUoPWRh5JjndhOURWBKOV 4FQocqjpIHeU7gWi8aVSy3XfVAy9mZLmMsxMRpSWiacAp882u/1SE4Mm/5loFmdrpZVp tqdJedImsxt8Q7haCSye5XbcZlCntP7ywWVkHdXOf26Su3KqcTfWvu63IqUMoAxV0gi3 JRzjv2fA9BUUYus2aXtyv9mSUBWzdcIXYqm3QAuxeyX0/0DQLhD1qSgrOj17V8kmItnH r6og== 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:content-transfer-encoding :in-reply-to; bh=3oDJOT+hFQaaHp9yFjvDbOc4OXDwJjdRApGmJ7U3QFY=; b=LiBax5gPQCnwWTtRuNuMeGLR1HAqnUWwMyRpvIky9qZ3WSRDqLpVtA1EfUxL1LITUm kIHK6VxgXbv8QYStaLAy8Q6QUAQNFdhu5wHxoX5b9b1mSDRqZ1wkL0uEI2Y5osVenQuE jlG6Aqs+fjLbJn8a9ef/6RZLfRD+Ui37vIVcxLAlqXfISBO5kNXCdYuGdN0KXP3CVc2X 9a02eguz8DUJ6kWio4gOBn5rXga0s9UtPS17r8pbx4PnV+ZuRFTIl38Aw5yWY/qfLeos wo+V0RAHBilhRs2oRvayf1hM1Y75PNv9nXb3ZSfkkqgl7vsroNKDZBZ2xcNQrP67Nbg7 sIgA== X-Gm-Message-State: APjAAAXZ/EgNOeAJf0LenyOqPQXbWZyssV6ZBIClvZWYeL3ayAvWVhz4 VyhWmILBDaVLOAV2DI+Im6+6/w== X-Google-Smtp-Source: APXvYqyVbsVGLJDqX7Nvv6EgFiw+cYYCwlO/vrncDgkwze5c2Ffq4wm24A/DnHxtCJh3newq2j6CsQ== X-Received: by 2002:a05:600c:2249:: with SMTP id a9mr885191wmm.117.1574801461176; Tue, 26 Nov 2019 12:51:01 -0800 (PST) Received: from localhost (ip-86-49-253-92.net.upcbroadband.cz. [86.49.253.92]) by smtp.gmail.com with ESMTPSA id f140sm4702442wme.21.2019.11.26.12.51.00 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 26 Nov 2019 12:51:00 -0800 (PST) Date: Tue, 26 Nov 2019 21:50:58 +0100 From: Tomas Vondra To: Andrey Salnikov Cc: Alvaro Herrera , Andres Freund , pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16125: Crash of PostgreSQL's wal sender during logical replication Message-ID: <20191126205058.d5cny7brrqyqm4et@development> References: <16125-a78dafa032d53f7e@postgresql.org> <20191118205816.5wne6hxedrm7zd5p@development> <20191119221732.pcqtxljac334zgb4@development> <20191119232849.qhtuhq4udrqrz4he@alap3.anarazel.de> <20191126171054.GB4161@alvherre.pgsql> <20191126182752.byojog7xws2or73x@development> <44842A4A-2A43-43C3-933C-BBCC48B4CBBF@dataegret.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <44842A4A-2A43-43C3-933C-BBCC48B4CBBF@dataegret.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Tue, Nov 26, 2019 at 11:35:51PM +0300, Andrey Salnikov wrote: >Hi, I’m sorry for late response. > >> 26 нояб. 2019 г., в 21:27, Tomas Vondra написал(а): >> >> >> I think having more information about the structure (tables, indexes, >> mapping for relfilenodes) and a better idea what the transaction is >> doing, would be helpful. > >Here is information about tables > > filenode | relation >----------+--------------------------- > 88964815 | table1 > 88964795 | pg_toast.pg_toast_4029268 (toast table2) > 88964792 | table2 >(1 row) > > Table "public.table1" > Column | Type | Collation | Nullable | Default | Storage | Stats target | Description >------------+----------+-----------+----------+---------+----------+--------------+------------- > column1 | jsonb | | | | extended | | > column3 | smallint | | not null | | plain | | > column4 | integer | | not null | | plain | | > column5 | integer | | not null | | plain | | > column6 | integer | | not null | | plain | | > column7 | smallint | | not null | | plain | | >Indexes: > "table1_pkey" PRIMARY KEY, btree (column6, column7, column5, column4, column3) WITH (fillfactor='70') > "table1_index1" btree (column7, column5, column4) WHERE column7 = ? OR column7 = ? OR column7 = ? >Publications: > "pub1" >Replica Identity: FULL >Options: autovacuum_vacuum_scale_factor=0.0, autovacuum_vacuum_threshold=1000, autovacuum_analyze_scale_factor=0.0, autovacuum_analyze_threshold=1000, autovacuum_enabled=true, fillfactor=100 > > Table "public.table2" > Column | Type | Collation | Nullable | Default | Storage | Stats target | Description >--------------+-----------------------+-----------+----------+---------+----------+--------------+------------- > column1 | integer | | not null | | plain | | > column2 | jsonb | | | | extended | | > column3 | jsonb | | | | extended | | > column5 | bigint | | | | plain | | > column6 | double precision | | | | plain | | > column7 | character varying(32) | | | | extended | | > column8 | bigint | | | | plain | | > column10 | smallint | | | | plain | | > column11 | bigint | | | | plain | | > column12 | bigint | | | | plain | | > column13 | integer | | | | plain | | > column14 | bigint | | | | plain | | >Indexes: > "table2_pkey" PRIMARY KEY, btree (column1) > "table2_index1" btree (column1, column14, column12) WITH (fillfactor='90') > "table2_index2" btree (column11, column14, column12, column8, column1) WITH (fillfactor='50') >Publications: > "pub1" >Replica Identity: FULL >Options: autovacuum_vacuum_scale_factor=0.0, autovacuum_vacuum_threshold=1000, autovacuum_analyze_scale_factor=0.0, autovacuum_analyze_threshold=1000, autovacuum_enabled=true, fillfactor=100 > > attrelid | attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attidentity | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions >--------------+----------+------------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+-------------+--------------+------------+-------------+--------------+--------+------------+--------------- > table1 | 4029244 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | column1 | 3802 | -1 | -1 | 1 | 0 | -1 | -1 | f | x | i | f | f | | f | t | 0 | 0 | | | > table1 | 4029244 | ........pg.dropped.2........ | 0 | 0 | -1 | 2 | 0 | -1 | -1 | f | x | i | f | f | | t | t | 0 | 0 | | | > table1 | 4029244 | column3 | 21 | -1 | 2 | 3 | 0 | -1 | -1 | t | p | s | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | column4 | 23 | -1 | 4 | 4 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | column5 | 23 | -1 | 4 | 5 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | column6 | 23 | -1 | 4 | 6 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table1 | 4029244 | column7 | 21 | -1 | 2 | 7 | 0 | -1 | -1 | t | p | s | t | f | | f | t | 0 | 0 | | | > table2 | 4029268 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table2 | 4029268 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table2 | 4029268 | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table2 | 4029268 | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table2 | 4029268 | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table2 | 4029268 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t | f | | f | t | 0 | 0 | | | > table2 | 4029268 | column1 | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | t | f | | f | t | 0 | 0 | | | > table2 | 4029268 | column2 | 3802 | -1 | -1 | 2 | 0 | -1 | -1 | f | x | i | f | f | | f | t | 0 | 0 | | | > table2 | 4029268 | column3 | 3802 | -1 | -1 | 3 | 0 | -1 | -1 | f | x | i | f | f | | f | t | 0 | 0 | | | > table2 | 4029268 | ........pg.dropped.4........ | 0 | 0 | -1 | 4 | 0 | -1 | -1 | f | x | i | f | f | | t | t | 0 | 0 | | | > table2 | 4029268 | column5 | 20 | -1 | 8 | 5 | 0 | -1 | -1 | t | p | d | f | f | | f | t | 0 | 0 | | | > table2 | 4029268 | column6 | 701 | -1 | 8 | 6 | 0 | -1 | -1 | t | p | d | f | f | | f | t | 0 | 0 | | | > table2 | 4029268 | column7 | 1043 | -1 | -1 | 7 | 0 | -1 | 36 | f | x | i | f | f | | f | t | 0 | 100 | | | > table2 | 4029268 | column8 | 20 | -1 | 8 | 8 | 0 | -1 | -1 | t | p | d | f | f | | f | t | 0 | 0 | | | > table2 | 4029268 | ........pg.dropped.9........ | 0 | 0 | 4 | 9 | 0 | -1 | -1 | t | p | i | f | f | | t | t | 0 | 0 | | | > table2 | 4029268 | column10 | 21 | -1 | 2 | 10 | 0 | -1 | -1 | t | p | s | f | f | | f | t | 0 | 0 | | | > table2 | 4029268 | column11 | 20 | -1 | 8 | 11 | 0 | -1 | -1 | t | p | d | f | f | | f | t | 0 | 0 | | | > table2 | 4029268 | column12 | 20 | -1 | 8 | 12 | 0 | -1 | -1 | t | p | d | f | f | | f | t | 0 | 0 | | | > table2 | 4029268 | column13 | 23 | -1 | 4 | 13 | 0 | -1 | -1 | t | p | i | f | f | | f | t | 0 | 0 | | | > table2 | 4029268 | column14 | 20 | -1 | 8 | 14 | 0 | -1 | -1 | t | p | d | f | f | | f | t | 0 | 0 | | | > >And extracted information from wal file by pg_waldump -s 25EE/D66F0438 -e 25EE/D6DE6F00 in attached file. Can you also show how those relations map to the relfilenodes referenced by the WAL? This should do the trick, I think: SELECT relname FORM pg_class WHERE relfilenode IN (88964795, 88964797, 88964795, 88964792, 88964798, 88964799, 88964800, 88964815); Also, any idea what the transactions does? It seems it inserts 2 rows into 88964795, then one row into 88964792, and then it deletes those new records in the same subxact. And then it does a delete on 88964815 which triggers the segfault. How do you create the subtransactions? plpgsql procedure with exception blocks, savepoints? I'm trying to reproduce it and I'm not sure if those details matter. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services