Re: [HACKERS] [DOCS] Streaming replication document improvements
On Tue, Apr 20, 2010 at 11:04 AM, Robert Haas wrote: > Instead of doing this, could we just change the logic in InitPostgres? > > Current logic says we hit the connection limit if: > > if (!am_superuser && > ReservedBackends > 0 && > !HaveNFreeProcs(ReservedBackends)) > > Couldn't we just change this to: > > if ((!am_superuser || am_walsender) && > ReservedBackends > 0 && > !HaveNFreeProcs(ReservedBackends)) > > Seems like that'd be a whole lot simpler, if it'll do the job... It's very simple, but prevents superuser replication connection from being established when connection limit exceeds for non-superusers. It seems strange to me that superuser cannot use superuser_reserved_connections slots. If we'd like to forbid replication connection to use the slots, I think that we should just get rid of a superuser privilege from it instead. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
On Tue, Apr 20, 2010 at 5:47 AM, Fujii Masao wrote: > On Tue, Apr 20, 2010 at 11:04 AM, Robert Haas wrote: >> Instead of doing this, could we just change the logic in InitPostgres? >> >> Current logic says we hit the connection limit if: >> >> if (!am_superuser && >> ReservedBackends > 0 && >> !HaveNFreeProcs(ReservedBackends)) >> >> Couldn't we just change this to: >> >> if ((!am_superuser || am_walsender) && >> ReservedBackends > 0 && >> !HaveNFreeProcs(ReservedBackends)) >> >> Seems like that'd be a whole lot simpler, if it'll do the job... > > It's very simple, but prevents superuser replication connection > from being established when connection limit exceeds for > non-superusers. It seems strange to me that superuser cannot use > superuser_reserved_connections slots. If we'd like to forbid > replication connection to use the slots, I think that we should > just get rid of a superuser privilege from it instead. Let's just stop for a second and think about why we have superuser_reserved_connections in the first place. As I understand it, the point is that we want to make sure that superusers don't get locked out of the database, because superuser intervention might be necessary to recover from whatever series of unfortunate events has caused all of the connection slots to get used up. For example, if there are several different applications that connect to the database, the superuser might like to log in and see which application has requested more than its usual allotment of connections, or the superuser might like to log in and terminate those backends which, in his judgement, ought to be terminated. In other words, the purpose of superuser_reserved_connections is to allow the database to recover from a bad state that it has gotten into: specifically, a state where all the connection slots have been used up and regular users can't connect. If replication connections can use up superuser_reserved_connections slots, then it's very possible that this safety valve will fail completely. If the server is being flooded with connection attempts, and one of the streaming replication connection dies, then a regular backend will immediate grab that slot. When the streaming replication slave automatically tries to reconnect, it will now grab one of the superuser_reserved_connections slots, putting us one step closer to the bad scenario where there's no way for the superuser to log in interactively and troubleshoot. In other words, I don't care whether or not the replication connection is or is not technically a superuser connection. What I think is important is trying to preserve the ability for a superuser to log in interactively and clean up the mess even when the regular supply of connections is maxed out. ...Robert -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
Robert Haas writes: > If replication connections can use up superuser_reserved_connections > slots, then it's very possible that this safety valve will fail > completely. Only if replication can use up *all* the superuser_reserved_connections slots. regards, tom lane -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
On Tue, Apr 20, 2010 at 9:40 AM, Tom Lane wrote: > Robert Haas writes: >> If replication connections can use up superuser_reserved_connections >> slots, then it's very possible that this safety valve will fail >> completely. > > Only if replication can use up *all* the superuser_reserved_connections > slots. Sure. In many cases someone will have 3 superuser_reserved_connections and only 1 wal_sender, so it won't be an issue. However, I still think we oughta make this (apparently one-line) fix so that people will have the number of emergency superuser connections that they expect to have, rather than some smaller number that might be 0 if they have a number of SR slaves. ...Robert -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
On Tue, Apr 20, 2010 at 7:52 PM, Robert Haas wrote: > Let's just stop for a second and think about why we have > superuser_reserved_connections in the first place. As I understand > it, the point is that we want to make sure that superusers don't get > locked out of the database, because superuser intervention might be > necessary to recover from whatever series of unfortunate events has > caused all of the connection slots to get used up. For example, if > there are several different applications that connect to the database, > the superuser might like to log in and see which application has > requested more than its usual allotment of connections, or the > superuser might like to log in and terminate those backends which, in > his judgement, ought to be terminated. In other words, the purpose of > superuser_reserved_connections is to allow the database to recover > from a bad state that it has gotten into: specifically, a state where > all the connection slots have been used up and regular users can't > connect. > > If replication connections can use up superuser_reserved_connections > slots, then it's very possible that this safety valve will fail > completely. If the server is being flooded with connection attempts, > and one of the streaming replication connection dies, then a regular > backend will immediate grab that slot. When the streaming replication > slave automatically tries to reconnect, it will now grab one of the > superuser_reserved_connections slots, putting us one step closer to > the bad scenario where there's no way for the superuser to log in > interactively and troubleshoot. > > In other words, I don't care whether or not the replication connection > is or is not technically a superuser connection. What I think is > important is trying to preserve the ability for a superuser to log in > interactively and clean up the mess even when the regular supply of > connections is maxed out. Yeah, I agree with you, but the difference is only how to achieve. ISTM that there are three choices: 1. Heikki's proposal > ReservedBackends = superuser_reserved_connections + max_wal_senders > MaxBackends = max_connections + autovacuum_max_workers + max_wal_senders + 1 2. My proposal Remove superuser privilege from replication connection 3. Your proposal Treat superuser replication connection like non-superuser one Since 3. is confusing for me, I like 1. or 2. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
On Tue, Apr 20, 2010 at 9:47 AM, Fujii Masao wrote: > Yeah, I agree with you, but the difference is only how to achieve. > ISTM that there are three choices: > > 1. Heikki's proposal >> ReservedBackends = superuser_reserved_connections + max_wal_senders >> MaxBackends = max_connections + autovacuum_max_workers + max_wal_senders + 1 This seemed sensible to me when Heikki first described it, but now it seems overly complex. > 2. My proposal > Remove superuser privilege from replication connection I'm not sure this really fixes the problem. If we add a separate replication privilege, then presumably superusers will automatically have that privilege, in accord with our usual policy on such things. So potentially someone could still set up replication using a superuser account and then they could still get bitten by this problem. > 3. Your proposal > Treat superuser replication connection like non-superuser one Well, only for this one very specific purpose. I would adjust the docs like this: Determines the number of connection "slots" that are reserved for connections by PostgreSQL superusers. At most max_connections connections can ever be active simultaneously. Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections will be accepted only for superusers, and no new replication connections will be accepted. I think that's pretty simple and clear. If we want to burn an extra sentence explaining what this is all about, we could add: (If replication connections were permitted to use the reserved connection slots, an installation with max_wal_senders set to a value greater than or equal to the value set for superuser_reserved_connections might find that no reserved connections remained for interactive access to the database.) > Since 3. is confusing for me, I like 1. or 2. What do others think? ...Robert -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
Robert Haas writes: > On Tue, Apr 20, 2010 at 9:47 AM, Fujii Masao wrote: >> 3. Your proposal >> Treat superuser replication connection like non-superuser one > Well, only for this one very specific purpose. I would adjust the > docs like this: > Determines the number of connection "slots" that are reserved for > connections by PostgreSQL superusers. At most max_connections > connections can ever be active simultaneously. Whenever the number of > active concurrent connections is at least max_connections minus > superuser_reserved_connections, new connections will be accepted only > for superusers, and no new replication connections will be accepted. > I think that's pretty simple and clear. +1. I'm not sure about the consequences of converting walsender to non-superuser across the board, and would prefer not to experiment with it at this stage of the release cycle. regards, tom lane -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
Tom Lane wrote: > Robert Haas writes: >> Fujii Masao wrote: >>> 3. Your proposal >>>Treat superuser replication connection like non-superuser one > >> Well, only for this one very specific purpose. I would adjust >> the docs like this: > >> Determines the number of connection "slots" that are reserved for >> connections by PostgreSQL superusers. At most max_connections >> connections can ever be active simultaneously. Whenever the >> number of active concurrent connections is at least >> max_connections minus superuser_reserved_connections, new >> connections will be accepted only for superusers, and no new >> replication connections will be accepted. > >> I think that's pretty simple and clear. > > +1. I'm not sure about the consequences of converting walsender > to non-superuser across the board, and would prefer not to > experiment with it at this stage of the release cycle. +1 -Kevin -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
[DOCS] Inconsistency in docs for OVERLAPS
Per 8.3 and 8.4 docs: "In addition to these functions, the SQL OVERLAPS operator is supported: (start1, end1) OVERLAPS (start2, end2) (start1, length1) OVERLAPS (start2, length2) This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval." So we accept (datetime,datetime) OVERLAPS (datetime,datetime) or (datetime,interval) OVERLAPS (datetime,interval) However, the example shows yet another case: "SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: true SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: false And in reality, (timestamp, interval) OVERLAPS (timestamp, timestamp) does work, first part of this is incomplete. -- Jim C. Nasby, Database Architect [email protected] 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
Robert Haas writes: > Current logic says we hit the connection limit if: > if (!am_superuser && > ReservedBackends > 0 && > !HaveNFreeProcs(ReservedBackends)) > Couldn't we just change this to: > if ((!am_superuser || am_walsender) && > ReservedBackends > 0 && > !HaveNFreeProcs(ReservedBackends)) As of the patch I just committed, that code is not reached anymore by a walsender process. However, it shouldn't be hard to put a similar test into the walsender code path. regards, tom lane -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
On Tue, Apr 20, 2010 at 11:01 PM, Robert Haas wrote: >> 3. Your proposal >> Treat superuser replication connection like non-superuser one > > Well, only for this one very specific purpose. I would adjust the > docs like this: > > Determines the number of connection "slots" that are reserved for > connections by PostgreSQL superusers. At most max_connections > connections can ever be active simultaneously. Whenever the number of > active concurrent connections is at least max_connections minus > superuser_reserved_connections, new connections will be accepted only > for superusers, and no new replication connections will be accepted. > > I think that's pretty simple and clear. Yeah, I'm sold. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
