Thread: ALTER DATABASE RENAME with HS/SR

ALTER DATABASE RENAME with HS/SR

From
Bernd Helmle
Date:
Our documentation in 
<http://www.postgresql.org/docs/9.0/interactive/hot-standby.html> currently 
says the following:

<snip>
Running DROP DATABASE, ALTER DATABASE ... SET TABLESPACE, or ALTER DATABASE 
... RENAME on the primary will generate a WAL entry 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_streaming_delay.
</snip>

However, renaming a database doesn't trigger a disconnect here on a HS/SR 
setup:

* first, on the primary do:

CREATE DATABASE foo;

* ...wait until database creation arrived on the standby and connect:

psql foo

* now rename the database on the primary

ALTER DATABASE foo RENAME TO bar;

* on the standby do in the same connection as before:

foo=# SELECT datname FROM pg_database; datname
-----------template1template0postgresberndpgbenchbar
(6 rows)

That looks contrary to the documented behavior. Shouldn't i get a forced 
disconnect on this connection instead?

-- 
Thanks
Bernd


Re: ALTER DATABASE RENAME with HS/SR

From
Fujii Masao
Date:
On Mon, Oct 4, 2010 at 2:05 AM, Bernd Helmle <mailings@oopsware.de> wrote:
> Our documentation in
> <http://www.postgresql.org/docs/9.0/interactive/hot-standby.html> currently
> says the following:
>
> <snip>
> Running DROP DATABASE, ALTER DATABASE ... SET TABLESPACE, or ALTER DATABASE
> ... RENAME on the primary will generate a WAL entry 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_streaming_delay.
> </snip>
>
> However, renaming a database doesn't trigger a disconnect here on a HS/SR
> setup:
>
> * first, on the primary do:
>
> CREATE DATABASE foo;
>
> * ...wait until database creation arrived on the standby and connect:
>
> psql foo
>
> * now rename the database on the primary
>
> ALTER DATABASE foo RENAME TO bar;
>
> * on the standby do in the same connection as before:
>
> foo=# SELECT datname FROM pg_database;
>  datname
> -----------
> template1
> template0
> postgres
> bernd
> pgbench
> bar
> (6 rows)
>
> That looks contrary to the documented behavior. Shouldn't i get a forced
> disconnect on this connection instead?

Probably yes. To do that, ISTM that we should make ALTER DATABASE .. RENAME
issue something like XLOG_DBASE_RENAME record, and make the standby server
call ResolveRecoveryConflictWithDatabase() when that record is applied.
Simon?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: ALTER DATABASE RENAME with HS/SR

From
Robert Haas
Date:
On Mon, Oct 4, 2010 at 7:38 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Mon, Oct 4, 2010 at 2:05 AM, Bernd Helmle <mailings@oopsware.de> wrote:
>> Our documentation in
>> <http://www.postgresql.org/docs/9.0/interactive/hot-standby.html> currently
>> says the following:
>>
>> <snip>
>> Running DROP DATABASE, ALTER DATABASE ... SET TABLESPACE, or ALTER DATABASE
>> ... RENAME on the primary will generate a WAL entry 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_streaming_delay.
>> </snip>
>>
>> However, renaming a database doesn't trigger a disconnect here on a HS/SR
>> setup:
>>
>> * first, on the primary do:
>>
>> CREATE DATABASE foo;
>>
>> * ...wait until database creation arrived on the standby and connect:
>>
>> psql foo
>>
>> * now rename the database on the primary
>>
>> ALTER DATABASE foo RENAME TO bar;
>>
>> * on the standby do in the same connection as before:
>>
>> foo=# SELECT datname FROM pg_database;
>>  datname
>> -----------
>> template1
>> template0
>> postgres
>> bernd
>> pgbench
>> bar
>> (6 rows)
>>
>> That looks contrary to the documented behavior. Shouldn't i get a forced
>> disconnect on this connection instead?
>
> Probably yes. To do that, ISTM that we should make ALTER DATABASE .. RENAME
> issue something like XLOG_DBASE_RENAME record, and make the standby server
> call ResolveRecoveryConflictWithDatabase() when that record is applied.
> Simon?

I understand that we need to disconnect users if the database is
dropped (it's kind of hard to access a database that's not there any
more...) but I'm fuzzy on why we'd need to do that if it is merely
renamed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


Re: ALTER DATABASE RENAME with HS/SR

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> I understand that we need to disconnect users if the database is
> dropped (it's kind of hard to access a database that's not there any
> more...) but I'm fuzzy on why we'd need to do that if it is merely
> renamed.

