Re: [HACKERS] Keepalives win32
Hello, Bruce. You wrote: BM Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 28, 2010 at 8:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: What I was trying to say is I think we could dispense with the setsockopt() code path, and just always use the WSAIoctl() path anytime keepalives are turned on. I don't know what system default values you're speaking of, if they're not the registry entries; and I definitely don't see the point of consulting such values if they aren't user-settable. We might as well just consult the RFCs and be done. FWIW, I think I prefer Magnus's approach, but I'm not 100% sure I can defend that preference... Well, basically what I don't like about Magnus' proposal is that setting one of the two values changes the default that will be used for the other one. (Or, if it does not change the default, the extra code is useless anyway.) If we just always go through the WSAIoctl() path then we can clearly document the default for this on Windows is so-and-so. How is the documentation going to explain the behavior of the proposed code? BM Let's look at the usage probabilities. 99% of Win32 users will not use BM any of these settings. Let me disagree with this statement. As DAC developer I'm faced with opposite reality. There are a lot of users demanding this functionality. BM I would hate to come up with a solution that BM changes the default behavior for that 99%. BM Therefore, I think using hard-coded defaults only for cases where BM someone sets one but not all settings is appropriate. The documentation BM text would be: BM On Windows, if a keepalive settings is set, then defaults will be BM used for any unset values, specifically, keepalives_idle (200) and BM keepalives_interval(4). Windows does not allow control of BM keepalives_count. BM Seems simple enough. BM -- BM Bruce Momjian br...@momjian.ushttp://momjian.us BM EnterpriseDB http://enterprisedb.com BM + None of us is going to be here forever. + -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
2010/6/30 Robert Haas robertmh...@gmail.com: By the way, does the SQL standard say anything about materialized views? AFAIK, nope. Probably for the same reason that indexes are not mentioned by the standard: both are only performance enhancements, and one could easily imagine future SQL database systems that manage their creation and removal automatically (based on usage patterns or available disk space or somesuch). Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
2010/6/30 Pavel Golub pa...@microolap.com: Hello, Bruce. You wrote: BM Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 28, 2010 at 8:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: What I was trying to say is I think we could dispense with the setsockopt() code path, and just always use the WSAIoctl() path anytime keepalives are turned on. I don't know what system default values you're speaking of, if they're not the registry entries; and I definitely don't see the point of consulting such values if they aren't user-settable. We might as well just consult the RFCs and be done. FWIW, I think I prefer Magnus's approach, but I'm not 100% sure I can defend that preference... Well, basically what I don't like about Magnus' proposal is that setting one of the two values changes the default that will be used for the other one. (Or, if it does not change the default, the extra code is useless anyway.) If we just always go through the WSAIoctl() path then we can clearly document the default for this on Windows is so-and-so. How is the documentation going to explain the behavior of the proposed code? BM Let's look at the usage probabilities. 99% of Win32 users will not use BM any of these settings. Let me disagree with this statement. As DAC developer I'm faced with opposite reality. There are a lot of users demanding this functionality. It's very intersting to hear from somebody who expects to actually use this. But are you aware that we're only talking about *adjusting* the keepalive values, not enabling them? Because we will, as the code stands now, enable keepalive by defaults - just use the system default values for timeout intervals. (Meaning this is how we do it on Unix as of HEAD, irregardless of my patch) Do you have an opinion on the two choices for handling keepalives_idle and keepalives_interval? They basically are: 1) When not configured, use system defaults. When only one of the two parameters configured, use RFC default for the other one (overwrite system default). 2) When not configured, use RFC defaults (overwrite system defaults). When only one of the two parameters configured, use RFC default for the other one (overwrite system default) 3) When not configured, use system defaults. When only one of the two parameters configured, throw error. I can see pros and cons with both. Given that I still think *most* people will not configure the intervals at all, I think #1 is the one that follows principle of least surprise. Perhaps option *3* is the one that does this for partial configuration? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Hello, Magnus. You wrote: MH 2010/6/30 Pavel Golub pa...@microolap.com: Hello, Bruce. You wrote: BM Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 28, 2010 at 8:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: What I was trying to say is I think we could dispense with the setsockopt() code path, and just always use the WSAIoctl() path anytime keepalives are turned on. I don't know what system default values you're speaking of, if they're not the registry entries; and I definitely don't see the point of consulting such values if they aren't user-settable. We might as well just consult the RFCs and be done. FWIW, I think I prefer Magnus's approach, but I'm not 100% sure I can defend that preference... Well, basically what I don't like about Magnus' proposal is that setting one of the two values changes the default that will be used for the other one. (Or, if it does not change the default, the extra code is useless anyway.) If we just always go through the WSAIoctl() path then we can clearly document the default for this on Windows is so-and-so. How is the documentation going to explain the behavior of the proposed code? BM Let's look at the usage probabilities. 99% of Win32 users will not use BM any of these settings. Let me disagree with this statement. As DAC developer I'm faced with opposite reality. There are a lot of users demanding this functionality. MH It's very intersting to hear from somebody who expects to actually use MH this. But are you aware that we're only talking about *adjusting* the MH keepalive values, not enabling them? Because we will, as the code MH stands now, enable keepalive by defaults - just use the system default MH values for timeout intervals. (Meaning this is how we do it on Unix as MH of HEAD, irregardless of my patch) MH Do you have an opinion on the two choices for handling keepalives_idle MH and keepalives_interval? They basically are: MH 1) When not configured, use system defaults. When only one of the two MH parameters configured, use RFC default for the other one (overwrite MH system default). MH 2) When not configured, use RFC defaults (overwrite system defaults). MH When only one of the two parameters configured, use RFC default for MH the other one (overwrite system default) MH 3) When not configured, use system defaults. When only one of the two MH parameters configured, throw error. MH I can see pros and cons with both. Given that I still think *most* MH people will not configure the intervals at all, I think #1 is the one MH that follows principle of least surprise. Perhaps option *3* is the MH one that does this for partial configuration? Frankly speaking I cannot decide what is the best approach. :) It's up to you guys. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for 9.1: WAL streaming from WAL buffers
On Wed, Jun 30, 2010 at 11:26 AM, Robert Haas robertmh...@gmail.com wrote: Maybe. As Heikki pointed out upthread, the standby can't even write the WAL to back to the OS until it's been fsync'd on the master without risking the problem under discussion. If we change the startup process so that it doesn't go ahead of the master's fsync location even after the walreceiver is terminated, we would have no need to worry about that risk. For further robustness, the walreceiver might be able to zero the WAL records which have not been fsync'd on the master yet, when being terminated. But, if the standby crashes after the master crashes, restart of the standby might replay that non-fsync'd WAL wrongly because it cannot remember the master's fsync location. In this case, if we promote the standby to the master, we still don't have to worry about that risk. But instead of performing a failover, if we restart the master and make the standby connect to the master again, the database on the standby would get corrupted. For now, I don't have good idea to avoid that database corruption by the double failure (crash of both master and standby)... So we can stream the WAL from master to standby as long as the standby just buffers it in memory (or somewhere other than the usual location in pg_xlog). Yeah, I was just thinking the same thing. But the problem is that the buffer size might become too big (might be bigger than 16MB). For example, synchronous_commit = off and wal_writer_delay = 1ms on the master would delay the fsync significantly and increase the buffer size on the standby. Before we get too busy frobnicating this gonkulator, I'd like to see a little more discussion of what kind of performance people are expecting from sync rep. Sounds to me like the best we can expect here is, on every commit: (a) wait for master fsync to complete, (b) send message to standby, (c) wait for reply for reply from standby indicating that fsync is complete on standby. Even assuming that the network overhead is minimal, that halves the commit rate. Are the people who want sync rep OK with that? Is there any way to do better? (c) would depend on the synchronization mode the user chooses: #1 Wait for WAL to be received by the standby #2 Wait for WAL to be received and flushed by the standby #3 Wait for WAL to be received, flushed and replayed by the standby (a) would depend on synchronous_commit. Personally I'm interested in disabling synchronous_commit on the master and choosing #1 as the sync mode. Though this may be very optimistic configuration :) The point for performance of sync rep is to parallelize (a) and (b)+(c), I think. If they are performed in a serial manner, the performance overhead on the master would become high. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Server crash while trying to read expression using pg_get_expr()
On 15/06/10 15:19, Florian Pflug wrote: On Jun 15, 2010, at 9:31 , Heikki Linnakangas wrote: You could avoid changing the meaning of fn_expr by putting the check in the parse analysis phase, into transformFuncCall(). That would feel safer at least for back-branches. For 9.0, wouldn't a cleaner way to accomplish this be a seperate type for expressions, say pg_expr, instead of storing them as text? With an input function that unconditionally raises and error and no cast to pg_expr, creating new instances of that type would be impossible for normal users. The output function and casts to text would call pg_get_expr() with zero as the second argument. The internal representation wouldn't change, it's just the type's OID in the catalog that'd be different. Yeah, that would be quite elegant. I think it's too late for 9.0, but something to consider in the future. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cannot cancel the change of a tablespace
On Tue, Jun 29, 2010 at 11:25 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Jun 29, 2010 at 9:42 PM, Bruce Momjian br...@momjian.us wrote: So this is not something we want fixed for 9.0, as indicated by Simon? I don't see the patch on the commit-fest page yet. I tend to think we should fix it for 9.0, but could be talked out of it if someone has a compelling argument to make. Er, maybe I lost count, but I thought you were the one objecting to the patch. No, I just wasn't sure whether it was safe. If it's safe, I'm 100% in favor of applying it and back-patching. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for 9.1: WAL streaming from WAL buffers
On Wed, Jun 30, 2010 at 5:36 AM, Fujii Masao masao.fu...@gmail.com wrote: Before we get too busy frobnicating this gonkulator, I'd like to see a little more discussion of what kind of performance people are expecting from sync rep. Sounds to me like the best we can expect here is, on every commit: (a) wait for master fsync to complete, (b) send message to standby, (c) wait for reply for reply from standby indicating that fsync is complete on standby. Even assuming that the network overhead is minimal, that halves the commit rate. Are the people who want sync rep OK with that? Is there any way to do better? (c) would depend on the synchronization mode the user chooses: #1 Wait for WAL to be received by the standby #2 Wait for WAL to be received and flushed by the standby #3 Wait for WAL to be received, flushed and replayed by the standby (a) would depend on synchronous_commit. Personally I'm interested in disabling synchronous_commit on the master and choosing #1 as the sync mode. Though this may be very optimistic configuration :) The point for performance of sync rep is to parallelize (a) and (b)+(c), I think. If they are performed in a serial manner, the performance overhead on the master would become high. Right. So we to try to come up with a design that permits that, which must be robust in the face of any number of crashes on the two machines, in any order. Until we have that, we're just going around in circles. One thought that occurred to me is that if the master and standby were more tightly coupled, you could recover after a crash by making the one with the further-advanced WAL position the master, and the other one the standby. That would get around this problem, though at the cost of considerable additional complexity. But then if one of the servers comes up and can't talk to the other, you need some mechanism for preventing split-brain syndrome. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Check constraints on non-immutable keys
We currently allow this: postgres=# create table t(a timestamptz not null primary key, check(a now())); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t_pkey for table t CREATE TABLE Which seems very wrong. For one thing, a dump of this database can not be restored if now() has advanced enough into the future (which it will eventually). It also makes impossible to do things like SET a=a on the table. Yes, this is clearly a stupidly defined constraint, but why do we allow it? Shouldn't we disallow anything that's not IMMUTABLE in a check constraint? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Additional startup logging
It seems potentially useful to LOG the version() string in the log file during startup. It might also help to LOG any settings which might result in the loss of committed transactions or in database corruption during startup. (After a crash, the postgresql.conf file might not show the values which were in effect during startup, and it is too late to show the values.) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Magnus Hagander mag...@hagander.net writes: Do you have an opinion on the two choices for handling keepalives_idle and keepalives_interval? They basically are: 1) When not configured, use system defaults. When only one of the two parameters configured, use RFC default for the other one (overwrite system default). 2) When not configured, use RFC defaults (overwrite system defaults). When only one of the two parameters configured, use RFC default for the other one (overwrite system default) 3) When not configured, use system defaults. When only one of the two parameters configured, throw error. It's hard to argue about this when most of us have no idea what these system defaults are, or whether they really are any different from the RFC values in the first place, or whether ordinary users know how to alter them or even find out their values. Please provide some background if you want intelligent comments. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
On Wed, Jun 30, 2010 at 16:27, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Do you have an opinion on the two choices for handling keepalives_idle and keepalives_interval? They basically are: 1) When not configured, use system defaults. When only one of the two parameters configured, use RFC default for the other one (overwrite system default). 2) When not configured, use RFC defaults (overwrite system defaults). When only one of the two parameters configured, use RFC default for the other one (overwrite system default) 3) When not configured, use system defaults. When only one of the two parameters configured, throw error. It's hard to argue about this when most of us have no idea what these system defaults are, or whether they really are any different from the RFC values in the first place, or whether ordinary users know how to alter them or even find out their values. Please provide some background if you want intelligent comments. The system defaults are whatever the user has configured at a machine level (by editing the registry, by hand or by tool (including policies)). I doubt many users have configured them by hand. There may well be tools that do it for them. Anyway, after some checking i realized #3 can't be implemented anyway in the backend, since guc won't let us know early enough. So that's out. Thus, let's go with #2. Which was your suggestion :) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
Magnus Hagander mag...@hagander.net writes: Shouldn't we disallow anything that's not IMMUTABLE in a check constraint? I think you'd get too many howls of pain ... also, such a restriction is likely contrary to SQL spec. The example seems to me to be in the category of so don't do that rather than something that we need to save users from. Yes, it's stupid, but for example the very similar check of a now() would be perfectly safe and arguably useful. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On Wed, Jun 30, 2010 at 16:38, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Shouldn't we disallow anything that's not IMMUTABLE in a check constraint? I think you'd get too many howls of pain ... also, such a restriction is likely contrary to SQL spec. Really? That sounds strange, but I can't claim to k now the SQL spec.. The example seems to me to be in the category of so don't do that rather than something that we need to save users from. Yes, it's In that case, should we at least throw a warning? stupid, but for example the very similar check of a now() would be perfectly safe and arguably useful. Well, things like timezone settings could come in effect there as well... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Magnus Hagander mag...@hagander.net writes: On Wed, Jun 30, 2010 at 16:27, Tom Lane t...@sss.pgh.pa.us wrote: It's hard to argue about this when most of us have no idea what these system defaults are, or whether they really are any different from the RFC values in the first place, or whether ordinary users know how to alter them or even find out their values. Please provide some background if you want intelligent comments. The system defaults are whatever the user has configured at a machine level (by editing the registry, by hand or by tool (including policies)). I doubt many users have configured them by hand. There may well be tools that do it for them. But you previously stated that this code was ignoring the registry values. So doesn't system defaults boil down to whatever Windows' wired-in defaults are? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
On Wed, Jun 30, 2010 at 16:48, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Wed, Jun 30, 2010 at 16:27, Tom Lane t...@sss.pgh.pa.us wrote: It's hard to argue about this when most of us have no idea what these system defaults are, or whether they really are any different from the RFC values in the first place, or whether ordinary users know how to alter them or even find out their values. Please provide some background if you want intelligent comments. The system defaults are whatever the user has configured at a machine level (by editing the registry, by hand or by tool (including policies)). I doubt many users have configured them by hand. There may well be tools that do it for them. But you previously stated that this code was ignoring the registry values. So doesn't system defaults boil down to whatever Windows' wired-in defaults are? The order is Windows wired-in-defaults - registry values - what app chooses. And yes, we *are* ignoring whatever the user has put in the registry, making our path Windows documented-wired-in-defaults - what app chooses if we do this. Windows default for idle is 2 hours, for interval 1 second. Assume the user had reconfigured his default in the registry to 1 hour. If the user makes no config change at all, that means it will run with 1 hour for idle and 1 second for interval. If we now set tcp_interval to 10 seconds (to change the default), we will now also change his idle value back to the system default, so he will get 2 hours for idle and 10 seconds for interval. Thus, we are ignoring the changes he made globally on his system. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: On Wed, Jun 30, 2010 at 16:27, Tom Lane t...@sss.pgh.pa.us wrote: It's hard to argue about this when most of us have no idea what these system defaults are, or whether they really are any different from the RFC values in the first place, or whether ordinary users know how to alter them or even find out their values. ?Please provide some background if you want intelligent comments. The system defaults are whatever the user has configured at a machine level (by editing the registry, by hand or by tool (including policies)). I doubt many users have configured them by hand. There may well be tools that do it for them. But you previously stated that this code was ignoring the registry values. So doesn't system defaults boil down to whatever Windows' wired-in defaults are? For Magnus, #2 was to use the RFC defaults. The OS defaults might be different for different versions of Windows. We could use the OS defaults for _some_ version of Windows, but I am not sure that is an improvement. I still like #1 because it affects the fewest people, and that option uses the RFC defaults only for unset values when others are set. I still think we can do #3 (error), but we have to add a check in an unrelated place to check for unset values, and the code is likely to be ugly. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Magnus Hagander mag...@hagander.net writes: But you previously stated that this code was ignoring the registry values. So doesn't system defaults boil down to whatever Windows' wired-in defaults are? The order is Windows wired-in-defaults - registry values - what app chooses. And yes, we *are* ignoring whatever the user has put in the registry, How does that statement square with your follow-on example? Assume the user had reconfigured his default in the registry to 1 hour. If the user makes no config change at all, that means it will run with 1 hour for idle and 1 second for interval. If we now set tcp_interval to 10 seconds (to change the default), we will now also change his idle value back to the system default, so he will get 2 hours for idle and 10 seconds for interval. Thus, we are ignoring the changes he made globally on his system. With the code as you have it, yes, but if we do it as I'm suggesting, that doesn't happen --- the effective value of the other parameter doesn't change. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
Magnus Hagander mag...@hagander.net writes: On Wed, Jun 30, 2010 at 16:38, Tom Lane t...@sss.pgh.pa.us wrote: The example seems to me to be in the category of so don't do that rather than something that we need to save users from. Yes, it's In that case, should we at least throw a warning? I don't see a reason to do that. If we could distinguish actually problematic cases from safe cases, it would be helpful, but we can't. Moreover, throwing a warning would encourage people to do actively *unsafe* things to suppress the warning --- like marking functions as immutable when they really aren't. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Bruce Momjian br...@momjian.us writes: I still like #1 because it affects the fewest people, and that option uses the RFC defaults only for unset values when others are set. What's your idea of affecting the fewest people? There is no previous history to be backward-compatible with, because we never supported keepalive on Windows before. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I still like #1 because it affects the fewest people, and that option uses the RFC defaults only for unset values when others are set. What's your idea of affecting the fewest people? There is no previous history to be backward-compatible with, because we never supported keepalive on Windows before. Well, starting in 9.0, keepalives in libpq will default to 'on': Controls whether client-side TCP keepalives are used. The default value is 1, meaning on, but you can change this to 0, meaning off, if keepalives are not wanted. This parameter is ignored for connections made via a Unix-domain socket. My definition is whether we should affect keepalive behavior for the 99% of people who do not change the libpq defaults, meaning the other keepalive settings. #2 would cause these people to use non-registry-controlled keepalive behavior by using RFC defaults, and even if we use Windows defaults, those defaults might be different for different Windows versions. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: What's your idea of affecting the fewest people? There is no previous history to be backward-compatible with, because we never supported keepalive on Windows before. Well, starting in 9.0, keepalives in libpq will default to 'on': Yes, which is already a change in behavior. I don't understand why you are worrying about backwards compatibility to parameter values that weren't in use before. I think self-consistency of the new version is far more important than that. even if we use Windows defaults, those defaults might be different for different Windows versions. I'm not sure if that's an issue or not, but if it is, that seems to me to argue for #2 not #1. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Magnus Hagander mag...@hagander.net wrote: Windows default for idle is 2 hours, for interval 1 second. And it defaults to five retries. With these settings, you could have a TCP connection break with as little as a five second network outage, if it happened to come after two hours of silence on the connection; although an outage of up to two hours could go totally unnoticed. The RFC values have a total of nine tries at 75 second intervals, so for a single network outage to break a connection, it would have to last at least ten minutes; but again, an outage of up to two hours could occur before it started to check for problems. I'm inclined toward option 2 (previously described on this thread), because the Windows defaults are dumb. Wait two hours and then test for five seconds??? I also think we may want to suggest that for most environments, people may want to change these settings to something more aggressive, like a 30 to 120 second initial delay, with a 10 or 20 second retry interval. The RFC defaults seem approximately right for a TCP connection to a colony on the surface of the moon, where besides the round trip latency of 2.5 seconds they might have to pay by the byte. In other words, it is *so* conservative that I have trouble seeing it ever causing a problem compared to not having keepalive enabled, but it will eventually clean things up. In practice people usually want something more aggressive. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On Wed, Jun 30, 2010 at 11:02 AM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Wed, Jun 30, 2010 at 16:38, Tom Lane t...@sss.pgh.pa.us wrote: The example seems to me to be in the category of so don't do that rather than something that we need to save users from. Yes, it's In that case, should we at least throw a warning? I don't see a reason to do that. If we could distinguish actually problematic cases from safe cases, it would be helpful, but we can't. Moreover, throwing a warning would encourage people to do actively *unsafe* things to suppress the warning --- like marking functions as immutable when they really aren't. My scintillating contribution to this discussion is the observation that unrestorable dumps suck. A lot. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: What's your idea of affecting the fewest people? There is no previous history to be backward-compatible with, because we never supported keepalive on Windows before. Well, starting in 9.0, keepalives in libpq will default to 'on': Yes, which is already a change in behavior. I don't understand why you are worrying about backwards compatibility to parameter values that weren't in use before. I think self-consistency of the new version is far more important than that. I am worried about compatibility/consistency with other Windows processes. even if we use Windows defaults, those defaults might be different for different Windows versions. I'm not sure if that's an issue or not, but if it is, that seems to me to argue for #2 not #1. I assume if someone modified the registry, they want it to be used for all applications that use keepalives on their system. Also, keep in mind that, unlike the backend, which has postgresql.conf, it is burdensome to set a libpq setting for all applications (without using pg_service.conf). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Kevin Grittner kevin.gritt...@wicourts.gov writes: I also think we may want to suggest that for most environments, people may want to change these settings to something more aggressive, like a 30 to 120 second initial delay, with a 10 or 20 second retry interval. The RFC defaults seem approximately right for a TCP connection to a colony on the surface of the moon, where besides the round trip latency of 2.5 seconds they might have to pay by the byte. Well, the RFCs were definitely written at a time when bandwidth was a lot more expensive than it is today. In other words, it is *so* conservative that I have trouble seeing it ever causing a problem compared to not having keepalive enabled, but it will eventually clean things up. Yes. This is a large part of the reason why I think it's okay for us to turn libpq keepalive on by default in 9.0 --- the default parameters for it are so conservative as to be unlikely to cause trouble. If Windows isn't using RFC-equivalent default parameters, that seems like a good reason to disregard the system settings and force use of the RFC values as defaults. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
Robert Haas robertmh...@gmail.com writes: My scintillating contribution to this discussion is the observation that unrestorable dumps suck. No doubt, but is this a real problem in practice? I can't recall many field complaints about it. And the ones I do recall wouldn't have been prevented by a check as stupid as are there immutable functions in here. I still say that what such a check is likely to do is encourage people to mis-label mutable functions as immutable ... which will cause them a lot of *other* headaches. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On Wed, Jun 30, 2010 at 11:49 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: My scintillating contribution to this discussion is the observation that unrestorable dumps suck. No doubt, but is this a real problem in practice? Magnus tells me that that was what prompted his original email. I can't recall many field complaints about it. And the ones I do recall wouldn't have been prevented by a check as stupid as are there immutable functions in here. Hopefully there aren't too many ways to get data into a table that doesn't satisfy its check constraint - what else are you thinking of? Short of direct system catalog manipulation with malice aforethought, redefining a function to return different results after the fact is the only other case I can think of, and I'd propose we block that somehow too if I could figure out how. I still say that what such a check is likely to do is encourage people to mis-label mutable functions as immutable ... which will cause them a lot of *other* headaches. If it does, those headaches are their fault, whereas this one, at least as I see it, is our fault. The fact that you can injure yourself badly with a sharp knife is not an excuse for someone to hand it to you pointy-end-first. I think it would be useful to have check constraints that are only enforced on new data, and allowing immutable functions there would make sense. But I can't think of any reasonable use case for having a non-immutable check constraint of the type we have now. Can you? Besides breaking pg_dump, it can also potentially foul up constraint exclusion. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On 30/06/10 17:11, Robert Haas wrote: On Wed, Jun 30, 2010 at 11:49 AM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haasrobertmh...@gmail.com writes: My scintillating contribution to this discussion is the observation that unrestorable dumps suck. No doubt, but is this a real problem in practice? Magnus tells me that that was what prompted his original email. I've done it. Luckily only with a small and fully functioning database so I could drop the constraint and re-dump it. Had a recent_date domain that was making sure new diary-style entries had a plausible date. Of course, two years later my dump can no longer restore the oldest record :-( IMHO The real solution would be something that could strip/rewrite the constraint on restore rather than trying to prevent people being stupid though. People *will* just tag their functions as immutable to get them to work. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0beta2 - server crash when using HS + SR
Fujii Masao wrote:
On Mon, Jun 14, 2010 at 9:16 AM, Greg Smith g...@2ndquadrant.com wrote:
I wouldn't be adverse to improving the error messages emitted when this
happens by the server to make it more obvious what's gone wrong in 9.1.
?That's the only genuine improvement I'd see value in here, to cut down on
other people running into what you did and being as confused by it.
What about the attached patch? When we encounter that problem, we get
the following hint message:
FATAL: directory /path_to/ts does not exist
HINT: create /path_to/ts directory for tablespace before
restarting the server
CONTEXT: xlog redo create ts: 16384 /path_to/ts
This is an interesting patch idea. One problem with the patch is that
create_tablespace_directories() is called both during recovery and when
creating a tablespace, and the hint only makes sense in the first case.
The attached patch shows the hint only during recovery. Unless there
are objections, I will apply this for 9.0. I do think people will be
hit by this more often in 9.0.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Index: src/backend/commands/tablespace.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/tablespace.c,v
retrieving revision 1.74
diff -c -c -r1.74 tablespace.c
*** src/backend/commands/tablespace.c 26 Feb 2010 02:00:39 - 1.74
--- src/backend/commands/tablespace.c 30 Jun 2010 17:08:42 -
***
*** 85,91
static void create_tablespace_directories(const char *location,
! const Oid tablespaceoid);
static bool destroy_tablespace_directories(Oid tablespaceoid, bool redo);
--- 85,91
static void create_tablespace_directories(const char *location,
! const Oid tablespaceoid, const bool in_recovery);
static bool destroy_tablespace_directories(Oid tablespaceoid, bool redo);
***
*** 333,339
/* Record dependency on owner */
recordDependencyOnOwner(TableSpaceRelationId, tablespaceoid, ownerId);
! create_tablespace_directories(location, tablespaceoid);
/* Record the filesystem change in XLOG */
{
--- 333,339
/* Record dependency on owner */
recordDependencyOnOwner(TableSpaceRelationId, tablespaceoid, ownerId);
! create_tablespace_directories(location, tablespaceoid, false);
/* Record the filesystem change in XLOG */
{
***
*** 533,539
* to the specified directory
*/
static void
! create_tablespace_directories(const char *location, const Oid tablespaceoid)
{
char *linkloc = palloc(OIDCHARS + OIDCHARS + 1);
char *location_with_version_dir = palloc(strlen(location) + 1 +
--- 533,540
* to the specified directory
*/
static void
! create_tablespace_directories(const char *location, const Oid tablespaceoid,
! const bool in_recovery)
{
char *linkloc = palloc(OIDCHARS + OIDCHARS + 1);
char *location_with_version_dir = palloc(strlen(location) + 1 +
***
*** 550,559
if (chmod(location, 0700) != 0)
{
if (errno == ENOENT)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_FILE),
! errmsg(directory \%s\ does not exist,
! location)));
else
ereport(ERROR,
(errcode_for_file_access(),
--- 551,568
if (chmod(location, 0700) != 0)
{
if (errno == ENOENT)
! {
! if (!in_recovery)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_FILE),
! errmsg(directory \%s\ does not exist, location)));
! else
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_FILE),
! errmsg(directory \%s\ does not exist, location),
! errhint(create \%s\ directory for tablespace before
! restarting the server, location)));
! }
else
ereport(ERROR,
(errcode_for_file_access(),
***
*** 1359,1365
xl_tblspc_create_rec *xlrec = (xl_tblspc_create_rec *) XLogRecGetData(record);
char *location = xlrec-ts_path;
! create_tablespace_directories(location, xlrec-ts_id);
}
else if (info == XLOG_TBLSPC_DROP)
{
--- 1368,1374
xl_tblspc_create_rec *xlrec = (xl_tblspc_create_rec *) XLogRecGetData(record);
char *location = xlrec-ts_path;
! create_tablespace_directories(location, xlrec-ts_id, true);
}
else if (info == XLOG_TBLSPC_DROP)
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
Robert Haas robertmh...@gmail.com writes: On Wed, Jun 30, 2010 at 11:49 AM, Tom Lane t...@sss.pgh.pa.us wrote: I can't recall many field complaints about it. And the ones I do recall wouldn't have been prevented by a check as stupid as are there immutable functions in here. Hopefully there aren't too many ways to get data into a table that doesn't satisfy its check constraint - what else are you thinking of? Nobody is talking about having bypassed a check constraint --- the problem here is what if the same constraint condition is true today and false tomorrow. The cases that I can recall were not directly about time passing, but rather about check constraints that were designed to examine the contents of other tables or other rows in the same table. Functions that do that are properly declared STABLE not VOLATILE, but they'd still be rejected by Magnus' proposed restriction. The problem is that people would be *very* likely to just mark them IMMUTABLE rather than understand that what they're trying is fundamentally unreliable. That would cause them other problems, and they'd still be at risk of their dumps not reloading. I concur with the thought that the most useful solution might be a way to tell pg_restore to remove or disable check constraints. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On 30/06/10 18:11, Magnus Hagander wrote: On Wed, Jun 30, 2010 at 18:33, Richard Huxtond...@archonet.com wrote: IMHO The real solution would be something that could strip/rewrite the constraint on restore rather than trying to prevent people being stupid though. People *will* just tag their functions as immutable to get them to work. Are you sure? The people most likely to just tag their functions as immutable, are the same ones most unlikely to know *how to do that*. At least for what I think is the majority case - which is calling builtin functions. People just cut and paste this stuff from ancient blog entries. Understanding is not necessary. Hell, I do it sometimes if I'm dealing with something like LDAP where I don't really have a deep knowledge of the situation. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On Wed, Jun 30, 2010 at 18:33, Richard Huxton d...@archonet.com wrote: On 30/06/10 17:11, Robert Haas wrote: On Wed, Jun 30, 2010 at 11:49 AM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haasrobertmh...@gmail.com writes: My scintillating contribution to this discussion is the observation that unrestorable dumps suck. No doubt, but is this a real problem in practice? Magnus tells me that that was what prompted his original email. I've done it. Luckily only with a small and fully functioning database so I could drop the constraint and re-dump it. Had a recent_date domain that was making sure new diary-style entries had a plausible date. Of course, two years later my dump can no longer restore the oldest record :-( IMHO The real solution would be something that could strip/rewrite the constraint on restore rather than trying to prevent people being stupid though. People *will* just tag their functions as immutable to get them to work. Are you sure? The people most likely to just tag their functions as immutable, are the same ones most unlikely to know *how to do that*. At least for what I think is the majority case - which is calling builtin functions. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0beta2 - server crash when using HS + SR
Bruce Momjian br...@momjian.us writes: FATAL: directory /path_to/ts does not exist HINT: create /path_to/ts directory for tablespace before restarting the server CONTEXT: xlog redo create ts: 16384 /path_to/ts This is an interesting patch idea. One problem with the patch is that create_tablespace_directories() is called both during recovery and when creating a tablespace, and the hint only makes sense in the first case. Please make the hint conform to the project message style guidelines. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0beta2 - server crash when using HS + SR
Bruce Momjian br...@momjian.us writes: The attached patch shows the hint only during recovery. BTW, it would be easier and more consistent with the rest of the code to look at InRecovery, instead of messing around with the function signature. And the usual way to emit a hint conditionally is (InRecovery ? errhint(...) : 0) rather than duplicate a lot of surrounding code. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On Wed, Jun 30, 2010 at 19:16, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Jun 30, 2010 at 11:49 AM, Tom Lane t...@sss.pgh.pa.us wrote: I can't recall many field complaints about it. And the ones I do recall wouldn't have been prevented by a check as stupid as are there immutable functions in here. Hopefully there aren't too many ways to get data into a table that doesn't satisfy its check constraint - what else are you thinking of? Nobody is talking about having bypassed a check constraint --- the problem here is what if the same constraint condition is true today and false tomorrow. The cases that I can recall were not directly about time passing, but rather about check constraints that were designed to examine the contents of other tables or other rows in the same table. Functions that do that are properly declared STABLE not VOLATILE, but they'd still be rejected by Magnus' proposed restriction. The problem is that people would be *very* likely to just mark them IMMUTABLE rather than understand that what they're trying is fundamentally unreliable. That would cause them other problems, and they'd still be at risk of their dumps not reloading. I concur with the thought that the most useful solution might be a way to tell pg_restore to remove or disable check constraints. Uh, say what? Are you saying pg_restore should actually remove something from the database schema? And thus no longer be valid for taking database backups? Or are you just saying that it should have the constraints off, load the data, and then somehow create the constraint without having it validate the exinsting data (like the NOCHECK option in MSSQL?) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On Wed, Jun 30, 2010 at 9:47 AM, Magnus Hagander mag...@hagander.net wrote: We currently allow this: postgres=# create table t(a timestamptz not null primary key, check(a now())); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t_pkey for table t CREATE TABLE Which seems very wrong. For one thing, a dump of this database can not be restored if now() has advanced enough into the future (which it will eventually). It also makes impossible to do things like SET a=a on the table. Yes, this is clearly a stupidly defined constraint, but why do we allow it? Shouldn't we disallow anything that's not IMMUTABLE in a check constraint? suppose you did do this: shouldn't you then also recheck the constraint if the function is create/replaced? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
Magnus Hagander mag...@hagander.net writes: On Wed, Jun 30, 2010 at 19:16, Tom Lane t...@sss.pgh.pa.us wrote: I concur with the thought that the most useful solution might be a way to tell pg_restore to remove or disable check constraints. Uh, say what? Are you saying pg_restore should actually remove something from the database schema? And thus no longer be valid for taking database backups? pg_restore, not pg_dump. It's no more unreasonable an idea than the current pg_restore options for selective restores, AFAICS. You can already cause pg_restore to not restore PK and FK constraints, for example, so why not check constraints? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Server crash while trying to read expression using pg_get_expr()
On 23/06/10 21:36, Robert Haas wrote: On Mon, Jun 21, 2010 at 7:50 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 15/06/10 10:31, Heikki Linnakangas wrote: You could avoid changing the meaning of fn_expr by putting the check in the parse analysis phase, into transformFuncCall(). That would feel safer at least for back-branches. Here's a patch using that approach. I grepped through PostgreSQL and pgadmin source code to find the system columns where valid node-strings are stored: pg_index.indexprs pg_index.indprep pg_attrdef.adbin pg_proc.proargdefaults pg_constraint.conbin Am I missing anything? I think that pg_type.typdefaultbin is used by pg_dump. Yep, added that. pg_rewrite.ev_qual, pg_rewrite.ev_action, pg_trigger.tgqual also contain nodeToString() output but I didn't have any luck using them with pg_get_expr() so maybe they don't need to be included. I left them out. The only other thing I notice is that, obviously, the FIXME comment needs to be FIXMEd before commit. Fixed. I'd still be in favor of inserting at least some basic error checks into readfuncs.c, though just in HEAD. The restrictions implemented here seem adequate to prevent a security vulnerability, but superusers can still invoke those functions manually, and while superusers can clearly crash the system in any number of ways, that doesn't seem (to me) like an adequate justification for ignoring the return value of strtok(). YMMV, of course. Agreed. I'll do that as a separate patch. Thanks for the review! -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On Wed, Jun 30, 2010 at 20:13, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Wed, Jun 30, 2010 at 19:16, Tom Lane t...@sss.pgh.pa.us wrote: I concur with the thought that the most useful solution might be a way to tell pg_restore to remove or disable check constraints. Uh, say what? Are you saying pg_restore should actually remove something from the database schema? And thus no longer be valid for taking database backups? pg_restore, not pg_dump. It's no more unreasonable an idea than the current pg_restore options for selective restores, AFAICS. You can already cause pg_restore to not restore PK and FK constraints, for example, so why not check constraints? Oh, sorry, I misread that - I thought you suggested it would do so by default. Clearly, I should've left work about 2 minutes earlier and not bothered you with that response :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Admission Control
On Fri, 2010-06-25 at 13:10 -0700, Josh Berkus wrote: The problem with centralized resource control We should talk about the problem of lack of centralized resource control as well, to balance. Another well observed problem is that work_mem is user settable, so many programs acting together with high work_mem can cause swapping. The reality is that inefficient resource control leads to about 50% resource wastage. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Keeping separate WAL segments for each database
I talked to Sybase people about their latest technologies. Many features that shipped with their latest ASE 15.5 product has been in PostgreSQL since many years (like functional indexes, etc). :) One of the things that interested me was parallel recovery feature. They said that they are keeping separate xlogs for each database, which speeds ups recovery in case of a crash. It also would increase performance, since we could write xlogs to separate disks. Is that doable for PostgreSQL, too? -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Keepalive for max_standby_delay
On Mon, 2010-06-28 at 10:09 -0700, Josh Berkus wrote: It will get done. It is not the very first thing on my to-do list. ??? What is then? If it's not the first thing on your priority list, with 9.0 getting later by the day, maybe we should leave it to Robert and Simon, who *do* seem to have it first on *their* list? I swear, when Simon was keeping his branch to himself in August everyone was on his case. It sure seems like Tom is doing exactly the same thing. Hmmm, yes, looks that way. At that time I was actively working on the code, not just locking it to prevent other activity. The only urgency on my part here was to fulfil my responsibility to the project. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keeping separate WAL segments for each database
Excerpts from Devrim GÜNDÜZ's message of mié jun 30 14:54:06 -0400 2010: One of the things that interested me was parallel recovery feature. They said that they are keeping separate xlogs for each database, which speeds ups recovery in case of a crash. It also would increase performance, since we could write xlogs to separate disks. I'm not sure about this. You'd need to have one extra WAL stream, for shared catalogs; and what would you do to a transaction that touches both shared catalogs and also local objects? You'd have to split the WAL entries in those two WAL streams. I think you could try to solve this by having yet another WAL stream for transaction commit, and have the database-specific streams reference that one. Operations touching shared catalogs would act as barriers: all other databases' WAL streams would have to be synchronized to that one. This would still allow you to have some concurrency because, presumably, operations on shared catalogs are rare. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Issue: Deprecation of the XML2 module 'xml_is_well_formed' function
Excerpts from David Fetter's message of lun jun 28 12:00:47 -0400 2010: While tracking this down, I didn't see a way to get SQLSTATE or the corresponding condition name via psql. Is this an oversight? A bug, perhaps? IIRC \pset VERBOSITY verbose to get the SQLSTATE. I don't think you can get the condition name that way, though. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalive for max_standby_delay
Simon Riggs wrote: On Mon, 2010-06-28 at 10:09 -0700, Josh Berkus wrote: It will get done. It is not the very first thing on my to-do list. ??? What is then? If it's not the first thing on your priority list, with 9.0 getting later by the day, maybe we should leave it to Robert and Simon, who *do* seem to have it first on *their* list? I swear, when Simon was keeping his branch to himself in August everyone was on his case. It sure seems like Tom is doing exactly the same thing. Hmmm, yes, looks that way. At that time I was actively working on the code, not just locking it to prevent other activity. The only urgency on my part here was to fulfil my responsibility to the project. Simon, you have a very legitimate concern. I phoned Tom and he is planning to start working on the max_standby_delay tomorrow. I am unclear how it is different from your version, but I hope once Tom is done we can review his work and decide how to proceed. The fact that we allowed Tom this huge amount of time to submit an alternative patch is unusual and hopefully rare. FYI, Tom and I are hoping to work through all the outstanding issues before we package up 9.0 beta3 on Thursday, July 8. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LLVM / clang
On Fri, 25 Jun 2010 15:49:40 -0400, Peter Eisentraut pete...@gmx.net wrote: For the record, here is a patch that would address these issues. At the moment, I'm waiting to get my hands on the new version 2.7 of clang to see if some of these issues have gone away. Considering that clang already helped us find one bug in the code, I think it's worth trying to make this work. I tried your patch, but it is only working, when I set CLANG=yes. As I'm not really an expert in makefiles, my first thought was, that it should work, when I set CC=clang or is it not possible to detect, which compiler is used? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error with GIT Repository
On Fri, Jun 11, 2010 at 10:19 AM, Magnus Hagander mag...@hagander.net wrote: Especially if someone has a clue how to do it. The last time I fixed it by runnin repack, but that didn't work this time. I have no clue why it's asking for a file that doesn't exist. Does the repo run 'update-server-info' in some hook? Yup, it runs after every time it pulls from cvs. Is this still a problem? I was just noticing this thread unceremoniously died, and a long time ago now I remembering discussing a problem involving the Postgres git mirror accumulating packfiles eternally. It seemed that whatever repacking scheme was used would get rid of loose objects, turning them into packs but never consolidate packs. Why not just run 'git gc'? This is probably the only quasi-regularly required maintenance command, so much so that git (I think) runs it from time to time when certain thresholds are passed in modern day. (For a clone-source it is probably a good idea to run it a bit more liberally) fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On ons, 2010-06-30 at 10:38 -0400, Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: Shouldn't we disallow anything that's not IMMUTABLE in a check constraint? I think you'd get too many howls of pain ... also, such a restriction is likely contrary to SQL spec. kibo The search condition shall simply contain a boolean value expression that is retrospectively deterministic. This is then defined in a rather complex manner that ends up disallowing col now() but allowing col now(). /kibo -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LLVM / clang
On ons, 2010-06-30 at 20:10 +0200, Gibheer wrote: On Fri, 25 Jun 2010 15:49:40 -0400, Peter Eisentraut pete...@gmx.net wrote: For the record, here is a patch that would address these issues. At the moment, I'm waiting to get my hands on the new version 2.7 of clang to see if some of these issues have gone away. Considering that clang already helped us find one bug in the code, I think it's worth trying to make this work. I tried your patch, but it is only working, when I set CLANG=yes. As I'm not really an expert in makefiles, my first thought was, that it should work, when I set CC=clang or is it not possible to detect, which compiler is used? I suspect you didn't run autoreconf. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keeping separate WAL segments for each database
2010/6/30 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Devrim GÜNDÜZ's message of mié jun 30 14:54:06 -0400 2010: One of the things that interested me was parallel recovery feature. They said that they are keeping separate xlogs for each database, which speeds ups recovery in case of a crash. It also would increase performance, since we could write xlogs to separate disks. I'm not sure about this. You'd need to have one extra WAL stream, for shared catalogs; and what would you do to a transaction that touches both shared catalogs and also local objects? You'd have to split the WAL entries in those two WAL streams. I think you could try to solve this by having yet another WAL stream for transaction commit, and have the database-specific streams reference that one. Operations touching shared catalogs would act as barriers: all other databases' WAL streams would have to be synchronized to that one. This would still allow you to have some concurrency because, presumably, operations on shared catalogs are rare. I think one per database and one extra one for the shared catalogs would be enough. Most transactions would either touch either just the database, or just the shared catalogs, so you'd write the commit record in whichever stream was appropriate. If you had a transaction that touched both, you'd write the commit record in both places, and include in each stream a reference to the other stream. On replay, when you reach a commit record that references the another stream, you pause until the reference stream also reaches the matching commit record. If you reach the end of that WAL stream without finding the commit record, then, in archive recovery, you just keep waiting for more of the stream to arrive; and, in crash recovery, you write a matching commit record at the end of WAL. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
Peter Eisentraut pete...@gmx.net writes: kibo The search condition shall simply contain a boolean value expression that is retrospectively deterministic. This is then defined in a rather complex manner that ends up disallowing col now() but allowing col now(). /kibo Oh, cute. Seems to have been added in SQL:2003. I guess somebody nagged them about wanting to be able to write CHECK(col = now()). The detailed definition is amazingly laborious and yet limited, though, as it basically doesn't address the problem except for that specific case and close relatives. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On Wed, Jun 30, 2010 at 6:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: kibo The search condition shall simply contain a boolean value expression that is retrospectively deterministic. This is then defined in a rather complex manner that ends up disallowing col now() but allowing col now(). /kibo Oh, cute. Seems to have been added in SQL:2003. I guess somebody nagged them about wanting to be able to write CHECK(col = now()). The detailed definition is amazingly laborious and yet limited, though, as it basically doesn't address the problem except for that specific case and close relatives. Well, solving the problem in general is equivalent to the halting problem, so... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keeping separate WAL segments for each database
Robert Haas robertmh...@gmail.com writes: I think one per database and one extra one for the shared catalogs would be enough. Most transactions would either touch either just the database, or just the shared catalogs, so you'd write the commit record in whichever stream was appropriate. If you had a transaction that touched both, you'd write the commit record in both places, and include in each stream a reference to the other stream. On replay, when you reach a commit record that references the another stream, you pause until the reference stream also reaches the matching commit record. If you reach the end of that WAL stream without finding the commit record, then, in archive recovery, you just keep waiting for more of the stream to arrive; and, in crash recovery, you write a matching commit record at the end of WAL. Surely you'd have to roll back, not commit, in that situation. You have no excuse for assuming that you've replayed all effects of the transaction. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keeping separate WAL segments for each database
2010/6/30 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: I think one per database and one extra one for the shared catalogs would be enough. Most transactions would either touch either just the database, or just the shared catalogs, so you'd write the commit record in whichever stream was appropriate. If you had a transaction that touched both, you'd write the commit record in both places, and include in each stream a reference to the other stream. On replay, when you reach a commit record that references the another stream, you pause until the reference stream also reaches the matching commit record. If you reach the end of that WAL stream without finding the commit record, then, in archive recovery, you just keep waiting for more of the stream to arrive; and, in crash recovery, you write a matching commit record at the end of WAL. Surely you'd have to roll back, not commit, in that situation. You have no excuse for assuming that you've replayed all effects of the transaction. Hmm, good point. But you could make it work either way, I think. If you flush WAL stream A, write commit record to WAL stream B, flush WAL stream B, write commit record to WAL stream A, then commit is correct. If you write commit record to A, flush A, write commit record to B, flush B, then abort is correct. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
mag...@hagander.net (Magnus Hagander) writes: I concur with the thought that the most useful solution might be a way to tell pg_restore to remove or disable check constraints. Uh, say what? Are you saying pg_restore should actually remove something from the database schema? And thus no longer be valid for taking database backups? Or are you just saying that it should have the constraints off, load the data, and then somehow create the constraint without having it validate the exinsting data (like the NOCHECK option in MSSQL?) Well, consider the way that foreign keys are handled by pg_dump. - Initially, it dumps out the table schema, NOT replete with foreign key constraints. - Data is loaded, *without* checking foreign keys. - Foreign keys are added in, afterwards. That's not a scenario where constraints are ignored - their evaluation is merely deferred. For constraints that involve dates, I can certainly see a potential for foot guns. It points me towards making sure that our apps don't do over-aggressive things like having constraints to prevent data from being inserted back-dated, as that would cause restores of backups to break. That's a dangerous kind of constraint. It's *possible* that it would be an idea to apply the check constraint late in the pg_dump, so that the ill effects might be imagined to be alleviated. That seems rather wishful. -- output = (cbbrowne @ gmail.com) http://linuxdatabases.info/info/languages.html HEADLINE: Suicidal twin kills sister by mistake! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Admission Control
On 29/06/10 05:36, Josh Berkus wrote: Having tinkered with it, I'll tell you that (2) is actually a very hard problem, so any solution we implement should delay as long as possible in implementing (2). In the case of Greenplum, what Mark did originally IIRC was to check against the global memory pool for each work_mem allocation. This often resulted in 100's of global locking checks per query ... like I said, feasible for DW, not for OLTP. Actually only 1 lock check per query, but certainly extra processing and data structures to maintain the pool information... so, yes certainly much more suitable for DW (AFAIK we never attempted to measure the additional overhead for non DW workload). Cheers Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keeping separate WAL segments for each database
Robert Haas robertmh...@gmail.com writes: 2010/6/30 Tom Lane t...@sss.pgh.pa.us: Surely you'd have to roll back, not commit, in that situation. You have no excuse for assuming that you've replayed all effects of the transaction. Hmm, good point. But you could make it work either way, I think. If you flush WAL stream A, write commit record to WAL stream B, flush WAL stream B, write commit record to WAL stream A, then commit is correct. I don't think so. I flushed this is not equivalent to it is certain that it will be possible to read this again. In particular, corruption of WAL stream A leaves you in trouble if you take the commit on B as a certificate for stream A being complete. (thinks for a bit...) Maybe if the commit record on B included a minimum stopping point for stream A, it'd be all right. This wouldn't be exactly the expected LSN of the A commit record, mind you, because you don't want to block insertions into the A stream while you're flushing B. But it would say that all non-commit records for the xact on stream A are known to be before that point. If you've replayed A that far then you can take the transaction as being committable. (thinks some more...) No, you still lose, because a commit record isn't just a single bit. What about subtransactions for example? I guess maybe the commit record written/flushed first is the real commit record with all the auxiliary data, and the one written second isn't so much a commit record as a fencepoint record to prevent advancing beyond that point in stream A before you've processed the relevant commit from B. (thinks some more...) Maybe you don't even need the fencepoint record per se. I think all it's doing for you is making sure you don't process commit records on different streams out-of-order. There might be some other, more direct way to do that. (thinks yet more...) Actually the weak point in this scheme is that it wouldn't serialize transactions that occur in different databases and don't touch any shared catalogs. It'd be entirely possible for T1 in DB1 to be reported committed, then T2 in DB2 to be reported committed, then a crash occurs after which T2 is seen committed and T1 not. While this would be all right if the clients for T1 and T2 can't communicate, that isn't the real world. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0beta2 - server crash when using HS + SR
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: The attached patch shows the hint only during recovery. BTW, it would be easier and more consistent with the rest of the code to look at InRecovery, instead of messing around with the function signature. And the usual way to emit a hint conditionally is (InRecovery ? errhint(...) : 0) rather than duplicate a lot of surrounding code. Thanks for the hints. I was thinking there was a way to use ? : for the hint, but couldn't find an example. I see examples now. Updated patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: src/backend/commands/tablespace.c === RCS file: /cvsroot/pgsql/src/backend/commands/tablespace.c,v retrieving revision 1.74 diff -c -c -r1.74 tablespace.c *** src/backend/commands/tablespace.c 26 Feb 2010 02:00:39 - 1.74 --- src/backend/commands/tablespace.c 30 Jun 2010 22:45:21 - *** *** 552,559 if (errno == ENOENT) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_FILE), ! errmsg(directory \%s\ does not exist, ! location))); else ereport(ERROR, (errcode_for_file_access(), --- 552,560 if (errno == ENOENT) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_FILE), ! errmsg(directory \%s\ does not exist, location), ! InRecovery ? errhint(Create directory \%s\ for this tablespace before ! restarting the server., location) : 0)); else ereport(ERROR, (errcode_for_file_access(), -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
Robert Haas robertmh...@gmail.com writes: On Wed, Jun 30, 2010 at 6:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: The detailed definition is amazingly laborious and yet limited, though, as it basically doesn't address the problem except for that specific case and close relatives. Well, solving the problem in general is equivalent to the halting problem, so... So is proving determinism. They had the sense to *not* try to define what that means. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keeping separate WAL segments for each database
2010/6/30 Tom Lane t...@sss.pgh.pa.us: (thinks some more...) Maybe you don't even need the fencepoint record per se. I think all it's doing for you is making sure you don't process commit records on different streams out-of-order. There might be some other, more direct way to do that. (thinks yet more...) Actually the weak point in this scheme is that it wouldn't serialize transactions that occur in different databases and don't touch any shared catalogs. It'd be entirely possible for T1 in DB1 to be reported committed, then T2 in DB2 to be reported committed, then a crash occurs after which T2 is seen committed and T1 not. While this would be all right if the clients for T1 and T2 can't communicate, that isn't the real world. Eh? If T1 and T2 are both reported committed, then they'll still be committed after crash recovery, assuming synchronous_commit is turned on. If not, our ACID has no D. Still, I suspect you're right that there are serialization anomalies buried in here somewhere that can't happen today. And at any rate, the per-database thing isn't really the design goal, anyway. It would be much nicer if we could find a way to support N1 WAL streams without requiring that they be segregated by database. We'd like to be able to write WAL faster, and commit faster, during normal operation, and recover more quickly during recovery, especially archive recovery. You need to make sure not only that you replay commit records in order, but also that, for example, you don't replay an XLOG_HEAP2_CLEAN record too early. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keeping separate WAL segments for each database
On 06/30/2010 05:52 PM, Robert Haas wrote: And at any rate, the per-database thing isn't really the design goal, anyway. FWIW, I've run into more than one client where PITR and/or warm standby on a per-database level would be a killer feature. Joe signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Keeping separate WAL segments for each database
Robert Haas robertmh...@gmail.com writes: You need to make sure not only that you replay commit records in order, but also that, for example, you don't replay an XLOG_HEAP2_CLEAN record too early. Hm, good point. That probably means that you *do* need fencepost records, and furthermore that you might need an interlock to ensure that you get the fencepost in early enough on the other stream. Ugh --- there goes your concurrency. What about having a single WAL stream for all commit records (thereby avoiding any possible xact-serialization funnies) and other WAL records divided up among multiple streams in some fashion or other? A commit record would bear minimum-LSN pointers for all the streams that its transaction had written to. Things like HEAP_CLEAN records would bear minimum-LSN pointers for the commit stream. Workable? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] server authentication over Unix-domain sockets
On tis, 2010-06-22 at 09:37 +0900, KaiGai Kohei wrote:
As you described at the source code comments as follows,
it is not portable except for Linux due to the getsockopt() API.
+ // TODO: currently Linux-only code, needs to be made
+ // portable; see backend/libpq/auth.c
I expect it shall be fixed (using the code come from ident_unix()?)
before committing.
Updated patch attached.
Note that the code that gets the user ID from the other end of a socket
appears to have two different modes of operation. On some platforms
(Linux, OpenBSD, Solaris), you call a function and get the answer. On
some other platforms (other BSDs?), you need to send a packet and read
the answer. I don't have any possibility to test the latter approach,
and it seemed a bit complicated to code blindly. So I have omitted
support for that, but if someone else wants to do the porting, that is
of course possible.
I'd like to point out one other point.
It uses getpwuid() to translate a user identifier into a user name,
but it returns a pointer of the static variable within glibc.
So, it is not thread-safe. I recommend to use getpwnam_r() instead.
Good catch. pqGetpwuid() was actually the right function to use.
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 8f0a9cf..6a811c5 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -503,6 +503,28 @@
/listitem
/varlistentry
+varlistentry id=libpq-connect-requirepeer xreflabel=requirepeer
+ termliteralrequirepeer/literal/term
+ listitem
+ para
+ For Unix-domain socket connections, if this parameter is
+ set, the client checks at the beginning of the connection
+ that the server process runs under the specified user name,
+ otherwise the connection is aborted with an error. This
+ parameter can be used to achieve the kind of server
+ authentication that SSL certificates achieve on TCP/IP
+ connections. (Note that if the Unix-domain socket is
+ in filename/tmp/filename or another publically writable
+ location, any user could start a server there. Use this
+ parameter to ensure that you are connected to a server run
+ by a trusted user,
+ e.g., literalrequirepeer=postgres/literal.) This
+ option is only supported on some platforms, currently
+ Linux, FreeBSD, NetBSD, OpenBSD, BSD/OS, and Solaris.
+ /para
+ /listitem
+/varlistentry
+
varlistentry id=libpq-connect-krbsrvname xreflabel=krbsrvname
termliteralkrbsrvname/literal/term
listitem
@@ -6136,6 +6158,16 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
listitem
para
indexterm
+ primaryenvarPGREQUIREPEER/envar/primary
+ /indexterm
+ envarPGREQUIREPEER/envar behaves the same as the xref
+ linkend=libpq-connect-requirepeer connection parameter.
+ /para
+/listitem
+
+listitem
+ para
+ indexterm
primaryenvarPGKRBSRVNAME/envar/primary
/indexterm
envarPGKRBSRVNAME/envar behaves the same as the xref
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index ed37bbd..74595e0 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -226,6 +226,9 @@ static const PQconninfoOption PQconninfoOptions[] = {
{sslcrl, PGSSLCRL, NULL, NULL,
SSL-Revocation-List, , 64},
+ {requirepeer, PGREQUIREPEER, NULL, NULL,
+ Require-Peer, , 10},
+
#if defined(KRB5) || defined(ENABLE_GSS) || defined(ENABLE_SSPI)
/* Kerberos and GSSAPI authentication support specifying the service name */
{krbsrvname, PGKRBSRVNAME, PG_KRB_SRVNAM, NULL,
@@ -592,6 +595,8 @@ fillPGconn(PGconn *conn, PQconninfoOption *connOptions)
conn-sslmode = strdup(require);
}
#endif
+ tmp = conninfo_getval(connOptions, requirepeer);
+ conn-requirepeer = tmp ? strdup(tmp) : NULL;
#if defined(KRB5) || defined(ENABLE_GSS) || defined(ENABLE_SSPI)
tmp = conninfo_getval(connOptions, krbsrvname);
conn-krbsrvname = tmp ? strdup(tmp) : NULL;
@@ -1673,6 +1678,85 @@ keep_going: /* We will come back to here until there is
char *startpacket;
int packetlen;
+#ifdef HAVE_UNIX_SOCKETS
+if (conn-requirepeer)
+{
+ char pwdbuf[BUFSIZ];
+ struct passwd pass_buf;
+ struct passwd *pass;
+ uid_t uid;
+
+#if defined(HAVE_GETPEEREID)
+ gid_t gid;
+
+ errno = 0;
+ if (getpeereid(sock, uid, gid) != 0)
+ {
+ appendPQExpBuffer(conn-errorMessage,
+ libpq_gettext(could not get peer credentials: %s\n),
+ pqStrerror(errno, sebuf, sizeof(sebuf)));
+ goto error_return;
+ }
+#elif defined(SO_PEERCRED)
+ struct ucred peercred;
+ ACCEPT_TYPE_ARG3 so_len = sizeof(peercred);
+
+ errno = 0;
+ if (getsockopt(conn-sock,
Re: [HACKERS] Streaming Replication: Checkpoint_segment and wal_keep_segments on standby
Did these changes ever get into the docs? I don't think so.
---
Fujii Masao wrote:
On Thu, Jun 10, 2010 at 7:19 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
--- 1902,1908
? ? ? ? ?for standby purposes, and the number of old WAL segments
available
? ? ? ? ?for standbys is determined based only on the location of the
previous
? ? ? ? ?checkpoint and status of WAL archiving.
+ ? ? ? ? This parameter has no effect on a restartpoint.
? ? ? ? ?This parameter can only be set in the
filenamepostgresql.conf/
? ? ? ? ?file or on the server command line.
? ? ? ? /para
Hmm, I wonder if wal_keep_segments should take effect during recovery too?
We don't support cascading slaves, but if you have two slaves connected to
one master (without an archive), and you perform failover to one of them,
without wal_keep_segments the 2nd slave might not find all the files it
needs in the new master. Then again, that won't work without an archive
anyway, because we error out at a TLI mismatch in replication. Seems like
this is 9.1 material..
Yep, since currently SR cannot get over the gap of TLI, wal_keep_segments
is not worth taking effect during recovery.
*** a/doc/src/sgml/wal.sgml
--- b/doc/src/sgml/wal.sgml
***
*** 424,429
--- 424,430
? ?para
? ? There will always be at least one WAL segment file, and will normally
? ? not be more than (2 + varnamecheckpoint_completion_target/varname)
* varnamecheckpoint_segments/varname + 1
+ ? ?or varnamecheckpoint_segments/ + xref
linkend=guc-wal-keep-segments + 1
? ? files. ?Each segment file is normally 16 MB (though this size can be
? ? altered when building the server). ?You can use this to estimate space
? ? requirements for acronymWAL/acronym.
That's not true, wal_keep_segments is the minimum number of files retained,
independently of checkpoint_segments. The corret formula is (2 +
checkpoint_completion_target * checkpoint_segments, wal_keep_segments)
You mean that the maximum number of WAL files is: ?
max {
(2 + checkpoint_completion_target) * checkpoint_segments,
wal_keep_segments
}
Just after a checkpoint removes old WAL files, there might be
wal_keep_segments
WAL files. Additionally, checkpoint_segments WAL files might be generated
before
the subsequent checkpoint removes old WAL files. So I think that the maximum
number is
max {
(2 + checkpoint_completion_target) * checkpoint_segments,
wal_keep_segments + checkpoint_segments
}
Am I missing something?
? ?para
+ ? ?In archive recovery or standby mode, the server periodically performs
+ ? ?firsttermrestartpoints/indextermprimaryrestartpoint//
+ ? ?which are similar to checkpoints in normal operation: the server
forces
+ ? ?all its state to disk, updates the filenamepg_control/ file to
+ ? ?indicate that the already-processed WAL data need not be scanned
again,
+ ? ?and then recycles old log segment files if they are in the
+ ? ?filenamepg_xlog/ directory. Note that this recycling is not
affected
+ ? ?by varnamewal_keep_segments/ at all. A restartpoint is triggered,
+ ? ?if at least one checkpoint record has been replayed since the last
+ ? ?restartpoint, every varnamecheckpoint_timeout/ seconds, or every
+ ? ?varnamecheckoint_segments/ log segments only in standby mode,
+ ? ?whichever comes first
That last sentence is a bit unclear. How about:
A restartpoint is triggered if at least one checkpoint record has been
replayed and varnamecheckpoint_timeout/ seconds have passed since last
restartpoint. In standby mode, a restartpoint is also triggered if
varnamecheckoint_segments/ log segments have been replayed since last
restartpoint and at least one checkpoint record has been replayed since.
Thanks! Seems good.
... In log shipping case, the checkpoint interval
+ ? ?on the standby is normally smaller than that on the master.
+ ? /para
What does that mean? Restartpoints can't be performed more frequently than
checkpoints in the master because restartpoints can only be performed at
checkpoint records.
Yes, that's what I meant.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Additional startup logging
Kevin Grittner kevin.gritt...@wicourts.gov wrote: It seems potentially useful to LOG the version() string in the log file during startup. It might also help to LOG any settings which might result in the loss of committed transactions or in database corruption during startup. (After a crash, the postgresql.conf file might not show the values which were in effect during startup, and it is too late to show the values.) I think such logs depends on purposes, so they should be customizable. You could write a module, that is registered in 'shared_preload_libraries' and logs internal information you want from _PG_init() or shmem_startup_hook. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Additional startup logging
On Jun 30, 2010, at 22:43 , Takahiro Itagaki wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: It seems potentially useful to LOG the version() string in the log file during startup. It might also help to LOG any settings which might result in the loss of committed transactions or in database corruption during startup. (After a crash, the postgresql.conf file might not show the values which were in effect during startup, and it is too late to show the values.) I think such logs depends on purposes, so they should be customizable. You could write a module, that is registered in 'shared_preload_libraries' and logs internal information you want from _PG_init() or shmem_startup_hook. For long-running systems, you may not have the beginning of the log file. Perhaps a method of dumping the version and/or setting information on demand (or perhaps at the beginning of each log file?): Shouldn't be too hard to put together a function which prints out such information via RAISE even now using PL/pgSQL. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication: Checkpoint_segment and wal_keep_segments on standby
On Thu, Jul 1, 2010 at 11:39 AM, Bruce Momjian br...@momjian.us wrote:
Did these changes ever get into the docs? I don't think so.
Thanks for reminding me. I attached the updated patch.
That last sentence is a bit unclear. How about:
A restartpoint is triggered if at least one checkpoint record has been
replayed and varnamecheckpoint_timeout/ seconds have passed since last
restartpoint. In standby mode, a restartpoint is also triggered if
varnamecheckoint_segments/ log segments have been replayed since last
restartpoint and at least one checkpoint record has been replayed since.
... In log shipping case, the checkpoint interval
+ ? ?on the standby is normally smaller than that on the master.
+ ? /para
What does that mean? Restartpoints can't be performed more frequently than
checkpoints in the master because restartpoints can only be performed at
checkpoint records.
I adopted these Heikki's sentences.
*** a/doc/src/sgml/wal.sgml
--- b/doc/src/sgml/wal.sgml
***
*** 424,429
--- 424,430
? ?para
? ? There will always be at least one WAL segment file, and will normally
? ? not be more than (2 + varnamecheckpoint_completion_target/varname)
* varnamecheckpoint_segments/varname + 1
+ ? ?or varnamecheckpoint_segments/ + xref
linkend=guc-wal-keep-segments + 1
? ? files. ?Each segment file is normally 16 MB (though this size can be
? ? altered when building the server). ?You can use this to estimate space
? ? requirements for acronymWAL/acronym.
That's not true, wal_keep_segments is the minimum number of files retained,
independently of checkpoint_segments. The corret formula is (2 +
checkpoint_completion_target * checkpoint_segments, wal_keep_segments)
You mean that the maximum number of WAL files is: ?
max {
(2 + checkpoint_completion_target) * checkpoint_segments,
wal_keep_segments
}
Just after a checkpoint removes old WAL files, there might be
wal_keep_segments
WAL files. Additionally, checkpoint_segments WAL files might be generated
before
the subsequent checkpoint removes old WAL files. So I think that the maximum
number is
max {
(2 + checkpoint_completion_target) * checkpoint_segments,
wal_keep_segments + checkpoint_segments
}
Am I missing something?
I've left this part as it is. Before committing the patch, we need to check
whether my thought is true.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
trigger_restartpoint_doc_v2.patch
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: parallelizing subplan execution (was: [HACKERS] explain and PARAM_EXEC)
On Sat, Jun 26, 2010 at 6:01 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jun 25, 2010 at 10:47 PM, Mark Wong mark...@gmail.com wrote: http://pages.cs.wisc.edu/~dewitt/includes/publications.html Some of these papers aren't the type of parallelism we're talking about here, but the ones that I think are relevant talk mostly about parallelizing hash based joins. I think we might be lacking an operator or two though in order to do some of these things. This part (from the first paper linked on that page) is not terribly encouraging. Current database query optimizers do not consider all possible plans when optimizing a relational query. While cost models for relational queries running on a single processor are now well-understood [SELI79], they still depend on cost estimators that are a guess at best. Some dynamically select from among several plans at run time depending on, for example, the amount of physical memory actually available and the cardinalities of the intermediate results [GRAE89]. To date, no query optimizers consider all the parallel algorithms for each operator and all the query tree organizations. More work is needed in this area. The section (from that same paper) on parallelizing hash joins and merge-join-over-sort is interesting, and I can definitely imagine those techniques being a win for us. But I'm not too sure how we'd know when to apply them - that is, what algorithm would the query optimizer use? I'm sure we could come up with something, but I'd get a warmer, fuzzier feeling if we could implement the fruits of someone else's research rather than rolling our own. I found another starting point for more papers here: http://infolab.stanford.edu/joker/joqrs.html The links on this page don't work anymore but many of these are easily found by searching for the title. I've only gone through some abstracts so far, but it seems to me that they discuss some query optimization techniques for parallel systems. Regards, Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
Hello, Tom. You wrote: TL Bruce Momjian br...@momjian.us writes: Tom Lane wrote: What's your idea of affecting the fewest people? There is no previous history to be backward-compatible with, because we never supported keepalive on Windows before. Well, starting in 9.0, keepalives in libpq will default to 'on': TL Yes, which is already a change in behavior. I don't understand why you TL are worrying about backwards compatibility to parameter values that TL weren't in use before. I think self-consistency of the new version is TL far more important than that. Absolutely agree. even if we use Windows defaults, those defaults might be different for different Windows versions. TL I'm not sure if that's an issue or not, but if it is, that seems to me TL to argue for #2 not #1. TL regards, tom lane -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
