"clearing" a relation file !

Hi,

Here is the execution of a script for a cluster where checksums are enabled.
The script could be more simple. However, I simplified it compared to my
original script.

This is version and platform information (System is CentOS Stream release 8)

cours=# select version();

version

----------------------------------------------------------------------------
----------------------------

PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514
(Red Hat 8.5.0-4), 64-bit

(1 ligne)

First the creation of the table:

\set ECHO all

\set jours 1000

drop table if exists test cascade;

DROP TABLE

create table test

(

seq bigint,

date_op timestamp,

valeur real

);

CREATE TABLE

select setseed(0.5);

setseed

---------

(1 ligne)

insert into test (seq, date_op, valeur)

select

seq,

current_timestamp - interval :'jours' day + make_interval(mins => seq)
date_op,

100 * random() valeur

from generate_series(0, :jours * 24 * 60 - 1) seq;

INSERT 0 1440000

alter table test

add constraint pk_test

primary key (seq);

ALTER TABLE

Now we will corrupt storage file of table test using ‘dd’

\set ECHO all

show data_checksums;

data_checksums

----------------

on

(1 ligne)

select

current_setting('data_directory') ||

'/' ||

pg_relation_filepath('test') file_path;

file_path

-----------------------------------------------------------

/pgsql/pgdata/pg_tblspc/16384/PG_14_202107181/16386/16444

(1 ligne)

\gset

\setenv file_path :file_path

\! dd if=/dev/zero of=$file_path bs=1024 count=100

100+0 enregistrements lus

100+0 enregistrements écrits

102400 octets (102 kB, 100 KiB) copiés, 0,000910942 s, 112 MB/s

select count(*) from test;

count

-------

1884

(1 ligne)

Well, I think this is cache in action. If server is restarted, then the
reply is 0. That is despite the deliberate choice of non 8KB multiplier
size. PostgreSQL doesn’t complain during ordinary operations. Complaints
come when we try to play with checksums using pg_checksums.

select count(*) from test;

count

-------

0

(1 ligne)

I consider this is a serious problem.

Best regards

Michel SALAIS