I think that modern backends might survive that okay (though they didn't
use to; we once had global variable(s) containing the DB name).  But
it's much less clear that clients would cope sanely.  "I'm connected to
database foo".  "No you're not".  Connection poolers in particular are
likely to get bent out of shape by this.

OTOH, we don't have a similar interlock to prevent renaming users
who have active sessions, so maybe we are being overprotective here.
        regards, tom lane


Re: ALTER DATABASE RENAME with HS/SR

From
Robert Haas
Date:
On Mon, Oct 4, 2010 at 2:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I understand that we need to disconnect users if the database is
>> dropped (it's kind of hard to access a database that's not there any
>> more...) but I'm fuzzy on why we'd need to do that if it is merely
>> renamed.
>
> I think that modern backends might survive that okay (though they didn't
> use to; we once had global variable(s) containing the DB name).  But
> it's much less clear that clients would cope sanely.  "I'm connected to
> database foo".  "No you're not".  Connection poolers in particular are
> likely to get bent out of shape by this.

True.  Don't we already have some mechanism for notifying clients of
parameter changes they might care about?  Could it be adapted to this
situation?

> OTOH, we don't have a similar interlock to prevent renaming users
> who have active sessions, so maybe we are being overprotective here.

I think probably so.  I continue to think that we need to work on
reducing the number of things that require interrupting normal
database operation, and anything that requires kicking all users out
of a database falls into that category.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


Re: ALTER DATABASE RENAME with HS/SR

From
Josh Berkus
Date:
On 10/4/10 10:24 AM, Robert Haas wrote:
> I understand that we need to disconnect users if the database is
> dropped (it's kind of hard to access a database that's not there any
> more...) but I'm fuzzy on why we'd need to do that if it is merely
> renamed.

This seems like an unexpected benefit, and the behavior which users
would desire if they could choose it.  Why would we break what's not broken?

+1 to keep ALTER DATABASE functionality the way it is, and merely fix
the docs.

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: ALTER DATABASE RENAME with HS/SR

From
Bernd Helmle
Date:

--On 4. Oktober 2010 13:24:37 -0400 Robert Haas <robertmhaas@gmail.com> 
wrote:

> I understand that we need to disconnect users if the database is
> dropped (it's kind of hard to access a database that's not there any
> more...) but I'm fuzzy on why we'd need to do that if it is merely
> renamed.

Yeah, if there's no real technical reason (besides some potential confusion 
by the user...), this feels like overkill. If the behavior is okay, we need 
to change the documentation however.

-- 
Thanks
Bernd


Re: ALTER DATABASE RENAME with HS/SR

From
"Kevin Grittner"
Date:
Robert Haas <robertmhaas@gmail.com> wrote:
> Don't we already have some mechanism for notifying clients of
> parameter changes they might care about?
Are you thinking of GUC_REPORT?
-Kevin


Re: ALTER DATABASE RENAME with HS/SR

From
Robert Haas
Date:
On Mon, Oct 4, 2010 at 3:16 PM, Josh Berkus <josh@agliodbs.com> wrote:
> On 10/4/10 10:24 AM, Robert Haas wrote:
>> I understand that we need to disconnect users if the database is
>> dropped (it's kind of hard to access a database that's not there any
>> more...) but I'm fuzzy on why we'd need to do that if it is merely
>> renamed.
>
> This seems like an unexpected benefit, and the behavior which users
> would desire if they could choose it.  Why would we break what's not broken?
>
> +1 to keep ALTER DATABASE functionality the way it is, and merely fix
> the docs.

Well, the current behavior isn't too consistent.  If you try to rename
a database then:

(1) If there's a connection to that database on the primary, it blocks
for a bit and then gives up, complaining of clients connected to that
database.
-but-
(2) If there's a connection to that database on the secondary, it
obviously doesn't block and recovery isn't blocked either.  It just
replays the record and, boom, you're connected to the new database,
except you don't know it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


Re: ALTER DATABASE RENAME with HS/SR

From
Fujii Masao
Date:
On Tue, Oct 5, 2010 at 3:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> OTOH, we don't have a similar interlock to prevent renaming users
> who have active sessions, so maybe we are being overprotective here.

Yep. What is worse is that we can drop users who have active sessions
on the standby. Then we can get the following error from those sessions:
   => SELECT current_user;   ERROR:  invalid role OID: 16384

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: ALTER DATABASE RENAME with HS/SR

