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

On Wed, Dec 22, 2021 at 4:26 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:

>> Basically, ALTER TABLE SET TABLESPACE, will register the
>> SYNC_UNLINK_REQUEST for the table files w.r.t the old tablespace, but
>> those will get unlinked during the next checkpoint. Although the
>> files must be truncated during commit itself but unlink might not have
>> been processed until the next checkpoint. This is the explanation for
>> the behavior you found during your investigation, but I haven't looked
>> into the issue so I will do it latest by tomorrow and send my
>> analysis.
>>
>> Thanks for working on this.
>
>
> Yeah the problem here is that the old rel file that needs to be unlinked still exists in the old tablespace. Earlier, without your changes we were doing force checkpoint before starting with the actual work for the alter database which unlinked/deleted the rel file from the old tablespace, but that is not the case here. Now we have removed the force checkpoint from movedb() which means until the auto checkpoint happens the old rel file will remain in the old tablespace thereby creating this problem.

One solution to this problem could be that, similar to mdpostckpt(),
we invent one more function which takes dboid and dsttblspc oid as
input and it will unlink all the requests which are w.r.t. the dboid
and tablespaceoid, and before doing it we should also do
ForgetDatabaseSyncRequests(), so that next checkpoint does not flush
some old request.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com