Horton

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

Neha Sharma <neha.sharma@enterprisedb.com>

View thread View on postgr.es
From: Neha Sharma <neha.sharma@enterprisedb.com>
To: Ashutosh Sharma <ashu.coek88@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>
Cc: 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-09T13:53:17Z
Lists: pgsql-hackers 
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

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.