Horton

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

Ashutosh Sharma <ashu.coek88@gmail.com>

View thread View on postgr.es
From: Ashutosh Sharma <ashu.coek88@gmail.com>
To: Neha Sharma <neha.sharma@enterprisedb.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>, Bruce Momjian <bruce@momjian.us>, Greg Nancarrow <gregn4422@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, Heikki Linnakangas <hlinnaka@iki.fi>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-12-22T08:18:54Z
Lists: pgsql-hackers 
Hi Dilip,

On Tue, Dec 21, 2021 at 11:10 AM Ashutosh Sharma <ashu.coek88@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.
>

Is this expected? I think it is not.

--
With Regards,
Ashutosh Sharma.

Commits

  1. When using the WAL-logged CREATE DATABASE strategy, bulk extend.

  2. Avoid using a fake relcache entry to own an SmgrRelation.

  3. Fix data-corruption hazard in WAL-logged CREATE DATABASE.

  4. initdb: When running CREATE DATABASE, use STRATEGY = WAL_COPY.

  5. Simplify a needlessly-complicated regular expression.

  6. In 020_createdb.pl, change order of command-line arguments.

  7. Add new block-by-block strategy for CREATE DATABASE.

  8. Fix replay of create database records on standby

  9. Refactor code for reading and writing relation map files.

  10. Replace RelationOpenSmgr() with RelationGetSmgr().

  11. Refactor the fsync queue for wider use.