Re: [HACKERS] pltcl.so patch
On 25 Sep 2002, Neil Conway wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Yes, I do get the similar results. A quick investigation shows that the SPI_freetuptable at the end of pltcl_SPI_exec is trying to free a tuptable of value 0x82ebe64 (which looks sensible to me) but which has a memory context of 0x7f7f7f7f (the unallocated marker). Attached is a patch against CVS HEAD which fixes this, I believe. The problem appears to be the newly added free of the tuptable at the end of pltcl_SPI_exec(). I've added a comment to that effect: /* * Do *NOT* free the tuptable here. That's because if the loop * body executed any SQL statements, it will have already free'd * the tuptable itself, so freeing it twice is not wise. We could * get around this by making a copy of SPI_tuptable-vals and * feeding that to pltcl_set_tuple_values above, but that would * still leak memory (the palloc'ed copy would only be free'd on * context reset). */ That's certainly where the fault was happening. However, that's where the original memory leak problem was coming from (without the SPI_freetuptable call). It could be I got that fix wrong and the extra calls you've added are the right fix for that. I'll take a look to see what I can learn later. At least, I *think* that's the problem -- I've only been looking at the code for about 20 minutes, so I may be wrong. In any case, this makes both memleak() and memleak(1) work on my machine. Let me know if it works for you, and/or if someone knows of a better solution. I'll have to check later. I also added some SPI_freetuptable() calls in some places where Nigel didn't, and added some paranoia when dealing with statically sized buffers (snprintf() rather than sprintf(), and so on). I also didn't include Nigel's changes to some apparently unrelated PL/Python stuff -- this patch includes only the PL/Tcl changes. I dare say the plpython needs to be checked by someone who knows how to since I can well imagine the same nested call fault will exist there. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Alvaro Herrera kirjutas K, 25.09.2002 kell 02:45: Hannu Krosing dijo: For me it feels assymmetric (unless we will make attislocal also int instead of boolean ;). This assymetric nature will manifest itself when we will have ADD COLUMN which can put back the DROP ONLY COLUMN and it has to determine weather to remove the COLUMN definition from the child. Well, the ADD COLUMN thing is something I haven't think about. Let's see: if I have a child with a local definition of the column I'm adding, I have to add one to its inhcount, that's clear. But do I have to reset its attislocal? I'd guess that it should reset attislocal if ONLY is specified (to be symmetric with behaviour of drop ONLY). What does the current model do in the following case: create table p (f1 int, g1 int); create table c (f1 int) inherits(p); drop column c.f1; Will it just set attisinh = 1 on c.f1 ? No, it will forbid you to drop the column. That was the intention on the first place: if a column is inherited, you shouldn't be allowed to drop or rename it. You can only do so at the top of the inheritance tree, either recursively or non-recursively. And when you do it non-recursively, the first level is marked non-inherited. And my views differed from Tom's on weather to do it always or only when the column was dropped the last parent providing it for inheritance. Lets hope that possible move from INHERITS to (LIKE,...)UNDER will make these issues clearer and thus easier to discuss and agree upon. There seem to be actually 3 different possible behaviours for DROP COLUMN for hierarchies. Well, I'm not too eager to discuss this kind of thing: it's possible that multiple inheritance goes away in a future release, and all these issues will possibly vanish. But I'm not sure I understand the implications of interfaces (a la Java multiple inheritance). I don't think that issues for inheriting multiple columns will vanish even for SQL99 way of doing nheritance (LIKE/UNDER), as there can be multiple LIKE's and afaik they too should track changes in parent columns. But I don't think that it is very important to reach concensus for 7.3 as the whole inheritance area in postgres will likely be changed. I think these will be items for discussion once 7.4 cycle starts. - Hannu ---(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] ECPG
On Tue, Sep 24, 2002 at 09:53:10AM -0400, Tom Lane wrote: *Everyone* who checks out from our CVS needs to build the bison output files. There seem to be quite a few such people; they will all be I though time stamping is done to make sure the .c file is newer than the .y one. forced to upgrade their local bison installations when ecpg starts requiring a newer bison. Valid point. | Thanks for the report, this is addressed in 1.49c. We should upload | the latter soon. So I'm guessing that a full release is not just around the corner :-( Argh. But when we remove features from ecpg I would prefer to just remove pretty obscure stuff and stuff introduced after 7.2 was released so we won't break much. Does anyone have a list of newly added commands? Or do I have to get the diff from CVS? Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] [akim@epita.fr: Re: bison 1.49 release]
Just got this. :-) Michael - Forwarded message from Akim Demaille [EMAIL PROTECTED] - To: Michael Meskes [EMAIL PROTECTED] Subject: Re: bison 1.49 release From: Akim Demaille [EMAIL PROTECTED] Date: 25 Sep 2002 11:32:42 +0200 Michael == Michael Meskes [EMAIL PROTECTED] writes: Michael We are already in feature freeze. I'd say release will be in Michael about a month. Bison in Two weeks is doable. Is this enough? - End forwarded message - -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] contrib/earthdistance missing regression test files
On Tue, Sep 24, 2002 at 23:57:29 -0400, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: How do I run the regression tests for /contrib stuff? make make install make installcheck AFAICT, earthdistance is nowhere near passing yet :-(. It looks to me like the regression test is depending on the cube-based features that we decided to hold off for 7.4. Bruno, is that right? It shouldn't be. When I resubmitted the patch I intended to take out all of the cube related tests. If there is a reference to cube in there it is by mistake. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] contrib/earthdistance missing regression test files
AFAICT, earthdistance is nowhere near passing yet :-(. It looks to me like the regression test is depending on the cube-based features that we decided to hold off for 7.4. Bruno, is that right? It shouldn't be. When I resubmitted the patch I intended to take out all of the cube related tests. If there is a reference to cube in there it is by mistake. I took a look at the diff file I submitted and the only reference to cube in the regression test was in a comment I didn't change after removing the tests for the cube based distance stuff. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib/earthdistance missing regression test files
OK, I reinstalled the proper earthdistance.out/sql files and it passes regession now. Sorry for the mistake. --- Bruno Wolff III wrote: AFAICT, earthdistance is nowhere near passing yet :-(. It looks to me like the regression test is depending on the cube-based features that we decided to hold off for 7.4. Bruno, is that right? It shouldn't be. When I resubmitted the patch I intended to take out all of the cube related tests. If there is a reference to cube in there it is by mistake. I took a look at the diff file I submitted and the only reference to cube in the regression test was in a comment I didn't change after removing the tests for the cube based distance stuff. -- 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 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
[HACKERS] New SSL code to be removed
Because the new 7.3 SSL code doesn't work (per Peter), and the author is not responding, I am about to yank out that code. Peter suggests ripping out all the new code rather than try to pick around and remove just the broken parts. -- 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]
[HACKERS] beta2 ... someone wanna verify?
build cleanly, just wanna make sure tha i haven't overlooked anything... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGXLOG variable worthwhile?
On Wed, 25 Sep 2002, Curt Sampson wrote: On Tue, 24 Sep 2002, Jan Wieck wrote: And AFAICS it is scary only because screwing that up will simply corrupt your database. Thus, a simple random number (okay, and a timestamp of initdb) in two files, one in $PGDATA and one in $PGXLOG would be a totally sufficient safety mechanism to prevent starting with the wrong XLOG directory. But still, why set up a situation where your database might not start? Why not set it up so that if you get just *one* environment or command-line variable right, you can't set another inconsistently and screw up your start anyway? Why store configuration information outside of the database data directory in a form that's not easily backed up, and not easily found by other utilities? It's almost like people *don't* want to put this in the config file or something Curt, did you see my post about this earlier? I'll repeat it now, just in case anyone else missed it. Problem: - People need to move the pg_xlog directory around on heavily loaded systems to improve performance Constraints: - Windows can't reliably use links to do this. - If the pg_xlog directory is moved wrong or referenced incorrectly, data corruption may occur. This makes using a switch or environmental var dangerous I consider using a GUC in the postgresql.conf file to be better than any other option listed so far, but it is still a dangerous place for it to be. So, the way I think that would work best would be: If there's a directory called pg_xlog in the $PGDATA directory, then use that. If there's a file called pg_xlog in the $PGDATA directory, then it will contain the path to the real pg_xlog directory. If you want to move the pg_xlog directory, you called a custom script called mvpgxlog or something like it that: 1: Checks to make sure the database is shut down 2: Checks to make sure the destination path has enough free space for the xlogs 3: If these are both true (and whatever logic we need here for safety) then copy the current pg_xlog directory contents to the new pg_xlog (even if we are already using an alternative location, this should work), set proper permissions, rename / move the pg_xlog file / directorry, then edit/create the $PGDATA/pg_xlog file to point to the new directory. This method has several advantages, and no real disadvantages I can think of. The advantages are: - It makes it easy to move the pg_xlog directory. - It works equally well for Windows and Unix. - Gets rid of another GUC setting people can scram their database with. - It is easy to backup your pg_xlog setting. - If painted green it should not rust. How's that sound for a general theory of operation? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] beta2 ... someone wanna verify?
Marc G. Fournier [EMAIL PROTECTED] writes: build cleanly, just wanna make sure tha i haven't overlooked anything... The tarball seems to match my local tree ... 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
[HACKERS] Cause of can't wait without a PROC structure
I've identified the reason for the occasional can't wait without a PROC structure failures we've seen reported. I had been thinking that this must occur during backend startup, before MyProc is initialized ... but I was mistaken. Actually, it happens during backend shutdown, and the reason is that ProcKill (which releases the PGPROC structure and resets MyProc to NULL) is called before ShutdownBufferPoolAccess. But the latter tries to acquire the bufmgr LWLock. If it has to wait, kaboom. The ordering of these shutdown hooks is the reverse of the ordering of the startup initialization of the modules. It looks like we'll need to rejigger the startup ordering ... and it also looks like that's going to be a rather ticklish issue. (See comments in BaseInit and InitPostgres.) Any thoughts on how to do it? 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] Cause of can't wait without a PROC structure
On Wed, 2002-09-25 at 09:52, Tom Lane wrote: I've identified the reason for the occasional can't wait without a PROC structure failures we've seen reported. I had been thinking that this must occur during backend startup, before MyProc is initialized ... but I was mistaken. Actually, it happens during backend shutdown, and the reason is that ProcKill (which releases the PGPROC structure and resets MyProc to NULL) is called before ShutdownBufferPoolAccess. But the latter tries to acquire the bufmgr LWLock. If it has to wait, kaboom. Great news that you've identified the problem. We continue to see this every few days and it's the only thing that takes our servers down over weeks of pounding. The ordering of these shutdown hooks is the reverse of the ordering of the startup initialization of the modules. It looks like we'll need to rejigger the startup ordering ... and it also looks like that's going to be a rather ticklish issue. (See comments in BaseInit and InitPostgres.) Any thoughts on how to do it? Sorry I can't add any insight at this level...but I can say that it would be significant to my customer(s) and my ability to recommend PG to future ex-Oracle users ;) to see a fix make it into the 7.3 final. ss Scott Shattuck Technical Pursuit Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGXLOG variable worthwhile?
I don't see the gain of having a file called pg_xlog vs. using GUC. --- scott.marlowe wrote: On Wed, 25 Sep 2002, Curt Sampson wrote: On Tue, 24 Sep 2002, Jan Wieck wrote: And AFAICS it is scary only because screwing that up will simply corrupt your database. Thus, a simple random number (okay, and a timestamp of initdb) in two files, one in $PGDATA and one in $PGXLOG would be a totally sufficient safety mechanism to prevent starting with the wrong XLOG directory. But still, why set up a situation where your database might not start? Why not set it up so that if you get just *one* environment or command-line variable right, you can't set another inconsistently and screw up your start anyway? Why store configuration information outside of the database data directory in a form that's not easily backed up, and not easily found by other utilities? It's almost like people *don't* want to put this in the config file or something Curt, did you see my post about this earlier? I'll repeat it now, just in case anyone else missed it. Problem: - People need to move the pg_xlog directory around on heavily loaded systems to improve performance Constraints: - Windows can't reliably use links to do this. - If the pg_xlog directory is moved wrong or referenced incorrectly, data corruption may occur. This makes using a switch or environmental var dangerous I consider using a GUC in the postgresql.conf file to be better than any other option listed so far, but it is still a dangerous place for it to be. So, the way I think that would work best would be: If there's a directory called pg_xlog in the $PGDATA directory, then use that. If there's a file called pg_xlog in the $PGDATA directory, then it will contain the path to the real pg_xlog directory. If you want to move the pg_xlog directory, you called a custom script called mvpgxlog or something like it that: 1: Checks to make sure the database is shut down 2: Checks to make sure the destination path has enough free space for the xlogs 3: If these are both true (and whatever logic we need here for safety) then copy the current pg_xlog directory contents to the new pg_xlog (even if we are already using an alternative location, this should work), set proper permissions, rename / move the pg_xlog file / directorry, then edit/create the $PGDATA/pg_xlog file to point to the new directory. This method has several advantages, and no real disadvantages I can think of. The advantages are: - It makes it easy to move the pg_xlog directory. - It works equally well for Windows and Unix. - Gets rid of another GUC setting people can scram their database with. - It is easy to backup your pg_xlog setting. - If painted green it should not rust. How's that sound for a general theory of operation? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- 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] Cause of can't wait without a PROC structure
Scott Shattuck [EMAIL PROTECTED] writes: Sorry I can't add any insight at this level...but I can say that it would be significant to my customer(s) and my ability to recommend PG to future ex-Oracle users ;) to see a fix make it into the 7.3 final. Rest assured that it *will* be fixed in 7.3 final; this is a must fix item in my book ... and now that we know the cause, it's just a matter of choosing the cleanest solution. 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
[HACKERS] inquiry
Dear Momjian£¬ hello, I want to make the show variable SQL to returnmessage like pgresult. how can I revise the source code? Any suggestion? I really need you help. Jinqiang Han
Re: [HACKERS] making use of large TLB pages
Tom Lane [EMAIL PROTECTED] writes: Neil Conway [EMAIL PROTECTED] writes: I'd like to enable PostgreSQL to use large TLB pages, if the OS and processor support them. Hmm ... it seems interesting, but I'm hesitant to do a lot of work to support something that's only available on one hardware-and-OS combination. True; further, I personally find the current API a little cumbersome. For example, we get 4MB pages on Solaris with a few lines of code: #if defined(solaris) defined(__sparc__) /* use intimate shared memory on SPARC Solaris */ memAddress = shmat(shmid, 0, SHM_SHARE_MMU); But given that (a) Linux on x86 is probably our most popular platform (b) Every x86 since the Pentium has supported large pages (c) Other archs, like IA64 and SPARC, also support large pages I think it's worthwhile implementing this, if possible. I trust it at least supports inheriting the page mapping over a fork()? I'll check on this, but I'm pretty sure that it does. The SysV API provides a reliable interlock to prevent this scenario: we read the old shared memory block ID from the old postmaster's postmaster.pid file, and look to see if that block (a) still exists and (b) still has attached processes (presumably backends). If the postmaster is starting up and the segment still exists, could we assume that's an error condition, and force the admin to manually fix it? It does make the system less robust, but I'm suspicious of any attempts to automagically fix a situation in which we *know* something has gone seriously wrong... Another possibility might be to still allocate a small SysV shmem area, and use that to provide the interlock, while we allocate the buffer area using sys_alloc_hugepages. That's somewhat of a hack, but I think it would resolve the interlock problem, at least. Any ideas for better answers? Still scratching my head on this one, and I'll let you know if I think of anything better. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Gana con Es-Fácil!
Es facil, pruébalo ... ¡Hola! Recibes este mensaje en nombre de Francisco, que está disfrutando de las ventajas de ser un usuario de Es-Fácil! A través de nuestro servicio, podrás recibir información en tu buzón de correo, y además cobrar por ello. Además, te ofrecemos otras vías para aumentar tu cuenta y conseguir más dinero. Te preguntarás cómo lo hacemos. Es facil. Simplemente pagamos a nuestros usuarios una parte del dinero que cobramos a las empresas por promocionarse a través nuestro. De este modo, las empresas pueden realizar promociones de acuerdo a sus necesidades, y nuestros usuarios ganan dinero por mantenerse informados de los temas que le interesan. Si estás interesado/a, conéctate a: http://www.es-facil.com/ganar/alta?Id=63334514 y rellena el sencillo formulario. ¡¡No esperes más!! Conéctate a: http://www.es-facil.com/ganar/alta?Id=63334514 y empieza a ganar dinero ya!!!. Ah! También puedes ganar importantes sumas de dinero con nuestro programa de afiliados. Esperando que pronto seas un nuevo usuario, recibe un cordial saludo, El equipo de Es-Fácil! en nombre de Francisco Alvarez Ortiz ---(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] inquiry
thank you for your information. I want to use the imformation of show or other utilities sql in jdbc interface. generally resultset is empty when such sql is execute. I want to get the information like psql. How can I do? Thanks again. Youshouldaskthisongeneral,andbemorespecificabouthowpgresult isdifferentfromthatSHOWcurrentlydoes. Duetotimeconstraints,IdonotdirectlyanswergeneralPostgreSQL questions.Forassistance,pleasejointheappropriatemailinglistand postyourquestion: http://www.postgresql.org/users-lounge Youcanalsotrythe#postgresqlIRCchannel.SeethePostgreSQLFAQ formoreinformation. --- [?GB2312?]wrote: DearMomjian£¬ hello, IwanttomaketheshowvariableSQLtoreturnmessagelikepgresult.howcanIrevisethesourcecode?Anysuggestion? Ireallyneedyouhelp. JinqiangHan -- BruceMomjian|http://candle.pha.pa.us [EMAIL PROTECTED]|(610)359-1001 +Ifyourlifeisaharddrive,|13RobertsRoad +Christcanbeyourbackup.|NewtownSquare,Pennsylvania19073
Re: [HACKERS] making use of large TLB pages
Neil Conway [EMAIL PROTECTED] writes: I think it's worthwhile implementing this, if possible. I wasn't objecting (I work for Red Hat, remember ;-)). I was just saying there's a limit to the messiness I think we should accept. The SysV API provides a reliable interlock to prevent this scenario: we read the old shared memory block ID from the old postmaster's postmaster.pid file, and look to see if that block (a) still exists and (b) still has attached processes (presumably backends). If the postmaster is starting up and the segment still exists, could we assume that's an error condition, and force the admin to manually fix it? It wasn't clear from your description whether large-TLB shmem segments even have IDs that one could use to determine whether the segment still exists. If the segments are anonymous then how do you do that? It does make the system less robust, but I'm suspicious of any attempts to automagically fix a situation in which we *know* something has gone seriously wrong... We've spent a lot of effort on trying to ensure that we (a) start up when it's safe and (b) refuse to start up when it's not safe. While (b) is clearly the more critical point, backsliding on (a) isn't real nice either. People don't like postmasters that randomly fail to start. Another possibility might be to still allocate a small SysV shmem area, and use that to provide the interlock, while we allocate the buffer area using sys_alloc_hugepages. That's somewhat of a hack, but I think it would resolve the interlock problem, at least. Not a bad idea ... I have not got a better one offhand ... but watch out for SHMMIN settings. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGXLOG variable worthwhile?
I do. The problem is that if you change the location of pg_xlog and do one thing wrong, poof, your database is now corrupt. Like Tom said earlier, imagine a command like switch called please-dont-scram-my-database and if you ever forgot it then your data is gone. Is it better to move such a switch into the postgresql.conf file? Imagine a GUC setting called butter-and-bread that when set would delete all your data. That's what the equivalent here is, if you make a single mistake. Having a FILE called pg_xlog isn't the fix here, it's the result of the fix, which is to take all the steps of moving the pg_xlog directory and put them into one script file the user doesn't need to understand to do it right. I.e. idiot proof the system as much as possible. We could do it much simpler, if everyone was on Unix. We could just write a script that would do everything the same but instead of using a file called pg_xlog, would make a link. the reason for the file is to make it more transportable to brain damaged OSes like Windows. Do you really think the GUC variable is a safe way of referencing the pg_xlog directory all by itself? I can see MANY posts to the lists that will go like this: I just installed Postgresql 7.4 and it's been working fine. I needed more speed, so I looked up the GUC for the pg_xlog and set it to /vol/vol3/ on my machine. Now my database won't come up. I set it back but it still won't come up. What can I do to fix that? Here's the email we'd get from my solution: Hey, I just tried to move my pg_xlog directory with the mvpgxlog script, and it gave an error of permission denied on destination. What does that mean? The choice is yours. On Wed, 25 Sep 2002, Bruce Momjian wrote: I don't see the gain of having a file called pg_xlog vs. using GUC. --- scott.marlowe wrote: On Wed, 25 Sep 2002, Curt Sampson wrote: On Tue, 24 Sep 2002, Jan Wieck wrote: And AFAICS it is scary only because screwing that up will simply corrupt your database. Thus, a simple random number (okay, and a timestamp of initdb) in two files, one in $PGDATA and one in $PGXLOG would be a totally sufficient safety mechanism to prevent starting with the wrong XLOG directory. But still, why set up a situation where your database might not start? Why not set it up so that if you get just *one* environment or command-line variable right, you can't set another inconsistently and screw up your start anyway? Why store configuration information outside of the database data directory in a form that's not easily backed up, and not easily found by other utilities? It's almost like people *don't* want to put this in the config file or something Curt, did you see my post about this earlier? I'll repeat it now, just in case anyone else missed it. Problem: - People need to move the pg_xlog directory around on heavily loaded systems to improve performance Constraints: - Windows can't reliably use links to do this. - If the pg_xlog directory is moved wrong or referenced incorrectly, data corruption may occur. This makes using a switch or environmental var dangerous I consider using a GUC in the postgresql.conf file to be better than any other option listed so far, but it is still a dangerous place for it to be. So, the way I think that would work best would be: If there's a directory called pg_xlog in the $PGDATA directory, then use that. If there's a file called pg_xlog in the $PGDATA directory, then it will contain the path to the real pg_xlog directory. If you want to move the pg_xlog directory, you called a custom script called mvpgxlog or something like it that: 1: Checks to make sure the database is shut down 2: Checks to make sure the destination path has enough free space for the xlogs 3: If these are both true (and whatever logic we need here for safety) then copy the current pg_xlog directory contents to the new pg_xlog (even if we are already using an alternative location, this should work), set proper permissions, rename / move the pg_xlog file / directorry, then edit/create the $PGDATA/pg_xlog file to point to the new directory. This method has several advantages, and no real disadvantages I can think of. The advantages are: - It makes it easy to move the pg_xlog directory. - It works equally well for Windows and Unix. - Gets rid of another GUC setting people can scram their database with. - It is easy to backup your pg_xlog setting. - If painted green it should not rust. How's that sound for a general theory of operation? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)---
Re: [HACKERS] contrib/earthdistance missing regression test files
Bruce Momjian [EMAIL PROTECTED] writes: OK, I reinstalled the proper earthdistance.out/sql files and it passes regession now. Sorry for the mistake. Looks good here too. Thanks. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New SSL code to be removed
Bruce Momjian wrote: Because the new 7.3 SSL code doesn't work (per Peter), and the author is not responding, I am about to yank out that code. Peter suggests ripping out all the new code rather than try to pick around and remove just the broken parts. Agreed. I allways wondered what SSL DB-connections are good for. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New SSL code to be removed
Jan Wieck wrote: Bruce Momjian wrote: Because the new 7.3 SSL code doesn't work (per Peter), and the author is not responding, I am about to yank out that code. Peter suggests ripping out all the new code rather than try to pick around and remove just the broken parts. Agreed. I allways wondered what SSL DB-connections are good for. I am not going to rip out SSL, just the changes. We do have people who use SSL quite a bit. Looking at the code, however, I may see an easy way to allow SSL connections without requiring server certificates. If that is doable, I may just make that change and let the rest of the code stay. -- 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]
[HACKERS] Bug in PL/pgSQL GET DIAGNOSTICS?
Hi,
I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS.
In a PL/pgSQL function I want to insert into a table and get the OID back.
That usually works with
GET DIAGNOSTICS last_oid = RESULT_OID;
right after the insert statement.
But if the table that I insert to has a rule (or perhaps a trigger?) that
updates another table, the RESULT_OID after the insert will be 0 (zero).
Can this be fixed (I have no such problem with JDBC and getLastOID())?
Testcase:
CREATE TABLE pltest (
id BIGINT default cs_nextval('invoice_invoice_id') NOT NULL,
t TEXT,
primary key (id)
);
CREATE TABLE plcounter (
counter INTEGER NOT NULL
);
CREATE FUNCTION pltestfunc(integer) RETURNS BOOLEAN AS'
DECLARE
lastOID OID;
BEGIN
FOR i IN 1..$1 LOOP
INSERT INTO pltest (t) VALUES (\'test\');
GET DIAGNOSTICS lastOID = RESULT_OID;
RAISE NOTICE \'RESULT_OID: %\', lastOID;
IF lastOID = 0 THEN
RAISE EXCEPTION \'RESULT_OID is zero\';
END IF;
END LOOP;
RETURN true;
END;
' LANGUAGE 'plpgsql';
-- comment out the rule and the test will work
CREATE RULE pltest_insert AS
ON INSERT TO pltest DO
UPDATE plcounter SET counter=counter+1;
INSERT INTO plcounter VALUES (0);
SELECT pltestfunc(10);
SELECT * FROM pltest;
DROP FUNCTION pltestfunc(integer);
DROP TABLE pltest;
Regards,
Michael
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] PGXLOG variable worthwhile?
Curt Sampson wrote: On Tue, 24 Sep 2002, Jan Wieck wrote: And AFAICS it is scary only because screwing that up will simply corrupt your database. Thus, a simple random number (okay, and a timestamp of initdb) in two files, one in $PGDATA and one in $PGXLOG would be a totally sufficient safety mechanism to prevent starting with the wrong XLOG directory. But still, why set up a situation where your database might not start? Why not set it up so that if you get just *one* environment or command-line variable right, you can't set another inconsistently and screw up your start anyway? Why store configuration information outside of the database data directory in a form that's not easily backed up, and not easily found by other utilities? With the number of screws our product has, there are so many possible combinations that don't work, why worry about one more or less? Seriously, if you move around files, make symlinks or adjust config variable to reflect that, there's allways the possibility that you fatfinger it and cannot startup. The point is not to make it pellethead-safe so that the damned thing will start allways, but to make it pellethead-safe so that an attempt to start with wrong settings doesn't blow away the whole server. It's almost like people *don't* want to put this in the config file or something I want to have it it the config file. Just that that doesn't prevent anything. And if we have a signature file in the xlog and data directories, you can make it dummy-safe as you like ... if the config option is set wrong, first search for it on all drives before bailing out and if found, postmaster corrects the config setting. That way the admin can play hide and seek with our database ... ;-) Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PGXLOG variable worthwhile?
Bruce Momjian [EMAIL PROTECTED] writes: I don't see the gain of having a file called pg_xlog vs. using GUC. Well, the point is to have a safety interlock --- but I like Jan's idea of using matching identification files in both directories. With that, a GUC variable seems just fine. 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] PGXLOG variable worthwhile?
On Wed, 25 Sep 2002, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I don't see the gain of having a file called pg_xlog vs. using GUC. Well, the point is to have a safety interlock --- but I like Jan's idea of using matching identification files in both directories. With that, a GUC variable seems just fine. Agreed, the interlock is a great idea. I hadn't seen that one go by. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] inquiry
In 7.3, SHOW returns a query results that can be resturned to jdbc. We are using beta1/2 now, so you can test that from ftp.postgresql.org. --- [ ?GB2312?] wrote: thank you for your information. I want to use the imformation of show or other utilities sql in jdbc interface. generally resultset is empty when such sql is execute. I want to get the information like psql. How can I do? Thanks again. You should ask this on general, and be more specific about how pgresult is different from that SHOW currently does. Due to time constraints, I do not directly answer general PostgreSQL questions. For assistance, please join the appropriate mailing list and post your question: http://www.postgresql.org/users-lounge You can also try the #postgresql IRC channel. See the PostgreSQL FAQ for more information. --- [ ?GB2312?] wrote: Dear Momjian£¬ hello, I want to make the show variable SQL to return message like pgresult. how can I revise the source code? Any suggestion? I really need you help. Jinqiang Han -- 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 -- 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/users-lounge/docs/faq.html
[HACKERS] Insert Performance
Hi, I am wondering about bad INSERT performance compared against the speed of COPY. (I use 7.2.2 on RedHat 7.2) I have a table with about 30 fields, some constraints, some indexes, some foreign key constraints. I use COPY to import old data. Copying about 10562 rows takes about 19 seconds. For testing I have writtin a simple function in PL/pgSQL that inserts dummy records into the same table (just a FOR loop and an INSERT INTO ...). To insert another 10562 rows takes about 12 minutes now!!! What is the problem with INSERT in postgresql? I usually don't compare mysql and postgresql because mysql is just playing stuff, but I have think that the insert performance of mysql (even with innodb tables) is about 10 times better than the insert performance of postgresql. What is the reason and what can be done about it? Best Regards, Michael P.S: Perhaps you want to know about my postgresql.conf # # Shared Memory Size # shared_buffers = 12288 # 2*max_connections, min 16 max_fsm_relations = 100# min 10, fsm is free space map max_fsm_pages = 2 # min 1000, fsm is free space map max_locks_per_transaction = 64 # min 10 wal_buffers = 8# min 4 # # Non-shared Memory Sizes # sort_mem = 4096# min 32 (in Kb) vacuum_mem = 16384 # min 1024 # # Write-ahead log (WAL) # wal_files = 8 # range 0-64, default 0 wal_sync_method = fdatasync # the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync fsync = true ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Insert Performance
Michael Paesold [EMAIL PROTECTED] writes: To insert another 10562 rows takes about 12 minutes now!!! See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/populate.html particularly the point about not committing each INSERT as a separate transaction. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bug in PL/pgSQL GET DIAGNOSTICS?
Michael Paesold [EMAIL PROTECTED] writes: I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS. Hm. This seems to be SPI's version of the same definitional issue we're contending with for status data returned from an interactive query: SPI is currently set up to return the status of the last querytree it executes, which is probably the wrong thing to do in the presence of rule rewrites. But I'm hesitant to change SPI until we know what we're going to do for interactive query status. 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] Bug in PL/pgSQL GET DIAGNOSTICS?
Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS. Hm. This seems to be SPI's version of the same definitional issue we're contending with for status data returned from an interactive query: SPI is currently set up to return the status of the last querytree it executes, which is probably the wrong thing to do in the presence of rule rewrites. But I'm hesitant to change SPI until we know what we're going to do for interactive query status. regards, tom lane So this is not going to be fixed for 7.3 I suggest, no? Can you add the issue to the TODO list or can this thread be added to any appropriate TODO item? Regards, Michael Paesold ---(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] Insert Performance
Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: To insert another 10562 rows takes about 12 minutes now!!! See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/populate.html particularly the point about not committing each INSERT as a separate transaction. regards, tom lane As I said I wrote a function to insert the rows (PL/pgSQL). All values were inserted inside a single function call; I always though that a function call would be executed inside a transaction block. Experience says it does. About the other points in the docs: Use COPY FROM: Well, I am currently comparing INSERT to COPY ... ;) Remove Indexes: Doesn't COPY also have to update indexes? ANALYZE Afterwards: I have done a VACUUM FULL; VACUUM ANALYZE; just before running the test. So is it just the planner/optimizer/etc. costs? Would a PREPARE in 7.3 help? Best Regards, Michael Paesold ---(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
[HACKERS] CVS checkout errors
I am getting errors when doing a checkout, related to Marc's splitting up the CVS tree into modules: C pgsql/contrib/earthdistance/Makefile cvs checkout: move away pgsql/contrib/earthdistance/README.earthdistance; it is in the way C pgsql/contrib/earthdistance/README.earthdistance cvs checkout: move away pgsql/contrib/earthdistance/earthdistance.c; it is in the way I get this from a CVS checkout every time. Can someone fix 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] compiling client utils under win32 - current 7.3devel is broken
I'm trying to get the client utilities to compile under win32/VS.net per
http://developer.postgresql.org/docs/postgres/install-win32.html.
I was able to do this successfully using the 7.2.2 tarball, but using current
7.3devel there are a number of minor issues (missing defines, adjustments to
includes), and one more difficult item (at least so far). The latter is the
use of gettimeofday in fe-connect.c:connectDBComplete for which there does not
seem to be a good alternate under win32.
In connectDBComplete I see:
/*
* Prepare to time calculations, if connect_timeout isn't zero.
*/
if (conn-connect_timeout != NULL)
{
remains.tv_sec = atoi(conn-connect_timeout);
so it seems that the connection timeout can only be specified to the nearest
second. Given that, is there any reason not to use time() instead of
gettimeofday()?
It looks like there is a great deal of complexity added to the function just
to accommodate the fact that gettimeofday returns seconds and microseconds as
distinct members of the result struct. I think switching this code to use
time() would both simplify it, and make it win32 compatible.
Comments?
Joe
---(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] Insert Performance
Michael Paesold [EMAIL PROTECTED] writes: To insert another 10562 rows takes about 12 minutes now!!! As I said I wrote a function to insert the rows (PL/pgSQL). All values were inserted inside a single function call; I always though that a function call would be executed inside a transaction block. Experience says it does. Well, there's something fishy about your results. Using CVS tip I see about a 4-to-1 difference between COPYing 1 rows and INSERT'ing 1 rows (as one transaction). That's annoyingly high, but it's still way lower than what you're reporting ... I used the contents of table tenk1 in the regression database for test data, and dumped it out with pg_dump -a with and without -d. I then just timed feeding the scripts to psql ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] compiling client utils under win32 - current 7.3devel is broken
Joe Conway [EMAIL PROTECTED] writes: ...it seems that the connection timeout can only be specified to the nearest second. Given that, is there any reason not to use time() instead of gettimeofday()? As the code stands it's pretty necessary. Since we'll go around the loop multiple times, in much less than a second per loop in most cases, the timeout resolution will be really poor if we only measure each iteration to the nearest second. It looks like there is a great deal of complexity added to the function just to accommodate the fact that gettimeofday returns seconds and microseconds as distinct members of the result struct. It is ugly coding; if you can think of a better way, go for it. It might work to measure time since the start of the whole process, or until the timeout target, rather than accumulating adjustments to the remains count each time through. In other words something like at start: targettime = time() + specified-timeout each time we are about to wait: set select timeout to targettime - time(). This bounds the error at 1 second which is probably good enough (you might want to add 1 to targettime to ensure the error is in the conservative direction of not timing out too soon). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PGXLOG variable worthwhile?
scott.marlowe wrote: Having a FILE called pg_xlog isn't the fix here, it's the result of the fix, which is to take all the steps of moving the pg_xlog directory and put them into one script file the user doesn't need to understand to do it right. I.e. idiot proof the system as much as possible. And your script/program cannot modify postgresql.conf instead of creating a new file? Please remember: A fool with a tool is still a fool. You can provide programs and scripts as many as you want. There have allways been these idiots who did stuff like truncating pg_log ... Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Insert Performance
Update: vacuum full; vacuum analyze; select bench_invoice(1000); select bench_invoice(1000); ... (10 times) It seems performance is degrading with every insert! Here is the result (time in seconds in bench_invoice(), commit between selects just under a second) 13, 24, 36, 47, 58, 70, 84, 94, 105, 117, ... (seconds per 1000 rows inserted) Isn't that odd? I have tried again. vacuum analyze alone (without full) is enough to lower times again. They will start again with 13 seconds. Tested further what exactly will reset insert times to lowest possible: vacuum full; helps vacuum analyze; helps analyze tablename; of table that I insert to doesn't help! analyze tablename; of any table reference in foreign key constraints doesn't help! Only vacuum will reset the insert times to the lowest possible! What does the vacuum code do?? :-] Regards, Michael Paesold ---(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] Insert Performance
Only vacuum will reset the insert times to the lowest possible! What does the vacuum code do?? :-] Please see the manual and the extensive discussions on this point in the archives. This behaviour is well known -- though undesirable. It is an effect of the multi-version concurrency control system. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] compiling client utils under win32 - current 7.3devel
Tom Lane wrote: It might work to measure time since the start of the whole process, or until the timeout target, rather than accumulating adjustments to the remains count each time through. In other words something like at start: targettime = time() + specified-timeout each time we are about to wait: set select timeout to targettime - time(). This bounds the error at 1 second which is probably good enough (you might want to add 1 to targettime to ensure the error is in the conservative direction of not timing out too soon). I was working with this approach, when I noticed on *unmodified* cvs tip (about a day old): test=# set statement_timeout=1; SET test=# \dt ERROR: Query was cancelled. test=# At: http://developer.postgresql.org/docs/postgres/runtime-config.html#LOGGING the setting is described like this: STATEMENT_TIMEOUT (integer) Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer. The proposed change will take this to a 1 second granularity anyway, so I was thinking we should change the setting to have a UOM of seconds, and fix the documentation. Any comments or concerns with regard to this plan? Thanks, Joe ---(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] PGXLOG variable worthwhile?
On Wed, 25 Sep 2002, Jan Wieck wrote: With the number of screws our product has, there are so many possible combinations that don't work, why worry about one more or less? That's just silly, so I won't even bother replying. Seriously, if you move around files, make symlinks or adjust config variable to reflect that, there's allways the possibility that you fatfinger it and cannot startup. True. But once your symlink is in place, it is stored on disk in the postgres data directory. An environment variable is a transient setting in memory, which means that you have to have a program set it, and you have to make sure that program gets run before any startup, be it an automated startup from /etc/rc on boot or a manual startup. I want to have it it the config file. Well, then we're agreed. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Web site
On 24 Sep 2002, Neil Conway wrote: Gavin Sherry [EMAIL PROTECTED] writes: It occurs to me that opening web page on www.postgresql.org, asking the user to select the mirror, is rather unprofessional. I agree; not only that, it has advertisements on it. What's the justification for that, considering that none of the mirror sites (AFAIK) have ads on them? Actually, that is part of the redesign as well ... ---(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] pltcl.so patch
Okay, I've looked again at spi_exec and I believe I can fix the bug I introduced and the memory leak. However, I have only looked quickly and not made these most recent changes to the execp version nor to the plpython code. Therefore I am not attaching a patch at the moment, just mentioning that I've straightened this out in my brain a bit more. On Wed, 25 Sep 2002, Nigel J. Andrews wrote: On 25 Sep 2002, Neil Conway wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Yes, I do get the similar results. A quick investigation shows that the SPI_freetuptable at the end of pltcl_SPI_exec is trying to free a tuptable of value 0x82ebe64 (which looks sensible to me) but which has a memory context of 0x7f7f7f7f (the unallocated marker). Attached is a patch against CVS HEAD which fixes this, I believe. The problem appears to be the newly added free of the tuptable at the end of pltcl_SPI_exec(). I've added a comment to that effect: /* * Do *NOT* free the tuptable here. That's because if the loop * body executed any SQL statements, it will have already free'd * the tuptable itself, so freeing it twice is not wise. We could * get around this by making a copy of SPI_tuptable-vals and * feeding that to pltcl_set_tuple_values above, but that would * still leak memory (the palloc'ed copy would only be free'd on * context reset). */ That's certainly where the fault was happening. However, that's where the original memory leak problem was coming from (without the SPI_freetuptable call). It could be I got that fix wrong and the extra calls you've added are the right fix for that. I'll take a look to see what I can learn later. At least, I *think* that's the problem -- I've only been looking at the code for about 20 minutes, so I may be wrong. In any case, this makes both memleak() and memleak(1) work on my machine. Let me know if it works for you, and/or if someone knows of a better solution. I'll have to check later. I also added some SPI_freetuptable() calls in some places where Nigel didn't, and added some paranoia when dealing with statically sized buffers (snprintf() rather than sprintf(), and so on). I also didn't include Nigel's changes to some apparently unrelated PL/Python stuff -- this patch includes only the PL/Tcl changes. I dare say the plpython needs to be checked by someone who knows how to since I can well imagine the same nested call fault will exist there. -- Nigel J. Andrews ---(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] Insert Performance
Michael Paesold [EMAIL PROTECTED] writes: Only vacuum will reset the insert times to the lowest possible! What does the vacuum code do?? :-] It removes dead tuples. Dead tuples can only arise from update or delete operations ... so you have not been telling us the whole truth. An insert-only test would not have this sort of behavior. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] New PostgreSQL Tool available :
You should have chosen a better foundation. pg_bench is notorious for producing results that are (a) nonrepeatable and (b) not relevant to a wide variety of situations. All it really tells you about is the efficiency of a large number of updates to a small number of rows. You might want to try -N option of pgbench. It avoids updates to branches and tellers tables. -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] New PostgreSQL Tool available :pg_autotune
Tatsuo Ishii wrote: You should have chosen a better foundation. pg_bench is notorious for producing results that are (a) nonrepeatable and (b) not relevant to a wide variety of situations. All it really tells you about is the efficiency of a large number of updates to a small number of rows. You might want to try -N option of pgbench. It avoids updates to branches and tellers tables. Cool. Do you feel this will noticeable increase the consistency of the measurements? The inconsistency of the internal benchmark results means that pg_autotune has been using 5-run averages, and using a large tolerance factor by default. It would be good to improving on that. :-) Regards and best wishes, Justin Clift -- Tatsuo Ishii -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] unicode
The actual checking is done in INSERT/UPDATE/COPY. However, the
checking is currently very limited: every byte of a mutibyte character
must be greater than 0x7f.
Tatsuo,
do I understand correctly that there is no checking for
convertion between local charset and unicode in insert and
checking is done only in select ?
test=# create table qq (a text);
CREATE TABLE
test=# \encoding koi8
test=# insert into qq values('бартунов');
INSERT 24617 1
test=# \encoding unicode
test=# select * from qq;
a
--
п�п�я���п�п�
(1 row)
test=# \encoding unicode
test=# insert into qq values('бартунов');
INSERT 24618 1
test=# select * from qq;
a
--
п�п�я���п�п�
(2 rows)
test=# \encoding koi8
test=# select * from qq;
WARNING: UtfToLocal: could not convert UTF-8 (0xc2c1). Ignored
WARNING: UtfToLocal: could not convert UTF-8 (0xd2d4). Ignored
WARNING: UtfToLocal: could not convert UTF-8 (0xd5ce). Ignored
WARNING: UtfToLocal: could not convert UTF-8 (0xcfd7). Ignored
a
--
бартунов
(2 rows)
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bug in PL/pgSQL GET DIAGNOSTICS?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I already have a TODO item: * Return proper effected tuple count from complex commands [return] I am unsure if it will be fixed in 7.3 or not. It is still on the open items list, and I think we have a general plan to fix it. I got distracted and wasn't following the thread a few days ago about the topic. Did people come to a consensus about how it should work? OK, I am back. I think the most promising proposal was from you, Tom: http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html It basically breaks down the three results (tag, oid, tuple count), and the INSTEAD/non-INSTEAD behavior. I actually got a big chuckle from this paragraph: Come on, guys, work with me a little here. I've thrown out several alternative suggestions already, and all I've gotten from either of you is refusal to think about the problem. I liked the work with me phrase. To summarize, with non-INSTEAD, we get the tag, oid, and tuple count of the original query. Everyone agrees on that. For non-INSTEAD, we have: 1) return original tag 2) return oid if all inserts in the rule insert only one row 3) return tuple count of all commands with the same tag For item 2, it is possible to have multiple INSERTS in the rule and return an oid if the sum of the inserts is only one row. Item 3 is the most controversial. Some say sum all tuple counts, i.e. sum INSERT/UPDATE/DELETE. That just seems to messy to me. I think summing only the matching tags has the highest probability of returning a meaningful number. Also, item 2 and 3 work well together with INSERT because a tuple count of 1 returns an oid, while 1 does not, which is consistent with a non-rule insert. (FYI, I am still working SSL.) -- 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 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] postmaster -d option (was Re: [GENERAL] Relation 0 does not exist)
Tom Lane wrote: Patrick Welche [EMAIL PROTECTED] writes: ... I was running postmaster -d4, yet the only query I saw was the last LOG one. I pretty sure that I would see all queries with -d3 before.. It looked to me like you were just running with the recently-added frill to log only queries that cause errors; which is on by default. (Looks at code...) Ah. It looks like -d to the postmaster no longer means anywhere near what it used to. Bruce --- compare the handling of -d in the backend (postgres.c lines 1251ff) with its handling in the postmaster (postmaster.c lines 444ff). Big difference. Are we going to make these more alike? If so, which one do we like? I am sorry but I don't understand. They look like they both set server_min_messages. There was a comment in one that said client_min_messages but I just fixed that. -- 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/backend/tcop/postgres.c === RCS file: /cvsroot/pgsql-server/src/backend/tcop/postgres.c,v retrieving revision 1.294 diff -c -c -r1.294 postgres.c *** src/backend/tcop/postgres.c 25 Sep 2002 20:31:40 - 1.294 --- src/backend/tcop/postgres.c 26 Sep 2002 01:57:48 - *** *** 1258,1267 sprintf(debugstr, debug%s, optarg); SetConfigOption(server_min_messages, debugstr, ctx, gucsource); pfree(debugstr); - /* * -d is not the same as setting !* client_min_messages because it enables other * output options. */ if (atoi(optarg) = 1) --- 1258,1266 sprintf(debugstr, debug%s, optarg); SetConfigOption(server_min_messages, debugstr, ctx, gucsource); pfree(debugstr); /* * -d is not the same as setting !* server_min_messages because it enables other * output options. */ if (atoi(optarg) = 1) *** *** 1275,1288 if (atoi(optarg) = 5) SetConfigOption(debug_print_rewritten, true, ctx, gucsource); } - else - - /* -* -d 0 allows user to prevent postmaster debug -* from propagating to backend. -*/ - SetConfigOption(server_min_messages, notice, - ctx, gucsource); } break; --- 1274,1279 ---(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] compiling client utils under win32 - current 7.3devel
Joe Conway wrote: I was working with this approach, when I noticed on *unmodified* cvs tip (about a day old): test=# set statement_timeout=1; SET test=# \dt ERROR: Query was cancelled. test=# At: http://developer.postgresql.org/docs/postgres/runtime-config.html#LOGGING the setting is described like this: STATEMENT_TIMEOUT (integer) Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer. The proposed change will take this to a 1 second granularity anyway, so I was thinking we should change the setting to have a UOM of seconds, and fix the documentation. Any comments or concerns with regard to this plan? Uh, I thought you were changing connection_timeout, which is libpq and not a GUC parameter, not statement_timeout. Do we want sub-second timeout values? Not sure. -- 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] compiling client utils under win32 - current 7.3devel
Bruce Momjian wrote: Uh, I thought you were changing connection_timeout, which is libpq and not a GUC parameter Yup, you're right -- I got myself confused. Sorry. not statement_timeout. Do we want sub-second timeout values? Not sure. I found it surprising that the statement_timeout was not in units of seconds, but that's only because I read the docs after I tried it instead of before. I can't think of a reason to have sub-second values, but it's probably not worth changing it at this point. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postmaster -d option (was Re: [GENERAL] Relation 0 does not exist)
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: (Looks at code...) Ah. It looks like -d to the postmaster no longer means anywhere near what it used to. Bruce --- compare the handling of -d in the backend (postgres.c lines 1251ff) with its handling in the postmaster (postmaster.c lines 444ff). Big difference. Are we going to make these more alike? If so, which one do we like? I am sorry but I don't understand. They look like they both set server_min_messages. Yeah, but postgres.c *also* sets log_connections, log_statement, debug_print_parse, debug_print_plan, debug_print_rewritten depending on the -d level. This behavior is not random; it's an attempt to reproduce the effects of the historical -d switch. The postmaster.c code is blowing off all those considerations. *** 1275,1288 if (atoi(optarg) = 5) SetConfigOption(debug_print_rewritten, true, ctx, gucsource); } - else - - /* - * -d 0 allows user to prevent postmaster debug - * from propagating to backend. - */ - SetConfigOption(server_min_messages, notice, - ctx, gucsource); } break; I think you are deleting your own code there ... why? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postmaster -d option (was Re: [GENERAL] Relation 0 does not exist)
Uh, yes, it is a little confusing and I am not sure that patch is right anymore. I haven't applied it. Another issue is that we used to have a global debug_level variable that was propogated to the client. Now, we just have the GUC value which does propogate like the global one did. Does the postmaster still pass -dX down to the child like it used to? I don't see why you say, The postmaster.c code is blowing off all those considerations. I -d0 think functions properly except that it sets the value to 'notice' rather than resetting it to the postgresql.conf value. Is there a way to do that? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: (Looks at code...) Ah. It looks like -d to the postmaster no longer means anywhere near what it used to. Bruce --- compare the handling of -d in the backend (postgres.c lines 1251ff) with its handling in the postmaster (postmaster.c lines 444ff). Big difference. Are we going to make these more alike? If so, which one do we like? I am sorry but I don't understand. They look like they both set server_min_messages. Yeah, but postgres.c *also* sets log_connections, log_statement, debug_print_parse, debug_print_plan, debug_print_rewritten depending on the -d level. This behavior is not random; it's an attempt to reproduce the effects of the historical -d switch. The postmaster.c code is blowing off all those considerations. *** 1275,1288 if (atoi(optarg) = 5) SetConfigOption(debug_print_rewritten, true, ctx, gucsource); } - else - - /* -* -d 0 allows user to prevent postmaster debug -* from propagating to backend. -*/ - SetConfigOption(server_min_messages, notice, - ctx, gucsource); } break; I think you are deleting your own code there ... why? regards, tom lane -- 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] compiling client utils under win32 - current 7.3devel
Joe Conway wrote: Bruce Momjian wrote: Uh, I thought you were changing connection_timeout, which is libpq and not a GUC parameter Yup, you're right -- I got myself confused. Sorry. not statement_timeout. Do we want sub-second timeout values? Not sure. I found it surprising that the statement_timeout was not in units of seconds, but that's only because I read the docs after I tried it instead of before. I can't think of a reason to have sub-second values, but it's probably not worth changing it at this point. Most queries are sub-second in duration so it seemed logical to keep it the same as deadlock_timeout. I can see someone setting a 1/2 second delay for queries. -- 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] Bug in PL/pgSQL GET DIAGNOSTICS?
Bruce Momjian [EMAIL PROTECTED] writes: OK, I am back. I think the most promising proposal was from you, Tom: http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html But that wasn't a specific proposal --- it was more or less an enumeration of the possibilities. What are we picking? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postmaster -d option (was Re: [GENERAL] Relation 0 does not exist)
Bruce Momjian [EMAIL PROTECTED] writes: ... Now, we just have the GUC value which does propogate like the global one did. Does the postmaster still pass -dX down to the child like it used to? Evidently not; else Patrick wouldn't be complaining that it doesn't work like it used to. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] compiling client utils under win32 - current 7.3devel
Bruce Momjian [EMAIL PROTECTED] writes: Joe Conway wrote: I can't think of a reason to have sub-second values, but it's probably not worth changing it at this point. Most queries are sub-second in duration so it seemed logical to keep it the same as deadlock_timeout. And machines get faster all the time. I'm not too concerned about resolution of a connection timeout, but I think we want to be able to express small query timeouts. 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] Bug in PL/pgSQL GET DIAGNOSTICS?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, I am back. I think the most promising proposal was from you, Tom: http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html But that wasn't a specific proposal --- it was more or less an enumeration of the possibilities. What are we picking? The rest of my message explains your poposal while clarifying certain options you gave in the email. -- 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] SSL code fixed
Jan Wieck wrote:
Bruce Momjian wrote:
Because the new 7.3 SSL code doesn't work (per Peter), and the author is
not responding, I am about to yank out that code. Peter suggests
ripping out all the new code rather than try to pick around and remove
just the broken parts.
Agreed. I allways wondered what SSL DB-connections are good for.
OK, I have aplied the following patch to allow SSL to work without
client certificates. There was some confusion in the code because while
the comments said client certificates were not required, the
infrastructure on the client side was required. This patch removes the
requirement, and adds a comment so Bear can make adjustments for 7.4. I
don't think we ever want to _require_ client-side certificates.
I did not remove the code because after quick review I saw that his code
actually filled in areas our pre-7.3 code was missing. I will have him
review this patch and make any adjustments.
--
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: doc/src/sgml/runtime.sgml
===
RCS file: /cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.139
diff -c -c -r1.139 runtime.sgml
*** doc/src/sgml/runtime.sgml 25 Sep 2002 21:16:10 - 1.139
--- doc/src/sgml/runtime.sgml 26 Sep 2002 04:36:08 -
***
*** 2876,2881
--- 2876,2882
Enter the old passphrase to unlock the existing key. Now do
programlisting
openssl req -x509 -in cert.req -text -key cert.pem -out cert.cert
+ chmod og-rwx cert.pem
cp cert.pem replaceable$PGDATA/replaceable/server.key
cp cert.cert replaceable$PGDATA/replaceable/server.crt
/programlisting
Index: src/backend/libpq/be-secure.c
===
RCS file: /cvsroot/pgsql-server/src/backend/libpq/be-secure.c,v
retrieving revision 1.14
diff -c -c -r1.14 be-secure.c
*** src/backend/libpq/be-secure.c 4 Sep 2002 23:31:34 - 1.14
--- src/backend/libpq/be-secure.c 26 Sep 2002 04:36:12 -
***
*** 642,650
--- 642,654
snprintf(fnbuf, sizeof fnbuf, %s/root.crt, DataDir);
if (!SSL_CTX_load_verify_locations(SSL_context, fnbuf, CA_PATH))
{
+ return 0;
+ #ifdef NOT_USED
+ /* CLIENT CERTIFICATES NOT REQUIRED bjm 2002-09-26 */
postmaster_error(could not read root cert file (%s): %s,
fnbuf, SSLerrmessage());
ExitPostmaster(1);
+ #endif
}
SSL_CTX_set_verify(SSL_context,
SSL_VERIFY_PEER | SSL_VERIFY_CLIENT_ONCE,
verify_cb);
Index: src/interfaces/libpq/fe-secure.c
===
RCS file: /cvsroot/pgsql-server/src/interfaces/libpq/fe-secure.c,v
retrieving revision 1.13
diff -c -c -r1.13 fe-secure.c
*** src/interfaces/libpq/fe-secure.c22 Sep 2002 20:57:21 - 1.13
--- src/interfaces/libpq/fe-secure.c26 Sep 2002 04:36:23 -
***
*** 726,735
--- 726,739
pwd-pw_dir);
if (stat(fnbuf, buf) == -1)
{
+ return 0;
+ #ifdef NOT_USED
+ /* CLIENT CERTIFICATES NOT REQUIRED bjm 2002-09-26 */
printfPQExpBuffer(conn-errorMessage,
libpq_gettext(could not read root certificate list
(%s): %s\n),
fnbuf, strerror(errno));
return -1;
+ #endif
}
if (!SSL_CTX_load_verify_locations(SSL_context, fnbuf, 0))
{
***
*** 789,794
--- 793,800
/* check the certificate chain of the server */
+ #ifdef NOT_USED
+ /* CLIENT CERTIFICATES NOT REQUIRED bjm 2002-09-26 */
/*
* this eliminates simple man-in-the-middle attacks and simple
* impersonations
***
*** 802,807
--- 808,814
close_SSL(conn);
return -1;
}
+ #endif
/* pull out server distinguished and common names */
conn-peer = SSL_get_peer_certificate(conn-ssl);
***
*** 824,829
--- 831,838
/* verify that the common name resolves to peer */
+ #ifdef NOT_USED
+ /* CLIENT CERTIFICATES NOT REQUIRED bjm 2002-09-26 */
/*
* this is necessary to eliminate man-in-the-middle attacks and
* impersonations where the attacker somehow learned the server's
***
*** 834,839
--- 843,849
close_SSL(conn);
Re: [HACKERS] New SSL code to be removed
Jan Wieck wrote: Bruce Momjian wrote: Because the new 7.3 SSL code doesn't work (per Peter), and the author is not responding, I am about to yank out that code. Peter suggests ripping out all the new code rather than try to pick around and remove just the broken parts. Agreed. I allways wondered what SSL DB-connections are good for. I am now in email contact with Bear and he is assisting me in disabling all certificates for 7.3. The code will be marked as NOT_USED and can therefore be enables in later relases. He wants to get back this. -- 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] PGXLOG variable worthwhile?
On Wed, 25 Sep 2002, Jan Wieck wrote: scott.marlowe wrote: Having a FILE called pg_xlog isn't the fix here, it's the result of the fix, which is to take all the steps of moving the pg_xlog directory and put them into one script file the user doesn't need to understand to do it right. I.e. idiot proof the system as much as possible. And your script/program cannot modify postgresql.conf instead of creating a new file? That's a minor point. It could be anywhere. It's just that much like a symlink is visible from the shell with a simple ls -l, so too is pg_xlog being a file an obvious sign that pg_xlog doesn't live here anymore. Please remember: A fool with a tool is still a fool. You can provide programs and scripts as many as you want. There have allways been these idiots who did stuff like truncating pg_log So, should we take out seatbelts from cars, safeties from guns, and have everyone run about with sharp sticks too? :-) I know that the second we make something more idiot proof, someone will make a better idiot, but that doesn't mean we shouldn't make things more idiot proof, we should just try to anticipate the majority of idiots (and let's face it, we can all be idiots at the right moments sometimes.) But, I have a few more questions about the signature file solution. Is the signature file going to be updated by date or something everytime the database is started up and shut down? If not, then it's quite possible that someone could copy the pg_xlog dir somewhere, run it for a while, then they change it back to the base pg_xlog will the database know that those xlogs are stale and not start up, or will it start up and corrupt the database with the old xlogs? As long as there's a time stamp in both places it should work fine. ---(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] PGXLOG variable worthwhile?
scott.marlowe wrote: On Wed, 25 Sep 2002, Jan Wieck wrote: So, should we take out seatbelts from cars, safeties from guns, and have everyone run about with sharp sticks too? :-) I know that the second we make something more idiot proof, someone will make a better idiot, but that doesn't mean we shouldn't make things more idiot proof, we should just try to anticipate the majority of idiots (and let's face it, we can all be idiots at the right moments sometimes.) Can we wait for someone to be injured in a car accident before putting in heavy seat belts? -- 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] PGXLOG variable worthwhile?
scott.marlowe wrote: [...] But, I have a few more questions about the signature file solution. Is the signature file going to be updated by date or something everytime the database is started up and shut down? If not, then it's quite possible that someone could copy the pg_xlog dir somewhere, run it for a while, then they change it back to the base pg_xlog will the database know that those xlogs are stale and not start up, or will it start up and corrupt the database with the old xlogs? As long as there's a time stamp in both places it should work fine. Good question. Actually, I think it'd be a perfect place and use for a copy of the controlfile. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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
OT: Seatbelts (was: Re: [HACKERS] PGXLOG variable worthwhile?)
Bruce Momjian wrote: scott.marlowe wrote: On Wed, 25 Sep 2002, Jan Wieck wrote: So, should we take out seatbelts from cars, safeties from guns, and have everyone run about with sharp sticks too? :-) I know that the second we make something more idiot proof, someone will make a better idiot, but that doesn't mean we shouldn't make things more idiot proof, we should just try to anticipate the majority of idiots (and let's face it, we can all be idiots at the right moments sometimes.) Sure, been there, done that ... Can we wait for someone to be injured in a car accident before putting in heavy seat belts? About the car seatbelts I have a theory. If we would not have seatbelts, and instead of Airbags sharp sticks instantly killing the driver in the case of an accident, most of these wannabe Racing-Champs on our streets would either drive more reasonable or get removed by natural selection. Maybe the overall number of accidents would drop below the actual number of deaths in traffic (remember, we only kill the drivers on purpose, not anyone else in the car) ... and for sure the far lower number of *only* crippled or disabled victims will take a big burden off of the healthcare and wellfare system ... Okay, okay, enough proof of the first statement ... back to business. Jan B-) -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PGXLOG variable worthwhile?
Bruce Momjian [EMAIL PROTECTED] writes: Can we wait for someone to be injured in a car accident before putting in heavy seat belts? Not the analogy you wanted to make ... if you knew there was a serious risk, that's called negligence in most American courts. Ask Ford about the Pinto ... 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] Cause of can't wait without a PROC structure
Tom Lane wrote: I said: The ordering of these shutdown hooks is the reverse of the ordering of the startup initialization of the modules. It looks like we'll need to rejigger the startup ordering ... and it also looks like that's going to be a rather ticklish issue. (See comments in BaseInit and InitPostgres.) Any thoughts on how to do it? I eventually decided that the most reasonable solution was to leave the startup sequence alone, and fold the ProcKill and ShutdownBufferPoolAccess shutdown hooks together. This is a little ugly but it seems to beat the alternatives. ShutdownBufferPoolAccess was effectively assuming that LWLockReleaseAll was called just before it, so the two modules aren't really independent anyway. I understand. Sometimes the dependencies are too intricate to break apart, and you just reorder them. -- 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] Cause of can't wait without a PROC structure
I said: The ordering of these shutdown hooks is the reverse of the ordering of the startup initialization of the modules. It looks like we'll need to rejigger the startup ordering ... and it also looks like that's going to be a rather ticklish issue. (See comments in BaseInit and InitPostgres.) Any thoughts on how to do it? I eventually decided that the most reasonable solution was to leave the startup sequence alone, and fold the ProcKill and ShutdownBufferPoolAccess shutdown hooks together. This is a little ugly but it seems to beat the alternatives. ShutdownBufferPoolAccess was effectively assuming that LWLockReleaseAll was called just before it, so the two modules aren't really independent anyway. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CVS checkout errors
Peter Eisentraut [EMAIL PROTECTED] writes: Bruce Momjian writes: I am getting errors when doing a checkout, related to Marc's splitting up the CVS tree into modules: This split should be reverted. I'm for that ... even if we have to do *another* set of fresh CVS checkouts :-( 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] Bug in PL/pgSQL GET DIAGNOSTICS?
Bruce Momjian [EMAIL PROTECTED] writes: I already have a TODO item: * Return proper effected tuple count from complex commands [return] I am unsure if it will be fixed in 7.3 or not. It is still on the open items list, and I think we have a general plan to fix it. I got distracted and wasn't following the thread a few days ago about the topic. Did people come to a consensus about how it should work? 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] Bug in PL/pgSQL GET DIAGNOSTICS?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I already have a TODO item: * Return proper effected tuple count from complex commands [return] I am unsure if it will be fixed in 7.3 or not. It is still on the open items list, and I think we have a general plan to fix it. I got distracted and wasn't following the thread a few days ago about the topic. Did people come to a consensus about how it should work? Well, sort of. It was similar to your original proposal. See the TODO link for details. I am heading out for 2 hours and will summarize when I return. -- 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
[HACKERS] postmaster -d option (was Re: [GENERAL] Relation 0 does not exist)
Patrick Welche [EMAIL PROTECTED] writes: ... I was running postmaster -d4, yet the only query I saw was the last LOG one. I pretty sure that I would see all queries with -d3 before.. It looked to me like you were just running with the recently-added frill to log only queries that cause errors; which is on by default. (Looks at code...) Ah. It looks like -d to the postmaster no longer means anywhere near what it used to. Bruce --- compare the handling of -d in the backend (postgres.c lines 1251ff) with its handling in the postmaster (postmaster.c lines 444ff). Big difference. Are we going to make these more alike? If so, which one do we like? 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] postmaster -d option (was Re: [GENERAL] Relation 0 does not exist)
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
... Now, we just have the GUC value which does
propogate like the global one did. Does the postmaster still pass -dX
down to the child like it used to?
Evidently not; else Patrick wouldn't be complaining that it doesn't
work like it used to.
OK, got it. I knew server_min_messages would propogate to the client,
but that doesn't trigger the -d special cases in postgres.c. I re-added
the -d flag propogation to the postmaster. I also changed the postgres
-d0 behavior to just reset server_min_messages rather than setting it to
'notice.
--
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/backend/postmaster/postmaster.c
===
RCS file: /cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.288
diff -c -c -r1.288 postmaster.c
*** src/backend/postmaster/postmaster.c 4 Sep 2002 20:31:24 - 1.288
--- src/backend/postmaster/postmaster.c 26 Sep 2002 05:15:33 -
***
*** 230,235
--- 230,237
static unsigned int random_seed = 0;
+ static intdebug_flag = 0;
+
extern char *optarg;
extern intoptind,
opterr;
***
*** 452,457
--- 454,460
SetConfigOption(server_min_messages,
debugstr,
PGC_POSTMASTER, PGC_S_ARGV);
pfree(debugstr);
+ debug_flag = atoi(optarg);
break;
}
case 'F':
***
*** 2028,2033
--- 2031,2037
char *remote_host;
char *av[ARGV_SIZE * 2];
int ac = 0;
+ chardebugbuf[ARGV_SIZE];
charprotobuf[ARGV_SIZE];
chardbbuf[ARGV_SIZE];
charoptbuf[ARGV_SIZE];
***
*** 2207,2212
--- 2211,2225
*/
av[ac++] = postgres;
+
+ /*
+* Pass the requested debugging level along to the backend.
+*/
+ if (debug_flag 0)
+ {
+ sprintf(debugbuf, -d%d, debug_flag);
+ av[ac++] = debugbuf;
+ }
/*
* Pass any backend switches specified with -o in the postmaster's own
Index: src/backend/tcop/postgres.c
===
RCS file: /cvsroot/pgsql-server/src/backend/tcop/postgres.c,v
retrieving revision 1.294
diff -c -c -r1.294 postgres.c
*** src/backend/tcop/postgres.c 25 Sep 2002 20:31:40 - 1.294
--- src/backend/tcop/postgres.c 26 Sep 2002 05:15:41 -
***
*** 1281,1288
* -d 0 allows user to prevent
postmaster debug
* from propagating to backend.
*/
! SetConfigOption(server_min_messages,
notice,
! ctx,
gucsource);
}
break;
--- 1281,1287
* -d 0 allows user to prevent
postmaster debug
* from propagating to backend.
*/
! ResetPGVariable(server_min_messages);
}
break;
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Memory Errors...
Patch applied. Thanks.
---
Nigel J. Andrews wrote:
Ok, below is the original email I sent, which I can not remember seeing come
across the patches list. Please do read the assumptions since they might throw
up problems with what I have done.
I have attached the pltcl patch again, just in case. For the sake of clarity
let's say this patch superscedes the previous one.
I have also attached a patch addressing the similar memory leak problem in
plpython. This includes a slight adjustment of the tests in the source
directory. The patch also includes a cosmetic change to remove a compiler
warning although I think the change makes the code look worse though.
Once again, please read my text below and also take a quick look at the comment
I've added in the plpython patch since it may well show that that
particular change is complete rubbish.
BTW, by my reckoning the memory leak would occur with prepared plans and
without. If that is not the case then I've been barking up the wrong tree.
Of further note, I have not tested for the memory leak in plpython but the
build passes the normal and big checks. However, I have tried testing using the
test.sh script in src/pl/plpython. This seems to be generating errors where
before there were warnings. Can anyone comment on the correctness of this?
Reversing my changes doesn't really help matters so I presume it is something
else that is causing the different behaviour.
--
Nigel J. Andrews
On Fri, 20 Sep 2002, Nigel J. Andrews wrote:
On Thu, 19 Sep 2002, Tom Lane wrote:
Ian Harding [EMAIL PROTECTED] writes:
It is pltcl [not plpgsql]
Ah. I don't think we've done much of any work on plugging leaks in
pltcl :-(.
It hurts when I do this:
drop function memleak();
create function memleak() returns int as '
for {set counter 1} {$counter 10} {incr counter} {
set sql select ''foo''
spi_exec $sql
}
' language 'pltcl';
select memleak();
Yeah, I see very quick memory exhaustion also :-(. Looks like the
spi_exec call is the culprit, but I'm not sure exactly why ...
anyone have time to look at this?
Attached is a patch that frees the SPI_tuptable in all post SPI_exec
non-elog paths in both pltcl_SPI_exec() and pltcl_SPI_execp().
The fault as triggered by the above code has been fixed by this patch but
please read my assumptions below to ensure they are correct.
I have assumed that Tom's comment about this only being required in non-elog
paths is correct, which seems a reasonable assumption to me.
I have also assumed, rather than verified, that freeing the tuptable does
indeed free the tuples as well. Tests with the above function show that the
process does not increase it's memory footprint during it's operation, although
if my assumption here is wrong this could be a feature of selecting
insignificantly sized tuples.
I have not worried about other uses of SPI_exec for selects in pltcl.c on the
basis that those are not under the control of the function writer and the
normal function management will release the storage.
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
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] pltcl.so patch
Oh, so this is the later version. Fine. Let me know when it is ready. --- Nigel J. Andrews wrote: Okay, I've looked again at spi_exec and I believe I can fix the bug I introduced and the memory leak. However, I have only looked quickly and not made these most recent changes to the execp version nor to the plpython code. Therefore I am not attaching a patch at the moment, just mentioning that I've straightened this out in my brain a bit more. On Wed, 25 Sep 2002, Nigel J. Andrews wrote: On 25 Sep 2002, Neil Conway wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Yes, I do get the similar results. A quick investigation shows that the SPI_freetuptable at the end of pltcl_SPI_exec is trying to free a tuptable of value 0x82ebe64 (which looks sensible to me) but which has a memory context of 0x7f7f7f7f (the unallocated marker). Attached is a patch against CVS HEAD which fixes this, I believe. The problem appears to be the newly added free of the tuptable at the end of pltcl_SPI_exec(). I've added a comment to that effect: /* * Do *NOT* free the tuptable here. That's because if the loop * body executed any SQL statements, it will have already free'd * the tuptable itself, so freeing it twice is not wise. We could * get around this by making a copy of SPI_tuptable-vals and * feeding that to pltcl_set_tuple_values above, but that would * still leak memory (the palloc'ed copy would only be free'd on * context reset). */ That's certainly where the fault was happening. However, that's where the original memory leak problem was coming from (without the SPI_freetuptable call). It could be I got that fix wrong and the extra calls you've added are the right fix for that. I'll take a look to see what I can learn later. At least, I *think* that's the problem -- I've only been looking at the code for about 20 minutes, so I may be wrong. In any case, this makes both memleak() and memleak(1) work on my machine. Let me know if it works for you, and/or if someone knows of a better solution. I'll have to check later. I also added some SPI_freetuptable() calls in some places where Nigel didn't, and added some paranoia when dealing with statically sized buffers (snprintf() rather than sprintf(), and so on). I also didn't include Nigel's changes to some apparently unrelated PL/Python stuff -- this patch includes only the PL/Tcl changes. I dare say the plpython needs to be checked by someone who knows how to since I can well imagine the same nested call fault will exist there. -- Nigel J. Andrews ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- 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
