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

Hi Dilip,

On Tue, Dec 21, 2021 at 11:10 AM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com>
wrote:

> I am getting the below error when running the same test-case that Neha
> shared in her previous email.
>
> ERROR: 55000: some relations of database "test1" are already in
> tablespace "tab1"
> HINT: You must move them back to the database's default tablespace before
> using this command.
> LOCATION: movedb, dbcommands.c:1555
>
> test-case:
> ========
> create tablespace tab1 location '/home/ashu/test1';
> create tablespace tab location '/home/ashu/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; -- this fails with the given
> error.
>
> Observations:
> ===========
> Please note that before running above alter database statement, the table
> 't' is moved to tablespace 'tab' from 'tab1' so not sure why ReadDir() is
> returning true when searching for table 't' in tablespace 'tab1'. It should
> have returned NULL here:
>
> while ((xlde = ReadDir(dstdir, dst_dbpath)) != NULL)
> {
> if (strcmp(xlde->d_name, ".") == 0 ||
> strcmp(xlde->d_name, "..") == 0)
> continue;
>
> ereport(ERROR,
> (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> errmsg("some relations of database \"%s\" are already
> in tablespace \"%s\"",
> dbname, tblspcname),
> errhint("You must move them back to the database's
> default tablespace before using this command.")));
> }
>
> Also, if I run the checkpoint explicitly before executing the above alter
> database statement, this error doesn't appear which means it only happens
> with the new changes because earlier we were doing the force checkpoint at
> the end of createdb statement.
>

Is this expected? I think it is not.

--
With Regards,
Ashutosh Sharma.