Message-ID: <c4e25cb1ce97f866915a9451b9e15fbe7672457f.camel@cybertec.at>
Subject: Re: [BUG] PostgreSQL 14.5: Data corruption in table
 tb_workstation_message (possible fstrim-related on eMMC)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?UTF-8?Q?=E7=AC=AC108=E6=AC=A1=E6=98=8E=E5=A4=A9?= <87326549@qq.com>,
 pgsql-bugs <pgsql-bugs@postgresql.org>
Date: Thu, 18 Dec 2025 09:40:56 +0100
In-Reply-To: <tencent_BA7C5BFF421FD1965E088C9E12CDD7EB6A06@qq.com>
References: <tencent_BA7C5BFF421FD1965E088C9E12CDD7EB6A06@qq.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/c4e25cb1ce97f866915a9451b9e15fbe7672457f.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-12-18 at 14:42 +0800, =E7=AC=AC108=E6=AC=A1=E6=98=8E=E5=A4=A9 =
wrote:
> Environment Information
>  * PostgreSQL Version: 14.5
> =C2=A0* OS: Debian GNU/Linux 11 (bullseye)
> =C2=A0* Architecture: aarch64 (8-core CPU)
> =C2=A0* Memory: 8GB
> =C2=A0* Storage Medium: eMMC
> Problem Description
> Data corruption occurred in the tb_workstation_message=C2=A0table (record=
 guid: ce04bd3f-232d-4c7f-9a91-540d0e581649).
> Queries targeting this record fail with:
> ERROR: invalid memory alloc request size 18446744073709551613
> Corruption details of the record:
> =C2=A0* Only the guid=C2=A0field remains valid;
> =C2=A0* All int-type fields (e.g., upload_status) are set to 0;
> =C2=A0* All timestamp-type fields (e.g., create_time) are reset to 2000-0=
1-01 00:00:00;
> =C2=A0* String-type fields (e.g., operator, content) are inaccessible (qu=
eryable only after forcing UPDATE=C2=A0to null).
> Investigation Details   1. Database Logs: No output between 2025-12-01 05=
:37:05.932 ~ 09:02:31.533 (no abnormalities found).
> =C2=A0=C2=A0=C2=A02. Dynamic Library Check: libpq.so=C2=A0had environment=
 variable misconfiguration (not pointing to PG install dir),
>       but the business app does NOT depend on this library =E2=80=94 low =
corruption probability.
> =C2=A0=C2=A0=C2=A03. eMMC Storage Analysis:
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0- Used dd=C2=A0to extract the c=
orrupted block; hex analysis shows only guid=C2=A0bytes are valid (others a=
re 0,
>          confirming physical data corruption).
> =C2=A0=C2=A0=C2=A04. fstrim Check:
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0- fstrim=C2=A0executed at 2025-=
12-01 00:27 (asynchronous, runs on disk idle); anomaly occurred after this.
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0- Reproduction attempt: Simulat=
ed data insertion + 10+ fstrim runs =E2=80=94 corruption not reproduced.
> Impact * Business failure: Query errors trigger business alerts.
> =C2=A0* Backup failure: Database backups are affected by corrupted data.
> Additional Notes * No system/app/PG restarts during the anomaly window.
> =C2=A0* No writes to tb_workstation_message=C2=A0after 2025-12-01 05:34:0=
0.
> =C2=A0* No online operations/terminals between 05:34:00 ~ 09:02:31.
> Request:
> We suspect this may relate to fstrim on eMMC (with PG 14.5) but cannot re=
produce it. Could the community
> help analyze the root cause, or guide further troubleshooting?

"fstrim" should not destroy data...

I am wondering how this problem can lead to backup failure - unless you are=
 using "pg_dump" for backups.

You probably figured out that you will have to delete the row to get rid of=
 the problem.
In addition, you should dump and restore the database to a new cluster - wh=
o knows what
other data corruption is lurking there.

I don't know what caused your problem, but I would suspect hardware failure=
.
Perhaps you should check or replace the disk.

Yours,
Laurenz Albe