Re: [HACKERS] Stats collection on Windows
Redmond crowd should be able to figure out that recycling process IDs instantly would be a stupid idea...) Can you explain more of this? IMHO, if we rely on feature like this, the difference is unstable-every-day vs. unstable-every-year. The mere existence of the kill() primitive should bring to mind reasons why it's a bad idea. Except the kill() primitive *does not exist* on windows. That said, how did you go about to confirm that the pid is recycled instantly? I was under the impression that it assignes any unused pid in random order, which is also what a quick glance at my XP box looks like (don't have a 2000 box around, but I wasn't aware of such a change between those - but it's certainly not impossible). But if oyu had some better method of determining it, please let me know :-) If that's how, several other OSes do the same thing AFAIK - for security reasons. For example OpenBSD. So if we rely heavily on that, we may be in trouble elsewhere as well. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stats collection on Windows
On Tue, Apr 04, 2006 at 11:02:11PM -0400, Tom Lane wrote: Peter Brant [EMAIL PROTECTED] writes: I think I've found the cause (or one of the causes) why stats collection is unreliable on Windows and I'm wondering about the best way to go about fixing it. The problem is that process IDs on Windows seem to be assigned without much rhyme or reason and it seems to happen relatively frequently that a new process will be assigned the same process ID as a process which recently died. If this happens before the backend has been expired out of pgstat.c's pgStatBeDead hash, the backend will be missed. That's an interesting theory, but do you have any actual evidence for it? The evidence I've seen says that our big problem on Windows is the stats collector process just quitting due to unexplained piperead() failures. (I mean, I'd love to blame Microsoft for everything, but even the Redmond crowd should be able to figure out that recycling process IDs instantly would be a stupid idea...) Why? :-) They use HANDLE. The process ID isn't nearly as useful as it is on UNIX. I haven't looked at that stuff in a long time, but process ID on Windows may be a compatibility method. Process ID isn't necessarily a good way of identifying tasks, precisely because they may be reused. Using a serial allocated at process start might make more sense. Relying on it not being reused, is not dissimilar to the old malloc() tricks of assuming that malloc() will not return something recently free()d. It's bad. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] I have changed employers
Hi, On Tue, 2006-04-04 at 20:06 -0400, Bruce Momjian wrote: FYI, I have left SRA and am now working for EnterpriseDB: Congratulations Bruce! It is good to see many community members working in PostgreSQL-related companies. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stats collection on Windows
On Tue, Apr 04, 2006 at 11:17:49PM -0400, Tom Lane wrote: Qingqing Zhou [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote Redmond crowd should be able to figure out that recycling process IDs instantly would be a stupid idea...) Can you explain more of this? IMHO, if we rely on feature like this, the difference is unstable-every-day vs. unstable-every-year. The mere existence of the kill() primitive should bring to mind reasons why it's a bad idea. CreateProcess - The process is assigned a process identifier. The identifier is valid until the process terminates. It can be used to identify the process, or specified in the OpenProcess function to open a handle to the process. The initial thread in the process is also assigned a thread identifier. ... TerminateProcess - Terminates the specified process and all of its threads. TerminateProcess takes a HANDLE, not a process identifier. Yes, they provide the kill primitive, but only as a compatibility measure. A good Windows process, should maintain a HANDLE to the process, and kill the process using the HANDLE. This way, there is no race. The HANDLE is also how you wait for the process to terminate normally. I prefer the Redmond way, in that I find UNIX's use of integer identifiers to be encouraging of race conditions. UNIX requires hacks like minimizing PID reuse, because UNIX is the one that is broken. :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Stats collection on Windows
On Wed, Apr 05, 2006 at 03:20:47AM -0400, [EMAIL PROTECTED] wrote: TerminateProcess takes a HANDLE, not a process identifier. Yes, they provide the kill primitive, but only as a compatibility measure. A good Windows process, should maintain a HANDLE to the process, and kill the process using the HANDLE. This way, there is no race. The HANDLE is also how you wait for the process to terminate normally. Which presents the solution, we should use the HANDLE on windows rather than the process identifier. I prefer the Redmond way, in that I find UNIX's use of integer identifiers to be encouraging of race conditions. UNIX requires hacks like minimizing PID reuse, because UNIX is the one that is broken. :-) Eh? A HANDLE is (or can be mapped to) an integer too. I don't see anything on that page about handle reuse. If you run a machine long enough I'm sure it can be reused also... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Stats collection on Windows
On Wed, Apr 05, 2006 at 09:30:06AM +0200, Martijn van Oosterhout wrote: On Wed, Apr 05, 2006 at 03:20:47AM -0400, [EMAIL PROTECTED] wrote: TerminateProcess takes a HANDLE, not a process identifier. Yes, they provide the kill primitive, but only as a compatibility measure. A good Windows process, should maintain a HANDLE to the process, and kill the process using the HANDLE. This way, there is no race. The HANDLE is also how you wait for the process to terminate normally. Which presents the solution, we should use the HANDLE on windows rather than the process identifier. Yes. I prefer the Redmond way, in that I find UNIX's use of integer identifiers to be encouraging of race conditions. UNIX requires hacks like minimizing PID reuse, because UNIX is the one that is broken. :-) Eh? A HANDLE is (or can be mapped to) an integer too. I don't see anything on that page about handle reuse. If you run a machine long enough I'm sure it can be reused also... Once upon a time, when I played with this stuff (I mostly use UNIX, not Windows), I concluded to myself that HANDLE was process-local, and that it was allocated. Meaning - it won't be re-used until you CloseHandle(). It's best then, to think of HANDLE as a opaque object. Regardless, of whether it is process-local or not, until you run CloseHandle(), it is yours to keep, and it won't be re-used. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stats collection on Windows
On Wed, Apr 05, 2006 at 03:38:28AM -0400, [EMAIL PROTECTED] wrote: Once upon a time, when I played with this stuff (I mostly use UNIX, not Windows), I concluded to myself that HANDLE was process-local, and that it was allocated. Meaning - it won't be re-used until you CloseHandle(). It's best then, to think of HANDLE as a opaque object. Regardless, of whether it is process-local or not, until you run CloseHandle(), it is yours to keep, and it won't be re-used. HANDLE is process local? That is worse then, because then there's no guarentee that each process will see a different identifier. The stats collector identifies processes by their process id, which they get using getpid(). If instead they used a handle for their own process (GetCurrentProcess() always returns -1, but you can apparently clone it to get a real handle), you have no idea whether that handle is unique amongst backends, because it's process local. The stats collector doesn't have any open handles for the backend, it's just a way for backends to identify themselves. It appears that process handles are not up to the task either... Do we have a plan C? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Stats collection on Windows
Once upon a time, when I played with this stuff (I mostly use UNIX, not Windows), I concluded to myself that HANDLE was process-local, and that it was allocated. Meaning - it won't be re-used until you CloseHandle(). It's best then, to think of HANDLE as a opaque object. Regardless, of whether it is process-local or not, until you run CloseHandle(), it is yours to keep, and it won't be re-used. HANDLE is process local? That is worse then, because then there's no guarentee that each process will see a different identifier. HANDLE is process local. What you need to do is run DuplicateHandle() on it specifying it should also be valid for process Y (for which you need a HANDLE opened, in this case the stats collector). This will give you a new handle whichi s valid in the *target process*, but it is *not* valid in your own process. A quick look shows that pids appear to be reused quite fast on Windows 2000, but *not* on XP or 2003. Don't have any other versions around to test. AFAIK, the pid on unix is also only vaild while the process is running. It's not likely to be reused quickly, which appears to be the same for XP+ on win32. Do we have a plan C? Well, first we need to know that this really *is* the problem. I'm unsure if we've seen proof that this actually causes a real problem. (It's certainly not the main issue with teh stats system on win32, which is the fact that it crashes now and then) How likely is it that the arrive-in-wrong-order really happens? And can we do something about that one? (I assume the problem is that the backend-die message is sent from postmaster, meaning that the die can appear before the alive sent from the backend itself. What if we changed it so we had one message sent from a backend doing a controlled shutdown (99.9% of the time) and a different one when the postmaster picks up a dead backend. And we apply the dead counter only to the messages from the postmaster, and expire the record right away when we receive one from a controlled shutdown (which would happen in-line with the backend-start message) //Magnus //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stats collection on Windows
Redmond crowd should be able to figure out that recycling process IDs instantly would be a stupid idea...) Can you explain more of this? IMHO, if we rely on feature like this, the difference is unstable-every-day vs. unstable-every-year. The mere existence of the kill() primitive should bring to mind reasons why it's a bad idea. CreateProcess - The process is assigned a process identifier. The identifier is valid until the process terminates. It can be used to identify the process, or specified in the OpenProcess function to open a handle to the process. The initial thread in the process is also assigned a thread identifier. ... TerminateProcess - Terminates the specified process and all of its threads. TerminateProcess takes a HANDLE, not a process identifier. Yes. You get the handle by doing OpenProcess() with PROCESS_TERMINATE access. The functions used to enumerate processes all return the process id, not a HANDLE.(See Process32First/Process32Next). You use the HANDLE only when you need to *modify* something (for example, killing it). Yes, they provide the kill primitive, but only as a compatibility measure. They do? Where is it? Certainly not in the documented SDK that I can see. A good Windows process, should maintain a HANDLE to the process, and kill the process using the HANDLE. This way, there is no race. The HANDLE is also how you wait for the process to terminate normally. A good Windows process would be using threads ;-) That's what the Windows API was written for, and that's why anything that deals with multiple processes working together is a lot harder than on Unix. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Stats collection on Windows
On Wed, Apr 05, 2006 at 09:58:54AM +0200, Martijn van Oosterhout wrote: On Wed, Apr 05, 2006 at 03:38:28AM -0400, [EMAIL PROTECTED] wrote: Once upon a time, when I played with this stuff (I mostly use UNIX, not Windows), I concluded to myself that HANDLE was process-local, and that it was allocated. Meaning - it won't be re-used until you CloseHandle(). It's best then, to think of HANDLE as a opaque object. Regardless, of whether it is process-local or not, until you run CloseHandle(), it is yours to keep, and it won't be re-used. HANDLE is process local? That is worse then, because then there's no guarentee that each process will see a different identifier. It's no different from a file descriptor on UNIX. Neither UNIX nor Windows promise that a process identifier is valid beyond the life of the process. UNIX avoids it from happening, as it is necessary to avoid races with system calls such as kill(). Windows does not have this problem. The stats collector identifies processes by their process id, which they get using getpid(). If instead they used a handle for their own process (GetCurrentProcess() always returns -1, but you can apparently clone it to get a real handle), you have no idea whether that handle is unique amongst backends, because it's process local. The stats collector doesn't have any open handles for the backend, it's just a way for backends to identify themselves. It appears that process handles are not up to the task either... Do we have a plan C? Sure. Serial. Allocate on process start. Or, back to another topic from months ago - UUID generation... :-) Process identifier should not be used beyond the life of the process. As soon as wait() removes the process on UNIX, the process identifier is no longer valid, and could be reused. That the operating system tries to prevent problems by avoiding recycling isn't necessarily a good reason to exploit this capability. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Stats collection on Windows
On Wed, Apr 05, 2006 at 10:31:37AM +0200, Magnus Hagander wrote: TerminateProcess takes a HANDLE, not a process identifier. Yes. You get the handle by doing OpenProcess() with PROCESS_TERMINATE access. The functions used to enumerate processes all return the process id, not a HANDLE.(See Process32First/Process32Next). You use the HANDLE only when you need to *modify* something (for example, killing it). Enumerating processes isn't a common operationg for programs other than 'top'. :-) But even so, one can use OpenProcess() to evaluate that the process being referenced is actually the one you think it is, before calling TerminateProcess(), eliminating the race. Yes, they provide the kill primitive, but only as a compatibility measure. They do? Where is it? Certainly not in the documented SDK that I can see. I believe it is called KillProcess(). A good Windows process, should maintain a HANDLE to the process, and kill the process using the HANDLE. This way, there is no race. The HANDLE is also how you wait for the process to terminate normally. A good Windows process would be using threads ;-) That's what the Windows API was written for, and that's why anything that deals with multiple processes working together is a lot harder than on Unix. Haha. Good point. :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stats collection on Windows
On Wed, Apr 05, 2006 at 06:03:31AM -0400, [EMAIL PROTECTED] wrote: It's no different from a file descriptor on UNIX. Neither UNIX nor Windows promise that a process identifier is valid beyond the life of the process. UNIX avoids it from happening, as it is necessary to avoid races with system calls such as kill(). Windows does not have this problem. But consider, why is the process id there? (Amongst other reasons) so that users can monitor pg_stat_activity and kill a backend that's out of control. The equivalent to this in windows would be to. 1. Get HANDLE from the process ID. 2. TerminateProcess with that HANDLE. Presumably users have a little GUI app that displays processes on the system with the process ID so they can kill it. If a process ID is quickly reused, they may end up killing the wrong one. The race condition in this case involves the user and you can't solve that programmatically. The non-reuse of pids is more for user-friendlyness than anything else. The Window use of HANDLE doesn't solve this problem at all. Sure. Serial. Allocate on process start. Or, back to another topic from months ago - UUID generation... :-) Neither of which solve the I'm a user and want to kill *that* backend problem. Because even on windows you'll have to get the process id, convert it to a handle an kill it. Same race condition. Process identifier should not be used beyond the life of the process. As soon as wait() removes the process on UNIX, the process identifier is no longer valid, and could be reused. That the operating system tries to prevent problems by avoiding recycling isn't necessarily a good reason to exploit this capability. Yeah, but it's very useful as a user. Consider this scenerio: process 1234 goes AWOL kill -INT 1234 check if process still there kill -TERM 1234 process still there, damn it! kill -9 1234 There gone. With no quick PID reuse I can be sure I won't kill the wrong one. This is presumably why recent versions of windows don't reuse pids quickly either... It for *users* not programs. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Stats collection on Windows
On Wed, Apr 05, 2006 at 12:20:49PM +0200, Martijn van Oosterhout wrote: On Wed, Apr 05, 2006 at 06:03:31AM -0400, [EMAIL PROTECTED] wrote: It's no different from a file descriptor on UNIX. Neither UNIX nor Windows promise that a process identifier is valid beyond the life of the process. UNIX avoids it from happening, as it is necessary to avoid races with system calls such as kill(). Windows does not have this problem. But consider, why is the process id there? (Amongst other reasons) so that users can monitor pg_stat_activity and kill a backend that's out of control. The equivalent to this in windows would be to. 1. Get HANDLE from the process ID. 2. TerminateProcess with that HANDLE. You missed 1.5. Ensure that HANDLE matches the process you intend. :-) This is a little bit of a distraction though, as any system that requires the user to be able to kill broken backends, is only indicative of a broken backend. We're talking about how to deal with a broken process, after the process owner (PostgreSQL) has forgotten about it. What would be wrong with having a PostgreSQL generated serial associated with each backend, that can be used by the backend process owner, to map to a HANDLE, which uses TerminateProcess() underneath? Presumably users have a little GUI app that displays processes on the system with the process ID so they can kill it. If a process ID is quickly reused, they may end up killing the wrong one. Sure. But the problem here, is that PostgreSQL is broken, so they find a need to go to their process listing GUI. And who is to say that the GUI doesn't do as I've suggested above? Ensure that TerminateProcess() is against the intended process? I have no idea if it does - but it could. The race condition in this case involves the user and you can't solve that programmatically. The non-reuse of pids is more for user-friendlyness than anything else. The Window use of HANDLE doesn't solve this problem at all. Sure you can. But it also shouldn't matter. Sure. Serial. Allocate on process start. Or, back to another topic from months ago - UUID generation... :-) Neither of which solve the I'm a user and want to kill *that* backend problem. Because even on windows you'll have to get the process id, convert it to a handle an kill it. Same race condition. No, and not if PostgreSQL kills the backend for you cleanly, using the HANDLE, that it owns for the process. Process identifier should not be used beyond the life of the process. As soon as wait() removes the process on UNIX, the process identifier is no longer valid, and could be reused. That the operating system tries to prevent problems by avoiding recycling isn't necessarily a good reason to exploit this capability. Yeah, but it's very useful as a user. Consider this scenerio: process 1234 goes AWOL kill -INT 1234 check if process still there kill -TERM 1234 process still there, damn it! kill -9 1234 There gone. With no quick PID reuse I can be sure I won't kill the wrong one. This is presumably why recent versions of windows don't reuse pids quickly either... It for *users* not programs. Users shouldn't need to kill programs. If they do - on the off chance that they do, they should be more careful than you list above. That's a kneejerk reaction to a problem that shouldn't occur in the first place. It's a habit trained by UNIX users. I rarely ever need to kill a process on my Windows box, and when I have, the process listing in the GUI hasn't offered me a process ID. I click on the item I wish to kill, and I right click End Process. A lot more user friendly, in my opinion. And if they happen to fix the race in the background using the method I suggest above? All the better... Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stats collection on Windows
It's no different from a file descriptor on UNIX. Neither UNIX nor Windows promise that a process identifier is valid beyond the life of the process. UNIX avoids it from happening, as it is necessary to avoid races with system calls such as kill(). Windows does not have this problem. But consider, why is the process id there? (Amongst other reasons) so that users can monitor pg_stat_activity and kill a backend that's out of control. The equivalent to this in windows would be to. 1. Get HANDLE from the process ID. 2. TerminateProcess with that HANDLE. Presumably users have a little GUI app that displays processes on the system with the process ID so they can kill it. If a process ID is quickly reused, they may end up killing the wrong one. Actualy, not quite. That's the equivalent of kill -9. What you'd do is pg_ctl kill TERM pid. Or whatever other signal you want to send. But you still need the pid, there is no way to get the required information from a plain handle when you're not in that process. Though I'd say I'm usually more interested in noticing what process it is, rather than killing it. Possibly canceling it, but very seldomly actually kill it. But it still needs the process id, yes. (BTW, the GUI would generaelly be Task Manager, so it's not like it's a special tool) snip rest //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stats collection on Windows
Yes, they provide the kill primitive, but only as a compatibility measure. They do? Where is it? Certainly not in the documented SDK that I can see. I believe it is called KillProcess(). No such function documented on my MSDN, other than one in SQL Server DMO. Are you referring to TerminateProcess()? If so, it can just terminate the process, not send it arbitrary signals. Considering windows doesnt' reallyi have the concept of signals, that's not very surprising though :-) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Stats collection on Windows
This is a little bit of a distraction though, as any system that requires the user to be able to kill broken backends, is only indicative of a broken backend. We're talking about how to deal with a broken process, after the process owner (PostgreSQL) has forgotten about it. Don't confuse the postmaster with the statistics system. the postmaster knows about the backends per handle, but the stats system runs outside of the postmaster, and can possibly get confused. What would be wrong with having a PostgreSQL generated serial associated with each backend, that can be used by the backend process owner, to map to a HANDLE, which uses TerminateProcess() underneath? It might be a good idea to use the combination pid+internalid. Note taht we *don't* just want to TerminateProcess(), we need the pid to send actual signals with. And in this case, that's not even the problem - the problem is that we're not dropping the process from our list (I think). Presumably users have a little GUI app that displays processes on the system with the process ID so they can kill it. If a process ID is quickly reused, they may end up killing the wrong one. Sure. But the problem here, is that PostgreSQL is broken, so they find a need to go to their process listing GUI. And who is to say that the GUI doesn't do as I've suggested above? Ensure that TerminateProcess() is against the intended process? I have no idea if it does - but it could. Because the GUI is most likely either Task Manager or Process Explorer. There is no need for a special GUI. Process identifier should not be used beyond the life of the process. As soon as wait() removes the process on UNIX, the process identifier is no longer valid, and could be reused. That the operating system tries to prevent problems by avoiding recycling isn't necessarily a good reason to exploit this capability. Yeah, but it's very useful as a user. Consider this scenerio: process 1234 goes AWOL kill -INT 1234 check if process still there kill -TERM 1234 process still there, damn it! kill -9 1234 There gone. With no quick PID reuse I can be sure I won't kill the wrong one. This is presumably why recent versions of windows don't reuse pids quickly either... It for *users* not programs. Users shouldn't need to kill programs. And normally they don't. But again, this isn't where the problem is. If your process is stuck and you need to kill it, it most likely will still be around once you get to killing it. If not, you were trying to kill it too soon. I rarely ever need to kill a process on my Windows box, and when I have, the process listing in the GUI hasn't offered me a process ID. I click on the item I wish to kill, and I right click End Process. A lot more user friendly, in my opinion. And if they happen to fix the race in the background using the method I suggest above? All the better... AFAIK, Task Manager uses the pid to kill the task. Try tracing it. (And it will happily show you the pid as well - just enable it in the checkbox. It's the only reasonable way to differ between two processes off the same exe, for example two different notepad:s...) //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] commit callback, request
Hello Is possible make transaction commit trigger without patching code now? I finding way , but all usable interfaces are static. I remember on diskussion about it and about changes in LISTEN/NOTIFY implementation. Is there any progress? I need it for simulation of Oracle dbms_alert.signal function. Whole dbms_alert package is similar our LISTEN/NOTIFY. Difference is dbms_alert is server side solution and L/N is client side. Is any chance so this interface will be in 8.2? Regards Pavel Stehule _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Stats collection on Windows
Magnus Hagander [EMAIL PROTECTED] writes: HANDLE is process local? That is worse then, because then there's no guarentee that each process will see a different identifier. HANDLE is process local. What you need to do is run DuplicateHandle() on it specifying it should also be valid for process Y (for which you need a HANDLE opened, in this case the stats collector). This will give you a new handle whichi s valid in the *target process*, but it is *not* valid in your own process. What happens if process Y goes away between the time you obtain a handle for it and the time you try to run this DuplicateHandle call? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Tru64/Alpha problems
I wrote: I have done some more digging on this. The buildfarm member had a couple of configuration issues which I have remedied, and which almost certainly account for the float test errors we saw. However, we still get an error when we try to start the installed s/w with the default listen_addresses: LOG: could not translate host name localhost, service 5832 to address: servname not supported for ai_socktype Of course, this won't be seen with make check, since it starts on Unix with listen_addresses='', which means we never even look for any sort of TCP addrinfo. I found a hint on the web that we should use -D_SOCKADDR_LEN. I tried this, but got a link failure, complaining about revc and send. This man page extract explains: [Tru64 UNIX] The recv() function is identical to the recvfrom() function with a zero-valued address_len parameter, and to the read() function if no flags are used. For that reason the recv() function is disabled when 4.4BSD behavior is enabled; that is, when the _SOCKADDR_LEN compile-time option is defined. I'd like to know some settings that we can use that will get Tru64 cleanly through the buildfarm set. If noone offers any, I propose that we revert the getaddrinfo() test in configure and use our own on Tru64 until they do. I have not had any response to this. Is there any objection to my reverting the configure changes for the head and 8.1 branches? If not I intend to do that around the end of the week. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stats collection on Windows
HANDLE is process local? That is worse then, because then there's no guarentee that each process will see a different identifier. HANDLE is process local. What you need to do is run DuplicateHandle() on it specifying it should also be valid for process Y (for which you need a HANDLE opened, in this case the stats collector). This will give you a new handle whichi s valid in the *target process*, but it is *not* valid in your own process. What happens if process Y goes away between the time you obtain a handle for it and the time you try to run this DuplicateHandle call? I don't know offhand. I would assume one of two things: 1) DuplicateHandle() fails. 2) You get a handle back that is valid in the dead process (meaning it's not valid). I can put together some quick test-code for this if you need me to? //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Stats collection on Windows
Magnus Hagander [EMAIL PROTECTED] writes: What happens if process Y goes away between the time you obtain a handle for it and the time you try to run this DuplicateHandle call? I can put together some quick test-code for this if you need me to? Nah, it was just a rhetorical question meant to poke a hole in the claim that Windows can avoid race conditions by using HANDLEs. AFAICS, don't-reuse-PIDs-too-quick has exact analogs that Windows has to solve by ensuring it doesn't reuse HANDLEs too quick. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stats collection on Windows
2006/4/5, Tom Lane [EMAIL PROTECTED]: AFAICS, don't-reuse-PIDs-too-quick has exact analogs that Windows hasto solve by ensuring it doesn't reuse HANDLEs too quick.There's a disconnect here. handles aren't process identifiers: they're reference counted pointers to the kernel structures for the process. If you are holding a handle (ie: from CreateProcess or OpenProcess) that handle cannot and will not be reclaimed until you call CloseHandle (or your process itself exits). You should never retain a handle after you've called CloseHandle on it. Which brings an interesting thought: are process ID's reclaimed while open handles remain? I'm willing to bet the answer is no. In that case, the stats collector could retain the handle until it's done with the process ID.
Re: [HACKERS] Stats collection on Windows
What happens if process Y goes away between the time you obtain a handle for it and the time you try to run this DuplicateHandle call? I can put together some quick test-code for this if you need me to? Nah, it was just a rhetorical question meant to poke a hole in the claim that Windows can avoid race conditions by using HANDLEs. That's what I thought, which is why I asked first. AFAICS, don't-reuse-PIDs-too-quick has exact analogs that Windows has to solve by ensuring it doesn't reuse HANDLEs too quick. Windows doesn't reuse HANDLEs in that way. Handles are like file descriptors. The kernel structure represnting the process has a *process id*, not a handle. That is what uniquely identifies the process. (In the kernel it's often referred to as a client id). The process structure will be held as long as anybody has an open handle to the process. Thus, as long as this happens, the pid *cannot* be reused. So one way to assure the pid isn't recycled too soon is to keep the postmasters handle open long enough (the postmaster already has a handle there - it's just the pgstats process that doesn't have one). RIght now we close the postmaster handle first (win32_removechild at ~ 2086 in postmaster.c) and then fire off the message (CleanupBackend at ~2239). //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] commit callback, request
Pavel Stehule [EMAIL PROTECTED] writes: Is possible make transaction commit trigger without patching code now? You can get pretty close with a deferred trigger. I don't think there's any way to have a guaranteed at commit trigger --- as soon as (1) there are two of them and (2) one can get an error, the transaction could fail after running an alleged at commit trigger. regards, tom lane hm. I don't have big problem with false notifications. Who want to use dbms_alert have to calculate with this possibility. But triggers has disadventage - I have to sometimes clean any table, which cary triggers :-(. It's solution. I hope 8.2 will has any general mechanis. Is it possible enhance SPI to parametrized NOTIFY? Default mode send message via libpq, nonstandard raise any callback. Thank You Pavel _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stats collection on Windows
There's a disconnect here. handles aren't process identifiers: they're reference counted pointers to the kernel structures for the process. If you are holding a handle (ie: from CreateProcess or OpenProcess) that handle cannot and will not be reclaimed until you call CloseHandle (or your process itself exits). You should never retain a handle after you've called CloseHandle on it. Which brings an interesting thought: are process ID's reclaimed while open handles remain? I'm willing to bet the answer is no. In that case, the stats collector could retain the handle until it's done with the process ID. Since the process id lives in the kernel structure they point to, they can't very well be recycled... (classical handle-leak-kills-windows-system-by-running-out-of-kernel-space scenario). The problem is, the stats collector doesn't have the handle in the first place. I guess it could open one upon receiving the bestart message, but it seems like a kludge. Would be nicer if it could be done cleaner :-) //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] recover without $PGDATA/global
here's a resume of what i am doing. note that i don't have the global direcotry of $PGDATA. first of all reinstall a new hard-drive and installed the same version of postgresql (8.0.4) with the same configuration. now doing a initdb somewhere on the new hd. then createdb the same database (with the same name) so now i have a working structure (with a global dir) now with oid2name i can find what is what in my $PGDATA/base so now it's time to put my $BACKUP/$PGDATA/base in the new $PGDATA/base but renaming my back-up with the new oid (only the first folder not sub-dir.) - i don't replace template0, template1 now a cp -R $BACKUP/$PGDATA/pg_xlog $PGDATA/ now a cp -R $BACKUP/$PGDATA/pg_clog $PGDATA/ now i have to use pg_resetxlog to fit my needs. this is what i'm not sure, i have 0001000E and 0001000F in pg_xlog. what should i use as arguments to pg_resetxlog -o -x -l. after finding the good arguments, i will be able to start postmaster and see my tables, cause right now it's always no realtions founds no matter my test with pg_resetxlog? also is it true that i will need to change also the oid of each files in $PGDATA/base/mydboid/* - this is impossible since i don't know the structure of my table before the hd crashed. yes and sorry for cross-posting to general... regards, pat ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] float8 regression test failure in head
On Mon, Mar 22, 2004 at 11:03:38PM -0500, Neil Conway wrote: Christopher Kings-Lynne wrote: This has not yet been fixed... Attached is a patch for this issue. I've checked with Chris, and this patch allows the regression tests to pass on his machine. I also updated float8-exp-three-digits expected file, but I wasn't able to test these changes (I don't have access to a QNX machine). Mystery: why I haven't I seen this problem before in the last 2 years... Anyway, on NetBSD-3.99.17/i386, I need the included patch to pass float8. float8-small-is-zero_1.out probably needs it too, float8.out and float8-exp-three-digits-win32.out already look OK. Cheers, Patrick Index: float8-small-is-zero.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/float8-small-is-zero.out,v retrieving revision 1.10 diff -u -r1.10 float8-small-is-zero.out --- float8-small-is-zero.out8 Jun 2005 21:15:29 - 1.10 +++ float8-small-is-zero.out5 Apr 2006 14:26:25 - @@ -13,17 +13,9 @@ SELECT '-10e400'::float8; ERROR: -10e400 is out of range for type double precision SELECT '10e-400'::float8; - float8 - - 0 -(1 row) - +ERROR: 10e-400 is out of range for type double precision SELECT '-10e-400'::float8; - float8 - --0 -(1 row) - +ERROR: -10e-400 is out of range for type double precision -- bad input INSERT INTO FLOAT8_TBL(f1) VALUES (''); ERROR: invalid input syntax for type double precision: @@ -377,7 +369,9 @@ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); ERROR: -10e400 is out of range for type double precision INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); +ERROR: 10e-400 is out of range for type double precision INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); +ERROR: -10e-400 is out of range for type double precision -- maintain external table consistency across platforms -- delete all values and reinsert well-behaved ones DELETE FROM FLOAT8_TBL; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stats collection on Windows
Peter Brant [EMAIL PROTECTED] writes: I added some strategic printfs to pgstat.c. Attached is the output when a little program is run which, in a loop, makes 10 connections, sleeps 3 seconds, closes them, sleeps another 3 seconds. My workstation (Windows XP) was otherwise idle. Search for is known to be dead, ignoring to find the re-used process IDs. Things start out clean, but after a few cycles anywhere between 1 and 5 backends are being missed. Looking at the pgstats code, I notice that once it makes an entry in the dead-backends hashtable, it keeps that entry (rejecting any messages with the same PID) for 10 seconds. That seems like approximately forever on modern machines, certainly much more than any plausible out-of-order condition in the UDP packet stream. It could easily be enough to get us in trouble on Unix machines, never mind Windows. A conservative suggestion would be to trim down the destroy interval. A more radical one is to question whether we need the destroy delay mechanism at all. What if we got rid of all that logic and simply let the collector delete stuff when it's told to? Out-of-order messages could cause entries to be re-created after they've been deleted, but I'm not sure that I see any harm in that. Bogus DB and table entries are already ignored in the pgstats views (because they won't join to anything in the system catalogs) and we also have a filter for bogus backend entries. There are also mechanisms that ensure these entries will go away eventually: pgstat_vacuum_tabstat for DB and table entries, and eventual re-use of a BackendId slot for backends. So I'm sort of thinking that the destroy delay has outlived its usefulness. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Stats collection on Windows
Search for is known to be dead, ignoring to find the re-used process IDs. Things start out clean, but after a few cycles anywhere between 1 and 5 backends are being missed. Looking at the pgstats code, I notice that once it makes an entry in the dead-backends hashtable, it keeps that entry (rejecting any messages with the same PID) for 10 seconds. That seems like approximately forever on modern machines, certainly much more than any plausible out-of-order condition in the UDP packet stream. It could easily be enough to get us in trouble on Unix machines, never mind Windows. A conservative suggestion would be to trim down the destroy interval. A more radical one is to question whether we need the destroy delay mechanism at all. What if we got rid of all that logic and simply let the collector delete stuff when it's told to? Out-of-order messages could cause entries to be re-created after they've been deleted, but I'm not sure that I see any harm in that. Bogus DB and table entries are already ignored in the pgstats views (because they won't join to anything in the system catalogs) and we also have a filter for bogus backend entries. There are also mechanisms that ensure these entries will go away eventually: pgstat_vacuum_tabstat for DB and table entries, and eventual re-use of a BackendId slot for backends. So I'm sort of thinking that the destroy delay has outlived its usefulness. After reviewing my own patch I just sent in (stats write delay), I realised I had just upped that from 10 seconds to 5 minutes. Which is then even longer than forever :) Which prompted me to consider suggesting just this - do we really need the destroy functionality. From what I could tell it did look reasonable to get rid of it for these reasons. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stats collection on Windows
Magnus Hagander [EMAIL PROTECTED] writes: After reviewing my own patch I just sent in (stats write delay), I realised I had just upped that from 10 seconds to 5 minutes. Which is then even longer than forever :) Which prompted me to consider suggesting just this - do we really need the destroy functionality. Which was in fact exactly my train of thought ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Summer of Code Preparation
If nothing else, any of the 'beginner todo' items are likely candidates, though I suspect none of them individually are enough work for an entire summer. If no one beats me to it, I'll try and compile a list of likely TODOs for this. On Apr 5, 2006, at 12:16 AM, Josh Berkus wrote: Folks, I've been warned that Summer of Code is coming up again soon. We need to be ready with proposals which are officially endorsed by the PostgreSQL project. Which means we need: a) Projects which could be accomplished in a summer, and b) Students to do them. We have one or two weeks to get this together. Your help is greatly desired .. and if you're a CS student hacker reading this, drop me a line! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] WAL Bypass for indexes
Title: Converted from Rich Text I wrote: I will run multiple tests and post the actual numbers. I did run more extensive tests and did not bother writing down the numbers, and here's why: the unmodified Pg ran pgbench with a whopping average of 6.3% time in IO wait. If I was able to totally eliminate that time (which is impossible), then the best we could hope for is a 7% increase in performance by skipping WAL of indexes. On a related note, we currently have some indexes that are unsafe during recovery (GIST and Hash come to mind). In the spirit of making Pg "safe at any speed," would it make sense to put some code in the recovery section that rebuilds all indexes whose integrity cannot be assured? M
Re: [HACKERS] WAL Bypass for indexes
Martin Scholes [EMAIL PROTECTED] writes: On a related note, we currently have some indexes that are unsafe during recovery (GIST and Hash come to mind). In the spirit of making Pg safe at any speed, would it make sense to put some code in the recovery section that rebuilds all indexes whose integrity cannot be assured? This is no longer true for GIST, and hash really ought to be fixed to support WAL. The combination of a performance advantage and being able to postpone fixing hash might be enough of a reason to add a post-recovery index rebuild feature, but I don't think the latter alone is --- fixing hash would be less work as well as being the right answer. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stats collection on Windows
On Wed, Apr 05, 2006 at 10:05:56AM -0400, Tom Lane wrote: What happens if process Y goes away between the time you obtain a handle for it and the time you try to run this DuplicateHandle call? Think of Windows HANDLE like UNIX fd, but Windows HANDLE works for everything - not just sockets, files, pipes, and character devices. Process Y doesn't go away until all references to it, via HANDLE, have been closed. It may not be running. It may have an exit status available. It doesn't go away, though, until you are done with it, and everybody who has a reference to it does CloseHandle(). Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Stats collection on Windows
On Wed, Apr 05, 2006 at 10:30:36AM -0400, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: What happens if process Y goes away between the time you obtain a handle for it and the time you try to run this DuplicateHandle call? I can put together some quick test-code for this if you need me to? Nah, it was just a rhetorical question meant to poke a hole in the claim that Windows can avoid race conditions by using HANDLEs. AFAICS, don't-reuse-PIDs-too-quick has exact analogs that Windows has to solve by ensuring it doesn't reuse HANDLEs too quick. No. It means you don't understand what a HANDLE is. But that's fine - because you understand DB stuff to compensate... :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stats collection on Windows
I'm going to rip out the destroy code and see how it goes. Patch will be forthcoming if things turn out well. Pete Tom Lane [EMAIL PROTECTED] 04/05/06 4:49 pm So I'm sort of thinking that the destroy delay has outlived its usefulness. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Control File
Quick (real) story to illustrate situation: Some time ago we used to have a server with 2 disk arrays attached to it. They were /mnt/array1 and /mnt/array2. PostgreSQL (8.0) had tablespaces on both. In one cold dark night, one SCSI controller from array2 stopped, and manufacturer was called to replace it. The system administrator (monkey) from the night called the DBA (sleeping...monkey? -- ups... that's me), and asked for him to stop the database so he could umount /mnt/array2. Sleeping DBA took his coffee, and stopped the database. Monkey umounted array2 and changed the SCSI controller which came alive again! Monkey calls wake DBA (again): - Everything went fine, SCSI controller successfully replaced. Could you please wake up again and start the database? DBA takes another coffee and finally started the database which... just came up! Few minutes latter lot off errors being displayed. What is that??? /mnt/array2 (50% of datafiles and tablespaces were there) was still umounted and even so PostgreSQL came up. -- PostgreSQL stopped, /mnt/array2 mounted, started and... - happy end! After that night, I started to ask myself if PostgreSQL should not have a control file to check if expected datafiles are where they should be and JUST warn about missing ones? If yes, I apply for developing the patch. C ya, Teolupus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Summer of Code Preparation
A list of simpler TODOs would be great. I might be interested in doing something (probably w/o the summer of code because I have a summer job). We'll see after exams finish. Please post something about where we can find this TODO list when it is available. Thanks, NathanOn 4/5/06, Jim Nasby [EMAIL PROTECTED] wrote: If nothing else, any of the 'beginner todo' items are likelycandidates, though I suspect none of them individually are enoughwork for an entire summer.If no one beats me to it, I'll try and compile a list of likely TODOs for this.On Apr 5, 2006, at 12:16 AM, Josh Berkus wrote: Folks, I've been warned that Summer of Code is coming up again soon.We need to be ready with proposals which are officially endorsed by the PostgreSQL project. Which means we need: a) Projects which could be accomplished in a summer, and b) Students to do them. We have one or two weeks to get this together.Your help is greatly desired .. and if you're a CS student hacker reading this, drop me a line! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ?http://www.postgresql.org/docs/faq --Jim C. Nasby, Sr. Engineering Consultant[EMAIL PROTECTED]Pervasive Softwarehttp://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] WAL Bypass for indexes
On Wed, 2006-04-05 at 09:40 -0700, Martin Scholes wrote: I will run multiple tests and post the actual numbers. I did run more extensive tests and did not bother writing down the numbers, and here's why: the unmodified Pg ran pgbench with a whopping average of 6.3% time in IO wait. If I was able to totally eliminate that time (which is impossible), then the best we could hope for is a 7% increase in performance by skipping WAL of indexes. The WAL becomes more of a hotspot as you scale up numbers of CPUs. I guess this idea doesn't make much difference for smaller systems. I think your idea still has merit, Martin. I'll do some tests also. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] request: muting notice CREATE TABLE will create implicit sequence
Hello I am working on general functions accessable from console too. I create tempory tables from functions. Is necessary print notice about using serial type? I think actually we don't need print it, becouse DROP TABLE use dependencies. Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] commit callback, request, SOLVED
Refered triggers works well, better than I expected. It's not equal NOTIFY, but it works. Thank You Pavel Stehule CREATE OR REPLACE FUNCTION dbms_alert._defered_signal() RETURNS trigger AS $$ BEGIN PERFORM dbms_alert._signal(NEW.event, NEW.message); DELETE FROM ora_alerts WHERE id=NEW.id; RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE; CREATE OR REPLACE FUNCTION dbms_alert.signal(_event text, _message text) RETURNS void AS $$ BEGIN PERFORM 1 FROM pg_catalog.pg_class c WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relkind='r' AND c.relname = 'ora_alerts'; IF NOT FOUND THEN CREATE TEMP TABLE ora_alerts(id serial PRIMARY KEY, event text, message text); REVOKE ALL ON TABLE ora_alerts FROM PUBLIC; CREATE CONSTRAINT TRIGGER ora_alert_signal AFTER INSERT ON ora_alerts INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE dbms_alert._defered_signal(); END IF; INSERT INTO ora_alerts(event, message) VALUES(_event, _message); END; $$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER; drop table test_alert cascade; create table test_alert(v varchar); create or replace function checkdata() returns void as $$ declare r record; d record; begin perform dbms_alert.register('refresh'); while true loop select into r * from dbms_alert.waitone('refresh',10); perform pg_sleep(0.1); -- I need wait moment select into d * from test_alert where v = r.message; raise notice 'found %', d; end loop; end; $$ language plpgsql; create or replace function ins(varchar) returns void as $$ begin insert into test_alert values($1); perform dbms_alert.signal('refresh',$1); end; $$ language plpgsql; _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] First Aggregate Funtion?
On Fri, Mar 31, 2006 at 15:02:47 -0600, Tony Caduto [EMAIL PROTECTED] wrote: Has there ever been any talk of adding a first aggregate function? It would make porting from Oracle and Access much easier. Note, that without special support those functions aren't going to run very fast. So you are still probably going to want to go back and rewrite them to use something like DISTINCT ON anyway. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Summer of Code Preparation
Summer of Code projects dont have to revolve around the core project... for example drupal got like 11 projects last year and bricolage got a few too; I got a small list of items that could be looked at that are sort of 3rd party projects, should we attempt to collaborate on putting up a list some place? Robert Treat On Wednesday 05 April 2006 11:22, Jim Nasby wrote: If nothing else, any of the 'beginner todo' items are likely candidates, though I suspect none of them individually are enough work for an entire summer. If no one beats me to it, I'll try and compile a list of likely TODOs for this. On Apr 5, 2006, at 12:16 AM, Josh Berkus wrote: Folks, I've been warned that Summer of Code is coming up again soon. We need to be ready with proposals which are officially endorsed by the PostgreSQL project. Which means we need: a) Projects which could be accomplished in a summer, and b) Students to do them. We have one or two weeks to get this together. Your help is greatly desired .. and if you're a CS student hacker reading this, drop me a line! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Summer of Code Preparation
Robert, Summer of Code projects dont have to revolve around the core project... for example drupal got like 11 projects last year and bricolage got a few too; I got a small list of items that could be looked at that are sort of 3rd party projects, should we attempt to collaborate on putting up a list some place? Sure, although the important part is to find students. I'm not sure how we do that. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Rigth toolset to compile under mingw?
I have tried to compile 8.1.3 with mingw but I am receiving come errors, I do not know with versions are the correct one.Could some body tell me the right versions ? and url's to download from ? please. I searched the mainling lists but it is not clear.Thanks.Atte.Juan Manuel Díaz Lara New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Re: [HACKERS] Summer of Code Preparation
On Wed, 5 Apr 2006, Josh Berkus wrote: Robert, Summer of Code projects dont have to revolve around the core project... for example drupal got like 11 projects last year and bricolage got a few too; I got a small list of items that could be looked at that are sort of 3rd party projects, should we attempt to collaborate on putting up a list some place? Sure, although the important part is to find students. I'm not sure how we do that. Do we have any professors online? How about anyone that works for Sun *wink*, who, I believe, have educational programs that we might be able to tap into? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Speaking of pgstats
Magnus Hagander [EMAIL PROTECTED] writes: While we're talking about pgstats... There was some talk a while back about the whole bufferer/collector combination perhaps being unnecessary as well, and that it might be a good idea to simplify it down to just a collector. I'm not 100% sure what the end result of that discussion was, thouhg, and I can't find it in the archives :-( Yeah, I was thinking that same thing this morning. AFAIR we designed the current structure on paper in a pghackers thread, and never did any serious experimentation to prove that it was worth having the extra process. I concur it's worth at least testing the simpler method. The general idea would be to still use UDP backend-stats but get rid of the pipe part (emulated by standard tcp sockets on win32), so we'd still have the lose packets instead of blocking when falling behind. Right. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Summer of Code Preparation
On Apr 5, 2006, at 5:04 PM, Marc G. Fournier wrote: Sure, although the important part is to find students. I'm not sure how we do that. Do we have any professors online? I'm not one, but I know some. If there is a link with details and perhaps a list of possible projects, I'll be happy to spread the word. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Speaking of pgstats
The general idea would be to still use UDP backend-stats but get rid of the pipe part (emulated by standard tcp sockets on win32), so we'd still have the lose packets instead of blocking when falling behind. Right. Please correct me if I am wrong, but using UDP logging on the same computer is a red herring. Any non-blocking I/O would do, no? If the buffer is full, then the non-blocking I/O send function will fail and the message is skipped. Has anyone observed UDP ever drop *written* packets on loopback? Looking at the Darwin 8 sources, it appears that the loopback streams all converge to the same stream code, which makes sense... If a kernel is too busy to handle I/O, doesn't it have higher priorities than switching to a user context? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Anyone want to finish BEFORE COMMIT triggers?
Folks, Anyone want to finish this work? http://gorda.di.uminho.pt/community/pgsqlhooks/ ... specifically the commit triggers? I may have funding for creating BEFORE COMMIT triggers. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] WAL Bypass for indexes
Title: Converted from Rich Text Simon, The WAL becomes more of a hotspot as you scale up numbers of CPUs. I tend to agree and the original idea came when I was working on a Sun quad-CPU system for a highly parallelized web application. Each page was broken into several dynamic images, each created "on the fly" from dozens of vector objects and all of the data, as well as state information was held in Pg. As a complex app, each page load would spawn a dozen or so processes, each hitting the DB pretty hard, where all of the business logic resided. It didn't take too many concurrent users to bring the server to its knees. Here's the point: some inspection showed that a lot of time was being spent on index output. At that point I realized that there had to be a better way. My simple home system is not capable of recreating those conditions. If you have an SMP box, please run some tests. M _ Original message _ Subject: Re: [HACKERS] WAL Bypass for indexes Author: Simon Riggs [EMAIL PROTECTED] Date: 05th April 2006 11:0:34 AM On Wed, 2006-04-05 at 09:40 -0700, Martin Scholes wrote: I will run multiple tests and post the actual numbers. I did run more extensive tests and did not bother writing down the numbers, and here's why: the unmodified Pg ran pgbench with a whopping average of 6.3% time in IO wait. If I was able to totally eliminate that time (which is impossible), then the best we could hope for is a 7% increase in performance by skipping WAL of indexes. The WAL becomes more of a hotspot as you scale up numbers of CPUs. Iguess this idea doesn't make much difference for smaller systems. I think your idea still has merit, Martin. I'll do some tests also. Best Regards, Simon Riggs ---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] psql \c error
On Mon, 3 Apr 2006 06:13 am, Neil Conway wrote: I've committed a patch to HEAD that should improve this behavior. Let me know if the current behavior is still unsatisfactory. Yes, thanks, it fixes the stuff that bugged me: [EMAIL PROTECTED] pgsql]$ psql -p5434 -dpyarra [snip opening car chase] pyarra=# \c foo You are now connected to database foo. foo=# \c philip - - 5432 You are now connected to database philip at port 5432. philip=# Regards, Philip. -- Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. - Brian W. Kernighan - Utiba Pty Ltd This message has been scanned for viruses and dangerous content by Utiba mail server and is believed to be clean. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Speaking of pgstats
Agent M [EMAIL PROTECTED] writes: Please correct me if I am wrong, but using UDP logging on the same computer is a red herring. Any non-blocking I/O would do, no? If the buffer is full, then the non-blocking I/O send function will fail and the message is skipped. Uh, not entirely. We'd like the thing to drop complete messages, not inject partial messages into the channel causing reader parsing errors. This is one reason for liking UDP semantics better than pipe semantics. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Summer of Code Preparation
One idea that comes to mind is to come up with a list of popular OSS projects that we'd like to see add PostgreSQL support and have students work on those... As for finding students, I believe a call on -general and -announce would probably produce results. I know there's some professors on the lists (most likely to be on -general...) On Apr 5, 2006, at 4:35 PM, Robert Treat wrote: Summer of Code projects dont have to revolve around the core project... for example drupal got like 11 projects last year and bricolage got a few too; I got a small list of items that could be looked at that are sort of 3rd party projects, should we attempt to collaborate on putting up a list some place? Robert Treat On Wednesday 05 April 2006 11:22, Jim Nasby wrote: If nothing else, any of the 'beginner todo' items are likely candidates, though I suspect none of them individually are enough work for an entire summer. If no one beats me to it, I'll try and compile a list of likely TODOs for this. On Apr 5, 2006, at 12:16 AM, Josh Berkus wrote: Folks, I've been warned that Summer of Code is coming up again soon. We need to be ready with proposals which are officially endorsed by the PostgreSQL project. Which means we need: a) Projects which could be accomplished in a summer, and b) Students to do them. We have one or two weeks to get this together. Your help is greatly desired .. and if you're a CS student hacker reading this, drop me a line! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Summer of Code Preparation
From the main website, hit developers, roadmap and then the TODO link on that page. On Apr 5, 2006, at 1:44 PM, Nathan Buchanan wrote: A list of simpler TODOs would be great. I might be interested in doing something (probably w/o the summer of code because I have a summer job). We'll see after exams finish. Please post something about where we can find this TODO list when it is available. Thanks, Nathan On 4/5/06, Jim Nasby [EMAIL PROTECTED] wrote: If nothing else, any of the 'beginner todo' items are likely candidates, though I suspect none of them individually are enough work for an entire summer. If no one beats me to it, I'll try and compile a list of likely TODOs for this. On Apr 5, 2006, at 12:16 AM, Josh Berkus wrote: Folks, I've been warned that Summer of Code is coming up again soon. We need to be ready with proposals which are officially endorsed by the PostgreSQL project. Which means we need: a) Projects which could be accomplished in a summer, and b) Students to do them. We have one or two weeks to get this together. Your help is greatly desired .. and if you're a CS student hacker reading this, drop me a line! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] commit callback, request
The only solution I know if is this patch: http://gorda.di.uminho.pt/community/pgsqlhooks/ Chris Pavel Stehule wrote: Hello Is possible make transaction commit trigger without patching code now? I finding way , but all usable interfaces are static. I remember on diskussion about it and about changes in LISTEN/NOTIFY implementation. Is there any progress? I need it for simulation of Oracle dbms_alert.signal function. Whole dbms_alert package is similar our LISTEN/NOTIFY. Difference is dbms_alert is server side solution and L/N is client side. Is any chance so this interface will be in 8.2? Regards Pavel Stehule _ Don’t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Christopher Kings-Lynne Technical Manager CalorieKing Tel: +618.9389.8777 Fax: +618.9389.8444 [EMAIL PROTECTED] www.calorieking.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Speaking of pgstats
Magnus Hagander [EMAIL PROTECTED] writes: While we're talking about pgstats... There was some talk a while back about the whole bufferer/collector combination perhaps being unnecessary as well, and that it might be a good idea to simplify it down to just a collector. I'm not 100% sure what the end result of that discussion was, thouhg, and I can't find it in the archives :-( After a bit of archives-digging, I think you must be remembering this thread: http://archives.postgresql.org/pgsql-hackers/2006-01/msg00074.php which was considering not only abandoning the intermediate buffer process, but abandoning the assumption that it's OK to drop messages under load. We might or might be ready to go that far, but it's worth re-reading and reflecting --- see particularly Jan's comment at http://archives.postgresql.org/pgsql-hackers/2006-01/msg00088.php regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Summer of Code Preparation
On Wed, 5 Apr 2006, Jim Nasby wrote: One idea that comes to mind is to come up with a list of popular OSS projects that we'd like to see add PostgreSQL support and have students work on those... As nice an idea as this is, we'd also need to quickly co-ordinate with those projects to make sure that there is a semblance of a chance of having those patches included in their distribution ... I realize that the Code of Summer program doesn't need a guarantee that the code will be committed, but if we're going to do something like the above, would rather see it done for projects that wanted the end results ... As for finding students, I believe a call on -general and -announce would probably produce results. I know there's some professors on the lists (most likely to be on -general...) On Apr 5, 2006, at 4:35 PM, Robert Treat wrote: Summer of Code projects dont have to revolve around the core project... for example drupal got like 11 projects last year and bricolage got a few too; I got a small list of items that could be looked at that are sort of 3rd party projects, should we attempt to collaborate on putting up a list some place? Robert Treat On Wednesday 05 April 2006 11:22, Jim Nasby wrote: If nothing else, any of the 'beginner todo' items are likely candidates, though I suspect none of them individually are enough work for an entire summer. If no one beats me to it, I'll try and compile a list of likely TODOs for this. On Apr 5, 2006, at 12:16 AM, Josh Berkus wrote: Folks, I've been warned that Summer of Code is coming up again soon. We need to be ready with proposals which are officially endorsed by the PostgreSQL project. Which means we need: a) Projects which could be accomplished in a summer, and b) Students to do them. We have one or two weeks to get this together. Your help is greatly desired .. and if you're a CS student hacker reading this, drop me a line! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Summer of Code Preparation
Josh Berkus josh@agliodbs.com wrote Sure, although the important part is to find students. I'm not sure how we do that. I noticed two email domains are @mit.edu and @cs.toronto.edu but I am afraid both of them are not students any more :-) Regards, Qingqing ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] domain_in performance considerations
Neil Conway wrote some pretty nice things here: http://www.advogato.org/person/nconway/diary.html?start=26 but commented It would be worthwhile to investigate whether this results in a performance regression, though: there's no easy way to cache the executor machinery needed to evaluate a CHECK constraint in this design, whereas the prior design allowed each call-site to implement its own EState caching. which echoes some performance worries I'd expressed in the original proposal back here: http://archives.postgresql.org/pgsql-hackers/2005-07/msg00320.php I wanted to mention a couple of things for the archives, before they fade from short-term memory: The patch-as-committed arranges to cache the results of GetDomainConstraints, which I think is the worst performance hit involved, since it requires at least one and possibly several indexscans of the pg_constraint system catalog. The cache has lifetime equal to the caller's caching of FmgrInfo for the domain_in function, which is ordinarily query-lifespan, which I think is about right. And the output of GetDomainConstraints is just a palloc'd node tree, so it'll go away when the memory context containing the FmgrInfo is freed. So I don't see any particular issues there. What is not so pleasant is that the domains.c code instantiates and destroys an EState and subsidiary structure for each call, if there are any CHECK constraints to be checked. This is normally only a few malloc's, so it's not *that* big a deal, but it could easily be a performance-limiting factor. It would be just a small change to make the code cache the EState across calls, saving a link to it in the FmgrInfo, but I am worried about that. If the EState's query context is made to be a child of the memory context containing the caller's FmgrInfo, then there is no problem as far as memory management goes --- destroying that parent context will make all the memory associated with the EState go away. The problem is that an EState might have other open resources, principally buffer pins, and there would not be any clean way to close down those resources when the EState is no longer needed. We don't have any sort of shutdown callback that domain_in could make use of in the general case. It's possible that caching the EState would be OK without any shutdown callback, because I suspect that an EState used only for legal CHECK constraint expressions (which may not contain sub-selects) would own no non-memory resources. But it'd take some effort to verify that, and it seems like a pretty fragile assumption over the long haul anyway. Another idea I had looked at seriously was to make callers of input functions pass in an EState if they had one available (this could be done via the context field we already have in FunctionCallInfoData). However, inspection of the existing callers of input functions showed that that'd only readily work for COPY. We could maybe have made it work for plpgsql, but I had a lot of questions as to whether the available EState would have the right lifespan relative to the FmgrInfo. Everybody else was out in the cold anyway, because they had no ready access to any suitable EState. So I felt that I should commit a version-zero of the patch that I was pretty confident would *work*, and not get into these dubious performance-enhancing tweaks. We can try to spin it up from here, if needed. It'd be worth first profiling some domain-intensive queries and seeing if there's any reason to worry or not. In any case, I'll fall back on the wisdom of the sage who said I can make this program arbitrarily fast ... if it doesn't have to give the right answer. Domains are giving measurably more-right answers today than they were yesterday. Making 'em fast comes after. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend