Thread: Hot Standby and DROP DATABASE

Hi,

While testing Hot Standby, I have encountered strange behavior with
DROP DATABASE command.

1) connect to "test" database at standby via psql
2) issue DROP DATABASE test command to primary
3) session #1 works fine
4) close session #1
5) "test" database dropped on standby

Fromt the manual:
Running DROP DATABASE, ALTER DATABASE ... SET TABLESPACE, or ALTERDATABASE ... RENAME on primary will generate a log
messagethat willcause all users connected to that database on the standby to beforcibly disconnected. This action
occursimmediately, whatever thesetting of max_standby_delay.
 

So it seems at least the behavior is quite different from what the
docs stats. Am I missing something here?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On Saturday 06 February 2010 02:25:33 Tatsuo Ishii wrote:
> Hi,
> 
> While testing Hot Standby, I have encountered strange behavior with
> DROP DATABASE command.
> 
> 1) connect to "test" database at standby via psql
> 2) issue DROP DATABASE test command to primary
> 3) session #1 works fine
> 4) close session #1
> 5) "test" database dropped on standby
> 
> Fromt the manual:
> 
>  Running DROP DATABASE, ALTER DATABASE ... SET TABLESPACE, or ALTER
>  DATABASE ... RENAME on primary will generate a log message that will
>  cause all users connected to that database on the standby to be
>  forcibly disconnected. This action occurs immediately, whatever the
>  setting of max_standby_delay.
> 
> So it seems at least the behavior is quite different from what the
> docs stats. Am I missing something here?
Its a small bug/typo in standby.c:ResolveRecoveryConflictWithDatabase

The line:    CancelDBBackends(dbid, PROCSIG_RECOVERY_CONFLICT_TABLESPACE, true);

has to be    CancelDBBackends(dbid, PROCSIG_RECOVERY_CONFLICT_DATABASE, true);


Andres

On Saturday 06 February 2010 17:32:43 Andres Freund wrote:
> On Saturday 06 February 2010 02:25:33 Tatsuo Ishii wrote:
> > Hi,
> > 
> > While testing Hot Standby, I have encountered strange behavior with
> > DROP DATABASE command.
> > 
> > 1) connect to "test" database at standby via psql
> > 2) issue DROP DATABASE test command to primary
> > 3) session #1 works fine
> > 4) close session #1
> > 5) "test" database dropped on standby
> > 
> > Fromt the manual:
> >  Running DROP DATABASE, ALTER DATABASE ... SET TABLESPACE, or ALTER
> >  DATABASE ... RENAME on primary will generate a log message that will
> >  cause all users connected to that database on the standby to be
> >  forcibly disconnected. This action occurs immediately, whatever the
> >  setting of max_standby_delay.
> > 
> > So it seems at least the behavior is quite different from what the
> > docs stats. Am I missing something here?
> 
> Its a small bug/typo in standby.c:ResolveRecoveryConflictWithDatabase
> 
> The line:
>         CancelDBBackends(dbid, PROCSIG_RECOVERY_CONFLICT_TABLESPACE, true);
For the case it should not be clear, the reason that  
PROCSIG_RECOVERY_CONFLICT_TABLESPACE did not kill the session is that 
currently all tablespace conflicts are valid only inside a transaction, so when 
receiving the recovery conflict checks whether its not inside a transaction 
block "anymore" and continues happily.

Andres

On Sat, 2010-02-06 at 17:32 +0100, Andres Freund wrote:
> > 
> > So it seems at least the behavior is quite different from what the
> > docs stats. Am I missing something here?
> Its a small bug/typo in standby.c:ResolveRecoveryConflictWithDatabase
> 
> The line:
>         CancelDBBackends(dbid, PROCSIG_RECOVERY_CONFLICT_TABLESPACE, true);
> 
> has to be
>         CancelDBBackends(dbid, PROCSIG_RECOVERY_CONFLICT_DATABASE, true);

Well spotted, thanks for the report and the analysis.

The code for drop database worked when committed but it looks like the
re-factoring of the code broke it. Will fix.

-- Simon Riggs           www.2ndQuadrant.com