Re: [HACKERS] Get rid of system attributes in pg_attribute?
I'm wondering how useful it is to store explicit representations of the system attributes in pg_attribute. We could very easily hard-wire those things instead, which would make for a large reduction in the number of entries in pg_attribute. (In the current regression database nearly half of the rows have attnum < 0.) I think the impact on the backend would be pretty minimal, but I'm wondering if removing these entries would be likely to break any client-side code. Does anyone know of client code that actually pays attention to pg_attribute rows with negative attnums? Well, apart from a "attnum > 0" clause in phpPgAdmin, I don't think so... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] slow mail server ?
Marc, Below is a message I just received and I'm wondering what's a problem of such delay ? 5 days is too much :) Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 -- Forwarded message -- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by ra.sai.msu.su (8.12.10/8.12.10) with ESMTP id j1L6Mo5P012614; Mon, 21 Feb 2005 09:22:50 +0300 (MSK) Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) by svr4.postgresql.org (Postfix) with ESMTP id 9264A5AFD51; Mon, 21 Feb 2005 06:22:48 + (GMT) X-Original-To: [EMAIL PROTECTED] Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3C73D8BA156 for <[EMAIL PROTECTED]>; Wed, 16 Feb 2005 20:35:42 + (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47785-08 for <[EMAIL PROTECTED]>; Wed, 16 Feb 2005 20:35:20 + (GMT) Received: from lnfm1.sai.msu.ru (lnfm1.sai.msu.ru [195.208.220.1]) by svr1.postgresql.org (Postfix) with ESMTP id 126A78B9EE3 for ; Wed, 16 Feb 2005 20:28:51 + (GMT) Received: from lnfm1.sai.msu.ru (localhost.localdomain [127.0.0.1]) by lnfm1.sai.msu.ru (8.12.8/8.12.8) with ESMTP id j1GKSjOg010158; Wed, 16 Feb 2005 23:28:45 +0300 Received: from localhost ([EMAIL PROTECTED]) by lnfm1.sai.msu.ru (8.12.8/8.12.8/Submit) with ESMTP id j1GKSjaM010154; Wed, 16 Feb 2005 23:28:45 +0300 X-Authentication-Warning: lnfm1.sai.msu.ru: math owned process doing -bs Date: Wed, 16 Feb 2005 23:28:45 +0300 (MSK) From: "Sergey E. Koposov" <[EMAIL PROTECTED]> To: Tom Lane <[EMAIL PROTECTED]> Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0 In-Reply-To: <[EMAIL PROTECTED]> Message-ID: <[EMAIL PROTECTED]> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: [EMAIL PROTECTED] "Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > LOOP > FETCH cur into rec; > RETURN NEXT rec; > EXIT WHEN NOT FOUND; > END LOOP; > RETURN; Don't you think you should have the EXIT *above* the RETURN NEXT? I would expect this to emit a bogus row of nulls after the last row returned by the cursor. (At least that's what I get with current sources. Pre-8.0 it might return the last row twice.) Yes, surely EXIT should be written before RETURN NEXT, it was my error, (thanks, but I've found that error by myself, after posting my message) But that small bug does not affect the original problem. Running it on a 500-million-row table would quite possibly run out of memory or disk space, too, because RETURN NEXT accumulates all the results before the function is actually exited. Yes, that's right, but I did not waited until the whole table was loaded in the function. The error, which is the subject of current thread occured just immediately after "select * from yyy()", so surely was not caused by memory overfilling. Concerning to the exact form of my functions (using cursors, but still collecting all the data in the memory). As I understand this is the only one way (or just the simplest way ???) to execute fully dynamic queries returned by C function in PL/SQL. For the real functions which I use, instead of query = ''SELECT * FROM usno''; I have query = my_C_function(some_args); (see full code in my first message) Sergey E. Koposov Sternberg Astronomical Institute, Moscow University (Russia) Max-Planck Institute for Astronomy (Germany) Internet: [EMAIL PROTECTED], http://lnfm1.sai.msu.su/~math/ ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
> >> One point that I no longer recall the reasoning behind is that xlog.c > >> doesn't think O_SYNC is a preferable default over fsync. > > > >For larger (>8k) transactions O_SYNC|O_DIRECT is only good with the recent > >pending patch to group WAL writes together. The fsync method gives the OS a > >chance to do the grouping. (Of course it does not matter if you have small > >tx < 8k WAL) > > This would be true for fdatasync() but not for fsync(), I think. No, it is only worse with fsync, since that adds a mandatory seek. > On win32 (which started this discussion, fsync will sync the directory > entry as well, which will lead to *at least* two seeks on the disk. > Writing two blocks after each other to an O_SYNC opened file should give > exactly two seeks. I think you are making the following not maintainable assumptions. 1. there is no other outstanding IO on that drive that the OS happily inserts between your two 8k writes 2. the rotational delay is neglectible 3. the per call overhead is neglectible You will at least wait until the heads reach the write position again, since you will not be able to supply the next 8k in time for the drive to continue writing (with the single backend large tx I was referring to). If you doubt what I am saying do dd blocksize tests on a raw device. The results are, that up to ~256kb blocksize you can increase the drive performance on a drive that does not have a powerfailsafe cache, and does not lie about write success. Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
> > On win32 (which started this discussion, fsync will sync the directory > > entry as well, which will lead to *at least* two seeks on the disk. > > Writing two blocks after each other to an O_SYNC opened file should give > > exactly two seeks. > > I think you are making the following not maintainable assumptions. > 1. there is no other outstanding IO on that drive that the OS happily > inserts between your two 8k writes > 2. the rotational delay is neglectible > 3. the per call overhead is neglectible > > You will at least wait until the heads reach the write position again, > since you will not be able to supply the next 8k in time for the drive to > continue writing (with the single backend large tx I was referring to). > > If you doubt what I am saying do dd blocksize tests on a raw device. > The results are, that up to ~256kb blocksize you can increase the drive > performance on a drive that does not have a powerfailsafe cache, and > does not lie about write success. On win32 with standard hardware, WAL O_SYNC gives about 2-3x performance according to pg_bench. This is in part because fsync() on win32 is the 'nuclear option', syncing meta data which slows down things considerably. Not sure about unix, but the win32 O_DIRECT equivalent disables the read cache and also gives slightly faster write performance (presumably from removing the overhead of the cache manager). The other issue is high performance RAID controllers. With dedicated memory and processor, a good raid controller w/bbu might perform significantly better with everything sent right to the controller, all the time. On win32, fsync() bypasses the raid write cache killing the performance gain from moving to a caching RAID controller. Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] slow mail server ?
On Mon, 21 Feb 2005, Oleg Bartunov wrote: Marc, Below is a message I just received and I'm wondering what's a problem of such delay ? 5 days is too much :) It was posted by someone not subscribed to the mailing list, and had to be manually approved by the moderator (me) before it would go through ... Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 -- Forwarded message -- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by ra.sai.msu.su (8.12.10/8.12.10) with ESMTP id j1L6Mo5P012614; Mon, 21 Feb 2005 09:22:50 +0300 (MSK) Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) by svr4.postgresql.org (Postfix) with ESMTP id 9264A5AFD51; Mon, 21 Feb 2005 06:22:48 + (GMT) X-Original-To: [EMAIL PROTECTED] Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3C73D8BA156 for <[EMAIL PROTECTED]>; Wed, 16 Feb 2005 20:35:42 + (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47785-08 for <[EMAIL PROTECTED]>; Wed, 16 Feb 2005 20:35:20 + (GMT) Received: from lnfm1.sai.msu.ru (lnfm1.sai.msu.ru [195.208.220.1]) by svr1.postgresql.org (Postfix) with ESMTP id 126A78B9EE3 for ; Wed, 16 Feb 2005 20:28:51 + (GMT) Received: from lnfm1.sai.msu.ru (localhost.localdomain [127.0.0.1]) by lnfm1.sai.msu.ru (8.12.8/8.12.8) with ESMTP id j1GKSjOg010158; Wed, 16 Feb 2005 23:28:45 +0300 Received: from localhost ([EMAIL PROTECTED]) by lnfm1.sai.msu.ru (8.12.8/8.12.8/Submit) with ESMTP id j1GKSjaM010154; Wed, 16 Feb 2005 23:28:45 +0300 X-Authentication-Warning: lnfm1.sai.msu.ru: math owned process doing -bs Date: Wed, 16 Feb 2005 23:28:45 +0300 (MSK) From: "Sergey E. Koposov" <[EMAIL PROTECTED]> To: Tom Lane <[EMAIL PROTECTED]> Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0 In-Reply-To: <[EMAIL PROTECTED]> Message-ID: <[EMAIL PROTECTED]> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: [EMAIL PROTECTED] "Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > LOOP > FETCH cur into rec; > RETURN NEXT rec; > EXIT WHEN NOT FOUND; > END LOOP; > RETURN; Don't you think you should have the EXIT *above* the RETURN NEXT? I would expect this to emit a bogus row of nulls after the last row returned by the cursor. (At least that's what I get with current sources. Pre-8.0 it might return the last row twice.) Yes, surely EXIT should be written before RETURN NEXT, it was my error, (thanks, but I've found that error by myself, after posting my message) But that small bug does not affect the original problem. Running it on a 500-million-row table would quite possibly run out of memory or disk space, too, because RETURN NEXT accumulates all the results before the function is actually exited. Yes, that's right, but I did not waited until the whole table was loaded in the function. The error, which is the subject of current thread occured just immediately after "select * from yyy()", so surely was not caused by memory overfilling. Concerning to the exact form of my functions (using cursors, but still collecting all the data in the memory). As I understand this is the only one way (or just the simplest way ???) to execute fully dynamic queries returned by C function in PL/SQL. For the real functions which I use, instead of query = ''SELECT * FROM usno''; I have query = my_C_function(some_args); (see full code in my first message) Sergey E. Koposov Sternberg Astronomical Institute, Moscow University (Russia) Max-Planck Institute for Astronomy (Germany) Internet: [EMAIL PROTECTED], http://lnfm1.sai.msu.su/~math/ ---(end of broadcast)--- TIP 8: explain analyze is your friend Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
On Feb 20, 2005, at 11:02 AM, Stephan Szabo wrote: My last company's experience with Oracle support still leaves me questioning that claim. They basically got "don't do that then or move to the newest major revision" when they had a construct which caused the server to stop responding. For the record, that is the kind of support I've got from Informix in the past. Even for issues where the db would reliably return invalid results. What is great is I have one informix db that I cannot take a backup of without causing several DAYS of downtime. Their solution: Do an in-place upgrade and hope it works. Yes, they actually said "And hopefully it will work. You'll need to take a backup to be safe" "But I can't take a backup. That is the point of this call" "Oh, well... umm.. it SHOULD work!" you get the idea. as for the xid wraparound... issuing a NOTICE / ERROR in a new version will be good, but backpatching won't be needed. As others have said, the people who really need this are not smart enough to upgrade / watch for patches / RTFM -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] slow mail server ?
On Mon, 21 Feb 2005, Marc G. Fournier wrote: On Mon, 21 Feb 2005, Oleg Bartunov wrote: Marc, Below is a message I just received and I'm wondering what's a problem of such delay ? 5 days is too much :) It was posted by someone not subscribed to the mailing list, and had to be manually approved by the moderator (me) before it would go through ... thanks, Marc. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 -- Forwarded message -- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by ra.sai.msu.su (8.12.10/8.12.10) with ESMTP id j1L6Mo5P012614; Mon, 21 Feb 2005 09:22:50 +0300 (MSK) Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) by svr4.postgresql.org (Postfix) with ESMTP id 9264A5AFD51; Mon, 21 Feb 2005 06:22:48 + (GMT) X-Original-To: [EMAIL PROTECTED] Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3C73D8BA156 for <[EMAIL PROTECTED]>; Wed, 16 Feb 2005 20:35:42 + (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47785-08 for <[EMAIL PROTECTED]>; Wed, 16 Feb 2005 20:35:20 + (GMT) Received: from lnfm1.sai.msu.ru (lnfm1.sai.msu.ru [195.208.220.1]) by svr1.postgresql.org (Postfix) with ESMTP id 126A78B9EE3 for ; Wed, 16 Feb 2005 20:28:51 + (GMT) Received: from lnfm1.sai.msu.ru (localhost.localdomain [127.0.0.1]) by lnfm1.sai.msu.ru (8.12.8/8.12.8) with ESMTP id j1GKSjOg010158; Wed, 16 Feb 2005 23:28:45 +0300 Received: from localhost ([EMAIL PROTECTED]) by lnfm1.sai.msu.ru (8.12.8/8.12.8/Submit) with ESMTP id j1GKSjaM010154; Wed, 16 Feb 2005 23:28:45 +0300 X-Authentication-Warning: lnfm1.sai.msu.ru: math owned process doing -bs Date: Wed, 16 Feb 2005 23:28:45 +0300 (MSK) From: "Sergey E. Koposov" <[EMAIL PROTECTED]> To: Tom Lane <[EMAIL PROTECTED]> Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0 In-Reply-To: <[EMAIL PROTECTED]> Message-ID: <[EMAIL PROTECTED]> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: [EMAIL PROTECTED] "Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > LOOP > FETCH cur into rec; > RETURN NEXT rec; > EXIT WHEN NOT FOUND; > END LOOP; > RETURN; Don't you think you should have the EXIT *above* the RETURN NEXT? I would expect this to emit a bogus row of nulls after the last row returned by the cursor. (At least that's what I get with current sources. Pre-8.0 it might return the last row twice.) Yes, surely EXIT should be written before RETURN NEXT, it was my error, (thanks, but I've found that error by myself, after posting my message) But that small bug does not affect the original problem. Running it on a 500-million-row table would quite possibly run out of memory or disk space, too, because RETURN NEXT accumulates all the results before the function is actually exited. Yes, that's right, but I did not waited until the whole table was loaded in the function. The error, which is the subject of current thread occured just immediately after "select * from yyy()", so surely was not caused by memory overfilling. Concerning to the exact form of my functions (using cursors, but still collecting all the data in the memory). As I understand this is the only one way (or just the simplest way ???) to execute fully dynamic queries returned by C function in PL/SQL. For the real functions which I use, instead of query = ''SELECT * FROM usno''; I have query = my_C_function(some_args); (see full code in my first message) Sergey E. Koposov Sternberg Astronomical Institute, Moscow University (Russia) Max-Planck Institute for Astronomy (Germany) Internet: [EMAIL PROTECTED], http://lnfm1.sai.msu.su/~math/ ---(end of broadcast)--- TIP 8: explain analyze is your friend Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)---
Re: [HACKERS] Query optimizer 8.0.1 (and 8.0)
Sounds a bit like multi-dimensional clustering ... http://www.research.ibm.com/mdc/ After the ARC experience though ... -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] sigint psql
Doug McNaught wrote: > John DeSoi <[EMAIL PROTECTED]> writes: > > > I'm communicating with psql via a pipe stream. This works pretty well, > > but one problem I have is trying to cancel an operation. If I send a > > sigint, psql dies. In looking at the source I gather this is because > > it assumes I'm in non-interactive mode (pset.notty is true). I was > > wondering if there was some way to work around this short of > > recompiling the source. I need to do the same thing on Windows. > > > > Thanks for any suggestions, > > On Unix you could run 'psql' through a pty rather than a pipe. No > idea what the Windows equivalent would be. We don't have signals on win32 like we have on Unix so we simulate them. Not sure it is possible to signal psql like you want on Win32. I just tried 'kill -2 _psql_pid_' on unix and it did cancel the current query. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] Data loss, vacuum, transaction wrap-around
Jeff wrote: > > On Feb 20, 2005, at 11:02 AM, Stephan Szabo wrote: > > > My last company's experience with Oracle support still leaves me > > questioning that claim. They basically got "don't do that then or > > move to > > the newest major revision" when they had a construct which caused the > > server to stop responding. > > For the record, that is the kind of support I've got from Informix in > the past. > Even for issues where the db would reliably return invalid results. > > What is great is I have one informix db that I cannot take a backup of > without causing several DAYS of downtime. Their solution: Do an > in-place upgrade and hope it works. Yes, they actually said "And > hopefully it will work. You'll need to take a backup to be safe" "But I > can't take a backup. That is the point of this call" "Oh, well... > umm.. it SHOULD work!" "Uh, did we mention we are sure it would work in DB2." :-) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > It occurs to me that without the explicit entries, we could stop > considering the system names to be reserved column names --- that is, > we could allow users to create ordinary columns by these names. > (The procedure for looking up a column name would be to first try in > pg_attribute, and if that failed to check an internal list of system > column names.) If you did make such a column, then you'd be unable to > get at the system column you'd masked in that particular table. I'm > unsure offhand if this would be a good thing or bad. This sounds bad to me. Maybe not for things like cmin and cmax, but I use ctid a lot, and would be quite thrown off if a table suddenly were allowed to create it's own ctid column that did not behave as the current one does. Perhaps if it was called "pg_ctid?" 1/2 :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200502211318 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCGiY0vJuQZxSWSsgRArjHAKDRsZ47E52fgJXDPPe5SUPoy7mqhACfY9eW QJXKFq0ZTIBnXtodNqXDZig= =kdBu -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] sigint psql
>> > I'm communicating with psql via a pipe stream. This works >pretty well, >> > but one problem I have is trying to cancel an operation. >If I send a >> > sigint, psql dies. In looking at the source I gather this >is because >> > it assumes I'm in non-interactive mode (pset.notty is true). I was >> > wondering if there was some way to work around this short of >> > recompiling the source. I need to do the same thing on Windows. >> > >> > Thanks for any suggestions, >> >> On Unix you could run 'psql' through a pty rather than a pipe. No >> idea what the Windows equivalent would be. > >We don't have signals on win32 like we have on Unix so we >simulate them. >Not sure it is possible to signal psql like you want on Win32. There is no way so signal *psql*, as in the client side process. The signal emulation only runs in the server. You can do the same thing in the server using pg_ctl kill, but that won't work on client processes. //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] sigint psql
On Feb 21, 2005, at 3:00 PM, Magnus Hagander wrote: There is no way so signal *psql*, as in the client side process. The signal emulation only runs in the server. You can do the same thing in the server using pg_ctl kill, but that won't work on client processes. Thanks to everyone for the feedback and suggestions. Since I need this to work on win32, I guess I'll just abort the stream and reconnect. The main issue with this is trying to restore any state associated with the connection (e.g. search_path). John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] sigint psql
Hi Magnus, On Feb 21, 2005, at 3:00 PM, Magnus Hagander wrote: There is no way so signal *psql*, as in the client side process. The signal emulation only runs in the server. But is there some equivalent of pressing ctrl-C to make psql stop what it is doing? For example, through the pipe stream I may execute a \i command and the user wants to stop before the file is processed. What mechanism is used to do this on win32? I thought it could be accomplished on Unix with kill -2, hence my original question. But maybe I'm looking in the wrong place. Thanks, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] sigint psql
John DeSoi wrote: > Hi Magnus, > > On Feb 21, 2005, at 3:00 PM, Magnus Hagander wrote: > > > There is no way so signal *psql*, as in the client side process. The > > signal emulation only runs in the server. > > > > But is there some equivalent of pressing ctrl-C to make psql stop what > it is doing? For example, through the pipe stream I may execute a \i > command and the user wants to stop before the file is processed. What > mechanism is used to do this on win32? I thought it could be > accomplished on Unix with kill -2, hence my original question. But > maybe I'm looking in the wrong place. Win32 doesn't have the granularity to do special signal numbers like we do on Unix, so we have to map control-C to call a special libpq function that communicates with the postmaster/backend to cancel the query. No way an outside process can do that. Your only solution would be to hack psql to accept a signal somehow to call that libpq function, and hack your client to send that signal to psql somehow. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] sigint psql
>> There is no way so signal *psql*, as in the client side process. The >> signal emulation only runs in the server. >> > >But is there some equivalent of pressing ctrl-C to make psql stop what >it is doing? For example, through the pipe stream I may execute a \i >command and the user wants to stop before the file is processed. What >mechanism is used to do this on win32? I thought it could be >accomplished on Unix with kill -2, hence my original question. But >maybe I'm looking in the wrong place. Take a look at GenerateConsoleCtrlEvent(). It can generate a fake Ctrl-C. You'll need to figure out a console group etc, and I can't say I've tested it, but it looks like a possible. //Magnus ---(end of broadcast)--- TIP 3: 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] Design notes for BufMgrLock rewrite
My understanding from this is: If we have a buffer cache hit ratio of 93%, then we should expect: - 93% of buffer requests to require only shared BufMappingLocks - 7% of buffer requests would require an exclusive BufFreelistLock then an exclusive BufMappingLock. That seems like an improvement would come from allowing multiple successful simultaneous cache hits, which would be welcome and that is a very good thing. I also like the simplicity with which the bgwriter will be able to easily stay ahead of the clock sweep, writing out dirty buffers, without taking exclusive system-wide locks. This would produce a StrategyDirtyBufferList design that is not dependant upon size of shared_buffers, further improving the efficacy of this new design since it will allow us to further increase shared_buffers. ISTM this new design will increase scalability directly in line with the cache hit ratio, but would still suffer from poor scalability for cache misses. That concerns me, since a large table scan would require all- exclusive locks to complete its scan, since it will typically be 95%+ cache misses. That would mean that well tuned OLTP applications could be more scalable, but DW or mixed applications would not be. Servers with few CPUs would not see this difference in as marked a way as higher-end servers. The cache would also be spoiled from scans, though I think we can handle those the same way as Vacuum. This design seems to be a clear improvement on the current design. I am still encouraged that the freelist structures should be subdivided into many smaller pieces, thereby producing finer grained locks (the earlier bufferpools proposal). This could be implemented as an additional feature on top of this patch, or as an alternate design on cvstip. [It might be worth having separate bufferpools for indexes and heap blocks, so that seq scans never effect the index cache.] Whatever is done from here, I think it is certain that we can improve things by providing hints from the higher code layers down to the buffer management layer, as everybody keeps suggesting for Vacuum. [I'm assuming that there are no system-wide locks held across I/Os, that bit seems a bit unclear from the description] Best Regards, Simon Riggs On Sun, 2005-02-13 at 17:07 -0500, Tom Lane wrote: > I'm working on an experimental patch to break up the BufMgrLock along > the lines we discussed a few days ago --- in particular, using a clock > sweep algorithm instead of LRU lists for the buffer replacement strategy. > I started by writing up some design notes, which are attached for > review in case anyone has better ideas. > > One thing I realized quickly is that there is no natural way in a clock > algorithm to discourage VACUUM from blowing out the cache. I came up > with a slightly ugly idea that's described below. Can anyone do better? > > regards, tom lane > > > Buffer manager's internal locking > - > > Before PostgreSQL 8.1, all operations of the shared buffer manager itself > were protected by a single system-wide lock, the BufMgrLock, which > unsurprisingly proved to be a source of contention. The new locking scheme > avoids grabbing system-wide exclusive locks in common code paths. It works > like this: > > * There is a system-wide LWLock, the BufMappingLock, that notionally > protects the mapping from buffer tags (page identifiers) to buffers. > (Physically, it can be thought of as protecting the hash table maintained > by buf_table.c.) To look up whether a buffer exists for a tag, it is > sufficient to obtain share lock on the BufMappingLock. Note that one > must pin the found buffer, if any, before releasing the BufMappingLock. > To alter the page assignment of any buffer, one must hold exclusive lock > on the BufMappingLock. This lock must be held across adjusting the buffer's > header fields and changing the buf_table hash table. The only common > operation that needs exclusive lock is reading in a page that was not > in shared buffers already, which will require at least a kernel call > and usually a wait for I/O, so it will be slow anyway. > > * A separate system-wide LWLock, the BufFreelistLock, provides mutual > exclusion for operations that access the buffer free list or select > buffers for replacement. This is always taken in exclusive mode since > there are no read-only operations on those data structures. The buffer > management policy is designed so that BufFreelistLock need not be taken > except in paths that will require I/O, and thus will be slow anyway. > (Details appear below.) It is never necessary to hold the BufMappingLock > and the BufFreelistLock at the same time. > > * Each buffer header contains a spinlock that must be taken when examining > or changing fields of that buffer header. This allows operations such as > ReleaseBuffer to make local state changes without taking any system-wide > lock. We use a spinlock, not an LWLock, si
Re: [HACKERS] 8.0.X and the ARC patent
On Mon, 2005-02-14 at 18:17 -0500, Bruce Momjian wrote: > For development, this means we will _not_ have a shortened, non-initdb > 8.1 release but a regular release cycle with the typical big batch of > features. Might we set a rough date for Beta freeze for 8.1 then? September 30th 2005 ? I see only benefit from publishing a not-before date now. It's up to Core if it slips, but it'll really help with gaining funding if people can accurately determine whether or not features can be added for inclusion in the next release. There are lots of potential donors waiting, so lets give them some certainty about which release their payback will occur in Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: 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] Design notes for BufMgrLock rewrite
Simon Riggs <[EMAIL PROTECTED]> writes: > [I'm assuming that there are no system-wide locks held across I/Os, that > bit seems a bit unclear from the description] That's always been true and still is, so I didn't dwell on it. Only a per-buffer lock is held while doing either input or output. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Design notes for BufMgrLock rewrite
On Mon, 2005-02-21 at 18:01 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > [I'm assuming that there are no system-wide locks held across I/Os, that > > bit seems a bit unclear from the description] > > That's always been true and still is, so I didn't dwell on it. Only a > per-buffer lock is held while doing either input or output. [Me too, thats why its in brackets at the bottom.] ...but do you agree with my comments on the lack of scalability in cache miss situations? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Design notes for BufMgrLock rewrite
Simon Riggs <[EMAIL PROTECTED]> writes: > ...but do you agree with my comments on the lack of scalability in cache > miss situations? No. Grabbing a lock during a cache miss is the least of your worries; you're going to do I/O, or at least a kernel call, so it hardly matters as long as you're not holding the lock for a time that's long in comparison to that overhead. The only test case I've seen that exposes a significant amount of bufmgr contention is one that involves zero I/O (100% cache hit rate), so that the fraction of time spent holding the BufMgrLock is a significant part of the total time. As soon as you move off 100%, the bufmgr isn't the critical path anymore. So I think the fact that this redesign is able to reduce the contention at all in that case is just gravy. (It does reduce contention because ReleaseBuffer doesn't take a global lock anymore, and because BufMappingLock and BufFreelistLock are separate locks.) If testing shows that we still have contention issues with this design then we can try subdividing the BufFreelistLock --- but right now my guess is that we'd just be giving up more cache management efficiency in return for not much. regards, tom lane ---(end of broadcast)--- TIP 3: 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] Design notes for BufMgrLock rewrite
Simon Riggs <[EMAIL PROTECTED]> writes: > This design seems to be a clear improvement on the current design. I am > still encouraged that the freelist structures should be subdivided into > many smaller pieces, thereby producing finer grained locks (the earlier > bufferpools proposal). As I already said, I'm dubious about that idea because of the consequent reduction of cache management efficiency (since any particular page has to fight for survival in a smaller pool). It occurs to me however that we could split up the BufMappingLock in a similar fashion at minimal efficiency penalty. The idea is to replace the global tag->buffer hash table by 2^N separate tables; you determine which one to use based on the low-order N bits of the hash code for the buffer tag, which you always know when accessing these tables. Then give each of these tables its own lock. Essentially this subdivides the buffer tag space into 2^N independent slices. This does not quite work perfectly; the tricky part comes when reclaiming a buffer for use as another page. In the patch as it stands, once we've written out the prior buffer contents we can atomically check for a conflict and reassign the buffer because we need only the one BufMapping lock to do it. But with this idea the old and new associations might belong to different tables. I think the logic would have to be lock old mapping table for buffer; check buffer's not dirty (if so unlock and start over) remove mapping from old table; unlock old table; // at this point we have pin on a completely unassigned buffer lock new mapping table for buffer; check for conflict against someone having already made same entry if found, unlock, put buffer in freelist, use other buffer; insert mapping into new table; unlock new table; This costs us an extra lock/unlock cycle, plus in case of a conflict we end up having unnecessarily evicted a page from cache. But conflicts should be pretty rare, so I think the penalty isn't that great. I don't currently believe that we need this extra complexity, but I thought I'd get the idea into the archives in case it does turn out to be useful later. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] UTF8 or Unicode
Tom Lane wrote: > Bruce Momjian writes: > > I think we just need to _favor_ UTF8. > > I agree. > > > The question is where are we > > favoring Unicode rather than UTF8? > > It's the canonical name of the encoding, both in the code and the docs. > > regression=# create database e encoding 'utf-8'; > CREATE DATABASE > regression=# \l > List of databases > Name| Owner | Encoding > +--+--- > e | postgres | UNICODE > regression | postgres | SQL_ASCII > template0 | postgres | SQL_ASCII > template1 | postgres | SQL_ASCII > (5 rows) > > As soon as we decide whether the canonical name is "UTF8" or "UTF-8" > ;-) we can fix it. I checked and it looks like "UTF-8" is the correct usage: http://www.unicode.org/glossary/ -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-hackers-win32] UNICODE/UTF-8 on win32
Magnus, where are we on this? Seems we should allow unicode encoding and just not unicode locale in pginstaller. Also, Unicode is changing to UTF-8 in 8.1. --- Tatsuo Ishii wrote: > I do understand the problem, but don't undertstand the decision you > guys made. The fact that UPPER/LOWER and some other functions does not > work in win32 is surely a problem for some languages, but not a > problem for otheres. For example, Japanese (and probably Chinese and > Korean) does not have a concept upper/lower. So the fact UPPER/LOWER > does not work with UTF-8/win32 is not problem for Japanese (and for > some other languages). Just using C locale with UTF-8 is enough in > this case. > > In summary, I think you guys are going to overkill the multibyte > support functionality on UTF-8/win32 because of the fact that some > langauges do not work. > > Same thing can be said to EUC-JP, EUC-CN and EUC-KR and so on as well. > > I strongly object the policy to try to unconditionaly disable UTF-8 > support on win32. > -- > Tatsuo Ishii > > From: "Magnus Hagander" <[EMAIL PROTECTED]> > Subject: RE: [pgsql-hackers-win32] UNICODE/UTF-8 on win32 > Date: Sat, 1 Jan 2005 14:48:04 +0100 > Message-ID: <[EMAIL PROTECTED]> > > > UNICODE/UTF-8 does not work on the win32 server. The reason is that > > strcoll() and friends don't work with it. To support it on win32, it > > needs to be converted to UTF16 and use the wide-character versions of > > the fucntion. Which we do not do. > > (See > > http://archives.postgresql.org/pgsql-hackers-win32/2004-11/msg00036.php > > and > > http://archives.postgresql.org/pgsql-hackers-win32/2004-12/msg00106.php) > > > > > > I don't *think* we need to disable ito n the client. AFAIK, the client > > interfaces don't use any of these functions, and I've seen reports of > > people using that long before we had a native win32 server. > > > > > > //Magnus > > > > > > >-Original Message- > > >From: Tatsuo Ishii [mailto:[EMAIL PROTECTED] > > >Sent: den 1 januari 2005 01:10 > > >To: [EMAIL PROTECTED] > > >Cc: Magnus Hagander; [EMAIL PROTECTED] > > >Subject: Re: [pgsql-hackers-win32] UNICODE/UTF-8 on win32 > > > > > > > > >Sorry, but I don't subscribe to pgsql-hackers-win32 list. What's the > > >problem here? > > >-- > > >Tatsuo Ishii > > > > > >> "Magnus Hagander" <[EMAIL PROTECTED]> writes: > > >> > We know it's broken and won't be fixed for 8.0. > > >> > > >> > If we just #ifndef WIN32 the definitions in > > >utils/mb/encnames.c it won't > > >> > be possible to select that encoding, right? Will that have > > >any other > > >> > unwanted effects (such as breaking client encodings)? If > > >not, I suggest > > >> > this is done. > > >> > > >> I believe the subscripts in those arrays have to match the encoding > > >> enum type, so you can't just ifdef out individual entries. > > >> > > >> > (Or perhaps something can be done in pg_valid_server_encoding?) > > >> > > >> Making the valid_server_encoding function reject it might work. > > >> Tatsuo-san would know for sure. > > >> > > >> Should we also reject it as a client encoding, or does that work OK? > > >> > > >> regards, tom lane > > >> > > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
Applied.
---
Bruce Momjian wrote:
> Nicolai Tufar wrote:
> > Hello all,
> >
> > I would like to submit my changes to src/port/snprintf.c to
> > enable %n$ format placeholder replacement in snprintf() and
> > vsnprintf(). Additionally I implemented a trivial printf().
> >
> > I also attach a diff for configure.in to include snprintf.o
> > in pgport but I am sure it is not the right thing to do.
> > Could someone give a hint on where I need to place such a
> > definition.
> >
> > Please review my patch. as Tom Lane pointed out there
> > are 150 messages in the following files that do not print
> > properly:
>
> It took me a while to understand this but I get it now. This is the
> best explanation I have seen, from Linux 2.6:
>
> One can also specify explicitly which argument is taken, at each place
> where an argument is required, by writing '%m$' instead of '%' and '*m$'
> instead of '*', where the decimal integer m denotes the position in the
> argument list of the desired argument, indexed starting from 1. Thus,
>
> printf("%*d", width, num);
>
> and
>
> printf("%2$*1$d", width, num);
>
> are equivalent. The second style allows repeated references to the
> same argument. The C99 standard does not include the style using '$',
> which comes from the Single Unix Specification. If the style using '$'
> is used, it must be used throughout for all conversions taking an
> argument and all width and precision arguments, but it may be mixed with
> '%%' formats which do not consume an argument. There may be no gaps in
> the numbers of arguments specified using '$'; for example, if
> arguments 1 and 3 are specified, argument 2 must also be specified
> somewhere in the format string.
>
> I can see why this would be useful for translations because it uncouples
> the order of the printf arguments from the printf string. However, I
> have learned that Win32, HP-UX, NetBSD 2.0, and BSD/OS do not support
> this. This is probably because it is not in C99 but in SUS (see above).
>
> Anyway, this is too large to put into 8.0, but I am attaching a patch
> for 8.1 that has the proper configure tests to check if the C library
> supports this behavior. If it does not, the build will use our
> port/snprintf.c.
>
> One problem with that is that our snprintf.c is not thread-safe. Seems
> the increases use of it will require us to fix this soon. I have added
> to TODO:
>
> * Make src/port/snprintf.c thread-safe
>
> One change to the original port is that there was a define of a union
> with no name:
>
> + union{
> + void* value;
> + long_long numvalue;
> + double fvalue;
> + int charvalue;
> + };
>
> As far as I know a union with no name is illegal. I just removed the
> "union {" and the closing brace.
>
> --
> Bruce Momjian| http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup.| Newtown Square, Pennsylvania 19073
> Index: configure
> ===
> RCS file: /cvsroot/pgsql/configure,v
> retrieving revision 1.425
> diff -c -c -r1.425 configure
> *** configure 18 Jan 2005 05:23:35 - 1.425
> --- configure 13 Feb 2005 23:50:46 -
> ***
> *** 12162,12167
> --- 12162,12224
> done
>
>
> + echo "$as_me:$LINENO: checking printf supports argument control" >&5
> + echo $ECHO_N "checking printf supports argument control... $ECHO_C" >&6
> + if test "${pgac_cv_printf_arg_control+set}" = set; then
> + echo $ECHO_N "(cached) $ECHO_C" >&6
> + else
> + if test "$cross_compiling" = yes; then
> + pgac_cv_printf_arg_control=cross
> + else
> + cat >conftest.$ac_ext <<_ACEOF
> + #line $LINENO "configure"
> + #include "confdefs.h"
> + #include
> +
> + int does_printf_have_arg_control()
> + {
> + char buf[100];
> +
> + /* can it swap arguments? */
> + snprintf(buf, 100, "%2$d|%1$d", 3, 4);
> + if (strcmp(buf, "4|3") != 0)
> + return 0;
> + return 1;
> + }
> + main() {
> + exit(! does_printf_have_arg_control());
> + }
> + _ACEOF
> + rm -f conftest$ac_exeext
> + if { (eval echo "$as_me:$LINENO: \"$ac_link\"") >&5
> + (eval $ac_link) 2>&5
> + ac_status=$?
> + echo "$as_me:$LINENO: \$? = $ac_status" >&5
> + (exit $ac_status); } && { ac_try='./conftest$ac_exeext'
> + { (eval echo "$as_me:$LINENO: \"$ac_try\"") >&5
> + (eval $ac_try) 2>&5
> + ac_status=$?
> + echo "$as_me:$LINENO: \$? = $ac_status" >&5
> + (exit $ac_status); }; }; then
> + pgac_cv_printf_arg_control=yes
> + el
[HACKERS] left-deep plans?
Presently the planner considers left-deep, right-deep, and bushy plans (i.e. it will consider plans in which the outer operand of a join is a join, the inner operand is a join, or both operands are joins). It is a fairly standard heuristic in the literature to restrict the search to left-deep plans, on the grounds that this significantly reduces the set of plans to consider, and the more efficient plans are _usually_ found in the set of left-deep plans (since we can do pipelining more efficiently). Has there been any thought about applying this optimization? (I doubt it would be wise to unconditionally restrict the search to left-deep plans, but there may be situations in which applying this heuristic would allow the regular planner to be used instead of GEQO. Perhaps a GUC variable?) -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] psql: recall previous command?
Is there a way to recall the previous command in psql? Obviously, "up arrow" or Ctrl-P using readline and the default readline bindings is close, but it recalls the previous _line_ of input. That is not at all the same thing in the case of a multiple line command, for example. If there is no way at present, I think there should be. Using "up arrow" is quite annoying when dealing with multiple line SQL statements. Two issues: - how to handle slash commands? Slash commands and SQL statements can be intermixed: neilc=# select 1 \timing Timing is off. neilc-# ; ?column? -- 1 (1 row) So I'm not quite sure what the right behavior here is. We could always just ignore slash commands (the command would "recall the previous SQL statement") -- since few slash commands are multi-line, I don't think that would be too bad. - when a multiple-line command is recalled, how should it be presented in the psql input buffer? Perhaps we could strip newlines from recalled command text, so that the recalled command would fit on a single line. That would mean the recalled command would look somewhat different than how the user typed it, although of course the semantics of the query should be the same. Any better ideas? -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] psql: recall previous command?
In <[EMAIL PROTECTED]>, on 02/22/05 at 03:26 PM, Neil Conway <[EMAIL PROTECTED]> said: >Is there a way to recall the previous command in psql? Obviously, "up >arrow" or Ctrl-P using readline and the default readline bindings is >close, but it recalls the previous _line_ of input. That is not at all >the same thing in the case of a multiple line command, for example. >If there is no way at present, I think there should be. Using "up arrow" >is quite annoying when dealing with multiple line SQL statements. >Two issues: >- how to handle slash commands? Slash commands and SQL statements can be >intermixed: >neilc=# select 1 \timing >Timing is off. >neilc-# ; > ?column? >-- > 1 >(1 row) >So I'm not quite sure what the right behavior here is. We could always >just ignore slash commands (the command would "recall the previous SQL >statement") -- since few slash commands are multi-line, I don't think >that would be too bad. >- when a multiple-line command is recalled, how should it be presented >in the psql input buffer? Perhaps we could strip newlines from recalled >command text, so that the recalled command would fit on a single line. >That would mean the recalled command would look somewhat different than >how the user typed it, although of course the semantics of the query >should be the same. Any better ideas? >-Neil How about a multi-line buffer that picks up everything from the last \g or ; to the next \g or ; That would pick up everything, That could then be pushed into the input buffer except for the last line containing the ; or \g The last line would have to pushed in when the user pressed the return key. -- --- [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] psql: recall previous command?
On Tue, Feb 22, 2005 at 03:26:52PM +1100, Neil Conway wrote: > Is there a way to recall the previous command in psql? Obviously, "up > arrow" or Ctrl-P using readline and the default readline bindings is > close, but it recalls the previous _line_ of input. That is not at all > the same thing in the case of a multiple line command, for example. Something else to be considered about readline behavior: prepending a # on M-# is useless. -- in that context would be much better, or /* if in a multiline command. (Just something I have had in my to-do list for a while ...) -- Alvaro Herrera (<[EMAIL PROTECTED]>) "La felicidad no es mañana. La felicidad es ahora" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Fwd: Apple Darwin disabled fsync?
On Sun, Feb 20, 2005 at 10:50:35PM -0500, Greg Stark wrote: > > Peter Bierman <[EMAIL PROTECTED]> writes: > > > I think the intent of fsync() is closer to what you describe, but the > > convention is that fsync() hands responsibility to the disk hardware. > > The "convention" was also that the hardware didn't confirm the command until > it had actually been executed... > > None of this matters to the application. A specification for fsync(2) that > says it forces the data to be shuffled around under the hood but fundamentally > the doesn't change the semantics (that the data isn't guaranteed to be in > non-volatile storage) means that fsync didn't really do anything. The real issue is this isn't specific to OS X. I know FreeBSD enables write-caching on IDE drives by default, and I suspect linux does as well. It's probably worth adding a big, fat WARNING in the docs in strategic places about this. -- Jim C. Nasby, Database Consultant [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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] left-deep plans?
Neil Conway <[EMAIL PROTECTED]> writes: > Presently the planner considers left-deep, right-deep, and bushy plans > (i.e. it will consider plans in which the outer operand of a join is a > join, the inner operand is a join, or both operands are joins). It is a > fairly standard heuristic in the literature to restrict the search to > left-deep plans, on the grounds that this significantly reduces the set > of plans to consider, and the more efficient plans are _usually_ found > in the set of left-deep plans (since we can do pipelining more > efficiently). Has there been any thought about applying this optimization? Yes, and it's been rejected. The notion is obviously bogus; it amounts to assuming that every database is a star schema with only one core table. The left-deep vs right-deep case is more tricky, since on its face that's redundant; but I believe we have things fixed so that we aren't considering redundant plans wholesale. (Note the elimination of match_unsorted_inner in joinpath.c.) Once we get into GEQO territory, we are using the left-deep-only heuristic because that's the only kind of plan GEQO can construct. But at that point you've already given up any notion of exhaustive search. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] left-deep plans?
Tom Lane wrote: Yes, and it's been rejected. The notion is obviously bogus; it amounts to assuming that every database is a star schema with only one core table. Interesting; yes, I suppose that's true. Once we get into GEQO territory, we are using the left-deep-only heuristic because that's the only kind of plan GEQO can construct. But at that point you've already given up any notion of exhaustive search. I think most applications would prefer an exhaustive, deterministic search of a subset of the search space over a non-exhaustive, non-deterministic search of the same subset, given approximately the same performance. In other words, if confining the search to left-deep plans allows people to use the normal planner in situations where they would normally be forced to use GEQO to get acceptable performance, I think that would be a win. Speaking of which, why does GEQO restrict its search to left-deep plans only? -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] left-deep plans?
Neil Conway <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Once we get into GEQO territory, we are using the left-deep-only >> heuristic because that's the only kind of plan GEQO can construct. > I think most applications would prefer an exhaustive, deterministic > search of a subset of the search space over a non-exhaustive, > non-deterministic search of the same subset, given approximately the > same performance. I am not by any means standing up to defend GEQO as being the best way to do partial searches ;-). Just saying that in the regime where we can hope to do complete searches, we shouldn't exclude bushy plans. > Speaking of which, why does GEQO restrict its search to left-deep plans > only? Well, because it's really a traveling-salesman algorithm, and it models the "find a good join tree" problem as "find a good tour". I've commented before that I don't believe this is a particularly good model --- intuitively it doesn't seem that the cost functions have the same structure. But I've not had time to look for a better heuristic algorithm. Just one of the many things on the TODO list ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] psql: recall previous command?
On Tue, 22 Feb 2005 15:26:52 +1100, Neil Conway <[EMAIL PROTECTED]> wrote: > Is there a way to recall the previous command in psql? Obviously, "up > arrow" or Ctrl-P using readline and the default readline bindings is > close, but it recalls the previous _line_ of input. That is not at all > the same thing in the case of a multiple line command, for example. Just checking: do you mean something like the \e and \p commands? test=> SELECT \timing Timing is off. test-> 'Hello World'; ?column? - Hello World (1 row) test=> \p SELECT 'Hello World'; test=> Ian Barwick ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] psql: recall previous command?
Neil Conway <[EMAIL PROTECTED]> writes: > - when a multiple-line command is recalled, how should it be presented > in the psql input buffer? Perhaps we could strip newlines from recalled > command text, so that the recalled command would fit on a single line. That fails on at least three cases: -- comments, newlines separating parts of quoted literals (cf bizarre SQL spec requirement that adjacent literals are merged only if separated by a newline), and newlines embedded in quoted literals. You could possibly work around the first two cases by doing nontrivial editing of the query string, but I see no way around the last one. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
