On Tue, Dec 21, 2021 at 11:10 AM Ashutosh Sharma <ashu.coe...@gmail.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.
>
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.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
