Jon Lapham <[EMAIL PROTECTED]> writes in pgsql-general: > If I run... > sleep 3; echo starting; createdb bar > ...and power off the VM while the "createdb bar" is running.
> Upon restart, about 50% of the time I can reproduce the following error > message: > [EMAIL PROTECTED] ~]$ psql bar > psql: FATAL: database "bar" does not exist > [EMAIL PROTECTED] ~]$ createdb bar > createdb: database creation failed: ERROR: could not create directory > "base/65536": File exists What apparently is happening here is that the same OID has been assigned to the new database both times. Even though the createdb didn't complete, the directory it started to build is there and so there's a filename collision. > So, running "createdb bar" a second time works. Yeah, because the OID counter has been advanced, and so the second createdb uses a nonconflicting OID. In theory this scenario should not happen, because a crash-and-restart is supposed to guarantee that the OID counter comes up at or beyond where it was before the crash. After thinking about it for awhile, I believe the problem is that CREATE DATABASE is breaking the "WAL rule": it's allowing a data change (specifically, creation of the new DB subdirectory) to hit disk without having guaranteed that associated WAL entries were flushed first. Specifically, if we generated an XLOG_NEXTOID WAL entry to record the consumption of an OID for the database, there isn't anything ensuring that record gets to disk before the mkdir occurs. (ie, the comment in XLogPutNextOid is correct as far as it goes, but it fails to account for outside-the-database effects such as creation of a directory named after the OID.) Hence after restart the OID counter might not get advanced as far as it should have been. We could fix this two different ways: 1. Put an XLogFlush into createdb() somewhere between making the pg_database entry and starting to create subdirectories. 2. Check for conflicting database directories while assigning the OID, comparable to what GetNewRelFileNode() does for table files. #2 has some appeal because it could deal with random junk in $PGDATA/base regardless of how the junk got there. However, to do that in a really bulletproof way we'd have to check all the tablespace directories too, and that's starting to get a tad tedious for something that shouldn't happen anyway. So I'm leaning to #1 as a suitably low-effort fix. Thoughts? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster