Skip site navigation (1) Skip section navigation (2)

Re: Rename a database that has connections

From: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Rename a database that has connections
Date: 2011-11-22 05:06:01
Message-ID: 4ECB2DB9.3040708@catalyst.net.nz (view raw or flat)
Thread:
Lists: pgsql-hackers
On 22/11/11 17:24, Mark Kirkwood wrote:
> On 22/11/11 16:41, Tom Lane wrote:
>> Mark Kirkwood<mark(dot)kirkwood(at)catalyst(dot)net(dot)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



Attachment: rename-force.patch.2
Description: text/plain (8.5 KB)

In response to

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2011-11-22 05:58:57
Subject: Re: explain analyze query execution time
Previous:From: Pavel StehuleDate: 2011-11-22 04:55:17
Subject: dblink: enable parameters

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group