Re: [HACKERS] Should bgwriter log checkpoint start/end?
On Tue, 2004-10-26 at 23:44, Tom Lane wrote: > In previous releases it was possible to observe whether an automatic > checkpoint was in progress by looking to see if there was a postmaster > child process doing one. In 8.0 this will not work because the bgwriter > is always there. I am thinking that for tasks such as performance > debugging it would be a good idea if the bgwriter could emit postmaster > log messages at start and end of a checkpoint. However, this should > probably not happen at the default LOG level since it would clutter the > logs with perfectly routine messages. Any opinions about what elog > level to use for this? > Yes, please. The end message should say: - checkpoint duration - blocks written - number of xlog files recycled The last one can then replace the multiple recycled file messages, or at least push them down to DEBUG2. For now, DEBUG1. Longer term, we need to discuss a "performance log" or some place to put regularly collected performance statistics, rather than site specific ones. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] plans for bitmap indexes?
On K, 2004-10-27 at 00:58, Andre Maasikas wrote: > Hannu Krosing wrote: > > the per-page clustering would make sure that all the tuples would be on > > 1 (or on a few) pages. > > I understand that You can cluster on one column, but how do you do it for > indexes on other columns? Thanks to PostgreSQL's MVCC each update inserts a complete new tuple - you just have to insert in the right page. so if I change foo=1 to foo=2 on a tuple that has bar=2 and baz=3 then the updated tuple will go to a page for which foo=2, bar=2 and baz=3. if no such page has enough free space left (found by anding bitmaps for foo=2, bar=2 and baz=3 and FSM) then a new page is inserted and the three corresponding indexes are updated to include that page. > BTW, lossy variants also lose count(), group by only from index PostgreSQL has never been able to do these from index only, as the visibility info is stored in the main relation, and not in index. Someone brings up adding visibility info to index about once in 6 months and is referred to previous discussions as to why it is a bad idea. The only thing that as been added to index is a bit telling if a tuple is definitely invisible (i.e. older than any pending transaction) which is updated when such tuple is accessed using this index. > > and what comes to updating the index, you have to do it only once per > > 100 pages ;) > > Sorry, how does that work, if I update foo = 'bar'->'baz' - I can flip > the 'baz' bit > on right away but I have to check every other row to see > if I can turn the 'bar' bit off You don't touch indexes, instead you select the right page for new tuple. The only times you touch indexes is when you insert a new page (or when the page becomes empty during vacuum) Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Unixware 714 pthreads
Dear Bruce, Thanks for your reply, I was desperate I did'nt get one! As I said, I'm quite sure there is a bug in pthread library, Before saying this to SCO, I have to prove it. Postgresql is the way to prove it! What I need is to know where to start from (I'd like to put elogs where statement_timeout is processed to see what really happens and why it doesn't cancel the query). Could someone tell me where to look for? If anyone is interessed in debugging this issue with me, I can set up an account on a test unixware machine. TIA On Tue, 26 Oct 2004, Bruce Momjian wrote: > Date: Tue, 26 Oct 2004 17:59:17 -0400 (EDT) > From: Bruce Momjian <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [HACKERS] Unixware 714 pthreads > > > The only help I can be is that on Unixware (only) the backend is > compiled with threading enabled. This might be showing some thread > bugs. > > > --- > > [EMAIL PROTECTED] wrote: > > Hi every one, > > > > I need help to debug the problem I have on Unixware 714 and beta3. > > postgresql make check hangs on plpgsql test when compiled with > > --enable-thread-safty. > > > > It does hang on select block_me(); > > > > This select should be canceled by the set statement_timeout=1000, instead, > > the backend is 100% CPU bound and only kill -9 can kill it. > > > > It works ok when compiled without -enable-thread-safty. > > > > I've tried almost every thing I could think of, but not knowing so much > > about threads and PG source code, I request that someone can help me as to > > find a way to debug this. It worked up until beta2, but I'm not sure > > block_me()was there. > > > > I really need someone to tell me where to begin. > > > > TIA > > > > -- > > Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) > > 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) > > 31190 AUTERIVE +33-6-07-63-80-64 (GSM) > > FRANCE Email: [EMAIL PROTECTED] > > -- > > Make your life a dream, make your dream a reality. (St Exupery) > > > > ---(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 > > > > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] rmtree() failure on Windows
problem area found. see below. Reini Urban wrote: Andrew Dunstan schrieb: Here is some more info. Below is a trace from dropdb. There is a loop around the rmdir() calls which I have set to time out at 600 seconds. The call eventually succeeds after around 300 seconds (I've seen this several times). It looks like we are the victim of some caching - the directory still thinks it has some of the files it has told us we have deleted successfully. 300 secs (!) fs timeout is really broken. Looks more like a locking or network timeout issue. What error codes does unlink(3) return? success. Why don't you use DeletFileA() instead of unlink()? Or even better, why don't you use this delete on close snippet instead: [snip] Before I tried anything like that I tried one more thing. I disabled the background writer and the problem stopped. So now we know the "culprit". It should only happen a ERROR_SHARING_VIOLATION on NT systems with such a long timeout. This is then a concurrency problem. win95 will not return ERROR_SHARING_VIOLATION, only ERROR_ACCESS_DENIED We don't support W95/W98/WME at all. The tests were done on XP-Pro. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] plans for bitmap indexes?
Hannu Krosing <[EMAIL PROTECTED]> writes: > so if I change foo=1 to foo=2 on a tuple that has bar=2 and baz=3 then > the updated tuple will go to a page for which foo=2, bar=2 and baz=3. > > if no such page has enough free space left (found by anding bitmaps for > foo=2, bar=2 and baz=3 and FSM) then a new page is inserted and the > three corresponding indexes are updated to include that page. This is all thinking in terms of a single index though. What do you do if I have a dozen bitmap indexes? Each could have a 10 distinct values. You would need 100,000 pages, each of which might only have a few tuples in them. In any case the user may prefer to have the data clustered around a btree index using the existing CLUSTER command. There's a logical separation between the idea of index methods and table storage mechanisms. Trying to implement something like this that breaks that abstraction will only make things far more confusing. I think what you're trying to accomplish is better accomplished through partitioned tables. Then the user can decide which keys to use to partition the data and the optimizer can use the data to completely exclude some partitions from consideration. And it wouldn't interfere with indexes to access the data within a partition. -- greg ---(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] rmtree() failure on Windows
Andrew Dunstan schrieb: problem area found. see below. Reini Urban wrote: Andrew Dunstan schrieb: Here is some more info. Below is a trace from dropdb. There is a loop around the rmdir() calls which I have set to time out at 600 seconds. The call eventually succeeds after around 300 seconds (I've seen this several times). It looks like we are the victim of some caching - the directory still thinks it has some of the files it has told us we have deleted successfully. 300 secs (!) fs timeout is really broken. Looks more like a locking or network timeout issue. What error codes does unlink(3) return? success. Oops! 5min timeout for success is certainly problematic. Why don't you use DeletFileA() instead of unlink()? Or even better, why don't you use this delete on close snippet instead: [snip] Before I tried anything like that I tried one more thing. I disabled the background writer and the problem stopped. So now we know the "culprit". Good! Relieve. It should only happen a ERROR_SHARING_VIOLATION on NT systems with such a long timeout. This is then a concurrency problem. win95 will not return ERROR_SHARING_VIOLATION, only ERROR_ACCESS_DENIED We don't support W95/W98/WME at all. The tests were done on XP-Pro. Ah sorry. I forgot. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] src/timezone/pgtz __imp__my_exec_path
beta4 - cygwin: postgres.exe fails to build, because __imp__my_exec_path from src/timezone/pgtz.o cannot be resolved. previously it was not imported. dlltool --dllname postgres.exe --output-exp postgres.exp --def postgres.def gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -fno-strict-aliasing -L../../src/port -L/usr/local/lib -o postgres.exe -Wl,--base-file,postgres.base postgres.exp access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o -lpgport_srv -lintl -lssl -lcrypto -lz -lreadline -lcrypt -lresolv ../../src/timezone/SUBSYS.o(.text+0x2192):pgtz.c: undefined reference to `__imp__my_exec_path' nm postgresql-8.0.0beta4/src/timezone/pgtz.o |grep my_exec U __imp__my_exec_path but: nm postgresql-8.0.0beta3/src/timezone/pgtz.o |grep my_exec U _my_exec_path The makefile didn't change. The src, cmdline and def file is also the same. It might related to some change in the header files with the pgport_srv seperation. Can somebody give me a hint? -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] plans for bitmap indexes?
Hi, On Wed, Oct 27, 2004 at 10:13:56AM -0400, Greg Stark wrote: > > There's a logical separation between the idea of index methods and table > storage mechanisms. Trying to implement something like this that breaks that > abstraction will only make things far more confusing. > > I think what you're trying to accomplish is better accomplished through > partitioned tables. Then the user can decide which keys to use to partition > the data and the optimizer can use the data to completely exclude some > partitions from consideration. And it wouldn't interfere with indexes to > access the data within a partition. this is not always the truth. In datawarehouosing applications you often use data paritioning (time based) and bitmap indexes for fast star-transformations. A very efficient way to solve that ist using bitmap indexes. Regards, Yann ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] rmtree() failure on Windows
Reini Urban wrote: 300 secs (!) fs timeout is really broken. Looks more like a locking or network timeout issue. What error codes does unlink(3) return? success. Oops! 5min timeout for success is certainly problematic. You misunderstood. The 300 secs is not in waiting for unlink() to return, it is in waiting for its effects to be seen by rmdir() (i.e. for the entry to actually be cleared from the directory). unlink() is returning very quickly. If the bgwriter is disabled then the entries are cleared very quickly too (i.e. before we even get to rmdir()) cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 8.0b4: COMMIT outside of a transaction echoes
On Tue, 2004-10-26 at 21:42 -0400, Bruce Momjian wrote: > > test=> begin; > > BEGIN > > test=> commit; > > COMMIT > > test=> commit; > > WARNING: there is no transaction in progress > > ROLLBACK > > > > Is there any reason ROLLBACK and not COMMIT is echoed here? > > Because the transaction was not committed, but rather rolled back. It's still a misleading message; in those circumstances, how about returning "NO ACTION" instead? -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "If a man abide not in me, he is cast forth as a branch, and is withered; and men gather them, and cast them into the fire, and they are burned." John 15:6 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] rmtree() failure on Windows
Andrew Dunstan wrote: > > > Reini Urban wrote: > > >>> > >>> 300 secs (!) fs timeout is really broken. > >>> Looks more like a locking or network timeout issue. > >>> What error codes does unlink(3) return? > >> > > > >> success. > > > > > > Oops! 5min timeout for success is certainly problematic. > > > > > > You misunderstood. The 300 secs is not in waiting for unlink() to > return, it is in waiting for its effects to be seen by rmdir() (i.e. for > the entry to actually be cleared from the directory). unlink() is > returning very quickly. If the bgwriter is disabled then the entries are > cleared very quickly too (i.e. before we even get to rmdir()) What I am thinking is that the way we open these files allows us to delete them while they are still open, but perhaps it doesn't allow us to actually delete the directory that contains the files. Also, it is possible bgwriter is keeping some files open in that directory and that is causing the long delays. -- 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] rmtree() failure on Windows
> >>> 300 secs (!) fs timeout is really broken. > >>> Looks more like a locking or network timeout issue. > >>> What error codes does unlink(3) return? > >> > > > >> success. > > > > > > Oops! 5min timeout for success is certainly problematic. > > > > > > You misunderstood. The 300 secs is not in waiting for unlink() to > return, it is in waiting for its effects to be seen by > rmdir() (i.e. for because the bgwriter obviously keeps them open, no ? Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] rmtree() failure on Windows
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Before I tried anything like that I tried one more thing. I disabled the > background writer and the problem stopped. So now we know the "culprit". Okay. So what that says is that win32_open's claim to allow unlinking an open file is a lie; or at least, it does not work the way the equivalent facility on Unix does. It sounds to me like Windows is simply marking the open file as to be deleted on last close --- the directory entry remains present and so the directory can't be dropped either. One relatively low-impact workaround would be to force a checkpoint (on Windows only) during DROP DATABASE, just before we actually fire the rmtree() operation. The bgwriter is already coded to close all its open files after a checkpoint ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] rmtree() failure on Windows
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: Before I tried anything like that I tried one more thing. I disabled the background writer and the problem stopped. So now we know the "culprit". Okay. So what that says is that win32_open's claim to allow unlinking an open file is a lie; or at least, it does not work the way the equivalent facility on Unix does. It sounds to me like Windows is simply marking the open file as to be deleted on last close --- the directory entry remains present and so the directory can't be dropped either. Looks that way to me too. One relatively low-impact workaround would be to force a checkpoint (on Windows only) during DROP DATABASE, just before we actually fire the rmtree() operation. The bgwriter is already coded to close all its open files after a checkpoint ... Works for me. If someone gives me a patch I'll be happy to test it. I did wonder if there should be a call that instead of forcing a flush could tell bgwriter just to forget about the file(s) because we're discarding them. But that was just idle speculation - I haven't looked at bgwriter at all. 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] rmtree() failure on Windows
Zeugswetter Andreas DAZ SD wrote: You misunderstood. The 300 secs is not in waiting for unlink() to return, it is in waiting for its effects to be seen by rmdir() (i.e. for because the bgwriter obviously keeps them open, no ? Yes. 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] rmtree() failure on Windows
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> One relatively low-impact workaround would be to force a checkpoint >> (on Windows only) during DROP DATABASE, just before we actually fire >> the rmtree() operation. The bgwriter is already coded to close all its >> open files after a checkpoint ... > Works for me. If someone gives me a patch I'll be happy to test it. Try putting "RequestCheckpoint(true)" in dbcommands.c just before remove_dbtablespaces (about line 630). It looks like the bgwriter is not quite up-to-speed for this, either; you should rearrange things near line 350 of bgwriter.c so that smgrcloseall is performed before marking the checkpoint done in shmem. Else RequestCheckpoint could come back before the files are all closed. > I did wonder if there should be a call that instead of forcing a flush > could tell bgwriter just to forget about the file(s) because we're > discarding them. But that was just idle speculation - I haven't looked > at bgwriter at all. Not necessary, as long as you put the checkpoint after the DropBuffers call in dbcommands.c. The bgwriter won't find anything to write. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] rmtree() failure on Windows
Andrew Dunstan <[EMAIL PROTECTED]> writes: > You misunderstood. The 300 secs is not in waiting for unlink() to > return, it is in waiting for its effects to be seen by rmdir() (i.e. for > the entry to actually be cleared from the directory). unlink() is > returning very quickly. If the bgwriter is disabled then the entries are > cleared very quickly too (i.e. before we even get to rmdir()) More specifically, 300 seconds is the most time you'd have to wait for a checkpoint to occur (with default checkpoint settings) ... 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] src/timezone/pgtz __imp__my_exec_path
> beta4 - cygwin: > postgres.exe fails to build, because __imp__my_exec_path from > src/timezone/pgtz.o cannot be resolved. previously it was not > imported. This could be related to the patch that went in last weekend to fix compiles on Win32. DLLIMPORT was added to the header. If the Makefile did not change, then that is your problem - that patch changed botht he makefile and the header. See http://archives.postgresql.org/pgsql-committers/2004-10/msg00321.php Does CYGWIN perhaps need the same Makefile patch? //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
On Mon, Oct 25, 2004 at 05:53:25PM -0400, Tom Lane wrote: > Greg Stark <[EMAIL PROTECTED]> writes: > > So I would suggest using something like 100us as the threshold for > > determining whether a buffer fetch came from cache. > > I see no reason to hardwire such a number. On any hardware, the > distribution is going to be double-humped, and it will be pretty easy to > determine a cutoff after minimal accumulation of data. The real question > is whether we can afford a pair of gettimeofday() calls per read(). > This isn't a big issue if the read actually results in I/O, but if it > doesn't, the percentage overhead could be significant. > How invasive would reading the "CPU counter" be, if it is available? A read operation should avoid flushing a cache line and we can throw out the obvious outliers since we only need an estimate and not the actual value. --Ken ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] segment default
hi. Please help me. I compiled and install pgadmin 3 version 1.0.2 with wxGTK 2.4.2_r1 and when i tried to connect to any server this send a beatiful error Segmentation default o something these. My distribution is gentoo 2004.2 I don't know how resolve this if you can tell me how i do it. Sorry for my english but only spoke spanish -- L.I. JUAN PATRICIO FLORES HERNANDEZ Desarrollador Departamento de Tecnologias de la Informacion EDICIONES TRATADOS Y EQUIPOS S.A. DE C.V. ETESA Vista Hermosa No. 100 Col. Lomas del Estadio Jalapa, Veracruz CP 91090 Tel:+52 228 841 7000 ext 2217, 2330 +52 228 841 7007 Fax:+52 228 841 7000 ---(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] 8.0b4: COMMIT outside of a transaction echoes ROLLBACK
Oliver Elphick wrote: > On Tue, 2004-10-26 at 21:42 -0400, Bruce Momjian wrote: > > > test=> begin; > > > BEGIN > > > test=> commit; > > > COMMIT > > > test=> commit; > > > WARNING: there is no transaction in progress > > > ROLLBACK > > > > > > Is there any reason ROLLBACK and not COMMIT is echoed here? > > > > Because the transaction was not committed, but rather rolled back. > > It's still a misleading message; in those circumstances, how about > returning "NO ACTION" instead? Uh, it took a lot of discussion to agree on ROLLBACK. It would take even more discussion to add a new tag return value. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] src/timezone/pgtz __imp__my_exec_path
Magnus Hagander schrieb: beta4 - cygwin: postgres.exe fails to build, because __imp__my_exec_path from src/timezone/pgtz.o cannot be resolved. previously it was not imported. This could be related to the patch that went in last weekend to fix compiles on Win32. DLLIMPORT was added to the header. If the Makefile did not change, then that is your problem - that patch changed botht he makefile and the header. See http://archives.postgresql.org/pgsql-committers/2004-10/msg00321.php Does CYGWIN perhaps need the same Makefile patch? You only patched your Makefile.win32, not Makefile.cygwin. That's it. It builds fine now. Please add also ifneq (,$(findstring timezone,$(subdir))) override CPPFLAGS+= -DBUILDING_DLL endif to the Makefile.cygwin. Without it doesn't break just contrib/tsearch, it even breaks cygwin postmaster. Maybe all win32.mak and bcc32.mak must also be checked. Does anybody do the msvc/borland suites? -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(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] src/timezone/pgtz __imp__my_exec_path
> >>beta4 - cygwin: > >>postgres.exe fails to build, because __imp__my_exec_path from > >>src/timezone/pgtz.o cannot be resolved. previously it was not > >>imported. > > > > This could be related to the patch that went in last weekend to fix > > compiles on Win32. DLLIMPORT was added to the header. If > the Makefile > > did not change, then that is your problem - that patch > changed botht > > he makefile and the header. See > > http://archives.postgresql.org/pgsql-committers/2004-10/msg00321.php > > > > Does CYGWIN perhaps need the same Makefile patch? > > You only patched your Makefile.win32, not Makefile.cygwin. > That's it. It builds fine now. > > Please add also > > ifneq (,$(findstring timezone,$(subdir))) override CPPFLAGS+= > -DBUILDING_DLL endif > > to the Makefile.cygwin. > Without it doesn't break just contrib/tsearch, it even breaks > cygwin postmaster. Soudns reasonable. > Maybe all win32.mak and bcc32.mak must also be checked. Does > anybody do the msvc/borland suites? Not affected. Only the frontend can be compiled with those, and this is a backend change. //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Unixware 714 pthreads
[EMAIL PROTECTED] wrote: > Dear Bruce, > > Thanks for your reply, I was desperate I did'nt get one! > > As I said, I'm quite sure there is a bug in pthread library, Before saying > this to SCO, I have to prove it. Postgresql is the way to prove it! > > What I need is to know where to start from (I'd like to put elogs where > statement_timeout is processed to see what really happens and why it > doesn't cancel the query). > > Could someone tell me where to look for? If anyone is interessed in > debugging this issue with me, I can set up an account on a test unixware > machine. My guess is that there is some problem with delivering alarm signals because that is how the timeout code works. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] copy - fields enclosed by, ignore x lines
Use python's (or another language) CSV reader module which will parse the quotes for you and write the values in a tab-delimited file. Don't forget to escape the tabs in the strings... it should be less than 10 lines of code. On Mon, 25 Oct 2004 14:45:57 -0700 (PDT), CSN <[EMAIL PROTECTED]> wrote: Any chance of changing \copy and COPY to allow specifying what the fields are enclosed by (such as quotes) and to ignore the first x number of lines? I have data like below and don't know of an easy way to finesse it for importing (a simple regexp would remove quotes, but I just got tripped up on commas *within* values). "field1","field2","field3" "val1","val2","val3" __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] src/timezone/pgtz __imp__my_exec_path
Reini Urban wrote: > Magnus Hagander schrieb: > >>beta4 - cygwin: > >>postgres.exe fails to build, because __imp__my_exec_path from > >>src/timezone/pgtz.o cannot be resolved. previously it was not > >>imported. > > > > This could be related to the patch that went in last weekend to fix > > compiles on Win32. DLLIMPORT was added to the header. If the Makefile > > did not change, then that is your problem - that patch changed botht he > > makefile and the header. See > > http://archives.postgresql.org/pgsql-committers/2004-10/msg00321.php > > > > Does CYGWIN perhaps need the same Makefile patch? > > You only patched your Makefile.win32, not Makefile.cygwin. That's it. It > builds fine now. > > Please add also > > ifneq (,$(findstring timezone,$(subdir))) > override CPPFLAGS+= -DBUILDING_DLL > endif > OK, patch applied. I also fixed the ecpg test in Makefile.win32. > to the Makefile.cygwin. > Without it doesn't break just contrib/tsearch, it even breaks cygwin > postmaster. > > Maybe all win32.mak and bcc32.mak must also be checked. Does anybody do > the msvc/borland suites? Those don't build the backend, only libpq and psql. -- 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 Index: src/makefiles/Makefile.cygwin === RCS file: /cvsroot/pgsql/src/makefiles/Makefile.cygwin,v retrieving revision 1.4 diff -c -c -r1.4 Makefile.cygwin *** src/makefiles/Makefile.cygwin 19 Jan 2004 21:20:06 - 1.4 --- src/makefiles/Makefile.cygwin 27 Oct 2004 19:12:29 - *** *** 23,28 --- 23,32 endif endif + ifneq (,$(findstring timezone,$(subdir))) + override CPPFLAGS+= -DBUILDING_DLL + endif + ifneq (,$(findstring ecpg/ecpglib,$(subdir))) override CPPFLAGS+= -DBUILDING_DLL endif Index: src/makefiles/Makefile.win32 === RCS file: /cvsroot/pgsql/src/makefiles/Makefile.win32,v retrieving revision 1.4 diff -c -c -r1.4 Makefile.win32 *** src/makefiles/Makefile.win3222 Oct 2004 22:33:58 - 1.4 --- src/makefiles/Makefile.win3227 Oct 2004 19:12:29 - *** *** 27,33 override CPPFLAGS+= -DBUILDING_DLL endif ! ifneq (,$(findstring ecpg/lib,$(subdir))) override CPPFLAGS+= -DBUILDING_DLL endif --- 27,33 override CPPFLAGS+= -DBUILDING_DLL endif ! ifneq (,$(findstring ecpg/ecpglib,$(subdir))) override CPPFLAGS+= -DBUILDING_DLL endif ---(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] Should bgwriter log checkpoint start/end?
Simon Riggs <[EMAIL PROTECTED]> writes: > For now, DEBUG1. Longer term, we need to discuss a "performance log" or > some place to put regularly collected performance statistics, rather > than site specific ones. As a data point, with Oracle we often found it useful that Oracle logs all checkpoints. One log entry every few minutes really isn't an excessive amount of logging, even if it is a routine non-error condition. I would suggest a higher level, INFO or NOTICE. -- greg ---(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] 8.0b4: COMMIT outside of a transaction echoes ROLLBACK
Bruce Momjian <[EMAIL PROTECTED]> writes: > Oliver Elphick wrote: >> On Tue, 2004-10-26 at 21:42 -0400, Bruce Momjian wrote: >>> test=> begin; >>> BEGIN >>> test=> commit; >>> COMMIT >>> test=> commit; >>> WARNING: there is no transaction in progress >>> ROLLBACK >> >> It's still a misleading message; in those circumstances, how about >> returning "NO ACTION" instead? > Uh, it took a lot of discussion to agree on ROLLBACK. It would take > even more discussion to add a new tag return value. I don't care for "NO ACTION" either. However, the prior discussion had to do with what to echo in the case that you are saying COMMIT in a failed transaction. I don't think anyone thought about this particular corner case, viz COMMIT outside any transaction. I think you could make a reasonable argument that the tag should remain COMMIT for this case, since we do not consider it an error. On the other hand, it's also a pretty minor issue, and if it turns out to require a lot of code rejiggering to make it do that, I'd not think it worthwhile. Comments? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Unixware 714 pthreads
On Wed, 27 Oct 2004, Bruce Momjian wrote: > Date: Wed, 27 Oct 2004 14:53:26 -0400 (EDT) > From: Bruce Momjian <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [HACKERS] Unixware 714 pthreads > > [EMAIL PROTECTED] wrote: > > Dear Bruce, > > > > Thanks for your reply, I was desperate I did'nt get one! > > > > As I said, I'm quite sure there is a bug in pthread library, Before saying > > this to SCO, I have to prove it. Postgresql is the way to prove it! > > > > What I need is to know where to start from (I'd like to put elogs where > > statement_timeout is processed to see what really happens and why it > > doesn't cancel the query). > > > > Could someone tell me where to look for? If anyone is interessed in > > debugging this issue with me, I can set up an account on a test unixware > > machine. > > My guess is that there is some problem with delivering alarm signals > because that is how the timeout code works. > That's my guess too. I've traked that to src/backend/storage/lmrg/proc.c where kill is called. Unixware doc says that kill to self_proc id delivers the signal to the thread that called it. For some reason, this backend has 2 threads (can't figure why) and INMHO kill should be pthread_kill. I wanted to try but found no way to find the other thread_id. I need the help of postgresql/thread guru here. Many thanks > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(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] plans for bitmap indexes?
Greg Stark wrote: I think what you're trying to accomplish is better accomplished through partitioned tables. Then the user can decide which keys to use to partition the data and the optimizer can use the data to completely exclude some partitions from consideration. And it wouldn't interfere with indexes to access the data within a partition. Though partitioning will help, you can only partition on one key (I guess the ability to partition *indexes* might help here). I think that bitmap indexes provide a flexible may to get fact access to the result set for multiple low cardinality conditions - something that partitioning will generally not do. regards Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plans for bitmap indexes?
Mark Kirkwood wrote: I think that bitmap indexes provide a flexible may to get fact access to the result set that should be *fast* access tosorry ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ARC Memory Usage analysis
On Mon, Oct 25, 2004 at 11:34:25AM -0400, Jan Wieck wrote: > On 10/22/2004 4:09 PM, Kenneth Marshall wrote: > > > On Fri, Oct 22, 2004 at 03:35:49PM -0400, Jan Wieck wrote: > >> On 10/22/2004 2:50 PM, Simon Riggs wrote: > >> > >> >I've been using the ARC debug options to analyse memory usage on the > >> >PostgreSQL 8.0 server. This is a precursor to more complex performance > >> >analysis work on the OSDL test suite. > >> > > >> >I've simplified some of the ARC reporting into a single log line, which > >> >is enclosed here as a patch on freelist.c. This includes reporting of: > >> >- the total memory in use, which wasn't previously reported > >> >- the cache hit ratio, which was slightly incorrectly calculated > >> >- a useful-ish value for looking at the "B" lists in ARC > >> >(This is a patch against cvstip, but I'm not sure whether this has > >> >potential for inclusion in 8.0...) > >> > > >> >The total memory in use is useful because it allows you to tell whether > >> >shared_buffers is set too high. If it is set too high, then memory usage > >> >will continue to grow slowly up to the max, without any corresponding > >> >increase in cache hit ratio. If shared_buffers is too small, then memory > >> >usage will climb quickly and linearly to its maximum. > >> > > >> >The last one I've called "turbulence" in an attempt to ascribe some > >> >useful meaning to B1/B2 hits - I've tried a few other measures though > >> >without much success. Turbulence is the hit ratio of B1+B2 lists added > >> >together. By observation, this is zero when ARC gives smooth operation, > >> >and goes above zero otherwise. Typically, turbulence occurs when > >> >shared_buffers is too small for the working set of the database/workload > >> >combination and ARC repeatedly re-balances the lengths of T1/T2 as a > >> >result of "near-misses" on the B1/B2 lists. Turbulence doesn't usually > >> >cut in until the cache is fully utilized, so there is usually some delay > >> >after startup. > >> > > >> >We also recently discussed that I would add some further memory analysis > >> >features for 8.1, so I've been trying to figure out how. > >> > > >> >The idea that B1, B2 represent something really useful doesn't seem to > >> >have been borne out - though I'm open to persuasion there. > >> > > >> >I originally envisaged a "shadow list" operating in extension of the > >> >main ARC list. This will require some re-coding, since the variables and > >> >macros are all hard-coded to a single set of lists. No complaints, just > >> >it will take a little longer than we all thought (for me, that is...) > >> > > >> >My proposal is to alter the code to allow an array of memory linked > >> >lists. The actual list would be [0] - other additional lists would be > >> >created dynamically as required i.e. not using IFDEFs, since I want this > >> >to be controlled by a SIGHUP GUC to allow on-site tuning, not just lab > >> >work. This will then allow reporting against the additional lists, so > >> >that cache hit ratios can be seen with various other "prototype" > >> >shared_buffer settings. > >> > >> All the existing lists live in shared memory, so that dynamic approach > >> suffers from the fact that the memory has to be allocated during ipc_init. > >> > >> What do you think about my other theory to make C actually 2x effective > >> cache size and NOT to keep T1 in shared buffers but to assume T1 lives > >> in the OS buffer cache? > >> > >> > >> Jan > >> > > Jan, > > > >>From the articles that I have seen on the ARC algorithm, I do not think > > that using the effective cache size to set C would be a win. The design > > of the ARC process is to allow the cache to optimize its use in response > > to the actual workload. It may be the best use of the cache in some cases > > to have the entire cache allocated to T1 and similarly for T2. If fact, > > the ability to alter the behavior as needed is one of the key advantages. > > Only the "working set" of the database, that is the pages that are very > frequently used, are worth holding in shared memory at all. The rest > should be copied in and out of the OS disc buffers. > > The problem is, with a too small directory ARC cannot guesstimate what > might be in the kernel buffers. Nor can it guesstimate what recently was > in the kernel buffers and got pushed out from there. That results in a > way too small B1 list, and therefore we don't get B1 hits when in fact > the data was found in memory. B1 hits is what increases the T1target, > and since we are missing them with a too small directory size, our > implementation of ARC is propably using a T2 size larger than the > working set. That is not optimal. > > If we would replace the dynamic T1 buffers with a max_backends*2 area of > shared buffers, use a C value representing the effective cache size and > limit the T1target on the lower bound to effective cache size - shared > buffers, then we basically moved the T1 cache into the OS buffers
[HACKERS] Beta 4 on Debian Sarge (MIPS/MIPSEL)
Hackers, just an fyi, Beta 4 passed ALL tests on Debian Sarge for both MIPS (Indy) and MIPSEL (Cobalt RAQ) I can test Debian Sarge Sparc, Alpha, PowerPC, PA-RISC and M68K if no one else has reported on these systems yet. Also, with a little work I could test Solaris, Tru64 (or what ever its called these days) and IRIX Keep up the good work. Jim ---(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] Beta 4 on Debian Sarge (MIPS/MIPSEL)
"Jim Buttafuoco" <[EMAIL PROTECTED]> writes: > I can test Debian Sarge Sparc, Alpha, PowerPC, PA-RISC and M68K if no one else has > reported on these systems yet. > Also, with a little work I could test Solaris, Tru64 (or what ever its called these > days) and IRIX You should probably wait till we issue the official call for port reports (though I hope that will happen soon). It might be worth testing M68K now, since I'll bet no one's tried that port in a good long while ;-). The odds that the other ones work should be pretty good. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.0b4: COMMIT outside of a transaction echoes ROLLBACK
On Wed, Oct 27, 2004 at 04:21:53PM -0400, Tom Lane wrote:
> On the other hand, it's also a pretty minor issue, and if it turns out
> to require a lot of code rejiggering to make it do that, I'd not think
> it worthwhile.
Patch attached. It passes the regression tests. It shouldn't have
secondary effects, but please test.
--
Alvaro Herrera ()
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)
Index: src/backend/access/transam/xact.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.192
diff -c -r1.192 xact.c
*** src/backend/access/transam/xact.c 16 Oct 2004 18:57:22 - 1.192
--- src/backend/access/transam/xact.c 27 Oct 2004 21:56:21 -
***
*** 2546,2552
ereport(WARNING,
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
errmsg("there is no transaction in
progress")));
! s->blockState = TBLOCK_ABORT_PENDING;
break;
/* These cases are invalid. */
--- 2546,2553
ereport(WARNING,
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
errmsg("there is no transaction in
progress")));
! result = true;
! s->blockState = TBLOCK_END;
break;
/* These cases are invalid. */
---(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] 8.0b4: COMMIT outside of a transaction echoes ROLLBACK
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Wed, Oct 27, 2004 at 04:21:53PM -0400, Tom Lane wrote: >> On the other hand, it's also a pretty minor issue, and if it turns out >> to require a lot of code rejiggering to make it do that, I'd not think >> it worthwhile. > Patch attached. It passes the regression tests. It shouldn't have > secondary effects, but please test. Wouldn't it be better to just stay in TBLOCK_STARTED state, as if the COMMIT were just some random utility command? In any case, the comment right above this needs adjustment ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
Tom Lane wrote: > Greg Stark <[EMAIL PROTECTED]> writes: > > So I would suggest using something like 100us as the threshold for > > determining whether a buffer fetch came from cache. > > I see no reason to hardwire such a number. On any hardware, the > distribution is going to be double-humped, and it will be pretty easy to > determine a cutoff after minimal accumulation of data. The real question > is whether we can afford a pair of gettimeofday() calls per read(). > This isn't a big issue if the read actually results in I/O, but if it > doesn't, the percentage overhead could be significant. > > If we assume that the effective_cache_size value isn't changing very > fast, maybe it would be good enough to instrument only every N'th read > (I'm imagining N on the order of 100) for this purpose. Or maybe we > need only instrument reads that are of blocks that are close to where > the ARC algorithm thinks the cache edge is. If it's decided to instrument reads, then perhaps an even better use of it would be to tune random_page_cost. If the storage manager knows the difference between a sequential scan and a random scan, then it should easily be able to measure the actual performance it gets for each and calculate random_page_cost based on the results. While the ARC lists can't be tuned on the fly, random_page_cost can. > One small problem is that the time measurement gives you only a lower > bound on the time the read() actually took. In a heavily loaded system > you might not get the CPU back for long enough to fool you about whether > the block came from cache or not. True, but that's information that you'd want to factor into the performance measurements anyway. The database needs to know how much wall clock time it takes for it to fetch a page under various circumstances from disk via the OS. For determining whether or not the read() hit the disk instead of just OS cache, what would matter is the average difference between the two. That's admittedly a problem if the difference is less than the noise, though, but at the same time that would imply that given the circumstances it really doesn't matter whether or not the page was fetched from disk: the difference is small enough that you could consider them equivalent. You don't need 100% accuracy for this stuff, just statistically significant accuracy. > Another issue is what we do with the effective_cache_size value once > we have a number we trust. We can't readily change the size of the > ARC lists on the fly. Compare it with the current value, and notify the DBA if the values are significantly different? Perhaps write the computed value to a file so the DBA can look at it later? Same with other values that are computed on the fly. In fact, it might make sense to store them in a table that gets periodically updated, and load their values from that table, and then the values in postgresql.conf or the command line would be the default that's used if there's nothing in the table (and if you really want fine-grained control of this process, you could stick a boolean column in the table to indicate whether or not to load the value from the table at startup time). -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Shared dependencies
Hackers, I'm currently playing with implementing a "shared dependency" catalog, to keep track of objects pointing to global objects, currently users and tablespaces. So it is forbidden to drop a user that owns tables (or whatever objects) on other databases. So far, it works nicely; it lists objects that depend on whatever user/ tablespace one is deleting and then aborts the deletion. Changing the owner or tablespace of an object works as expected. To this end, I have created a pg_sdepend shared catalog, very similar to pg_depend. Both users and tablespaces are kept track of there. That poses a problem, because users are identified internally using an AclId, while tablespaces use an Oid. Most likely those should not be put on the same column; it works now, but it can break at any time. So the first obvious question: should I create two separate catalogs, one for users and other for tablespaces? The other obvious question is whether we should keep track of only owner of objects, or also users that happen to be listed in their Acl. It would be good to have all the info, but I wonder this won't make the catalog too bloated. (The other global objects are currently databases and groups, which don't have objects depend on them, so there's nothing to record. If and when someone implements SQL roles, there may be something to consider here. Of course, if it's decided that's a good idea to record Acls, then groups will have to be included too.) Comments, opinions and ideas are welcome. -- Alvaro Herrera () "El día que dejes de cambiar dejarás de vivir" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] pg_get_serial_sequence is inconsistent
pg_get_serial_sequence() does dequoting/downcasing on its relation-name
argument, but not on its column-name argument.
regression=# create table "FOO" ("Ff1" serial);
NOTICE: CREATE TABLE will create implicit sequence "FOO_Ff1_seq" for serial column
"FOO.Ff1"
CREATE TABLE
regression=# select pg_get_serial_sequence('FOO','Ff1');
ERROR: column "Ff1" of relation "foo" does not exist
regression=# select pg_get_serial_sequence('"FOO"','Ff1');
pg_get_serial_sequence
public."FOO_Ff1_seq"
(1 row)
regression=# select pg_get_serial_sequence('"FOO"','"Ff1"');
ERROR: column ""Ff1"" of relation "FOO" does not exist
regression=#
This seems pretty inconsistent. I was expecting to find that it broke
pg_dump, as well, but it looks like pg_dump is expecting exactly
this behavior.
Should we change it? We need the quote-aware behavior for the relation
name (because that could be schema-qualified) so the choices seem to be
status quo or add dequoting/downcasing to the column name argument.
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] pg_get_serial_sequence is inconsistent
pg_get_serial_sequence() does dequoting/downcasing on its relation-name
argument, but not on its column-name argument.
regression=# create table "FOO" ("Ff1" serial);
NOTICE: CREATE TABLE will create implicit sequence "FOO_Ff1_seq" for serial column "FOO.Ff1"
CREATE TABLE
regression=# select pg_get_serial_sequence('FOO','Ff1');
ERROR: column "Ff1" of relation "foo" does not exist
regression=# select pg_get_serial_sequence('"FOO"','Ff1');
pg_get_serial_sequence
public."FOO_Ff1_seq"
(1 row)
I presume the reason for that is that the first paramater can be qualified:
select pg_get_serial_sequence('"public"."FOO"', 'Ff1');
Chris
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
