Re: [HACKERS] Locale question
[EMAIL PROTECTED] writes: > I have a few people in Europe trying out the rc1 port for OS/2 and they > have run into a problem with the locale settings > They have a locale set as de_DE_EURO and the initdb program really does > not like this because the setlocale(LC_MESSAGES, NULL) call returns a zero > length string. Hm. We've seen occasional reports of this sort of failure on several platforms for some time, but no one's quite figured out what's going on. In fact, no one's yet offered a test case that anyone else could reproduce reliably :-(. My bet is that this isn't an OS/2-specific issue but something centering on particular locale definitions. If you check the archives you will find very similar-looking reports on Mac OS X, and I think on some other platforms too. One idea I'd had is that we know locales are dependent on character set encodings, and so some platforms might reject an LC_MESSAGES (or LC_anything) setting that's not compatible with LC_CTYPE (assuming that that's what the platform takes as the bottom-level encoding spec). I don't have any proof of this though. It would be great to have a reproducible test case on an open-source platform to poke at, because then we could trace through the locale library and figure out exactly what it is unhappy about. OS/2 isn't open source, but maybe if you can determine the *exact* circumstances needed to provoke the problem there, we could transpose them to a platform we can debug better. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RC2 and open issues
Gavin Sherry <[EMAIL PROTECTED]> writes: > I was also thinking of benchmarking the effect of changing the algorithm > in StrategyDirtyBufferList(): currently, for each iteration of the loop we > read a buffer from each of T1 and T2. I was wondering what effect reading > T1 first then T2 and vice versa would have on performance. Looking at StrategyGetBuffer, it definitely seems like a good idea to try to keep the bottom end of both T1 and T2 lists clean. But we should work at T1 a bit harder. The insight I take away from today's discussion is that there are two separate goals here: try to keep backends that acquire a buffer via StrategyGetBuffer from being fed a dirty buffer they have to write, and try to keep the next upcoming checkpoint from having too much work to do. Those are both laudable goals but I hadn't really seen before that they may require different strategies to achieve. I'm liking the idea that bgwriter should alternate between doing writes in pursuit of the one goal and doing writes in pursuit of the other. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] rc2 bundled
Will do... thanks Lorne In <[EMAIL PROTECTED]>, on 12/21/04 at 01:56 AM, "Marc G. Fournier" <[EMAIL PROTECTED]> said: >check her over .. > >Marc G. Fournier Hub.Org Networking Services >(http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy > >ICQ: 7615664 >---(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 -- --- [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] rc2 bundled
check her over .. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Call for porting reports
Postgresql-8.0.0rc1 hardware: HP Dual PPro os: Linux Slackware 10.0.0 kernel: 2.6.9-ac16 SMP gcc: 3.3.4 configure: ./configure --prefix=/usr/local/pgsql --with-tcl --with-perl --with-x --enable-syslog --with-openssl --with-pgport=5432 --with-odbc --enable-thread-safety 8.0.0beta4 was installed make check failed the first time with a "cannot set locale" error. I installed 8.0.0rc1, make check was successful == All 96 tests passed. == Thanks for a great database, David Walker ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] VERY URGENT
Respected Sir This is srinvas. I have been working with Postgresql and have created tables,constraints and so on. Now i am writing stored functions using refcursor and the stored function have created successfully. But i have problem to validate the parameters passed in the call function.. The query in function returns all the rows with the following statement. open refcursor for 'select statement'; // without parameters validation Now i want to validate the parameters and add those validations to the above select query to restrict the output when the parameter is not null. Example: if parameter1 is not null then sqlstring := sqlstring || ' where num=1'; else sqlstring:= ' '; end if open refcursor for ' select statement ' || sqlstring; // Query should be select * from table_name where num=1; When i used the above statements and execute the function it's not returning the data even the parameter validation is true(num=1 is existing). Please help on above issues and also let me know how to diplay string values (sqlstring). Example :-(in oracle) dbms_output.put_line(sqlstring); I would be very greatful if you could accelerate your reply. Thank you Best Regards Srinivas
Re: [HACKERS] Port report: NetBSD 2.0 mac68k
Le 16 déc. 04, à 22:48, Bruce Momjian a écrit : I am confused by the threading failure. I don't see any free() call in thread_test.c. Would you go to the tools/thread directory and run the program manually and use a debugger to see the failure line? Is there some threading flag NetBSD requires for compiles or linking? Ok. I must have made an error reporting the output of the thread safety test. Here is the output, for ./configure --enable-thread-safety: configure:18831: ./conftest Your errno is thread-safe. Your system uses strerror() which is not thread-safe. ** Your system uses getpwuid() which is not thread-safe. ** Your system has getaddrinfo(); it does not need gethostbyname() or gethostbyname_r(). ** YOUR PLATFORM IS NOT THREAD-SAFE. ** Regards, Rémi Zara -- Rémi Zara http://www.remi-zara.net/ smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] RC2 and open issues
Gavin Sherry wrote: > Neil and I spoke with Jan briefly last week and he mentioned a few > different approaches he'd been tossing over. Firstly, for alternative > runs, start X% on from the LRU, so that we aren't scanning clean buffers > all the time. Secondly, follow something like the approach you've > mentioned above but remember the offset. So, if we're scanning 10%, after > 10 runs we will have written out all buffers. > > I was also thinking of benchmarking the effect of changing the algorithm > in StrategyDirtyBufferList(): currently, for each iteration of the loop we > read a buffer from each of T1 and T2. I was wondering what effect reading > T1 first then T2 and vice versa would have on performance. I haven't > thought about this too hard, though, so it might be wrong headed. So we are all thinking in the same direction. We might have only a few days to finalize this before final release. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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/faqs/FAQ.html
Re: [HACKERS] RC2 and open issues
On Mon, 20 Dec 2004, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Exactly. But 1% would be uselessly small with this definition. Offhand > >> I'd think something like 50% might be a starting point; maybe even more. > >> What that says is that a page isn't a candidate to be written out by the > >> bgwriter until it's fallen halfway down the LRU list. > > > So we are not scanning by buffer address but using the LRU list? Are we > > sure they are mostly dirty? > > No. The entire point is to keep the LRU end of the list mostly clean. > > Now that you mention it, it might be interesting to try the approach of > doing a clock scan on the buffer array and ignoring the ARC lists > entirely. That would be a fundamentally different way of envisioning > what the bgwriter is supposed to do, though. I think the main reason > Jan didn't try that was he wanted to be sure the LRU page was usually > clean so that backends would seldom end up doing writes for themselves > when they needed to get a free buffer. Neil and I spoke with Jan briefly last week and he mentioned a few different approaches he'd been tossing over. Firstly, for alternative runs, start X% on from the LRU, so that we aren't scanning clean buffers all the time. Secondly, follow something like the approach you've mentioned above but remember the offset. So, if we're scanning 10%, after 10 runs we will have written out all buffers. I was also thinking of benchmarking the effect of changing the algorithm in StrategyDirtyBufferList(): currently, for each iteration of the loop we read a buffer from each of T1 and T2. I was wondering what effect reading T1 first then T2 and vice versa would have on performance. I haven't thought about this too hard, though, so it might be wrong headed. > > Maybe we need a hybrid approach: clean a few percent of the LRU end of > the ARC list in order to keep backends from blocking on writes, plus run > a clock scan to keep checkpoints from having to do much. But that's way > beyond what we have time for in the 8.0 cycle. Definately. > > regards, tom lane Thanks, Gavin ---(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] RC2 and open issues
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Exactly. But 1% would be uselessly small with this definition. Offhand > >> I'd think something like 50% might be a starting point; maybe even more. > >> What that says is that a page isn't a candidate to be written out by the > >> bgwriter until it's fallen halfway down the LRU list. > > > So we are not scanning by buffer address but using the LRU list? Are we > > sure they are mostly dirty? > > No. The entire point is to keep the LRU end of the list mostly clean. > > Now that you mention it, it might be interesting to try the approach of > doing a clock scan on the buffer array and ignoring the ARC lists > entirely. That would be a fundamentally different way of envisioning > what the bgwriter is supposed to do, though. I think the main reason > Jan didn't try that was he wanted to be sure the LRU page was usually > clean so that backends would seldom end up doing writes for themselves > when they needed to get a free buffer. > > Maybe we need a hybrid approach: clean a few percent of the LRU end of > the ARC list in order to keep backends from blocking on writes, plus run > a clock scan to keep checkpoints from having to do much. But that's way > beyond what we have time for in the 8.0 cycle. OK, so we scan from the end of the LRU. If we scan X% and find _no_ dirty buffers perhaps we should start where we left off last time. If we don't start where we left off, I am thinking if you do a lot of writes then do nothing, the next checkpoint would be huge because a lot of the LRU will be dirty because the bgwriter never got to it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Locale question
Hi I have a few people in Europe trying out the rc1 port for OS/2 and they have run into a problem with the locale settings They have a locale set as de_DE_EURO and the initdb program really does not like this because the setlocale(LC_MESSAGES, NULL) call returns a zero length string. When the postgres code finds that the lc_messages does not = the return from LC_CTYPE it appears to attempt to see if it is a known language setup and something goes wrong. When the local is de_DE everything works fine. Can someone point me to where the locale string is tested to be a known type, or explain how to track down the problem? If I patch initdb to just use the return from LC_CTYPE as the value for lc_messages the init conpletes and the database will start up OK Should I be doing something else to fix this problem? This apparently also happened with the 7.4.x postgres code as well. Thanks Lorne -- --- [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RC2 and open issues
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Exactly. But 1% would be uselessly small with this definition. Offhand >> I'd think something like 50% might be a starting point; maybe even more. >> What that says is that a page isn't a candidate to be written out by the >> bgwriter until it's fallen halfway down the LRU list. > So we are not scanning by buffer address but using the LRU list? Are we > sure they are mostly dirty? No. The entire point is to keep the LRU end of the list mostly clean. Now that you mention it, it might be interesting to try the approach of doing a clock scan on the buffer array and ignoring the ARC lists entirely. That would be a fundamentally different way of envisioning what the bgwriter is supposed to do, though. I think the main reason Jan didn't try that was he wanted to be sure the LRU page was usually clean so that backends would seldom end up doing writes for themselves when they needed to get a free buffer. Maybe we need a hybrid approach: clean a few percent of the LRU end of the ARC list in order to keep backends from blocking on writes, plus run a clock scan to keep checkpoints from having to do much. But that's way beyond what we have time for in the 8.0 cycle. 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] RC2 and open issues
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I am confused. If we change the percentage to be X% of the entire > > buffer cache, and we set it to 1%, and we exit when either the dirty > > pages or % are reached, don't we end up just scanning the first 1% of > > the cache over and over again? > > Exactly. But 1% would be uselessly small with this definition. Offhand > I'd think something like 50% might be a starting point; maybe even more. > What that says is that a page isn't a candidate to be written out by the > bgwriter until it's fallen halfway down the LRU list. So we are not scanning by buffer address but using the LRU list? Are we sure they are mostly dirty? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RC2 and open issues
Bruce Momjian <[EMAIL PROTECTED]> writes: > I am confused. If we change the percentage to be X% of the entire > buffer cache, and we set it to 1%, and we exit when either the dirty > pages or % are reached, don't we end up just scanning the first 1% of > the cache over and over again? Exactly. But 1% would be uselessly small with this definition. Offhand I'd think something like 50% might be a starting point; maybe even more. What that says is that a page isn't a candidate to be written out by the bgwriter until it's fallen halfway down the LRU list. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] RC2 and open issues
Bruce Momjian <[EMAIL PROTECTED]> writes: > You need a "clock sweep" like BSD uses (and probably others). No, that's *fundamentally* wrong. The reason we are going to the trouble of maintaining a complicated cache algorithm like ARC is so that we can tell the heavily used pages from the lesser used ones. To throw away that knowledge in favor of doing I/O with a plain clock sweep algorithm is just wrong. What's more, I don't even understand what clock sweep would mean given that the ordering of the list is constantly changing. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] rc1 packaged ...
On Mon, 20 Dec 2004, Bruce Momjian wrote: Marc G. Fournier wrote: On Tue, 14 Dec 2004, Peter Eisentraut wrote: Am Samstag, 4. Dezember 2004 00:12 schrieb Marc G. Fournier: look her over ... I forced a sync to the ftp.postgresql.org server, so its available there ... will announce later this evening baring any 'its broken' commends ;) You are building the documentation with old stylesheets. Please use version 1.79, as announced some time ago on pgsql-docs. Let me know if you have problems, but we should get this fixed for the release. 'k, I'm about to screw up rc2 for this too ... FreeBSD ports is 'stuck' at 1.78 ... just went to http://sourceforge.net/projects/docbook, and there are two '1.79's ... do both need to be installed, or just one of them? I'm going to build the package right now, but if you can let me know which (or both) need to be installed, I'll get right on that ... Marc, I assume Peter is asleep right now in Germany. :-) Yup, which is why I'm goin to fix this for release, instead of rc2 :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] rc1 packaged ...
Marc G. Fournier wrote: > On Tue, 14 Dec 2004, Peter Eisentraut wrote: > > > Am Samstag, 4. Dezember 2004 00:12 schrieb Marc G. Fournier: > >> look her over ... I forced a sync to the ftp.postgresql.org server, so its > >> available there ... will announce later this evening baring any 'its > >> broken' commends ;) > > > > You are building the documentation with old stylesheets. Please use version > > 1.79, as announced some time ago on pgsql-docs. Let me know if you have > > problems, but we should get this fixed for the release. > > 'k, I'm about to screw up rc2 for this too ... FreeBSD ports is 'stuck' at > 1.78 ... just went to http://sourceforge.net/projects/docbook, and there > are two '1.79's ... do both need to be installed, or just one of them? > > I'm going to build the package right now, but if you can let me know which > (or both) need to be installed, I'll get right on that ... Marc, I assume Peter is asleep right now in Germany. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] RC2 and open issues
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I think the ideal solution would be to remove bgwriter_percent or change > > it to be a percentage of all buffers, not just dirty buffers, so we > > don't have to scan the entire list. If we set the new value to 10% with > > a delay of 1 second, and the bgwriter remembers the place it stopped > > scanning the buffer cache, you will clean out the buffer cache > > completely every 10 seconds. > > But we don't *want* it to clean out the buffer cache completely. You are only cleaning out in pieces over a 10 second period so it is getting dirty. You are not scanning the entire buffer at one time. > There's no point in writing a "hot" page every few seconds. So I don't > think I believe in remembering where we stopped anyway. I was thinking if you are doing this scanning every X milliseconds then after a while the front of the buffer cache will be mostly clean and the end will be dirty so you will always be going over the same early ones to get to the later dirty ones. Remembering the location gives the scan more uniform coverage of the buffer cache. You need a "clock sweep" like BSD uses (and probably others). > I think there's a reasonable case to be made for redefining > bgwriter_percent as the max percent of the total buffer list to scan > (not the max percent of the list to return --- Jan correctly pointed out > that the latter is useless). Then we could modify > StrategyDirtyBufferList so that the percent and maxpages parameters are > passed in, so it can stop as soon as either one is satisfied. This > would be a fairly small/safe code change and I wouldn't have a problem > doing it even at this late stage of the cycle. > > Howeve ... we would have to crank up the default bgwriter_percent, > and I don't know if we have any better idea what to set it to after > such a change than we do now ... Once we make the change we will have to get our testers working on it. We need those figure to change over time based on backends doing writes but ath isn't going to happen for 8.0. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Dump from cygwin directly to windows
Ok, I didn't think you could do a restore as a non-superuser. I had executed the command on the remote(cygwin) machine in this case. I didn't specify a user to have it run as on the windows side (thought it would default to postgres). On the cygwin side I did execute it under a non-superuser domain account. When I first saw all the \N errors I thought it was a unix to windows end of line character conversion error. Thanks for your help. Mike On Sat, 2004-12-18 at 11:00, Tom Lane wrote: > Mike G <[EMAIL PROTECTED]> writes: > > It looks like it started off as a permissions problem. I added the > > users to the database before trying again and this time it worked fine. > > I have attached the log from the original attempt if you wish to have a > > look. > > As best I can tell, you ran the restore script as a non-superuser, which > probably wasn't a good thing to do. 8.0 pg_dump generates scripts that > should cope with this situation a bit more reasonably, but it would > still end up as a do-over in most cases because none of the object > ownerships would come out right. > > regards, tom lane ---(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] postgres protocol dissector plugin for ethereal
At 2004-12-19 17:56:00 +0530, [EMAIL PROTECTED] wrote: > > I've asked the Ethereal people if they want to distribute this with > Ethereal. It's in Ethereal CVS now. -- ams ---(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] rc1 packaged ...
On Tue, 14 Dec 2004, Peter Eisentraut wrote: Am Samstag, 4. Dezember 2004 00:12 schrieb Marc G. Fournier: look her over ... I forced a sync to the ftp.postgresql.org server, so its available there ... will announce later this evening baring any 'its broken' commends ;) You are building the documentation with old stylesheets. Please use version 1.79, as announced some time ago on pgsql-docs. Let me know if you have problems, but we should get this fixed for the release. 'k, I'm about to screw up rc2 for this too ... FreeBSD ports is 'stuck' at 1.78 ... just went to http://sourceforge.net/projects/docbook, and there are two '1.79's ... do both need to be installed, or just one of them? I'm going to build the package right now, but if you can let me know which (or both) need to be installed, I'll get right on that ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Heads up: RC2 this evening
Andrew Dunstan <[EMAIL PROTECTED]> writes: > I am abandoning further effort altogether, because of this: > adunstan: ~/tcl8.4.9/win > $ ./configure --enable-shared > checking for Cygwin environment... yes > configure: error: Compiling under Cygwin is not currently supported. > A maintainer for the Cygwin port of Tcl/Tk is needed. See the README > file for information about building with Mingw. Wow. That implies that the Tcl community knows about the problems and considers them nontrivial to fix (else they'd have just fixed 'em). So we'll just forget that for now... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] RC2 and open issues
Bruce Momjian <[EMAIL PROTECTED]> writes: > I think the ideal solution would be to remove bgwriter_percent or change > it to be a percentage of all buffers, not just dirty buffers, so we > don't have to scan the entire list. If we set the new value to 10% with > a delay of 1 second, and the bgwriter remembers the place it stopped > scanning the buffer cache, you will clean out the buffer cache > completely every 10 seconds. But we don't *want* it to clean out the buffer cache completely. There's no point in writing a "hot" page every few seconds. So I don't think I believe in remembering where we stopped anyway. I think there's a reasonable case to be made for redefining bgwriter_percent as the max percent of the total buffer list to scan (not the max percent of the list to return --- Jan correctly pointed out that the latter is useless). Then we could modify StrategyDirtyBufferList so that the percent and maxpages parameters are passed in, so it can stop as soon as either one is satisfied. This would be a fairly small/safe code change and I wouldn't have a problem doing it even at this late stage of the cycle. Howeve ... we would have to crank up the default bgwriter_percent, and I don't know if we have any better idea what to set it to after such a change than we do now ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Heads up: RC2 this evening
Tom Lane wrote:
here's what is in /usr/lib/tclConfig.sh - maybe there's a clue in there
- or maybe it's just a problem with the Cygwin-supplied package - I have
deliberately not tried to fix this by installing my own build of tcl.
The Cygwin-supplied package is evidently broken beyond belief.
configure is correctly copying these settings from tclConfig.sh, but the
data therein is wrong (or at least useless). If tclConfig.sh doesn't
provide usable information, that is not our problem to fix.
I am abandoning further effort altogether, because of this:
adunstan: ~/tcl8.4.9/win
$ ./configure --enable-shared
creating cache ./config.cache
checking for gcc... gcc
checking whether the C compiler (gcc ) works... yes
checking whether the C compiler (gcc ) is a cross-compiler... no
checking whether we are using GNU C... yes
checking whether gcc accepts -g... yes
checking for ar... ar
checking for ranlib... ranlib
checking for windres... windres
checking whether make sets ${MAKE}... yes
checking for Cygwin environment... yes
configure: error: Compiling under Cygwin is not currently supported.
A maintainer for the Cygwin port of Tcl/Tk is needed. See the README
file for information about building with Mingw.
cheers
andrew
---(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] Heads up: RC2 this evening
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Agreed on missing -ltcl. It seems odd given that the Cygwin case in >> Makefile.shlib does include $(SHLIB_LINK) and pltcl's Makefile does add >> $(TCL_LIB_SPEC) to SHLIB_LINK. Is TCL_LIB_SPEC getting set reasonably >> by configure? > $ grep TCL_ Makefile.global > TCL_LIB_FILE= libtcl84.a > TCL_LIBS= > TCL_LIB_SPEC= > TCL_INCLUDE_SPEC= -I/nonexistent/include > TCL_SHARED_BUILD= 1 > TCL_SHLIB_LD_LIBS = Yuck. > here's what is in /usr/lib/tclConfig.sh - maybe there's a clue in there > - or maybe it's just a problem with the Cygwin-supplied package - I have > deliberately not tried to fix this by installing my own build of tcl. The Cygwin-supplied package is evidently broken beyond belief. configure is correctly copying these settings from tclConfig.sh, but the data therein is wrong (or at least useless). If tclConfig.sh doesn't provide usable information, that is not our problem to fix. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] RC2 and open issues
We are now packaging RC2. If nothing comes up after RC2 is released, we can move to final release. The open items list is attached. The doc changes can be easily completed before final. The only code issue left is with bgwriter. We always knew we needed to find better defaults for its parameters, but we are only now finding more fundamental issues. I think the summary I have seen recently pegs it right --- our use of % of dirty buffers requires a scan of the entire buffer cache, and the current delay of bgwriter is too high, but we can't lower it because the buffer cache scan will become too expensive if done too frequently. I think the ideal solution would be to remove bgwriter_percent or change it to be a percentage of all buffers, not just dirty buffers, so we don't have to scan the entire list. If we set the new value to 10% with a delay of 1 second, and the bgwriter remembers the place it stopped scanning the buffer cache, you will clean out the buffer cache completely every 10 seconds. Right now it seems no one can find proper values. We were clear that this was an issue but it is bad news that we are only addressing it during RC. The 8.1 solution is to have some feedback system so writes by individual backends cause the bgwriter to work more frequently. The big question is what to do during RC2? Do we just leave it as suboptimal knowing we will revisit it in 8.1 or try an incremental solution for 8.0 that might work better. We have to decide now. --- PostgreSQL 8.0 Open Items = Current version at http://candle.pha.pa.us/cgi-bin/pgopenitems. Changes --- * change bgwriter buffer scan behavior? * adjust bgwriter defaults Documentation - * synchonize supported encodings and docs * improve external interfaces documentation section * manual pages Fixed Since Last Beta - -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Documentation on PITR still scarce
Both added to TODO: --- Simon Riggs wrote: > On Mon, 2004-11-29 at 13:10, Bruce Momjian wrote: > > Or TODO maybe worded as: > > > > * Allow the PITR process to be debugged and data examined > > > > Yes, thats good for me... > > Greg's additional request might be worded: > > * Allow a warm standby system to also allow read-only queries > > Thanks. > > > --- > > > > Simon Riggs wrote: > > > On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: > > > > > > > Is this a TODO? > > > > > > Yes, but don't hold your breath on that feature. > > > > > > Gavin and I were discussing briefly a design that would allow something > > > similar to this. The design would allow the user to stop/start recovery > > > and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to > > > implement than the proposal below, which I agree is desirable. We > > > haven't hardly started that discussion yet though. > > > I called this "recovery console" functionality. > > > > > > I'm not sure I like the Suspended Animation phrase, I thought maybe > > > TARDIS or Langston Field sums it up better (kidding...) > > > > > > > Greg Stark wrote: > > > > > > > > > > Tom Lane <[EMAIL PROTECTED]> writes: > > > > > > > > > > > I suppose it might be useful to have some kind of "suspended > > > > > > animation" > > > > > > behavior where you could bring up a backend and look at the > > > > > > database in > > > > > > a strict read-only fashion, not really executing transactions at > > > > > > all, > > > > > > just to see what you had. Then you could end the recovery and go to > > > > > > normal operations, or allow the recovery to proceed further if you > > > > > > decided this wasn't where you wanted to be yet. However that would > > > > > > require a great deal of mechanism we haven't got (yet). In > > > > > > particular > > > > > > there is no such thing as strict read-only examination of the > > > > > > database. > > > > > > > > > > That would be a great thing to have one day for other reasons aside > > > > > from the > > > > > ability to test out a recovered database. It makes warm standby > > > > > databases much > > > > > more useful. > > > > > > > > > > A warm standby is when you keep a second machine constantly up to > > > > > date by > > > > > applying the archived PITR logs as soon as they come off your server. > > > > > You're > > > > > ready to switch over at the drop of a hat and don't have to go > > > > > through the > > > > > whole recovery process, you just switch the database from recovery > > > > > mode to > > > > > active mode and make it your primary database. But in the until then > > > > > the > > > > > backup hardware languishes, completely useless. > > > > > > > > > > Oracle has had a feature for a long time that you can actually open > > > > > the > > > > > standby database in a strict read-only mode and run queries. This is > > > > > great for > > > > > a data warehouse situation where you want to run long batch jobs > > > > > against > > > > > recent data. > > > > > > > > > > > -- > Best Regards, Simon Riggs > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Heads up: RC2 this evening
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Nope. What are the symptoms exactly? > log attached. Looks like there is at least a missing "-ltcl" in the call > to dllwrap, but that's not all. Agreed on missing -ltcl. It seems odd given that the Cygwin case in Makefile.shlib does include $(SHLIB_LINK) and pltcl's Makefile does add $(TCL_LIB_SPEC) to SHLIB_LINK. Is TCL_LIB_SPEC getting set reasonably by configure? On my machine, Makefile.global ends up with TCL_LIB_FILE= libtcl8.4.so TCL_LIBS= -ldld -lm TCL_LIB_SPEC= -L/opt/tcl8.4/lib -ltcl8.4 TCL_INCLUDE_SPEC= -I/opt/tcl8.4/include No idea about the other errors ... one would think they'd be suppressed by the -lpostgres, but evidently not. 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] Heads up: RC2 this evening
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> "Andrew Dunstan" <[EMAIL PROTECTED]> writes: >>> I have not been able to build Cygwin with pltcl, and neither has anyone else >>> to the best of my knowledge. >> >> Has that worked in prior releases? > I have no idea. It's hard to think of a reason in principle why it > shouldn't. Nope. What are the symptoms exactly? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Heads up: RC2 this evening
Tom Lane wrote: "Andrew Dunstan" <[EMAIL PROTECTED]> writes: Anybody got last-minute stuff? I have not been able to build Cygwin with pltcl, and neither has anyone else to the best of my knowledge. Has that worked in prior releases? I have no idea. It's hard to think of a reason in principle why it shouldn't. cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Heads up: RC2 this evening
"Andrew Dunstan" <[EMAIL PROTECTED]> writes: >> Anybody got last-minute stuff? > I have not been able to build Cygwin with pltcl, and neither has anyone else > to the best of my knowledge. Has that worked in prior releases? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] tool for incrementally shrinking bloated tables
Goal: on a prduction server, to gradually shrink a table (no matter how
large) back to < 10% free space without "noticeably" interrupting write
access to it. ("noticeably" = without taking any exclusive locks for
more than a few seconds at a time.)
I am thinking about making this if it proves to be not to difficult.
To accomplish this, tuples need to be moved into free space in the
beginning of the table, and the table must be shrunk using ftruncate().
It seems that I could make these two changes:
(a) Modifying the VACUUM command to take an option that means "pack the
free space map with the pages that occur earliest in
the table rather than the pages with the most free space."
(b) Create a command that will take an exclusive lock, scan a table
backwards until it comes to a tuple that cannot be removed (i.e., a
tuple that is not HEAPTUPLE_DEAD (see scan_heap() in
src/backend/commands/vacuum.c)) or until some preset amount of time has
elapsed, and then ftruncate() the table.
To use this system one would do this:
(1) VACUUM KEEP_EARLY_FREE_PAGES mybloatedtable; -- use
item (a) discussed above
(2) UPDATE mybloatedtable SET foo = foo WHERE ctid > '(n, 0)'; --
move tuples in end of the table to the front.
(3) SHRINK TABLE
mybloatedtable;
-- use item (b) discussed above
Then repeat as many times as necessary to accomplish the desired shrinking.
In defense of the need for this tool: Although this is usually
preventable by proper vacuuming and FSM configuration, often on the list
I see people say that they have a "huge" multi-gigabyte table that is
using up all their drive space, but they cannot afford the interruption
that VACUUM FULL would entail. Also, certain maintenance operations
(e.g., adding a column and populating it within a transaction) can
double the on-disk size of a table, not to mention user error such as
running an unconstrained UPDATE command inside a transaction and then
rolling it back.
Comments? Am I missing some obvious way of accomplishing this goal? Is
anyone working on something like this?
Paul Tillotson
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Heads up: RC2 this evening
Tom Lane said: > Barring loud squawks, Marc will bundle up 8.0RC2 this evening sometime. > Anybody got last-minute stuff? > I have not been able to build Cygwin with pltcl, and neither has anyone else to the best of my knowledge. I will investigate - probably a makefile issue - unless someone else has solved the problem. cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for port reports
8.0.0rc1 builds and passes 'make check' on Gentoo Linux (amd64) with the dependencies I have to hand (no tcl or kerberos): $ ./configure --prefix=/home/oliver/pg/8.0.0rc1 --with-pgport=5800 -enable-thread-safety --with-perl --with-python --with-pam -with-openssl $ uname -a Linux extrashiny 2.6.9-gentoo-r3-patched #3 Sun Nov 14 15:18:33 NZDT 2004 x86_64 AMD Athlon(tm) 64 Processor 3500+ AuthenticAMD GNU/Linux $ 8.0.0rc1/bin/psql template1 -t -c 'select version()' PostgreSQL 8.0.0rc1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 20040623 (Gentoo Linux 3.3.4-r1, ssp-3.3.2-2, pie-8.7.6) -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Re: [HACKERS] Shared row locking
Tom Lane <[EMAIL PROTECTED]> wrote on 20.12.2004, 19:34:21: > Alvaro Herrera writes: > > To solve the problem I want to solve, we have three orthogonal > > possibilities: > > > 1. implement shared row locking using the ideas outlined in the mail > > starting this thread (pg_clog-like seems to be the winner, details TBD). > > > 2. implement shared lock table spill-to-disk mechanism. > > > 3. implement lock escalation. > > Check. > > > - 2 could have a performance impact, and we don't even know how to > > start. For example, what would be an algorithm to decide what locks > > to send to disk? > > LRU, perhaps? That's all open for investigation still. > > #1 could have a pretty serious performance impact, too. For small > numbers of FOR UPDATE locks (too few to force spill to disk) I would > expect #2 to substantially beat #1. #1 essentially imposes the worst > case performance at all times, whereas #2 degrades (at a currently > unknown rate) when there are lots and lots of FOR UPDATE locks. Agreed. [My gut feeling would be against another permanent on-disk structure, since this is one more thing for a user to delete "to save space" etc...] > Most of the applications I've seen don't take out that many FOR UPDATE > locks at once, so I'm unclear on the rationale for choosing a fixed-but- > poor performance curve over one that is fast for few locks and degrades > for many locks. Especially when the value of "many" is > user-configurable. > > Furthermore, we have also seen issues with too many locks on ordinary > objects, which #2 would solve simultaneously. > > So I feel that #2 is clearly the approach to try first. If we find that > we can't do spill-to-disk without serious performance degradation, then I agree. We need to understand what type of application logic we are coding for. In general, I agree with Tom: I haven't seen many programs that use extended SELECT FOR UPDATE logic. However, the ones I have seen have been batch style programs written using a whole-table cursor - these latter ones have been designed for the cursor stability approach. It would be much better to analyze one or more representative application suites to understand which option to pick. Without a set of programs, or some driving force, the wrong one could be picked. Spill-to-disk would not be that bad, since WARNINGs could appear in the log. That's much better than doing a lock escalation, definitely. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Heads up: RC2 this evening
Michael Fuhr <[EMAIL PROTECTED]> writes: > Is the following a plperl problem or does it need to be fixed in > DBD::PgSPI? I never saw any responses. > http://archives.postgresql.org/pgsql-bugs/2004-12/msg00097.php AFAIK it's a PgSPI issue. plperl wraps its spi.c calls in a subtransaction, but it looks like PgSPI isn't doing that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Heads up: RC2 this evening
On Mon, Dec 20, 2004 at 02:04:46PM -0500, Tom Lane wrote: > Barring loud squawks, Marc will bundle up 8.0RC2 this evening sometime. > Anybody got last-minute stuff? Is the following a plperl problem or does it need to be fixed in DBD::PgSPI? I never saw any responses. http://archives.postgresql.org/pgsql-bugs/2004-12/msg00097.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Heads up: RC2 this evening
Barring loud squawks, Marc will bundle up 8.0RC2 this evening sometime. Anybody got last-minute stuff? 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] Shared row locking
Alvaro Herrera <[EMAIL PROTECTED]> writes: > To solve the problem I want to solve, we have three orthogonal > possibilities: > 1. implement shared row locking using the ideas outlined in the mail > starting this thread (pg_clog-like seems to be the winner, details TBD). > 2. implement shared lock table spill-to-disk mechanism. > 3. implement lock escalation. Check. > - 2 could have a performance impact, and we don't even know how to > start. For example, what would be an algorithm to decide what locks > to send to disk? LRU, perhaps? That's all open for investigation still. #1 could have a pretty serious performance impact, too. For small numbers of FOR UPDATE locks (too few to force spill to disk) I would expect #2 to substantially beat #1. #1 essentially imposes the worst case performance at all times, whereas #2 degrades (at a currently unknown rate) when there are lots and lots of FOR UPDATE locks. Most of the applications I've seen don't take out that many FOR UPDATE locks at once, so I'm unclear on the rationale for choosing a fixed-but- poor performance curve over one that is fast for few locks and degrades for many locks. Especially when the value of "many" is user-configurable. Furthermore, we have also seen issues with too many locks on ordinary objects, which #2 would solve simultaneously. So I feel that #2 is clearly the approach to try first. If we find that we can't do spill-to-disk without serious performance degradation, then I'd be inclined to try #1 next. I really don't care for the user-visible semantics changes implied by #3 ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] multi-key index
On Mon, Dec 20, 2004 at 11:58:21AM -0600, Jaime Casanova wrote: Jaime, > I was looking in the archives something about this but > i found nothing. Where can i found the thread (i > suppose should be one) about this issue? Did you use the search engine at http://www.pgsql.ru ? -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Before you were born your parents weren't as boring as they are now. They got that way paying your bills, cleaning up your room and listening to you tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Shared row locking
On Mon, Dec 20, 2004 at 11:47:41AM -0500, Tom Lane wrote: > To me, "performance buster" is better than "random, unrepeatable > deadlock failures". In any case, if we find we *can't* implement this > in a non-performance-busting way, then it would be time enough to look > at alternatives that force the user to manage the problem for us. I am confused by this discussion. To solve the problem I want to solve, we have three orthogonal possibilities: 1. implement shared row locking using the ideas outlined in the mail starting this thread (pg_clog-like seems to be the winner, details TBD). 2. implement shared lock table spill-to-disk mechanism. 3. implement lock escalation. Some people seems to think 3 is better than 2. What do they think of 1? Some facts: - DB2 implements 3 and some people have problems with deadlocks. - 2 could have a performance impact, and we don't even know how to start. For example, what would be an algorithm to decide what locks to send to disk? - I am interested in implementing 1, maybe 2. Don't know about 3. -- Alvaro Herrera (<[EMAIL PROTECTED]>) One man's impedance mismatch is another man's layer of abstraction. (Lincoln Yeoh) ---(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] multi-key index
Jaime Casanova wrote: > Hi everyone, > > >From the TODO items: > Use index to restrict rows returned by multi-key index > when used with non-consecutive keys to reduce heap > accesses. > For an index on col1,col2,col3, and a WHERE clause of > col1 = 5 and col3 = 9, spin though the index checking > for col1 and col3 matches, rather than just col1; also > called skip-scanning. > > > I was looking in the archives something about this but > i found nothing. Where can i found the thread (i > suppose should be one) about this issue? I don't remember any detailed discussion on it except that it would be a good idea. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 8: explain analyze is your friend
[HACKERS] multi-key index
Hi everyone, >From the TODO items: Use index to restrict rows returned by multi-key index when used with non-consecutive keys to reduce heap accesses. For an index on col1,col2,col3, and a WHERE clause of col1 = 5 and col3 = 9, spin though the index checking for col1 and col3 matches, rather than just col1; also called skip-scanning. I was looking in the archives something about this but i found nothing. Where can i found the thread (i suppose should be one) about this issue? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Shared row locking
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > I may be over my head here, but I think lock spillover is dangerous. In > the extreme situations where this would happen, it would be a real > performance buster. Personally, I would rather see locks escalate when > the table gets full, or at least allow this as a configuration > parameter. To me, "performance buster" is better than "random, unrepeatable deadlock failures". In any case, if we find we *can't* implement this in a non-performance-busting way, then it would be time enough to look at alternatives that force the user to manage the problem for us. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Shared row locking
Tom lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > I think if we allow the lock manager to spill to disk (and I think we do > > need to allow it) then we should also be able to control the amount of > > shared memory allocated. > > You mean like max_locks_per_transaction? IMO, max_locks_per_transaction could use a better name a little more documentation. I've mentioned this a couple of times before, but there is at least one type of lock that does not expire when the transaction ends (user locks). I may be over my head here, but I think lock spillover is dangerous. In the extreme situations where this would happen, it would be a real performance buster. Personally, I would rather see locks escalate when the table gets full, or at least allow this as a configuration parameter. Merlin ---(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] Help extending pg_class
overbored <[EMAIL PROTECTED]> writes: > Hi all, I added a new variable-length field to the pg_class catalog, but > I did something wrong, and I can't tell what else I'd need to change. > ... > The REVOKE command invokes ExecuteGrantStmt_Relation() to modify the > relacl attribute of pg_class, which is the last attribute and also > var-length. My new field is interfering with this operation somehow. For > some reason, in frame 2, the new 'value' array is allocated with length > numberOfAttributes = RelationGetForm(relation)->relnatts = 25, instead > of 26. I think that would come from Natts_pg_class (via formrdesc()). Are you sure you updated pg_class completely, did a full rebuild, and an initdb? regards, tom lane ---(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] Shared row locking
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Gavin also mentioned to me we should also control the amount of memory > the shared inval queue uses. Perhaps, but I've really seen no evidence that there's a need to worry about that. Without demonstrated problems I'd sooner keep that code a bit simpler and faster. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Shared row locking
Gavin Sherry <[EMAIL PROTECTED]> writes: > I think if we allow the lock manager to spill to disk (and I think we do > need to allow it) then we should also be able to control the amount of > shared memory allocated. You mean like max_locks_per_transaction? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Shared row locking
On Mon, Dec 20, 2004 at 06:23:24PM +1100, Gavin Sherry wrote: > On Sat, 18 Dec 2004, Bruce Momjian wrote: > > Agreed. Once concern I have about allowing the lock table to spill to > > disk is that a large number of FOR UPDATE locks could push out lock > > entries used by other backends, causing very poor performance. > > I think if we allow the lock manager to spill to disk (and I think we do > need to allow it) then we should also be able to control the amount of > shared memory allocated. There's little point spilling the lock table to > disk if we have huge amounts of memory. This is a interesting idea. Gavin also mentioned to me we should also control the amount of memory the shared inval queue uses. Causing all backends to refill the cache is (I assume) a big performance hit. Maybe we should expose this via new knobs in postgresql.conf, to ease implementation, or maybe not, to rid users of configuring it. As a start, we could have WARNINGs when the lock table is spilled and when a SInvalReset occurs. So the user can know whether he should increase memory use for those settings. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Thou shalt not follow the NULL pointer, for chaos and madness await thee at its end." (2nd Commandment for C programmers) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Permissions within a function
Hannu Krosing wrote: Would SECURITY DEFINER not work for pljava ? Or if you are looking for something that has to be done inside the pl handler maybe you should use another function with SECURITY DEFINER and owned by superuser for function lookups ? Of course. That's even better then a SetUser. Full control and no magic. KISS applied the way it should be :-) Thanks Hannu, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Permissions within a function
On R, 2004-12-17 at 21:12, Thomas Hallgren wrote: > I'd like some views on the following issue. > > The pljava function call handler will resolve a class name using a > loader that in turn uses a specific table in the PostgreSQL database. > Hence, the caller of the function must have select permissions on that > table or the function will fail. I would like to prevent this somehow > but I don't know how to go about that. Is there any way to bypass the > permissions when I do an SPI call from within a call handler somehow? Would SECURITY DEFINER not work for pljava ? Or if you are looking for something that has to be done inside the pl handler maybe you should use another function with SECURITY DEFINER and owned by superuser for function lookups ? Hannu ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Stable functions problem
Tom Lane wrote: Gaetano Mendola <[EMAIL PROTECTED]> writes: I'm having a bounce of errors because IMMUTABLE and STABLE attributes for some of my functions. Let me explain with an example, Hmm. This particular example is a bug in exec_eval_simple_expr() ... if we're going to bypass SPI then we'd better do the things SPI does that are needed to maintain the correct execution environment, and as of 8.0 one of those things is to advance ActiveSnapshot. I've applied a patch for this. Thank you. I'll try with the CVS version and I'll let you know. > (Memo to self: I'm beginning to wonder if exec_eval_simple_expr is worth the trouble at all, compared to just using SPI. The amount of overhead it saves seems to get less with each new release.) now here I can continue my function using the my_id_user, unfortunatelly that sp_id_user is declared as IMMUTABLE this mean that at the second call of sp_id_user my_id_user will not contain the user id. That actually doesn't have anything to do with it --- the same failure would have occurred if you'd (correctly) declared sp_id_user as STABLE. So it's a good bug report. Indeed I had the same "problem" declaring it as STABLE. > But I trust you do realize you are playing with fire. While I have been > heard to suggest mislabeling functions as immutable if they're only going > to be used in interactive queries, I don't think I have ever failed to mention that you *will* get burnt if you call such functions from other functions. When this coding someday does break for you, I won't have any sympathy at all... Yes, I'll take your suggestion as gold. Regards Gaetano Mendola ---(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] yyin's value of postgresql parser
Sibtay Abbas wrote: > what is the value of yyin variable for postgresql > parser. We don't use yyin. See scanner_init() in src/backend/parser/scan.l about the scanner initialization. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bgwriter changes
On Thu, 2004-12-16 at 11:07, Neil Conway wrote: > Zeugswetter Andreas DAZ SD wrote: > > This has the disadvantage of converging against 0 dirty pages. > > A system that has less than maxpages dirty will write every page with > > every bgwriter run. > > Yeah, I'm concerned about the bgwriter being overly aggressive if we > disable bgwriter_percent. If we leave the settings as they are (delay = > 200, maxpages = 100, shared_buffers = 1000 by default), we will be > writing all the dirty pages to disk every 2 seconds, which seems far too > much. > > It might also be good to reduce the delay, in order to more proactively > keep the LRUs clean (e.g. scanning to find N dirty pages once per second > is likely to reach father away from the LRU than scanning for N/M pages > once per 1/M seconds). On the other hand the more often the bgwriter > scans the buffer pool, the more times the BufMgrLock needs to be > acquired -- and in a system in which pages aren't being dirtied very > rapidly (or the dirtied pages tend to be very hot), each of those scans > is going to take a while to find enough dirty pages using #2. So perhaps > it is best to leave the delay as is for 8.0. I think this is probably the right thing to do, since the majority of users will have low/medium workloads, not the extremes of performance that we have mainly been discussing. > > This might have the disadvantage of either leaving too much for the > > checkpoint or writing too many dirty pages in one run. Is writing a lot > > in one run actually a problem though ? Or does the bgwriter pause > > periodically while writing the pages of one run ? > > The bgwriter does not pause between writing pages. What would be the > point of doing that? The kernel is going to be caching the write() anyway. > > > If this is expressed in pages it would naturally need to be more than the > > current maxpages (to accomodate for clean pages). The suggested 2% sounded > > way too low for me (that leaves 98% to the checkpoint). > > I agree this might be a problem, but it doesn't necessarily leave 98% to > be written at checkpoint: if the buffers in the LRU change over time, > the set of pages searched by the bgwriter will also change. Agreed. > I'm not sure > how quickly the pages near the LRU change in a "typical workload"; > moreover, I think this would vary between different workloads. Yes, clearly we need to be able to change the parameters according to the workloadand long term have them vary as needs change. My concern at the moment is that the bgwriter_delay looks to me like it needs to be set lower for busier workloads, yet that is not possible because of the contention for the BufMgrLock. Investigating optimal parameter settings isn't possible while this contention exists. Incidentally, setting debug_shared_buffers also causes some contention which I'll look at reducing for 8.1, so it can be be used more frequently as a log_ setting. -- Best Regards, Simon Riggs ---(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] [Testperf-general] BufferSync and bgwriter
On Thu, 2004-12-16 at 17:54, Richard Huxton wrote: > Josh Berkus wrote: > >>Clearly, OSDL-DBT2 is not a real world test! That is its benefit, since > >>it is heavily instrumented and we are able to re-run it many times > >>without different parameter settings. The application is well known and > >>doesn't suffer that badly from factors that would allow certain effects > >>to be swamped. If it had too much randomness or variation, it would be > >>difficult to interpret. > > > > > > I don't think you followed me. The issue is that for parameters designed > > to > > "smooth out spikes" like bgwriter and vacuum delay, it helps to have really > > bad spikes to begin with. There's a possibility that the parameters (and > > calculations) that work well for for a "steady-state" OLTP application are > > actually bad for an application with much more erratic usage, just as a > > high > > sort_mem is good for DSS and bad for OLTP. > > I'm a little concerned that in an erratic, or even just a changing > environment there isn't going to be any set of values that are "correct". > > If I've got this right, the behaviour we're trying to get is: > 1. Starting from the oldest dirty block, > 2. Write as many dirty blocks as you can, but don't... > 3. Re-write frequently used blocks too much (wasteful) > > So, can we not just keep track of two numbers: > 1. Change in the number of dirty blocks this time vs last > 2. Number of re-writes we perform (count collisions in a hash or > similar - doesn't need to be perfect). > > If #1 is increasing, then we need to become more active (reduce > bgwriter_delay, increase bgwriter_maxpages). > If #2 starts to go up, or goes past some threshold then we reduce > activity (increase bgwriter_delay, decrease bgwriter_maxpages). > If of the last N blocks written, C have been collisions then assume > we've run out of low-activity blocks to write, stop and sleep. > > This has a downside that the figures will never be completely accurate, > but has the advantage that it will automatically track activity. > > I'm clearly beyond my technical knowledge here, so if I haven't > understood / it's impractical / will never work, then don't be afraid to > step up and let me know. If it helps, you could always think of me as an > idiot savant who failed his savant exams :-) Richard, I like your ideas very much. For 8.1 or beyond, it seems clear to me that a self-adapting bgwriter with no/few parameters is the way forward. My first step will be to instrument the bgwriter, so we have more input about the dynamic behaviour of the ARC lists and their effect. Then use that information to trial an adaptive mechanism along the general lines you suggest. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Shared row locking
On Mon, 2004-12-20 at 06:34, Jim C. Nasby wrote: > On Sun, Dec 19, 2004 at 11:35:02PM +0200, Heikki Linnakangas wrote: > > On Sun, 19 Dec 2004, Tom Lane wrote: > > > > >Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > >>On Sun, 19 Dec 2004, Alvaro Herrera wrote: > > >>>This is not useful at all, because the objective of this exercise is to > > >>>downgrade locks, from exclusive row locking (SELECT ... FOR UPDATE) to > > >>>shared row locking. > > > > > >>Actually it might help in some scenarios. Remember, we're not talking > > >>about upgrading shared locks to exclusive locks. We're only talking about > > >>locking more rows than necessary (all rows). > > > > > >Nonetheless, it would mean that locks would be taken depending on > > >implementation-dependent, not-visible-to-the-user considerations. > > >Shared locks can still cause deadlocks, and so you would have an > > >unreliable application, which would only be unreliable under load. > > > > > >As I said in connection with the other proposal, weird user-visible > > >semantics should be the last resort not the first. > > > > I agree that lock escalation is not a good solution, we run into problems > > with DB2 lock escalation at work all the time. > > Does anyone know how Oracle deals with this? They use MVCC like > PostgreSQL, so they'd be a better source for inspiration. Oracle only uses MVCC in its widest sense - versioning info is stored in UNDO tablespaces (rollback segments). That implementation is covered by aggressive patent attorneys. Oracle implements locking at row level within each data block. The block header expands dynamically to accommodate a list of transactions that can access, with minimum and maximum sizes settable by the DBA. This works reasonably well. Each SELECT FOR UPDATE is actually a block-write, whether or not the rows are modified, which has some additional code to recover from this without crashing/redo. Later transactions end up cleaning up the lock header info (which later became a problem in Parallel Server). https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10743/consist.htm -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bgwriter changes
On Mon, 2004-12-20 at 01:17, Mark Kirkwood wrote: > Mark Kirkwood wrote: > > > It occurs to me that cranking up the number of transactions (say > > 1000->10) and seeing if said regression persists would be > > interesting. This would give the smoothing effect of the bgwriter > > (plus the ARC) a better chance to shine. > > I ran a few of these over the weekend - since it rained here :-) , and > the results are quite interesting: > > [2xPIII, 2G, 2xATA RAID 0, FreeBSD 5.3 with the same non default Pg > parameters as before] > > clients = 4 transactions = 10 (/client), each test run twice > > Version tps > 7.4.6 49 > 8.0.0.0RC1 50 > 8.0.0.0RC1 + rem49 > 8.0.0.0RC1 + bg250 > > Needless to way, all well within measurement error of each other (the > variability was about 1). > > I suspect that my previous tests had too few transactions to trigger > many (or any) checkpoints. With them now occurring in the test, they > look to be the most significant factor (contrast with 70-80 tps for 4 > clients with 1000 transactions). > > Also with a small number of transactions, the fsyn'ed blocks may have > all fitted in the ATA disk caches (2x2M). In hindsight I should have > disabled this! (might run the smaller no. transactions again with > hw.ata.wc=0 and see if this is enlightening) These test results do seem to have greatly reduced variability: thanks. >From what you say, this means parameter setting were: (?) shared_buffers = 1 bgwriter_delay = 200 bgwriter_maxpages = 100 My interpretation of this is that the bgwriter is not effective with these (the default) parameter settings. I think the optimum performance is by reducing both bgwriter_delay and bgwriter_maxpages, though reducing the delay isn't sensibly possible with 8.0RCn when shared_buffers is large. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
