Thread

1. Rename a database that has connections

   Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2011-11-22T03:13:13Z

   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).
   
   regards
   
   Mark
   
   
   

2. Re: Rename a database that has connections

   Bruce Momjian <bruce@momjian.us> — 2011-11-22T03:38:35Z

   Mark Kirkwood wrote:
   > 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).
   
   Uh, it isn't save to copy a database when someone else is connected. 
   How does this address that issue?
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + It's impossible for everything to be true. +
   
   

3. Re: Rename a database that has connections

   Tom Lane <tgl@sss.pgh.pa.us> — 2011-11-22T03:41:27Z

   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.
   
   			regards, tom lane
   
   

4. Re: Rename a database that has connections

   Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2011-11-22T04:02:29Z

   On 22/11/11 16:38, Bruce Momjian wrote:
   > Mark Kirkwood wrote:
   >> 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).
   > Uh, it isn't save to copy a database when someone else is connected.
   > How does this address that issue?
   >
   
   Copying a database is quite a different matter (compare with copying an 
   open unix file vs mv'ing it... the latter is quite safe as the inode 
   does not change).
   
   regards
   
   Mark
   
   

5. Re: Rename a database that has connections

   Bruce Momjian <bruce@momjian.us> — 2011-11-22T04:05:38Z

   Mark Kirkwood wrote:
   > On 22/11/11 16:38, Bruce Momjian wrote:
   > > Mark Kirkwood wrote:
   > >> 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).
   > > Uh, it isn't save to copy a database when someone else is connected.
   > > How does this address that issue?
   > >
   > 
   > Copying a database is quite a different matter (compare with copying an 
   > open unix file vs mv'ing it... the latter is quite safe as the inode 
   > does not change).
   
   Oh, I see, you are just renaming.  Well, Tom is right that either it is
   safe, or it isn't  --- a 'force' flag makes no sense.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + It's impossible for everything to be true. +
   
   

6. Re: Rename a database that has connections

   Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2011-11-22T04:24:24Z

   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.
   
   regards
   
   Mark
   
   

7. Re: Rename a database that has connections

   Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2011-11-22T04:29:46Z

   On 22/11/11 17:24, Mark Kirkwood wrote:
   >
   > 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.
   >
   >
   
   One point I did miss - the ps listing still uses the old dbname. 
   pg_stat_activity is correct however.
   
   regards
   
   Mark
   
   
   

8. Re: Rename a database that has connections

   Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2011-11-22T05:06:01Z

   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