I believe I have figured out the problem behind the recent reports we've
been seeing of "index is not a btree" errors. Here's how to reproduce
it (in 8.0 or HEAD):

-- drop database test if present
checkpoint;
create database test;
\c test
create table t1 ( name varchar(20) primary key );
-- kill -9 either current session or bgwriter, to force XLOG replay
-- now reconnect to test
vacuum t1;
ERROR: index "t1_pkey" is not a btree

On investigation, the filesystem shows t1_pkey exists but has size 0.

The reason for this is that the only entry in the XLOG concerning
t1_pkey is an "smgr create" record --- we didn't XLOG any of the
data inserted into the index, and particularly not the metapage.

Why is that a problem, if we fsynced the index? Because *replay of
CREATE DATABASE drops and recreates the entire database directory*.
This is not trivial to avoid, because the only way to generate the
new database is to copy from another database, and it's very hard
to tell what to copy if we want it done selectively.

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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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 #

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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?"

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq