Andrew Dunstan <[EMAIL PROTECTED]> writes:
Longer term, a robust mechanism for DB level locks would probably be preferable, I guess, so I'm not sure if my idea is worth doing in the mean time. Presumably it hasn't caused much of a problem up to now, since most people are not likely to monkey with their template dbs at the same time as trying to create dbs based on them.
Actually, we do get regular complaints about CREATE DATABASE failing because template1 is busy, and that's not because the users are actually *doing* anything to template1, it's just because template1 is the default database-to-connect-to for a whole lot of operations like createuser, createdb itself, psql -l, yadda yadda, and so other backends tend to be transiently connected to template1 even though they have no intention of doing anything to that database in particular.
A quick-and-dirty solution would be to make another template database, so that the default-connection-target could be different from the default-copy-source, but that would cost a fair amount of disk space (compared to a minimal installation) and create lots of backwards compatibility issues too.
I'd like to see a locking-type solution myself, but I'm not sure what the semantics look like. For things like createuser we'd need to invent a lock type that says "I'm connected to this database but I only plan to modify global tables", or something like that. Messy.
Right. So we have 2 problems - we catch too many cases, and we don't catch all the cases that matter.
How about (even quicker and dirtier) putting a limited loop (say 5 iterations?) with a small delay in it around the check for whether or not we are the only connection? Createdb is hardly a time critical operation, and taking a few seconds extra in the worst case wouldn't matter that much, would it? This wouldn't be a 100% solution but I suspect it would be a 99% solution for the spurious failures, and my mtime suggestion could take care of the case where the template db is changed after the connection check is performed.
cheers
andrew
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
