Re: file corruption goes undetected

This is expected behavior, not a bug. It'd be great to detect these
kinds of data corruption, but data checksums can't do that (and we don't
have other protections).

regards

On 8/6/26 16:11, Bram van der Vos wrote:
> *ISSUE*
>
> External modified datafile not detected, no error given but result produced;
>
> *REPRODUCTION*
>
> script.sh
> ---
>
> psql   <<DO_CLEAN_1
> drop database corruption;
> DO_CLEAN_1
>
> rm -rf /tmp/oscmd.sh
> rm rf /tmp/output_1.txt
> rm rf /tmp/output_2.txt
>
> psql   <<END_SCRIPT
>
> \o '/tmp/output_1.txt'
> select version();
> create database corruption;
> \c corruption
> show data_checksums;
> show ignore_checksum_failure;
> create table demo ( a serial, t timestamp with time zone);
> insert into demo (t)  values (now());
> select max(t)  from demo;
> select pg_relation_filepath('demo');
> copy (select command || setting ||'/'||file
>       from (select *  from (select 1 as my_order, 'rm -rf ' as  command,
> pg_relation_filepath('demo') as file
>           union all
>             select 2 as my_order , 'touch ' as command , 
> pg_relation_filepath('demo') as file )
>           cross join (select setting from  pg_settings where
> name='data_directory'))  order by my_order)  to '/tmp/oscmd.sh';
> END_SCRIPT
>
>
> cat /tmp/output_1.txt
> chmod 700 /tmp/oscmd.sh
> /tmp/oscmd.sh
>
> psql -d corruption  <<END_SCRIPT_2
>
> \o '/tmp/output_2.txt'
> select max(t)  from demo;
> END_SCRIPT_2
>
> cat /tmp/output_2.txt
> ---
>
> *ETC*
> Result in tmp/output_1.txt  en tmp/output_2.txt.    After file has been
> removed and recreated with touch  a NULL result is returned. The same
> happens when the has been replaced with dd -if /dev/zero of=<file> 
> bs=1024 count=64000
>
> When replacing file with bogus data (/dev/random)  an error is being
> returned.   Result when the datafile is being nullified
>
> side info: pg_backrest does recognise the file as not valid an replaces
> is during restore
>
>
> regards
>
>
> Bram
>
> --
> vrijdags afwezig
> LOGO <https://www.axisintoict.nl>
> Bram van der Vos
> bram(dot)van(dot)der(dot)vos(at)axisinto(dot)nl
> 06 127 27 547
> Albert Schweitzerlaan 10b
> 3451 EC Vleuten
> Twitter <https://twitter.com/AxisintoICT>Linkedin <https://
> nl.linkedin.com/in/bramvandervos>
>

--
Tomas Vondra