pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bungina@gmail.com
Subject: BUG #18811: PANIC,XX000,"WAL contains references to invalid pages"
Date: Thu, 13 Feb 2025 12:19:18 +0000
Message-ID: <18811-dbd06bbde2609075@postgresql.org> (raw )
The following bug has been logged on the website:
Bug reference: 18811
Logged by: Polina Bungina
Email address: bungina@gmail.com
PostgreSQL version: 16.6
Operating system: Ubuntu 22.04
Description:
We have just encountered this problem the second time within a month.
Standby starts to panic after the following sequence of events:
[primary]
2025-02-12 06:22:44.160 UTC,,,3268046,,67ac3e15.31ddce,63,,2025-02-12
06:22:13 UTC,614/47144,1969937340,ERROR,57014,"canceling autovacuum
task",,,,,"while truncating relation ""data.slice_trigger_13"" to 0 blocks
automatic vacuum of table ""db1.data.slice_trigger_13""",,,,"","autovacuum
worker",,0
2025-02-12 06:22:44.160
UTC,"user_app","db1",3263558,"10.2.30.104:42868",67ac3c0c.31cc46,75,"BIND
waiting",2025-02-12 06:13:32 UTC,412/2708655,1969937332,LOG,00000,"process
3263558 acquired RowExclusiveLock on relation 35182 of database 16710 after
1184.572 ms",,,,,,"delete from ""data"".""slice_trigger"" where
""data"".""slice_trigger"".""st_id"" in ($1)",,,"PostgreSQL JDBC
Driver","client backend",,-7230379448131803312
2025-02-12 06:22:44.160
UTC,"user_app","db1",3263678,"10.2.30.104:56256",67ac3c1a.31ccbe,55,"PARSE
waiting",2025-02-12 06:13:46 UTC,427/2212958,0,LOG,00000,"process 3263678
acquired AccessShareLock on relation 35182 of database 16710 after 1042.391
ms",,,,,,"with ""first_select"" as (select * from data.slice_trigger_13
order by ""st_occurred_at"" limit $1), ""windowed_select"" as (select rank()
over (partition by ""st_unit_identifier"", ""st_reservation_identifier""
order by ""st_occurred_at"", ""st_id"") as ""row_rank"", * from
first_select) select * from windowed_select where ""row_rank"" =
$2",39,,"PostgreSQL JDBC Driver","client backend",,0
2025-02-12 06:22:44.160
UTC,"user_app","db1",3263678,"10.2.30.104:56256",67ac3c1a.31ccbe,56,"PARSE",2025-02-12
06:13:46 UTC,427/2212958,0,LOG,00000,"duration: 1042.822 ms parse
<unnamed>: with ""first_select"" as (select * from data.slice_trigger_13
order by ""st_occurred_at"" limit $1), ""windowed_select"" as (select rank()
over (partition by ""st_unit_identifier"", ""st_reservation_identifier""
order by ""st_occurred_at"", ""st_id"") as ""row_rank"", * from
first_select) select * from windowed_select where ""row_rank"" =
$2",,,,,,,,,"PostgreSQL JDBC Driver","client
backend",,-4561709464811390503
[replica]
2025-02-12 06:23:25.302 UTC,,,413151,,6790b32a.64ddf,184,,2025-01-22
08:58:18 UTC,1/0,0,WARNING,01000,"page 1 of relation base/16710/35182 is
uninitialized",,,,,"WAL redo at 1732/47969908 for Heap2/VISIBLE:
snapshotConflictHorizon: 0, flags: 0x03; blkref #0: rel 1663/16710/35182,
fork 2, blk 0 FPW; blkref #1: rel 1663/16710/35182, blk
1",,,,"","startup",,0
2025-02-12 06:23:25.302 UTC,,,413151,,6790b32a.64ddf,185,,2025-01-22
08:58:18 UTC,1/0,0,PANIC,XX000,"WAL contains references to invalid
pages",,,,,"WAL redo at 1732/47969908 for Heap2/VISIBLE:
snapshotConflictHorizon: 0, flags: 0x03; blkref #0: rel 1663/16710/35182,
fork 2, blk 0 FPW; blkref #1: rel 1663/16710/35182, blk
1",,,,"","startup",,0
2025-02-12 06:23:26.233 UTC,,,413146,,6790b32a.64dda,7,,2025-01-22 08:58:18
UTC,,0,LOG,00000,"startup process (PID 413151) was terminated by signal 6:
Aborted",,,,,,,,,"","postmaster",,0
[wal records for the problematic table around 1732/47969908]
rmgr: Heap len (rec/tot): 54/ 54, tx: 1969939280, lsn:
1732/47306DF0, prev 1732/47306DC8, desc: DELETE xmax: 1969939280, off: 1,
infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16710/35182 blk
21
rmgr: Heap len (rec/tot): 54/ 54, tx: 1969939280, lsn:
1732/47306E28, prev 1732/47306DF0, desc: DELETE xmax: 1969939280, off: 2,
infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16710/35182 blk
21
rmgr: Heap len (rec/tot): 173/ 173, tx: 1969939315, lsn:
1732/4738B258, prev 1732/4738B230, desc: INSERT off: 7, flags: 0x00, blkref
#0: rel 1663/16710/35182 blk 21
rmgr: Heap len (rec/tot): 173/ 173, tx: 1969939352, lsn:
1732/4749E4E0, prev 1732/4749E488, desc: INSERT off: 8, flags: 0x00, blkref
#0: rel 1663/16710/35182 blk 21
rmgr: Heap len (rec/tot): 173/ 173, tx: 1969939352, lsn:
1732/474BF8F0, prev 1732/474BF898, desc: INSERT off: 9, flags: 0x00, blkref
#0: rel 1663/16710/35182 blk 21
rmgr: Heap2 len (rec/tot): 64/ 186, tx: 0, lsn:
1732/47969908, prev 1732/479698C8, desc: VISIBLE snapshotConflictHorizon: 0,
flags: 0x03, blkref #0: rel 1663/16710/35182 fork vm blk 0 FPW, blkref #1:
rel 1663/16710/35182 blk 1
rmgr: Heap2 len (rec/tot): 59/ 59, tx: 0, lsn:
1732/4796A440, prev 1732/4796A3E8, desc: VISIBLE snapshotConflictHorizon: 0,
flags: 0x03, blkref #0: rel 1663/16710/35182 fork vm blk 0, blkref #1: rel
1663/16710/35182 blk 2
rmgr: Heap2 len (rec/tot): 59/ 59, tx: 0, lsn:
1732/4796A480, prev 1732/4796A440, desc: VISIBLE snapshotConflictHorizon: 0,
flags: 0x03, blkref #0: rel 1663/16710/35182 fork vm blk 0, blkref #1: rel
1663/16710/35182 blk 3
<..>
rmgr: Heap2 len (rec/tot): 59/ 59, tx: 0, lsn:
1732/4796FCC0, prev 1732/4796FC80, desc: VISIBLE snapshotConflictHorizon: 0,
flags: 0x03, blkref #0: rel 1663/16710/35182 fork vm blk 0, blkref #1: rel
1663/16710/35182 blk 25
rmgr: Standby len (rec/tot): 90/ 90, tx: 0, lsn:
1732/4797E240, prev 1732/4797E0F8, desc: INVALIDATIONS ; inval msgs:
catcache 55 catcache 54 relcache 35182
rmgr: Heap len (rec/tot): 173/ 173, tx: 1969939561, lsn:
1732/479C0688, prev 1732/479C0630, desc: INSERT+INIT off: 1, flags: 0x01,
blkref #0: rel 1663/16710/35182 blk 1
[the table structure]
\d+ data.slice_trigger
Partitioned table "data.slice_trigger"
Column | Type | Collation | Nullable
| Default | Storage |
Compression | Stats target | Description
---------------------------+--------------------------+-----------+----------+---------------------------------------------------------+----------+-------------+--------------+-------------
st_id | bigint | | not null
| nextval('data.slice_trigger_st_id_seq'::regclass) | plain |
| |
st_site | text | | not null
| | extended |
| |
st_event_type | text | | not null
| | extended |
| |
st_data_type | text | |
| | extended |
| |
st_storing_state | text | |
| | extended |
| |
st_event_id | bigint | |
| nextval('data.slice_trigger_st_event_id_seq'::regclass) | plain |
| |
st_unit_identifier | text | | not null
| | extended |
| |
st_reservation_identifier | text | |
| | extended |
| |
st_occurred_at | timestamp with time zone | | not null
| clock_timestamp() | plain |
| |
st_event_json | text | |
| | extended |
| |
st_process | text | |
| | extended |
| |
Partition key: HASH (st_site, st_unit_identifier)
Indexes:
"slice_trigger_pkey" PRIMARY KEY, btree (st_id, st_site,
st_unit_identifier)
Partitions: data.slice_trigger_0 FOR VALUES WITH (modulus 32, remainder
0),
data.slice_trigger_1 FOR VALUES WITH (modulus 32, remainder
1),
data.slice_trigger_10 FOR VALUES WITH (modulus 32, remainder
10),
data.slice_trigger_11 FOR VALUES WITH (modulus 32, remainder
11),
data.slice_trigger_12 FOR VALUES WITH (modulus 32, remainder
12),
data.slice_trigger_13 FOR VALUES WITH (modulus 32, remainder
13),
...
there are plenty of "canceling autovacuum task while truncating relation"
log entries for other partitions of this table and other partitioned tables
before and after this happening, which did not cause the same issue.
Message-ID: <18811-dbd06bbde2609075@postgresql.org>
Permalink: ../18811-dbd06bbde2609075@postgresql.org/
Also on: postgresql.org/message-id/18811-dbd06bbde2609075@postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, bungina@gmail.com
Subject: Re: BUG #18811: PANIC,XX000,"WAL contains references to invalid pages"
In-Reply-To: <18811-dbd06bbde2609075@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox