Fundamental error in "no WAL log" index/file creation stuff

I wrote:

It seems our choices are (a) somehow fix things so CREATE DATABASE
replay doesn't have to zap the whole directory, (b) force a checkpoint
immediately after any CREATE DATABASE, so that we never have to replay
one except in a PITR situation, or (c) abandon non-WAL-logged index
and table builds.

Having overcome my initial dismay at missing such a fundamental problem,
I've thought harder about it and concluded that (b) is clearly the thing
to do.

I think it would take a wholesale redesign of the CREATE DATABASE
mechanism to do (a). While there are a number of ugly things about
CREATE DATABASE that could perhaps be fixed with a redesign,
I don't currently have any good idea about how to do it right.
In any case this path wouldn't be an acceptable backpatch for the 8.0
branch.

(c) is clearly not the direction we really wish to take, either.

Also, I realized that there is an entirely independent problem that (b)
will protect us against. Consider this scenario:

create database acopy;
\c template1
create table bozo(f1 int);
-- now system crashes and replays WAL

After this sequence it is possible for "acopy" to contain the table
"bozo", because the replay of the CREATE DATABASE will copy whatever
is on disk in template1. (You can actually duplicate this in 8.0
and HEAD, if you wait long enough for the bgwriter to write the
catalog changes, but not long enough for a checkpoint to happen.)

Accordingly, I've committed (b) into 8.0 and HEAD. (Earlier branches
are not at risk because they didn't WAL-log CREATE DATABASE at all.)

This might be sufficient reason to release an 8.0.4 pretty soon.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

(b) force a checkpoint
immediately after any CREATE DATABASE, so that we never have to replay
one except in a PITR situation

So wouldn't this mean that any CREATE DATABASE won't work properly in PITR?

--
greg

Greg Stark wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

(b) force a checkpoint
immediately after any CREATE DATABASE, so that we never have to replay
one except in a PITR situation

So wouldn't this mean that any CREATE DATABASE won't work properly in
PITR?

As I understand it: no. Because with PITR turned on there are no
"non-WAL-logged index
and table builds". Therefore the indexes and tables are WAL-logged and will
be recreated correctly.

I hope this is correct information :-)

Best Regards,
Michael Paesold

On 6/25/2005 6:58 PM, Tom Lane wrote:

I wrote:

It seems our choices are (a) somehow fix things so CREATE DATABASE
replay doesn't have to zap the whole directory, (b) force a checkpoint
immediately after any CREATE DATABASE, so that we never have to replay
one except in a PITR situation, or (c) abandon non-WAL-logged index
and table builds.

Having overcome my initial dismay at missing such a fundamental problem,
I've thought harder about it and concluded that (b) is clearly the thing
to do.

I think it would take a wholesale redesign of the CREATE DATABASE
mechanism to do (a). While there are a number of ugly things about
CREATE DATABASE that could perhaps be fixed with a redesign,
I don't currently have any good idea about how to do it right.
In any case this path wouldn't be an acceptable backpatch for the 8.0
branch.

One way of redesigning CREATE DATABASE would be to build the new
database directory from scratch using bki files. Doing so would lose the
current template mechanism, but that can easily be redesigned into a
utility that creates a bki file set from any existing database. The
other annoyance this redesign would fix is the createdb failures because
the template DB is in use.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Jan Wieck <JanWieck@Yahoo.com> writes:

One way of redesigning CREATE DATABASE would be to build the new
database directory from scratch using bki files. Doing so would lose the
current template mechanism, but that can easily be redesigned into a
utility that creates a bki file set from any existing database.

Not so easily as all that. The .bki mechanism is pretty darn restricted
as to what it can generate, because of the limitations of bootstrap
mode. (And when you are starting up without pg_proc or pg_type, it's
not trivial to remove those restrictions either.)

I thought for a bit about
	1. Clone newdb from template0 (which we assume doesn't change)
	2. pg_dump template1 | pg_restore newdb
but pg_dump isn't terribly fast, and in any case this still has issues
--- eg, if DBA has dropped the public schema in template1, it will fail
to propagate that fact.

regards, tom lane

Jan Wieck wrote:

One way of redesigning CREATE DATABASE would be to build the new
database directory from scratch using bki files. Doing so would lose
the current template mechanism, but that can easily be redesigned into
a utility that creates a bki file set from any existing database. The
other annoyance this redesign would fix is the createdb failures
because the template DB is in use.

This one should be largely fixed by provision of another default
connection target, which is in the pipeline.

cheers

andrew

Greg Stark <gsstark@mit.edu> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

(b) force a checkpoint
immediately after any CREATE DATABASE, so that we never have to replay
one except in a PITR situation

So wouldn't this mean that any CREATE DATABASE won't work properly in PITR?

It works fine in a rollforward situation. However see the note I added
to backup.sgml:

If a CREATE DATABASE command is executed while a base backup is
being taken, and then the template database that the CREATE
DATABASE copied is modified while the base backup is still in
progress, it is possible that recovery will cause those
modifications to be propagated into the created database as
well. This is of course undesirable. To avoid this risk, it is best
not to modify any template databases while taking a base backup.

I don't see anything much we can do about this except add the warning;
we cannot say which state of the template database will be picked up
by the filesystem backup.

regards, tom lane

Andrew Dunstan <andrew@dunslane.net> writes:

Jan Wieck wrote:

The other annoyance this redesign would fix is the createdb failures
because the template DB is in use.

This one should be largely fixed by provision of another default
connection target, which is in the pipeline.

Yeah, but that's a workaround not a solution. It's still one of the
issues I would like to see fixed by a still-hypothetical CREATE DATABASE
redesign.

There are many more-pressing problems of course, so I don't see anything
being done about CREATE DATABASE for a good while.

regards, tom lane

Would it be possible to rollback from WAL? My thought is this:

CREATE DATABASE is logged in WAL
If a replay of that CREATE DATABASE is performed, the template database
used is copied
Any modifications to the template database that occured after the CREATE
DATABASE are rolled back in the new database
WAL replay continues

Would this resolve the PITR issues with CREATE DATABASE?
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Tom Lane" <tgl@sss.pgh.pa.us> writes

So wouldn't this mean that any CREATE DATABASE won't work properly in

PITR?

It works fine in a rollforward situation.

Since there is no xlog replay mechanism to CREATE INDEX (bottom-up method),
so CREATE INDEX won't get replayed in PITR?

This seems also true for SET TABLESPACE command.

Regards,
Qingqing

"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:

Since there is no xlog replay mechanism to CREATE INDEX (bottom-up method),
so CREATE INDEX won't get replayed in PITR?

On what do you base either part of that statement?

regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes

Since there is no xlog replay mechanism to CREATE INDEX (bottom-up

method),

so CREATE INDEX won't get replayed in PITR?

On what do you base either part of that statement?

I see _bt_load() still relies on smgrimmedsync() to assure a correct disk
image of the btree after a crash.

As you found out, this won't work if we do a CREATE DATABASE + CREATE INDEX
and system crashes. If you forced a checkpoint, then CREATE DATABASE won't
get replayed after the crashed, so the btree disk image is still there, then
the problem is solved in this case.

However, if we take a physical copy of the database, then do CREATE DATABASE
+ CREATE INDEX again, and we want to rollforward, from my understanding of
current _bt_load() code, I don't see anywhere to get the btree disk image
again.

Regards,
Qingqing

On Thu, 2005-08-04 at 10:56 +0800, Qingqing Zhou wrote:

"Tom Lane" <tgl@sss.pgh.pa.us> writes

Since there is no xlog replay mechanism to CREATE INDEX (bottom-up

method),

so CREATE INDEX won't get replayed in PITR?

On what do you base either part of that statement?

I have learnt that Tom means: read the code. :-)

CREATE INDEX doesn't produce xlog records *except* when you use PITR, so
PITR does work correctly.

Tom's earlier warning about CREATE DATABASE stands...

Best Regards, Simon Riggs

"Simon Riggs" <simon@2ndquadrant.com> writes

I have learnt that Tom means: read the code. :-)

CREATE INDEX doesn't produce xlog records *except* when you use PITR, so
PITR does work correctly.

wstate.btws_use_wal = XLogArchivingActive() && !wstate.index->rd_istemp;

Ah-oh, that's true ;-)

Regards,
Qingqing