From
Simon Riggs
Date:
On Mon, 2010-10-04 at 20:38 +0900, Fujii Masao wrote:
> >
> > That looks contrary to the documented behavior. Shouldn't i get a forced
> > disconnect on this connection instead?
> 
> Probably yes. To do that, ISTM that we should make ALTER DATABASE .. RENAME
> issue something like XLOG_DBASE_RENAME record, and make the standby server
> call ResolveRecoveryConflictWithDatabase() when that record is applied.
> Simon?

Certainly contrary to documented behaviour, thanks for the report.

Question: do we want that documented behaviour, or should we leave it as
is? Probably want to throw a conflict, but it seems worth asking, since
I know for certain I just made up the documented behaviour.

I'll patch if we agree its required.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: ALTER DATABASE RENAME with HS/SR

From
Simon Riggs
Date:
On Tue, 2010-10-05 at 13:29 +0900, Fujii Masao wrote:
> On Tue, Oct 5, 2010 at 3:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > OTOH, we don't have a similar interlock to prevent renaming users
> > who have active sessions, so maybe we are being overprotective here.
> 
> Yep. What is worse is that we can drop users who have active sessions
> on the standby. Then we can get the following error from those sessions:
> 
>     => SELECT current_user;
>     ERROR:  invalid role OID: 16384

Hot Standby faithfully implements the current behaviour on the master in
that case.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: ALTER DATABASE RENAME with HS/SR

From
Fujii Masao
Date:
On Tue, Oct 5, 2010 at 5:11 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Tue, 2010-10-05 at 13:29 +0900, Fujii Masao wrote:
>> On Tue, Oct 5, 2010 at 3:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> > OTOH, we don't have a similar interlock to prevent renaming users
>> > who have active sessions, so maybe we are being overprotective here.
>>
>> Yep. What is worse is that we can drop users who have active sessions
>> on the standby. Then we can get the following error from those sessions:
>>
>>     => SELECT current_user;
>>     ERROR:  invalid role OID: 16384
>
> Hot Standby faithfully implements the current behaviour on the master in
> that case.

Oh, right. Sorry for noise.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: ALTER DATABASE RENAME with HS/SR

From
Robert Haas
Date:
On Mon, Oct 4, 2010 at 12:42 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Mon, 2010-10-04 at 20:38 +0900, Fujii Masao wrote:
>> >
>> > That looks contrary to the documented behavior. Shouldn't i get a forced
>> > disconnect on this connection instead?
>>
>> Probably yes. To do that, ISTM that we should make ALTER DATABASE .. RENAME
>> issue something like XLOG_DBASE_RENAME record, and make the standby server
>> call ResolveRecoveryConflictWithDatabase() when that record is applied.
>> Simon?
>
> Certainly contrary to documented behaviour, thanks for the report.
>
> Question: do we want that documented behaviour, or should we leave it as
> is? Probably want to throw a conflict, but it seems worth asking, since
> I know for certain I just made up the documented behaviour.
>
> I'll patch if we agree its required.

Per comments from Josh, Bernd, and myself upthread, I think the
consensus is that we should patch the documentation.  Aside from the
fact that the restriction seems fairly arbitrary in any event, I'm
unexcited about back-patching a WAL format change.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


Re: ALTER DATABASE RENAME with HS/SR

From
Simon Riggs
Date:
On Tue, 2010-10-05 at 08:56 -0400, Robert Haas wrote:
> On Mon, Oct 4, 2010 at 12:42 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > On Mon, 2010-10-04 at 20:38 +0900, Fujii Masao wrote:
> >> >
> >> > That looks contrary to the documented behavior. Shouldn't i get a forced
> >> > disconnect on this connection instead?
> >>
> >> Probably yes. To do that, ISTM that we should make ALTER DATABASE .. RENAME
> >> issue something like XLOG_DBASE_RENAME record, and make the standby server
> >> call ResolveRecoveryConflictWithDatabase() when that record is applied.
> >> Simon?
> >
> > Certainly contrary to documented behaviour, thanks for the report.
> >
> > Question: do we want that documented behaviour, or should we leave it as
> > is? Probably want to throw a conflict, but it seems worth asking, since
> > I know for certain I just made up the documented behaviour.
> >
> > I'll patch if we agree its required.
> 
> Per comments from Josh, Bernd, and myself upthread, I think the
> consensus is that we should patch the documentation. 

Yep, just working on it now.

>  Aside from the
> fact that the restriction seems fairly arbitrary in any event, I'm
> unexcited about back-patching a WAL format change.

Agreed, but just in case we need to in the future, we can use the XLOG
Info record type for such problems.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services