Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints

Hello Dilip,

While testing the v7 patches, I am observing a crash with the below test
case.

Test case:
create tablespace tab location '<dir_path>/test_dir';
create tablespace tab1 location '<dir_path>/test_dir1';
create database test tablespace tab;
\c test
create table t( a int PRIMARY KEY,b text);
CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS 'select
array_agg(md5(g::text))::text from generate_series(1, 256) g';
insert into t values (generate_series(1,2000000), large_val());
alter table t set tablespace tab1 ;
\c postgres
create database test1 template test;
alter database test set tablespace pg_default;
alter database test set tablespace tab;
\c test1
alter table t set tablespace tab;

Logfile says:
2021-12-08 23:31:58.855 +04 [134252] PANIC: could not fsync file
"base/16386/4152": No such file or directory
2021-12-08 23:31:59.398 +04 [134251] LOG: checkpointer process (PID
134252) was terminated by signal 6: Aborted

Thanks.
--
Regards,
Neha Sharma

On Tue, Dec 7, 2021 at 12:24 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

> On Mon, Dec 6, 2021 at 7:53 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com>
> wrote:
> >
> > Thank you, Dilip for the quick response. I am okay with the changes done
> in the v7 patch.
> >
> > One last point - If we try to clone a huge database, as expected CREATE
> DATABASE emits a lot of WALs, causing a lot of intermediate checkpoints
> which seems to be affecting the performance slightly.
>
> Yeah, that is a valid point because instead of just one WAL for
> createdb we will generate WAL for each page in the database, so I
> agree that if the max_wal_size is not enough for those WALs then we
> might have to pay the cost of multiple checkpoints. However, if we
> compare it with the current mechanism then now it is a forced
> checkpoint and there is no way to avoid it whereas with the new
> approach user can set enough max_wal_size and they can avoid it. So
> in other words now the checkpoint is driven by the amount of resource
> which is true for any other operation e.g. ALTER TABLE SET TABLESPACE
> so now it is in more sync with the rest of the system, but without the
> patch, it was a special purpose forced checkpoint only for the
> createdb.
>
> --
> Regards,
> Dilip Kumar
> EnterpriseDB: http://www.enterprisedb.com
>
>
>