Thread: ALTER DATABASE RENAME with HS/SR
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
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
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
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
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
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
--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
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
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
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
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
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
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
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
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