Re: Rename a database that has connections

On 22/11/11 17:24, Mark Kirkwood wrote:
> On 22/11/11 16:41, Tom Lane wrote:
>> Mark Kirkwood<mark.kirkwood@catalyst.net.nz>  writes:
>>> I've been helping out several customers recently who all seem to be
>>> wrestling with the same issue: wanting to update/refresh non-production
>>> databases from the latest corresponding prod version. Typically they
>>> have (fairly complex) scripts that at some point attempt to restore a
>>> dump into new database and then rename the to-be-retired db out of the
>>> way and rename the newly restored one to take over.
>>> In many cases such scripts would be simplified if a database could be
>>> renamed without requiring its connections terminated. I've been asked
>>> several times if this could be added... so I've caved in a done a patch
>>> that allows this.
>>> The default behavior is unchanged - it is required to specify an
>>> additional trailing FORCE keyword to elicit the more brutal behavior.
>>> Note that existing connections to the renamed database are unaffected,
>>> but obviously SELECT current_database() returns the new name (in the
>>> next transaction).
>> This patch seems to me to be pretty thoroughly misguided.  Either
>> renaming a database with open connections is safe, or it isn't.  If it
>> is safe, we should just allow it.  If it isn't, making people write an
>> extra FORCE keyword does not make it safe.  It's particularly silly
>> to allow someone to rename the database out from under other sessions
>> (which won't know what happened) but not rename it out from under his
>> own session (which would or at least could know it).
>>
>> What you need to be doing is investigating whether the comments about
>> this in RenameDatabase() are really valid concerns or not.
>>
>
> The reason I added FORCE was to preserve backwards compatibility - for
> any people out there that like the way it behaves right now. I am
> certainly willing to be convinced that such a concern is unneeded.
>
> You are quite right about the patch being inconsistent with respect to
> the renaming the current database, it should allow that too (will
> change if this overall approach makes sense).
>
> With respect to the concerns in RenameDatabase(), that seems to boil
> down to applications stashing the current dbname somewhere and caring
> about it. This was not viewed as a issue by any of the folks who I
> talked to about this (they are all application developers/architects
> etc so they understand that issue). However there may well be
> application frameworks out there that do care... which seemed to me to
> be another reason for making the forced rename require an extra keyword.
>
> I have not been able to find any other problems caused by this...
> renaming a db (many times) with hundreds of pgbench connections does
> not give rise to any issues.
>

Minor change to be allow current database to be renamed as well if FORCE
is used, which makes more sense.

Cheers

Mark