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>, 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-10T02:08:49Z
Lists: pgsql-hackers 
On Thu, Dec 9, 2021 at 7:23 PM Neha Sharma <neha.sharma@enterprisedb.com>
wrote:

> On Thu, Dec 9, 2021 at 11:12 AM Ashutosh Sharma <ashu.coek88@gmail.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@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.

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.