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

I see that this patch is reducing the database creation time by almost 3-4
times provided that the template database has some user data in it.
However, there are couple of points to be noted:

1) It makes the crash recovery a bit slower than before if the crash has
occurred after the execution of a create database statement. Moreover, if
the template database size is big, it might even generate a lot of WAL
files which the user needs to be aware of.

2) This will put a lot of load on the first checkpoint that will occur
after creating the database statement. I will experiment around this to see
if this has any side effects.

--

Further, the code changes in the patch looks good. I just have few comments:

+void
+LockRelationId(LockRelId *relid, LOCKMODE lockmode)
+{
+ LOCKTAG tag;
+ LOCALLOCK *locallock;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_RELATION(tag, relid->dbId, relid->relId);

Should there be an assertion statement here to ensure that relid->dbid
and relid->relid is valid?

--

if (info == XLOG_DBASE_CREATE)
{
xl_dbase_create_rec *xlrec = (xl_dbase_create_rec *)
XLogRecGetData(record);
- char *src_path;
- char *dst_path;
- struct stat st;
-
- src_path = GetDatabasePath(xlrec->src_db_id,
xlrec->src_tablespace_id);
- dst_path = GetDatabasePath(xlrec->db_id, xlrec->tablespace_id);
+ char *dbpath;

- /*
- * Our theory for replaying a CREATE is to forcibly drop the target
- * subdirectory if present, then re-copy the source data. This may
be
- * more work than needed, but it is simple to implement.
- */
- if (stat(dst_path, &st) == 0 && S_ISDIR(st.st_mode))
- {
- if (!rmtree(dst_path, true))
- /* If this failed, copydir() below is going to error. */
- ereport(WARNING,
- (errmsg("some useless files may be left behind in
old database directory \"%s\"",
- dst_path)));
- }

I think this is a significant change and probably needs some kind of
explanation/comments as-in why we are just creating a dir and copying the
version file when replaying create database operation. Earlier, this meant
replaying the complete create database operation, that doesn't seem to be
the case now.

--

Have you intentionally skipped pg_internal.init file from being copied to
the target database?

--
With Regards,
Ashutosh Sharma.

On Thu, Dec 2, 2021 at 7:20 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

> On Wed, Dec 1, 2021 at 6:04 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> > Thanks a lot for testing this. From the error, it seems like some of
> > the old buffer w.r.t. the previous tablespace is not dropped after the
> > movedb. Actually, we are calling DropDatabaseBuffers() after copying
> > to a new tablespace and dropping all the buffers of this database
> > w.r.t the old tablespace. But seems something is missing, I will
> > reproduce this and try to fix it by tomorrow. I will also fix the
> > other review comments raised by you in the previous mail.
>
> Okay, I got the issue, basically we are dropping the database buffers
> but not unregistering the existing sync request for database buffers
> w.r.t old tablespace. Attached patch fixes that. I also had to extend
> ForgetDatabaseSyncRequests so that we can delete the sync request of
> the database for the particular tablespace so added another patch for
> the same (0006).
>
> I will test the performance scenario next week, which is suggested by John.
>
>
> --
> Regards,
> Dilip Kumar
> EnterpriseDB: http://www.enterprisedb.com
>