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

On Thu, Dec 9, 2021 at 7:23 PM Neha Sharma <neha(dot)sharma(at)enterprisedb(dot)com>
wrote:

> On Thu, Dec 9, 2021 at 11:12 AM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com>
> wrote:
>
>> Hi,
>>
>> The issue here is that we are trying to create a table that exists inside
>> a non-default tablespace when doing ALTER DATABASE. I think this should be
>> skipped otherwise we will come across the error like shown below:
>>
>> ashu(at)postgres=# alter database test set tablespace pg_default;
>> ERROR: 58P02: could not create file
>> "pg_tblspc/16385/PG_15_202111301/16386/16390": File exists
>>
>
> Thanks Ashutosh for the patch, the mentioned issue has been resolved with
> the patch.
>
> But I am still able to reproduce the crash consistently on top of this
> patch + v7 patches,just the test case has been modified.
>
> create tablespace tab1 location '<dir_path>/test1';
> create tablespace tab location '<dir_path>/test';
> 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,100000), large_val());
> alter table t set tablespace tab1 ;
> \c postgres
> create database test1 template test;
> \c test1
> alter table t set tablespace tab;
> \c postgres
> alter database test1 set tablespace tab1;
>
> --Cancel the below command after few seconds
> alter database test1 set tablespace pg_default;
>
> \c test1
> alter table t set tablespace tab1;
>
>
> Logfile Snippet:
> 2021-12-09 17:49:18.110 +04 [18151] PANIC: could not fsync file
> "base/116398/116400": No such file or directory
> 2021-12-09 17:49:19.105 +04 [18150] LOG: checkpointer process (PID 18151)
> was terminated by signal 6: Aborted
> 2021-12-09 17:49:19.105 +04 [18150] LOG: terminating any other active
> server processes
>

This is different from the issue you raised earlier. As Dilip said, we need
to unregister sync requests for files that got successfully copied to the
target database, but the overall alter database statement failed. We are
doing this when the database is created successfully, but not when it fails.
Probably doing the same inside the cleanup function
movedb_failure_callback() should fix the problem.

--
With Regards,
Ashutosh Sharma.