Re: [HACKERS] RFC: Security documentation
While I can understand your concern over security I simply do not know how you can protect against: On Sat, 7 Feb 2004, Alex J. Avriette wrote: > ... or somebody may be passing in the > de rigeur '; select * from sensitive_table; ...' attempts (this is very > common, as you know, in CGI applications). Actually I can and it involves changing the backend to not permit multiple statements in one request. I can't imagine how that could sensibly be implemented, if at all, though. At some stage your interface code has to accept responsibility for preventing dangerous input from reaching libpq. Sure this means that if someone can bypass your that interface code then they can then inject the dangerous input but let's face it, if they're at that stage there's not a lot you can do to stop them submiting 'select * from sensitive_table' to the backend without all the leading/trailing crud to try and force that statement to execute in the middle of what should be a single statement. That immediately means that anything you've done to prevent multiple statements in one request is also bypassed. > The program in question is a set of stored procedures which are called > from Perl libraries (via DBD::Pg) I can't think of any way to ensure > that malicious input is sanitized, from within plpgsql. From within > perl, I can use DBI::quote, or I can come up with my own function using > y///. The simplist way is to use place holders in a prepared statement and then execute the statement supplying the data for those placeholders. DBI escapes the data automatically. > But when I began asking people what the "final word" was on the > subject, if there was somebody who was willing to suggest a path to > data security and stick by it, nobody could point you anywhere. > Essentially, it boils down to this: I can't put in the documentation > for my application "well, some guy on IRC said that this was safe > enough." I'd be fired if the application was compromised and the only > checking I had done was by asking people on IRC. > > As such, I would like to see some documentation about securing the > database at a data and application level. It would be nice to have some > general guidelines, as well as being able to cite documentation when > setting up a security policy for a database application. General guidlines for an application: Setup two db users, one is the owner of all the database objects, the other is granted select priviledges only on what it requires. If there is a exception that requires writing priviledges for the read-only side of the application, for example tracking pages a website visitor views, then create that interface function with owner execute flag. Oh, and did I mention, use functions, aka. stored procs, to do the work. Although that's a more contentious I think. > > That having been said, I would have submitted a patch with said > documentation if I knew where to start. I have submitted this RFC -- a > request for comments, nothing more serious than that -- because I'd > like to know what we can do to get some documentation included in the > next release. I don't feel that having zero documentation on this > subject is acceptable. Are you saying here you _do_ have some documentation to contribute? -- Nigel J. Andrews ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] feature request... case sensitivity without double
On Thu, 15 Jan 2004, Pete wrote: > Hi, > > I'm not sure if this is the correct place to make a feature request. If > not hopefully I can be kindly pointed in that direction. > > I have several project that use MySQL and I would like to port them to > PostgreSQL unfortunately they use a naming convention which uses upper > case and lower case letters > Example: > SELECT AccountID FROM Account > > I am aware that if you enclose those table and column names with " then > postgresql will take the case into consideration. Only problem is most > people who have current MySQL project have not written their statements > with " (MySQL parser uses no quotes of the ` back tick) and it would > take considerable man power to convert each SQL statement. I've not tried this but if it were me I would try updating the names of the objects and columns in pg_class and pg_attribute so that they weren't mixed case. I wonder though, how did these get created in the db in mixed case in the first place? Your creation scripts must have created them using the double quotes around the names. That strikes me as an inconsitency which shouldn't have existed. However, as I say, I'd look at renaming things in the system tables to try and repair the situation. Don't forget to back everything up first. Unfortunately, the folding the lowercase isn't the best since names should be folded to upper case, however, I'm with the likes of Tom who prefer to see things in lowercase most of the time. I guess that's what FORTRAN does to one's brain after a while of trying to read it. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Use of 'cp -r' in CREATE DATABASE
On Thu, 11 Dec 2003, Alvaro Herrera wrote: > On Thu, Dec 11, 2003 at 06:36:05PM -0500, Bruce Momjian wrote: > > Our dbcommands.c has for create database: > > > > snprintf(buf, sizeof(buf), "cp -r '%s' '%s'", src_loc, target_dir); > > > [...] > > > > I think we should switch to -R in our code. > > But you will have to write special code for Win32, won't you? > Maybe it would be better to avoid using system commands > altogether and copy the whole thing using syscalls ... That was my immediate thought. Unfortunately that means reinventing the wheel; or grabbing it from BSD or somewhere and distributing it with postgresql. -- Nigel J. Andrews ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > > $ time ./a.out 2>&1 | tee a.txt > > out of order tv_sec: 1070066197 273140, prev 1070066195 721010 > > out of order tv_usec: 1070066197 273140, prev 1070066195 721010 > > out of order tv_sec: 1070067322 116061, prev 1070067320 440490 > > out of order tv_usec: 1070067322 116061, prev 1070067320 440490 > > out of order tv_sec: 1070067833 514969, prev 1070067831 755019 > > out of order tv_usec: 1070067833 514969, prev 1070067831 755019 > > ^C > > AFAICT the above is a legal trace, indicating only that the test program > sometimes lost control for more than a second at a time. The "revised" > version of the test will not complain about this. Doh! I was looking for time going backwards so that's what I saw, even though I already new the same complaint would be made if time skipped forward a second or so. > One variable I didn't think to ask about is whether you are running > NTP. In my experience an ntp daemon that has achieved lock will never > step the clock back by even 1 usec (it's supposed to use much more > subtle methods than that to manage the clock ;-)) but maybe under > unstable conditions such things could happen. The machines I have > tested here all run NTP. Yes, all NTPed. The FreeBSD system, the one that doesn't skip back *smacks forehead*, is the local time server but is generally lightly loaded. Not to say that there's not going to be the odd job it has to run which could make the system busy and time time away from this test. It's just difficult to say that every so often there's some task that makes the system unresponsive for a couple of seconds, which is what this is saying. Anyway, I like your suggestion as the timeslicing being a possible cause. Seems a little strange though, presumably all the numbers going into the timeofday calc are gathered by the one system call at a place past where the thread could have had it's time sliced away and before the next. However, I have no idea how the BSD kernel operates so can't do more than assume and guess. -- Nigel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > On Fri, 28 Nov 2003, Tom Lane wrote: > >> "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45", but they > > are all just a microsecond. > >> > >> What do you mean by "just a microsecond"? > > > I mean it's always a "out of order tv_usec..." line and the difference is > > 1us. That is a.out gives: > > > out of order tv_usec: 1070065862 374978, prev 1070065862 374979 > > out of order tv_usec: 1070065867 814300, prev 1070065867 814301 > > Fascinating. I'd call that a bug too, but evidently one with a > different mechanism than the BSD issue we are chasing. > > FWIW, I have not seen any failures in a fair amount of runtime on > a 2.4.18 (Red Hat 8.0) kernel here, running on a Dell P4. What is > the hardware platform you're using? Ah, I have made a mistake. It's only a 2.2.18 kernal. Dual SMP P-III, perhaps that's the issue there. And on the FreeBSD system I've got this: $ time ./a.out 2>&1 | tee a.txt out of order tv_sec: 1070066197 273140, prev 1070066195 721010 out of order tv_usec: 1070066197 273140, prev 1070066195 721010 out of order tv_sec: 1070067322 116061, prev 1070067320 440490 out of order tv_usec: 1070067322 116061, prev 1070067320 440490 out of order tv_sec: 1070067833 514969, prev 1070067831 755019 out of order tv_usec: 1070067833 514969, prev 1070067831 755019 ^C real38m53.026s user6m13.953s sys 32m6.589s So not very often there. -- Nigel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45", but they > > are all just a microsecond. > > What do you mean by "just a microsecond"? I mean it's always a "out of order tv_usec..." line and the difference is 1us. That is a.out gives: out of order tv_usec: 1070065862 374978, prev 1070065862 374979 out of order tv_usec: 1070065867 814300, prev 1070065867 814301 out of order tv_usec: 1070065868 794176, prev 1070065868 794177 out of order tv_usec: 1070065871 553831, prev 1070065871 553832 etc. > > Attached is a tightened-up test program that will only complain if the > value of gettimeofday goes backward (at all) or forward by more than > 10 seconds (adjustable as MAX_SKIP). This should be suitable to run on > moderately loaded machines where the test program might occasionally not > get dispatched for a few seconds. I don't think that would show any for me. I've only got two skips showing from a longer run on my FreeBSD 3.3 and those differ in the seconds counter by only 2. Time between the occurances is about 1100s. -- Nigel ---(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] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > Darcy Buskermolen <[EMAIL PROTECTED]> writes: > > On November 28, 2003 12:33 pm, Tom Lane wrote: > >> Whoa. Try the following test program. Then send it in to your friendly > >> local BSD hackers > > > I've been running this code on a pair of FreeBSD (i386) boxen, for some time > > now, one of which is a 4.8-STABLE, the other is a 5.2-BETA. > > Could it be a hardware problem on Marc's box? Or specific to some other > aspect of that installation (Marc, is pgsql74.hub.org multi-CPU, for > example?) > > The failure is definitely quite repeatable on pgsql74.hub.org. I don't > see it on svr1.postgresql.org, though, which seems to be running almost > the same kernel. On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45", but they are all just a microsecond. On an Intel FreeBSD 3.3 I had one just after starting the program that was 2 secs behind. Then I stopped to restart with a leading under time and I've not seen any since. (4'50" it was running) .529 .2 user/sys time = 0.53 on the Linux system user/sys time = 0.2 on the FreeBSD one Make of that what you will. -- Nigel Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Commercial binary support?
On Sun, 23 Nov 2003, Oleg Bartunov wrote: > does tsearch2 in 7.4 still has the problem ? I apologies if we miss your > patches but certainly we're interested in clear explanation of the problem. The problem was memory allocations made through malloc and family were not being checked for failure before attempts made to use the memory, i.e. null pointer dereference. Tom or Bruce applied the patch in time for 7.4 release. The only issue with this was noone knew how the version of tsearch2 for PostgeSQL 7.3 was being maintained. I think I posted the patch for that to at least one of the lists but as I am using tsearch2 on 7.3 I also threw this into my own CVS. In short, I don't think there's anything to worry about in relation to my patches and 7.4. Just to remind you though, the original fault reporter reported he was still getting the fault after applying what I assume was my patches. Which surprised me as I expected the fault location to be moved somewhere else. I think the real problem he was having was that of memory exhaustion but we never got more than basic information for this last report. Nigel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Commercial binary support?
Oops, sorry folks. That was only meant to go to Joshua. On Sat, 22 Nov 2003, Nigel J. Andrews wrote: > > > However, I would love to see those patches. > > Sure. Should be in the archive. The version for 7.4 was submitted and applied > ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Commercial binary support?
> However, I would love to see those patches. Sure. Should be in the archive. The version for 7.4 was submitted and applied pre-release but if you really do want the 7.3 runnable stuff I can send it. It was only the unchecked returns from malloc and family patch in the snowball directory. I think the original fault reporter still had problems afterwards though, shame he didn't seem interested in persuing it or providing decent help to find the cause. Nigel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Commercial binary support?
On 19 Nov 2003, Robert Treat wrote: > I don't think *we* thought it was a hot button issue.. at least I > certainly didn't when I initially responded. There is no need for you to > apologize, in fact, I'll apologize for the list, we sometimes get a > little heated on -hackers. Hopefully you've not been to startled by this > outburst :-) Some people have obviously lead a sheltered 'net existence :) -- Nigel 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] Commercial binary support?
On Wed, 19 Nov 2003, Joshua D. Drake wrote: > Hello, > > I think what the person is looking for is: > > COMPANY PostgreSQL for Red Hat Enterprise 3.0. > > They probably have some commercial mandate that says that they have > to have a commercial company backing the product itself. This doesn't > work for most PostgreSQL companies because they back the "Open Source" > version of PostgreSQL. > > Where someone like Command Prompt, although we happily support the > Open Source version, we also sell Command Prompt PostgreSQL. That was sort of my point. I currently have a 7.3 installation for which I have my own patches applied, for tsearch2, and for which I run my own CVS of the cpntrob module. It seems this module isn't maintained in the community, what with it being a 7.4 thing really. My company is the sys. admin., DBA and DB developer for the project, except for the production server sys. admin.. These mods weren't applied because the client was asking for them but because I knew the faults existed, even though the project wasn't kicking them. Does that mean I have supplied Logictree Systems PostgreSQL? PostgreSQL with Logictree Systems TSearch2? And if I'd made no modifications to the code? I suppose I could have insisted that a separate contract be taken for the supply and support on top of the app. development contract. In fact, having written that I'm starting to think that should be the case. > It is purely a business thing, liability and the like. > > Sincerely, > > Joshua Drake > > > Nigel J. Andrews wrote: > > >On Wed, 19 Nov 2003, Bruce Momjian wrote: > > > > > > > >>Marc G. Fournier wrote: > >> > >> > >>>On Wed, 19 Nov 2003, Michael Meskes wrote: > >>> > >>> > >>> > >>>>On Tue, Nov 18, 2003 at 04:19:35PM -0600, Austin Gonyou wrote: > >>>> > >>>> > >>>>>I've been looking all over but I can't seem to see a company that is > >>>>>providing *up-to-date* postgresql support and provides their own > >>>>>supported binaries. Am I barking up the wrong tree entirely here? > >>>>> > >>>>> > >>>>Why do you insist on "their own binaries"? I think there are several > >>>>companies out there providing support for a given version of PostgreSQL > >>>>and doubt they all ask for their own binaries. At least we do not. > >>>> > >>>> > >>>We don't either, nor do we worry about specific platforms ... > >>> > >>> > >>And I know CommandPrompt doesn't care either. > >> > >> > > > > > >I don't even know what it means. If I were to build the 7.4 source, install it > >somewhere, tarball it up would that then count as providing our own supported > >binaries (assuming the support service is also offered of course)? Surely it's > >fairly common for someone to sell support and be happy to include the service > >of supplying the binaries so if requested, what's so special about it? > > -- Nigel Andrews ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Commercial binary support?
On Wed, 19 Nov 2003, Bruce Momjian wrote: > Marc G. Fournier wrote: > > On Wed, 19 Nov 2003, Michael Meskes wrote: > > > > > On Tue, Nov 18, 2003 at 04:19:35PM -0600, Austin Gonyou wrote: > > > > I've been looking all over but I can't seem to see a company that is > > > > providing *up-to-date* postgresql support and provides their own > > > > supported binaries. Am I barking up the wrong tree entirely here? > > > > > > Why do you insist on "their own binaries"? I think there are several > > > companies out there providing support for a given version of PostgreSQL > > > and doubt they all ask for their own binaries. At least we do not. > > > > We don't either, nor do we worry about specific platforms ... > > And I know CommandPrompt doesn't care either. I don't even know what it means. If I were to build the 7.4 source, install it somewhere, tarball it up would that then count as providing our own supported binaries (assuming the support service is also offered of course)? Surely it's fairly common for someone to sell support and be happy to include the service of supplying the binaries so if requested, what's so special about it? Nigel Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] About the partial tarballs
On Tue, 11 Nov 2003, Marc G. Fournier wrote: > ftp://ftp.postgresql.org/pub/README.dist-split > > to reduce the confusion, that would be great. I've just symlink'd it into > the source directories as the .message, so that its displays when you > enter the directory ... Does anyone actually read those messages? Similar sort of question to the 'Does anyone read any of those popup boxes produced by everything in Windows before hitting "OK"?' one. -- 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] Learning PostgreSQL
On Sun, 5 Oct 2003, Gevik Babakhani wrote: > Dear PostgreSQL masters, > > I know this might look like a childish question and you > probably might have a good laugh over this but I > would like to learn how PostgreSQL works inside-out. > Could anyone please give me some pointers of where to start > in/from the source code? > > I am grateful for any help. As Bruce says, the developers website (http://developer.postgresql.org/ - I think). On the other hand I'd suggest a good read of the user documentation first. Get an idea of what it can do from the user perspective then start poking around inside. When it comes to poking around inside a decent start might be to take a look at one of the PLs and contrib items. -- Nigel ---(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] Thoughts on maintaining 7.3
On Fri, 3 Oct 2003, Andrew Sullivan wrote: > On Thu, Oct 02, 2003 at 02:15:33PM -0500, Bruno Wolff III wrote: > > It might be better to split into two different trees. One just gets bug fixes, > > the other gets bug fixes plus enhancements that won't require an initdb. > > Yes, please. Please, please do not force all users to accept new > features in "stable" trees. I wanted to say something similar earlier in this thread. To me the stable branches are not for feature introduction. If features are going to be introduced it is better to not have them applied in a manner which means a pure bug fix only version can't be obtained. Obviously this means having two branches if features are going to be introduced. I agree sometimes one looks at new developments and thinks how good it would be to have that feature, imagine what it'll be like when tablespaces are introduced and you're using the previous stable version, but those features need to be kept separate from the version that fixes that particularly nasty index corruption someone only provided a fix for 12 months after the version you have based your system around was released. One could argue that what is really needed is a collection of patches providing a pick and choose facility for features, with dependecies where unavoidable of course. The patches being applicable to the latest bug patched version of the stable branch. As an example take tsearch2. If that were core code, not optional, contrib material, and one was running a 7.3 series server but wanted the nifty features of tsearch2 instead of tsearch, would you expect all people upgrading within the stable 7.3 branch for bug fixes to be forced to use tsearch2 and not tsearch? -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
On Mon, 29 Sep 2003, Christopher Kings-Lynne wrote: > > So a db designer made a bloody mistake. > > The problem is there's no easy way to find out what's missing. > > I'd really like EXPLAIN to display all subsequent triggered queries > > also, to see the full scans caused by missing indexes. > > I'd sure second that! That's only partially determinable though. The trigger code could branch and run two different queries depending on the values supplied in the 'input' tuple of the trigger. Nigel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] getaddrinfo() for threading instead of gethostbyname()
On Sat, 27 Sep 2003, Bruce Momjian wrote: > > OK, the thread test program is read for platform testing, > src/tools/thread_test. You will find the README, Makefile tests, and > program output to be very clear and almost error-proof. > > Please run it on platforms we support and report back. Thanks. Debian linux kernel 2.2.x (- which was 2.0 or 2.2 of debian I think, can't remember) glibc 2.1.3 gcc 2.95.2 it looks like the thread stuff is enabled in ports/linux in cvs tip h... [EMAIL PROTECTED]:~/database/postgres/pgsql/src/tools/thread$ make gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I../../../src/include -D_GNU_SOURCE -c -o thread_test.o thread_test.c gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -L../../../src/port -Wl,-rpath,/usr/local/stow/postgresql-7.3.4/lib -lpthread thread_test.o -lpam -lz -lreadline -lcrypt -lresolv -lnsl -ldl -lm -lpgport -o thread_test [EMAIL PROTECTED]:~/database/postgres/pgsql/src/tools/thread$ ./thread_test Make sure you have added any needed 'THREAD_CPPFLAGS' and 'THREAD_LIBS' defines to your template/$port file before compiling this program. Your gethostbyname() is _not_ thread-safe Your getpwuid() is _not_ thread-safe Not all non-*_r functions are thread-safe. Add this to your template/$port file: NEED_REENTRANT_FUNCS=yes [EMAIL PROTECTED]:~/database/postgres/pgsql/src/tools/thread$ Nigel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] feature request: show pgsql version when running initdb
On Fri, 26 Sep 2003, Christopher Kings-Lynne wrote: > > If you install many different versions in parallel, don't you give your > > installation paths some meaning that contain the version number? In any > > case, you can run initdb --version first if you're not sure about what is > > where. > > Yes I do, but sometimes as different users you don't know what the path > is. I guess I can just go --version. Or just: which initdb which is a bit easier to interpret than: echo $path which is what you really want to know, i.e. what are the default paths applied in the search for an executable invoked without an explicit path. I think the problem here is the assumption that you don't need to explicitly state the path to the executable when invoking a command from multiple installations. If you've got specific requirements on which version to run never just assume which one will be picked up, always take steps to verify which one it is, explicitly state which one to use or accept that you may well end up running the wrong and have to start again (if you're lucky enough to be trying something that isn't going to permanently move you into a state where you can't start again). To take the normal sort of example in reverse: I have a script for reading manuals, I call it rm, it can take some switches lets say r and f, as well as the name of something to read about. I happen to be sitting in / and I look and start wondering why there's a /sbin. So I think I'll see if there's anything in the document store about it. So I type: rm -rf sbin (to do a recursive search of formated documents perhaps). I don't think about it, verify it or anything. Why should I? It always just works. Unfortunately, although I do know I'm doing this as root it doesn't occur to me there might be another command called rm installed somewhere on the system that I might pick up instead. Moral of the story, if it's in your path first then it's the default and you should therefore be happy with the results or be prepared to live with them, otherwise make sure what you're running. -- Nigel J. Andrews ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pgsql procedures??
On Thu, 25 Sep 2003, Bruno Wolff III wrote: > On Wed, Sep 24, 2003 at 20:21:20 -0300, > Maurício Paiva <[EMAIL PROTECTED]> wrote: > > What we are trying to figure out is if postgresql supports > > returning multiple result sets from a stored procedure (psql > > function?) > > No, Postgres can't do that. But it can if you switch to one of the other languages like plpgsql, which isn't terribly complicated but does require the language to be installed in the database. -- Nigel J. Andrews ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] pgindented tsearch2 for 7.3.4
On Wed, 24 Sep 2003, Bruce Momjian wrote: > Nigel J. Andrews wrote: > > > > > > I never knew running indent was so damn complicated. All three of my > > development systems can not manage it without throughing a fault > ... > > There are about 6 files that can't be run through pgindent, and tsearch2 > has one of them: > > $ pgindent *.c > Hope you installed /src/tools/pgindent/indent.bsd.patch. > > gistidx.c > [EMAIL PROTECTED]: Unbalanced parens > [EMAIL PROTECTED]: Extra ) > [EMAIL PROTECTED]: Unbalanced parens > ... It was a far more basic problem than that! I'd have been pleased if I'd got as far as that. You developers of today don't know you're born. Back in my day we used to have to get up before noon just to turn the computer on... Actually, I had a problem in that I only had GNU indent and BSD indent that was core dumping but it was unpatched, plus I couldn't find the BSD indent source on the web. Eventually found it, in a linux distribution of all places, and then I got the above errors, by which time I was quite prepared to not count those as such. There are a few macro's defined that make me think of fortran programmers moving to C. Anyway Bruce, you'll see the patch eventually generated lower down you inbox. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] old pgindent change
On Mon, 22 Sep 2003, Nigel J. Andrews wrote: > > There was a simple change commited in revision 1.47 of pgindent, listed as > being "More updates for GNU indent". > > The questions are: why? and surely I can't be the only one whose hit this > problem since November 2001? > > ... I also had to apply the following change in order to avoid an awk compilation error: *** pgindentMon Sep 22 23:54:40 2003 --- pgindent.newMon Sep 22 23:54:27 2003 *** *** 65,71 line1 !~ "^typedef" && line1 !~ "^extern[ ][ ]*\"C\"" && line1 !~ "=" && ! line1 ~ ")") print "int pgindent_func_no_var_fix;"; line1 = line2; } --- 65,71 line1 !~ "^typedef" && line1 !~ "^extern[ ][ ]*\"C\"" && line1 !~ "=" && ! line1 ~ "\)") print "int pgindent_func_no_var_fix;"; line1 = line2; } The changed line was also added in November 2001, in revision 1.48 this time. awk -W version mawk 1.3.3 Nov 1996, Copyright (C) Michael D. Brennan and again with /bin/sh being a link to bash 2.03.0 -- 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])
[HACKERS] old pgindent change
There was a simple change commited in revision 1.47 of pgindent, listed as
being "More updates for GNU indent".
The questions are: why? and surely I can't be the only one whose hit this
problem since November 2001?
On a debian (woody or potato, which ever one had a 2.2 series kernal) using
GNU bash 2.03.0 via /bin/sh (in the pgindent script) I get:
which indent = /usr/local/bin/indent
indent -version = Berkeley indent 5.17
status = 0
You do not appear to have 'indent' installed on your system.
By adding appropiate echo commands before and after the indent -version line in
the script, I've attached my slightly modified version for completeness. I can
never remember the way around the test of $? succeds or fails but the above
experiment plus a look at the near by tests of $? in pgindent seem to show that
there is a problem here.
A simple test of 2.05a.0 on a newer system using:
ls
if [ $? -eq 0 ]; then echo success aaa; fi
lls
if [ $? -eq 0 ]; then echo success bbb; fi
shows that the lls (non-existant executable) status fails the test as expected
while the plain ls status passes.
I'm obviously missing something very significant here or there's a very strange
oddity that's been there, and specifically placed there, for nearly 2 years.
--
Nigel J. Andrews
#!/bin/sh
echo
echo -n Path=
echo $PATH
echo
# Known bugs:
#
# Blank line is added after, seen as a function definition, no space
# after *:
# y = (int) x *y;
trap "rm -f /tmp/$$ /tmp/$$a" 0 1 2 3 15
entab /dev/null
if [ "$?" -ne 0 ]
thenecho "Go to the src/tools/entab directory and do a 'make' and 'make install'."
>&2
echo "This will put the 'entab' command in your path." >&2
echo "Then run $0 again."
exit 1
fi
echo
a=`which indent`
echo "which indent = $a"
a=`indent -version 2>&1`
echo "indent -version = $a"
echo
indent -version /dev/null 2>&1
echo "status = $?"
if [ "$?" -ne 1 ]
thenecho "You do not appear to have 'indent' installed on your system." >&2
exit 1
fi
indent -gnu /dev/null 2>&1
if [ "$?" -eq 0 ]
thenecho "You appear to have GNU indent rather than BSD indent." >&2
echo "See the pgindent/README file for a description of its problems." >&2
EXTRA_OPTS="-cdb -bli0 -npcs -cli4 -sc"
elseecho "Hope you installed /src/tools/pgindent/indent.bsd.patch." >&2
EXTRA_OPTS="-cli1"
fi
for FILE
do
cat "$FILE" |
# convert // comments to /* */
sed 's;^\([ ]*\)//\(.*\)$;\1/* \2 */;g' |
# Avoid bug that converts 'x =- 1' to 'x = -1'
sed 's;=- ;-= ;g' |
# mark some comments for special treatment later
sed 's;/\* *---;/*---X_X;g' |
# workaround for indent bug with 'else' handling
sed 's;\([} ]\)else[]*\(/\*.*\)$;\1else\
\2;g' |
detab -t4 -qc |
# work around bug where function that defines no local variables misindents
# switch() case lines and line after #else. Do not do for struct/enum.
awk ' BEGIN {line1 = ""; line2 = ""}
{
line2 = $0;
if (NR >= 2)
print line1;
if (NR >= 2 &&
line2 ~ "^{[]*$" &&
line1 !~ "^struct" &&
line1 !~ "^enum" &&
line1 !~ "^typedef" &&
line1 !~ "^extern[ ][ ]*\"C\"" &&
line1 !~ "=" &&
line1 ~ ")")
print "int pgindent_func_no_var_fix;";
line1 = line2;
}
END {
if (NR >= 1)
print line1;
}' |
# prevent indenting of code in 'extern "C"' blocks
awk ' BEGIN {line1 = ""; line2 = ""; skips = 0}
{
line2 = $0;
if (skips > 0)
skips--;
if (line1 ~ "^#ifdef[ ]*__cplusplus" &&
line2 ~ "^extern[ ]*\"C\"[]*$")
{
print line1;
print line2;
if (getline &&
Re: [HACKERS] change of table name - any help
[I'm not convinced this is a -hackers issue so have cross posted to -general in the expectation followups will go there] I also didn't feel there was much I could cut from the earlier posts without losing relevent info, so I didn't. Sorry. On Fri, 19 Sep 2003, chakkara rangarajan wrote: > Christoph, > Thx for your response. We didn't change the search_parth variable. Moreover, > I tried all combination of drop statements like > > Drop table table_name > Drop table owner.table_name > Drop table "table_name" > Drop table "owner.table_name" > >... > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, September 19, 2003 2:37 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > We have a development server running > > > > OS - Linux development-server 2.4.20-openmosix-r4 #1 SMP Mon May 19 > 02:32:52 > > PDT 2003 i686 Intel(R) Xeon(TM) CPU 2.40GHz GenuineIntel GNU/Linux > > > > Database - PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC gcc > (GCC) > > 3.2.1 > > > > We have a table ctcert_name under postgres DB(postgres schema and > postgres > > user is the owner). Suddenly, this object started missing from the DB > (I am > > the only > > > > Person who connects to that server and did not drop/renamed it). When > I > > tried to recreate the same table, the system threw me back an error, > saying > > that "postgres.ctcert_name" already exists. I am neither able to drop > or > > rename the table. > > > > checked the DB logs and there is no drop/rename table statement in > that. > > > > I have the transaction logs, but not able to read, as they are not in > the > > human readable format. > > > > How can I decipher from the txn logs, if it captures the change > management. > > > > Can somebody please tell me, what cud have gone wrong and is the error > is > > reproduceable? What is the solution for this kind of problem. > > > Did you change the SEARCH_PATH variable? > Did I get this right: > You cannot > DROP TABLE postgres.ctcert_name ; > Mind, I left off the enclosing quotes. > And you cannot > CREATE TABLE postgres.ctcert_name( ... ) ; > > My suspicion is you are using these quotes and you shouldn't. > This last would try a create a table called owner.table_name in the current schema. In fact given that I'm starting from the point of already having a table named "test" the following shows this as well as a few other points. test=# \dt List of relations Schema | Name | Type | Owner ++---+--- ... public | test | table | test (13 rows) test=# create table test (a int); ERROR: Relation 'test' already exists test=# create table public.test (a int); ERROR: Relation 'test' already exists test=# create table "public.test" (a int); CREATE TABLE test=# create table "public.test" (a int); ERROR: Relation 'public.test' already exists test=# \dt List of relations Schema | Name | Type | Owner ++---+--- ... public | public.test| table | test ... public | test | table | test (14 rows) test=# Also the "all lower case variant" shouldn't make any difference to the unquoted names since unquoted names get folded to lower case. What would make a difference is if there was quoted upper case characters used one time but not another. However, I suspect I'm telling you thing you already know. Presumably you've tried using \dt in psql and the table isn't listed but others in the same schema are? What about the query: select c.oid, c.relname, c.relnamespace from pg_class c where relname ilike '%ctcert%'; followed by: select oid,* from pg_namespace where oid = ? where the ? in the second is the relnamespace value in results from the first query. It's difficult to see why there would be a pg_class entry with the same name as you are trying and in the same schema but the relname, relnamespace combination must be unique. Trying to create a new table that would violate that unique constraint I imagine would give you that error message. Bearing in mind the lack of drop table statements logged you should also check for updates/delete from pg_class. Although because the system thinks there is a conflict when creating the new table I'd be slightly worried that something's gone horribly wrong somewhere but then I don't know what data in the system tables would, validly, make it look like the object existed at the same time as it didn't -- Nigel 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] Why select * from function doesn't work when function
On Thu, 24 Jul 2003, Francisco Figueiredo Jr. wrote: > Nigel J. Andrews wrote: > > > On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: > > > > > >>>How's this for an alternative if you really don't want any rows returned: > >>> > >>>create function fincF ( ) returns setof integer as ' > >>> begin > >>> delete from blah; > >>> return; > >>> end; > >>>' language 'plpgsql'; > >>> > >>> > >> > >>This works, but what I really want is not to return any rows. I mean, > >>the problem is not return null, but the error I get if I select * from > >>voidfunction. > >> > >>I just wanted void functions behave like others when called as select * > >>from voidfunction So I dont have to do select voidfunction. :) > > > > > > But that last does exactly that. Doesn't even return a null. Give it a quick > > go, skip the delete statement obviously, and see. You'll get something like: > > > >? > > --- > > > > (0 rows) > > > > > Uhmmm, I think I didn't make myself clear. What I mean by void function > wasn't a function which just doesn't return anything. What I meant is a > function created like this: > > create function voidfunction returns *void* as [...] I knew what you meant but why the insistence on the void return type? All it's saying is that there isn't any interpretation that can be applied to anything that may (or may not) be returned from it so what are you trying to gain by forcing the void type when you're forced into ignoring the result anyway? > > The problem to me is the void in the returns ;) > > If you create a function with the returns void above you'll see that if > you do select * from voidfunction it gives you the error I said. But it > works with select voidfunction. Well don't create the function as returning void :) > > I just wanted it to work with select * from voidfunction too. :) > I think I did most of mine as returning integer type and the value 1 (just for something to return). Nigel Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Why select * from function doesn't work when function
On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: > > How's this for an alternative if you really don't want any rows returned: > > > > create function fincF ( ) returns setof integer as ' > > begin > >delete from blah; > >return; > > end; > > ' language 'plpgsql'; > > > > > > This works, but what I really want is not to return any rows. I mean, > the problem is not return null, but the error I get if I select * from > voidfunction. > > I just wanted void functions behave like others when called as select * > from voidfunction So I dont have to do select voidfunction. :) But that last does exactly that. Doesn't even return a null. Give it a quick go, skip the delete statement obviously, and see. You'll get something like: ? --- (0 rows) -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why select * from function doesn't work when function
On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: > > select * from funcF(); > > > > Yeap, it works, but you specified integer as the return type :) Yes, that's because I knew the void wouldn't work. :] > > I'd like to have the return type as void and be possible to call it with > select * from funcF(); I don't believe it is possible. Makes sense since void doesn't really make sense in that position in the statment. How's this for an alternative if you really don't want any rows returned: create function fincF ( ) returns setof integer as ' begin delete from blah; return; end; ' language 'plpgsql'; -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why select * from function doesn't work when function
On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: > > Hi all, > > I would like to know why does calling a function with select * from > function doesn't work when its return type is set to void. > > I'm asking this because I have a code which uses this syntax to add > support for returning resultsets from functions. This way, regardless > the function returns a resultset or a single value, I could do select * > from function and it works very well. > > The problem appears when the function has its returns type to void. > I get the following error message: > > npgsql_tests=> select * from funcF(); > ERROR: function funcf() in FROM has unsupported return type > ERROR: function funcf() in FROM has unsupported return type > > > where funcF is defined as: > > npgsql_tests=> create function funcF() returns void as 'delete from > tablea where field_serial > 5' language 'sql'; > > CREATE FUNCTION > > But it does work if I call it as: > > select funcF(); > > > > I'd like to know if would be possible to change this behaviour to return > an empty result set with a null value. This way, there would be > consistency in calling all functions regardless of its return type with > select * from function. Try returning an integer but returning a null for that integer...on the other hand I see you're using sql as the language and I don't know how that would work. Have you looked at plpgsql? Perhaps that is acceptable for you, in which case: create function funcF ( ) returns integer as ' begin delete from blah; return null; end; ' as language 'plpgsql'; select * from funcF(); I believe that would work but don't quote me :) -- Nigel J. Andrews ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] suggestions to improve postgresql suitability for
On Tue, 22 Jul 2003, Fabien COELHO wrote: > ... > > III) PL/pgSQL > - > > Ok, if postgresql does not want to do it my way, let us make it do it. > Thus I wrote some PL/pgSQL function for my purpose, something like: > > CREATE TEMPORARY TABLE tmp ( > area INTEGER, > type INTEGER, > month INTEGER, > amount INTEGER, > count INTEGER, > PRIMARY KEY(area, type, month) > ); > -- initialize tmp > FOR i IN 0..99 LOOP FOR j IN 0..3 LOOP FOR k IN 0..11 LOOP > INSERT INTO tmp VALUES(i,j,k,0,0); > END all LOOPs; > -- fill tmp > FOR tuple IN > SELECT area, type, month, amount FROM client, invoice WHERE id=client > LOOP > UPDATE tmp SET amount=amount+tuple.amount, count=count+1 > WHERE area=tuple.area AND type=tuple.type AND month=tuple.month > END LOOP; > ... > > It is very SLOOOW... 10 to 100 times slower than the > previous one. Exit PL/pgSQL. It will be, first you're doing the same join that generates the large result set you were complaining about in the plain SQL example and then you're looping over it generating a delete/insert for every tuple in that result set. > > IV) Basic client side (JDBC, DBI, libpq) > > > Then I wrote the same stuff on the client side in java with JDBC, perl > with DBI and C with libpq, by browsing the above SELECT in a simple > loop and aggregating the data directly in the language. In all 3 > cases, the process attempts to allocate the full result of the client > and invoice join in memory... a **very** bad idea indeed! But what about doing that in the server? > I checked that the postgres client-server protocol does not allow to > chunk the result of a select, as only one response is sent for one > query. > > I suggest that this behavior should be changed, as the ODBC/DBI/JDBC > interfaces are designed to allow the client to process data as the > come out of the database, even if the query is not finished yet. > > The library should do the chunking on its own automatically, either by > doing a CURSOR/FETCH's manually in the library implementation on > SELECT, or by changing the protocol so that results are sent by chunks > when required. > > This is listed in the todo list of the JDBC interface, but there is > nothing about the perl interface nor the libpq interface. > > > V) Less basic client side (DBI, libpq) > -- > > I've redone the previous stuff, but with an explicit CURSOR and a > FETCH loop. It worked better, but it is still slow and still requires > a lot of disk space. Indeed, the database seems to first generate the > join in a temporary table on disk (I need twice as much disk space > available as the original base), which is then sent back to the client. > Thus I pay a read/write/read of the whole tables although > I had hoped that reading the data only once would have been enough. > > I would suggest to make processing data on the fly be done really > on the fly, not with an intermediate storage and providing just > an on-the-fly interface without the real thing behind. I haven't seen > any item in the todo list about this issue. I'm not sure it is really > easy to implement. I thought it necessary for the result set to be generated before any data can be returned, in the general case and in your grouped by example specifically. The latter if only because if you're not using the hash aggregates then the sort is required and that of course requires all the result data to be known. > > Conclusion > -- > > I have not succeeded in getting from postgresql the performances > I was expecting for data-mining. > > I could get them if postgresql could be improved on some or all > of the following items: > > (1) the execution engine may aggregate grouped data without a sort in > some cases. As other's have said, this is in 7.4 > (2) the PL/pgSQL interpreter would be a great deal faster. It did what you told it to do. > > (3) the client programming interfaces would provide a real on-the-fly > (without intermediate storage) fetching mecanism. > > (4) Also, I noticed that temporary tables/indexes created by postgresql > when processing a request are stored in the same partition as the > database in use. What about "/tmp" or other partitions? Maybe > a set of other directories could be designated for this purpose? > > Hope this help... at least to add new items to the postgresql todo list;-) > > Have a nice day, > > -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch2 for 7.3.X
On Tue, 22 Jul 2003, Tom Lane wrote: > Oleg Bartunov <[EMAIL PROTECTED]> writes: > > OK. I'll suggest people to try new tsearch2 in README file of old > > tsearch. > > Okay, that works for me. Please patch the old tsearch README file in > both HEAD and REL7_3_STABLE branch as soon as possible --- we are going > to wrap a 7.3.4 release as soon as Bruce finds the time to make release > notes, possibly today. I don't expect you wouldn't put in the information but just to highlight that it'd be a good idea to put the location the tsearch2 tarball can picked up from in the README. -- Nigel Andrews ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Error messages --- now that we've got it, do you like
On Sun, 20 Jul 2003, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > VERBOSE doesn't seem like the right name for the \set parameter. > > VERBOSITY would be okay with me. > Sounds meaningful. I often want to say 'verbosity level' when talking such things. -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Error messages --- now that we've got it, do you like
Oops, just remembered I was going to look at the new style error codes to answer one of your emails before. Just not had the time. On Thu, 3 Jul 2003, Tom Lane wrote: > First fruits of all that work on error message rejiggering ... > > regression=# \set VERBOSE terse > > regression=# select 1!! ; > ERROR: operator does not exist: integer !! I can see why 'terse' contains the least amount of information and that generally it should not therefore contain information not in the next higher level but I would have thought 'terse' would include the error number. Even _just_ the error number. I presume this setting is completely different from the one to determine the verbosity in the server log. In the server logs I would think it better to be able to include the error code in the error line without having any other detail lines. In fact in the server log would it not be the case that the LOCATION detail came before the HINT detail in the verbosity stakes, or is it viewed as being closer to a debug setting and so requires more verbosity? > > regression=# \set VERBOSE default > > regression=# select 1!! ; > ERROR: operator does not exist: integer !! > HINT: No operator matches the given name and argument type(s). You may need to add > explicit typecasts. > > regression=# \set VERBOSE verbose > > regression=# select 1!! ; > ERROR: 42883: operator does not exist: integer !! > HINT: No operator matches the given name and argument type(s). You may need to add > explicit typecasts. > LOCATION: op_error, parse_oper.c:691 > > regression=# select 'z' && 'q'; > ERROR: 42725: operator is not unique: "unknown" && "unknown" > HINT: Unable to choose a best candidate operator. You may need to add explicit > typecasts. > LOCATION: op_error, parse_oper.c:684 > > Before we go too much further, does this look sane to people? > Any adjustments you want to make around the edges? > > (BTW, if you're wondering where the 42xxx error codes came from, > I borrowed them from DB2. The SQL99 spec seems happy to lump > all sorts of conditions under 42000 "syntax error or access > violation" ...) Looks good. Error codes are always handy to have and the extra details are just the ticket, I especially like the hint. -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Two weeks to feature freeze
On Thu, 26 Jun 2003, Thomas Swan wrote: > > > Is it possible the sourceforge compile farms could be used for some of > the automated testing? I'm not sure how that system works, but it could > be worth looking into. Isn't the sourceforge license very scary and along the lines of "whatever you put on here we own it's just we tend not to persue that at the moment as there's not much money in it for us but that doesn't stop us from claiming it at some indeterminate time in the future"? -- Nigel J. Andrews ---(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] Two weeks to feature freeze
On Mon, 23 Jun 2003, Dann Corbit wrote: > > -Original Message- > > From: scott.marlowe [mailto:[EMAIL PROTECTED] > > Sent: Monday, June 23, 2003 12:25 PM > > To: Dann Corbit > > Cc: Bruce Momjian; Tom Lane; Jason Earl; PostgreSQL-development > > Subject: Re: [HACKERS] Two weeks to feature freeze > > > > > > On Mon, 23 Jun 2003, Dann Corbit wrote: > > > > > Vendor A: "We think our tool is pretty solid and our end > > users hardly > > > ever turn up any bugs." > > > > > > Vendor B:" We think our tool is pretty solid and our 8500 tests > > > currently show only 3 defects with the released version, > > and these are > > > low impact issues. To view our current database of issues, > > log onto > > > web form ." > > > > > > Which tool would you prefer to install? > > > > The one I've tested and found to meet my needs, both now and > > by providing > > fixes when I needed it. How about the one that doesn't run tests in order to show how much better it is than the competition but to actually test operation? In other words Vendor B has an interest in having the tests pass, what gives you the confidence it just hasn't listed the ones that fail and that the tests that do pass are not just testing something vendor B wants to show it can do? > > Real world example: We run Crystal Reports Enterprise > > edition where I > > work. It's tested thouroughly (supposedly) and has all kinds of QA. > > However, getting it to work right and stay up is a nightmare. > > It's taken > > them almost a year to get around to testing against the OpenLDAP LDAP > > server we use. The box said "LDAP V3 compliant" and they > > assured us that > > it was. Well, it doesn't work with our LDAP V3 compliant > > LDAP server at > > all, and the problem is something they can't fix for months > > because it > > doesn't fit into their test cycle. > > > > > > Real world example: Postgresql aggregates in subselects. > > Someone found a bug in subselects in Postgresql with inner > > references to > > outter aggregates. The postgresql team delivered a patch in > > less than a > > week. User tested it and it works. > > > > I'm not against testing and all, but as one of the many beta > > testers for > > Postgresql, I do feel a bit insulted by your attitude that only a > > cohesive, organized testing effort can result in a reliable product. > > Let me rephrase it: > "Only a cohesive, organized testing effort can result in a product that > is proven reliable." > > Without such an effort, it is only an educated guess as to whether the > product is reliable or not. The data is the most valuable software > component in an organization. It is worth more than the hardware and it > is worth more than the software. If you are going to trust one billion > dollars worth of corporate data on a software system, you ought to > ensure that the system has been carefully tested. I don't think that is > just an opinion. It's simply common sense. So you've never worked on a project where the data is of high value, since in those circumstances the customer is always going to apply their own acceptance testing anyway. If you think that doesn't happen you try sitting through 2 solid days of Y2k testing on _one_ system and tell me customers never do their own testing. > Therefore, I am going to stop harping on it. But there is no need to, as has been mentioned before, if the testing is not upto your level of testing submit something that makes it so. Having said that I do believe you mentioned that you didn't have the time to create something but you would be happy to test it, i.e. test the test. -- Nigel J. Andrews ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Two weeks to feature freeze
On Thu, 19 Jun 2003, The Hermit Hacker wrote: > On Thu, 19 Jun 2003, Andrew Dunstan wrote: > > > > > Maybe a better strategy would be to get a release out soon but not wait > > 6 months for another release which would contain the Win32 port and the > > PITR stuff (assuming those aren't done in time for this release). > > > > Just a thought. > > And definitely in agreement here ... I'd rather see a shortened dev cycle > prompted by a big feature being added, then delaying a release because "oh > oh, I need another few weeks" that draws out when something unexpected > happens :( > >... I'm not sure why another delay is being considered. There's been a delay of a week because of the server problems hasn't there and wasn't the original delay only acceptable on the basis that that was that and there wasn't going to be another extension? -- Nigel Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Pre-allocation of shared memory ...
On Fri, 13 Jun 2003, Lamar Owen wrote: > On Friday 13 June 2003 11:55, Josh Berkus wrote: > > Regrettably, few of the GUI installers for Linux (SuSE or Red Hat, for > > example), include adequate swap space in their "suggested" disk formatting. > > Some versions of some distributions do not create a swap partition at all; > > others allocate only 130mb to this partition regardless of actual RAM. > > Incidentally, Red Hat as of about 7.0 began insisting on swap space at least > as large as twice RAM size. In my case on my 512MB RAM notebook, that meant > it wanted 1GB swap. If you upgrade your RAM you could get into trouble. In > that case, you create a swap file on one of your other partitions that the > kernel can use. I'm not sure I agree with this. To a large extent these days of cheap memory swap space is there to give you time to notice the excessive use of it and repair the system, since you'd normally be running everything in RAM. Using the old measure of twice physical memory for swap is excessive on a decent system imo. I certainly would not allocate 1GB of swap! Well, okay, I might if I've got a 16GB machine with the potential for an excessive but transitory workload, or say 4-8GB machine with a few very large memory usage processes that can be started as part of the normal work load. In short, imo these days swap is there to prevent valid processes dying for lack of system memory and not to provide normal workspace for them. Having said all that, I haven't read the start of this thread so I've probably missed the reason for the complaint about lack of swap space, like a problem on a small memory system. -- Nigel J. Andrews ---(end of broadcast)--- TIP 9: most folks find a random_page_cost between 1 or 2 is ideal
Re: [HACKERS] host and hostssl equivalence in pg_hba.conf
On Tue, 10 Jun 2003, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > How do people feel about changing matching for host and hostssl to be such that > > a plain host line in pg_hba.conf does not allow a SSL connection but requires > > the hostssl specifier? > > Then there would be no way to have a host entry that allowed both --- > which, aside from being a loss of functionality, would doubtless break > existing setups. Well, what I was thinking of would have allowed it, just using two entries, a host one and a hostssl one. > I'd hold still for a "hostnossl" keyword, I guess, but I don't entirely > see the use for it. Well Jon Jenson's posted something else on this which I should read when I've got my mind more in tune with it. > If your real gripe is that libpq insists on trying SSL connections > first, the server is the wrong end to be patching that problem at. > There should be a way to control libpq's allow_ssl_try state variable > from the outside. A quick read makes me think that's what Jon's post is on about. -- Nigel Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal to Re-Order Postgresql.Conf, part II
On Mon, 9 Jun 2003, Tom Lane wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > > Hey, I'm looking at the postgresql.conf.sample in CVS, and can't find the > > option that's supposed to let you turn off "Inserting missing FROM clause for > > table ..." > > Bruce hasn't applied that patch yet. I believe he's starting to catch > up the patch backlog today, though. > Are you sure about that? I seem to remember seeing the "will be applied within 24 hours" message a couple of weeks or so ago now. Is this a feature of the recent system problems and lost patches are having to be reapplied? As for it's name Josh, sorry, I don't have a record of my patch and the name used in the patch differs to that which I have in my source tree. -- Nigel Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] host and hostssl equivalence in pg_hba.conf
How do people feel about changing matching for host and hostssl to be such that a plain host line in pg_hba.conf does not allow a SSL connection but requires the hostssl specifier? I had been going to submit a very small patch to do this but then it occurred to me this was a good candidate for a GUC along the lines of allow_host_hostssl_equivalence (just a name picked out of the air for this post). As this is a little bit more work and I can't get to anoncvs to refresh my tree I thought I'd check if it was something to persue or forget. To recap another thread I started, I had problems with large objects, Tom suggested it might be SSL related as unix domain connections were fine, I confirmed I still had the problem in 7.3.3 but then was unable to switch off SSL for any IP connections without a rebuild as the host line in pg_hba.conf permits SSL connections. What I haven't done is confirm 7.4 has the problem (see the anoncvs comment above). I suggest this as GUC controlled feature since it seems from first impressions that it is a lot more work to fall back to without SSL if there is a matching host line but not a hostssl one. That is, connections from SSL enabled clients would be rejected if there no hostssl entry for them, even if there was a matching host entry, thus locking that client out of the server (unless there was some way to tell the client to not attempt SSL). Hmm...hope that's understandable, I seem to have rabbited on making this a lot longer than I was expecting to. -- Nigel Andrews ---(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] large objects
Note, primary list address changed to -general, I'd suggest any followups remove the -hackers, which I've left in just for 'closure'. On Fri, 6 Jun 2003, Nigel J. Andrews wrote: > On Fri, 6 Jun 2003, Tom Lane wrote: > > > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > > Now, I did a little bit of testing and when doing a \lo_export > > > in psql connected via localhost a SIGPIPE is generated in > > > write() in libc and psql quit, without printing any message to the > > > terminal. Perhaps interestingly > > > the file that gets written is always 65536 bytes long. > > > > Hm. Are you using an SSL connection? There are some known bugs in the > > SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've > > not tried it myself. > > Damn, yes I am, I noticed the notice when connecting but then didn't think > anything of it. Thanks Tom, I'll check that later when I do have time > (shouldn't have wasted the precious minutes joining the NULL != "" war). Ok, I tried to try this but I can not get SSL to _not_ be used when connecting via any tcp connection, unless the client hasn't been built with ssl support of course. The pg_hba.conf has: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD local all all md5 host all all 127.0.0.1 255.255.255.255 md5 psql -U me -h localhost db prints: SSL connection (cipher: EDH-RSA-DES-CBC3-SHA, bits: 168) psql -U me db doesn't. Am I losing my mind? Should I need hostssl on that second line of the config before ssl is allowed? I did look at that code once a few weeks ago and vaguely remember something about host and hostssl handling but can't remember the details. Is this really a bug, even if only in the documentation, or have I got completely the wrong end of the stick? -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] large objects
Repost just to add the information that this is [now] on 7.3.3, previously on 7.3.2. Sorry for the noise of the incomplete previous message, although the email lists seem to be very light this last week. Obviously the mail server is still feeling under the weather, I presume many posts have been lost in a hole somewhere much like another of my posts. On Sun, 8 Jun 2003, Nigel J. Andrews wrote: > > Note, primary list address changed to -general, I'd suggest any followups > remove the -hackers, which I've left in just for 'closure'. > > > On Fri, 6 Jun 2003, Nigel J. Andrews wrote: > > > On Fri, 6 Jun 2003, Tom Lane wrote: > > > > > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > > > Now, I did a little bit of testing and when doing a \lo_export > > > > in psql connected via localhost a SIGPIPE is generated in > > > > write() in libc and psql quit, without printing any message to the > > > > terminal. Perhaps interestingly > > > > the file that gets written is always 65536 bytes long. > > > > > > Hm. Are you using an SSL connection? There are some known bugs in the > > > SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've > > > not tried it myself. > > > > Damn, yes I am, I noticed the notice when connecting but then didn't think > > anything of it. Thanks Tom, I'll check that later when I do have time > > (shouldn't have wasted the precious minutes joining the NULL != "" war). > > Ok, I tried to try this but I can not get SSL to _not_ be used when connecting > via any tcp connection, unless the client hasn't been built with ssl support of > course. The pg_hba.conf has: > > # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD > > local all all md5 > host all all 127.0.0.1 255.255.255.255 md5 > > psql -U me -h localhost db > > prints: > > SSL connection (cipher: EDH-RSA-DES-CBC3-SHA, bits: 168) > > psql -U me db > > doesn't. > > Am I losing my mind? Should I need hostssl on that second line of the config > before ssl is allowed? I did look at that code once a few weeks ago and vaguely > remember something about host and hostssl handling but can't remember the > details. Is this really a bug, even if only in the documentation, or have I got > completely the wrong end of the stick? > -- 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] large objects
On Fri, 6 Jun 2003, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > Now, I did a little bit of testing and when doing a \lo_export > > in psql connected via localhost a SIGPIPE is generated in > > write() in libc and psql quit, without printing any message to the > > terminal. Perhaps interestingly > > the file that gets written is always 65536 bytes long. > > Hm. Are you using an SSL connection? There are some known bugs in the > SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've > not tried it myself. Damn, yes I am, I noticed the notice when connecting but then didn't think anything of it. Thanks Tom, I'll check that later when I do have time (shouldn't have wasted the precious minutes joining the NULL != "" war). -- Nigel Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] large objects
I realise large objects are liked anymore and that the wire protocol is changing in 7.4 but I've just posted this message into the PHP documentation: -- begin Using php 4.3.0 and PostgreSQL 7.3.1 I can write a simple script in which pg_lo_write seems to always return 1 and not the number of bytes written, as evidenced by extracting the data through another means. Further more, I can make this pg_lo_write fail, or at least fail to write all the data it's pretty difficult to tell without the number of bytes written being returned, and not return the false value. In addition to this, the lo resource has been adjusted so that the oid it contains is 0. Unfortunately, I do not know what exactly the failure mode is, it does seem to be in the ip network communication side of PostgreSQL, which is odd since the unix domain comms works fine for this. However, it would have been useful to have the pg_lo_write() function return as advertised, it would have saved some of the 2 man hours me and the dev. team put into diagnosing this problem. -- end Now, I did a little bit of testing and when doing a \lo_export in psql connected via localhost a SIGPIPE is generated in write() in libc and psql quit, without printing any message to the terminal. Perhaps interestingly the file that gets written is always 65536 bytes long. The server log shows: 2003-06-05 14:24:02 LOG: query: select proname, oid from pg_proc where proname = 'lo_open' or proname = 'lo_close' or proname = 'lo_creat'or proname = 'lo_unlink'or proname = 'lo_lseek'or proname = 'lo_tell' or proname = 'loread' or proname = 'lowrite' 2003-06-05 14:24:02 LOG: duration: 0.002924 sec 2003-06-05 14:24:03 LOG: pq_recvbuf: recv() failed: Success fwiw. The last 4 bytes saved and next 16 bytes that should follow are: 00fffc 74 f3 5f ff d0 d1 c6 b3 eb bb 01 26 d6 b3 51 a9 01000c 68 e5 70 54 Of course it could be way past there the failure point but I thought it worth including on the off chance. When I do the same except allowing psql to connect through the unix domain socket it works. The right number of bytes are returned and cmp shows no differences to the original. So, a) is this known? b) what is it? c) is it not going to happen in the new protocol? and d) does anyone care? -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] default locale considered harmful? (was Re: [GENERAL]
On Wed, 4 Jun 2003, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > That is one thing I liked about the initdb mention --- it clearly told > > them to watch out for something they might not have been looking for. > > Only if they read the message, though. People who are running RPM > installations probably never get to see what initdb has to say ... > so I can't put much faith in the usefulness of warnings emitted by > initdb. > Yes, I mentioned this when this thread was going a few weeks ago. I only caught the locale setting being wrong on a system before it went into production because I happened to install on another system and noticed the message. I then had to ask the hosting company's SA to first check and then re-initdb. I was even sat watching/directing what he was doing and missed it. He was using Redhat with RPMs I was doing it properly from source. Those RPMs are dangerous, they turn you mind off. I voted for setting 'C' by default. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgres config file: autocommit = off
On Sun, 1 Jun 2003, Rasmus Resen Amossen wrote: > >We have been there, done that, and decided it was a bad idea. I suggest > >you do a little reading in the mail list archives. > > I have searched the lists archives for the words "commit", "autocommit" and > "transaction" but couldn't find any discussion on wheter to give a database > administrator the option to turn automatic commit off was is a good idea or > not (not requirering the user to enter BEGIN; to start a transaction). > > Do you know the title of the thread or some more details on where to find > the arguments? I look forward to read that discussion. :-) > > Personally I am managing a database for approx. 500 people which are all > VERY dependant on the correctnes of the database. Sometimes, when we do > manually fixes in the database, it takes a series of statements before our > data mangeling leaves the databases in a consistent state again. Therefor it > is a quite serious problem for us if we forget the BEGIN-word (which happens > quite often :-( ), so we could really use the config file option mentioned. > I can't remember the discussion very clearly but I seem to recall that it was some sort of issue with some, but not all, of the interfaces. However, ignoring that you can set autocommit to off by altering the user. For example if the user you do the fixing as as described above is called dba_1 then if you do: alter user dba_1 set autocommit to off; whenever you log in as the dba_1 user you will find that the autocommit is turned off. -- 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] Primary key and references
On Tue, 18 Mar 2003, Shridhar Daithankar<[EMAIL PROTECTED]> wrote: > Hi, > > Today I discovered that if there is a compund primary key on a table, I can > not create a reference from another table to one of the fields in the primary > key.. > > Look at this.. > > phd=# create table tmp1(a integer,b integer,primary key(a,b)); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for > table 'tmp1' > CREATE TABLE > phd=# create table tmp2(a integer references tmp1(a)); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > ERROR: UNIQUE constraint matching given keys for referenced table "tmp1" not > found > phd=# drop table tmp1; > DROP TABLE > phd=# create table tmp1(a integer unique,b integer primary key); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for > table 'tmp1' > NOTICE: CREATE TABLE / UNIQUE will create implicit index 'tmp1_a_key' for > table 'tmp1' > CREATE TABLE > phd=# create table tmp2(a integer references tmp1(a)); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > CREATE TABLE > phd=# select version(); >version > - > PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4 > (1 row) > That's right. The a,b combination is unique not the individual fields. Consider: Table: a | b --- 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 3 | 1 Picking just a couple of examples from that a = 1 several times and b = 1 several times but there is no unique constraint violation because there isn't something like a = 1 and b = 1 as a combination appearing more than once. > > Note that I do not require unique check on tmp2. It is perfectly acceptable to > have duplicate values in table tmp2. However no duplicates are allowed in > table tmp1. > > I consider this as a bug but given my understanding of sql, I won't count on > it. Any comments? If a is to be referenced in a foreign key it needs to be unique or how could it it be known which of the rows with a given value are being refered to. It follows that if a can be referenced in a foreign key then a uniquely identifies a row in the referenced table and therefore a primary key of (a,b) necessarily is unique based solely on a, i.e. the (a,b) combination seems unlikely to be the primary key for the table. > > The workaround shown here is acceptable as I don't really need a compound > primary key. But If I need, I know it won't work.. I hope that helps. > > TIA.. > > Shridhar -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, mlw wrote: > Tom, I just don't understand why this is being resisted so vigorously. > What is wrong with starting PostgreSQL as: > > postmaster -C /etc/postgresql.conf > > UNIX admins would love to have this as a methodology, I don't think you > can deny this, can you? I, as a long term PG user, really really want > this, because in the long run, it makes PostgreSQL easier to administer. > > If a patch allows PG to function as it does, but also allows a > configuration file methodology, why not? I forgot to say that I don't see why this facility can't be included in addition to the existing scheme. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, Curt Sampson wrote: > On Thu, 13 Feb 2003, Christopher Browne wrote: > > > 1. It assumes that there is "a location" for "the configuration files > > for /the single database instance./" > > No; it assumes that there's a location for "the default instance." If > you have more than one, you could have one default and one elsewhere, or > just do what I often do, which is put in an empty config file except for > a comment saying "we have several instances of on this machine; look > in for them." > > > 2. It assumes I have write access to /etc > > > > If I'm a Plain Old User, as opposed to root, I may only have > > read-only access to /etc. > > Right. It's dependent on the sysadmin to create /etc/postgres/ and make > it writeable, or set up proper symlinks, or whatever. > > Fortunately, the files in /etc are only the defaults, to be used if > they're not overridden on the command line. If you're in a situation > like #2, you're basically stuck where we are now all the time: you have > to just put it somewhere and hope that, if someone else needs to find > it, they can. It doesn't follow this line of argument directly but it's to do with this thread... Is everyone forgetting that wherever the configuration file is stored and whether or not it needs a command line argument to specify it the database is not going to start up automatically unless at least part of the installation is done as root anyway? As I like to install software as a non root user normally anyway I am happy that the config file lives somewhere not requiring write access by the installer. However, I think having it in an etc directory is a good thing (tm). So, colour me an uncommited, fence sitter :) I'm not talking distribution/package installation here but just plain system administration. Being an untrusting soul I do _not_ want to type make install as root and find things installed outside of where I say I want things placed. That includes configuration files. Doing this as a normal user protects the system from bad software which assumes things about the host system. It also simplifies switching between versions of software, try doing that if your config is /etc/postgresql/postgres.conf. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Maximum Size for Large Object / TOASTed Object
On Tue, 11 Feb 2003, Paul Ramsey wrote: > Hi All, > > What is the maximum size a large object can be in PostgreSQL? > What is the maximum size a TOASTed object can be in PostgreSQL? > > The "PostgreSQL Limitations" page says the maximum size of a "field" is > 1 Gb, but does a large object constitute a field? :) > > Thanks, > Paul I don't know but large objects are stored in the filesystem so I presume any limit is going to apply there. A large object isn't a field, the large object id can, and very probably should, be stored in one though. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Interactive Documentation - how do you want it towork?
When I first saw this thread I thought of the PHP docs which I recently started
using, from a level of knowing absolutely nothing of PHP.
Sure there was some useful stuff in some of the comments but some of the pages
were very long, far more comment than manual page. A lot of the comments refer
to far earlier versions, if the reader is lucky the version addressed by a
comment is included in the comment. Also, even as a complete newbie I was able
to find errors in the comments. The upshot is that the PHP docs have a lot of
old information in them, it's guess work as to what version of PHP the comment
refers to and there are a lot of comments to read to find out this uncertain
mixture of applicable/not applicable error prone comments.[*]
On the whole, I tended stop reading the comments and looked just at the manual
page and experiment myself instead.
[*] Error prone to a large extent in the examples/techniques listed as how to
do things, the email address regexp example shown as the way that started a
whole string of followups springs to mind. However, I also found some
useful/interesting information/techniques I hadn't thought of before amongst
the stuff.
--
Nigel Andrews
On Tue, 4 Feb 2003, Bruce Momjian wrote:
>
> That was interesting. I love the TRS-80 mention. So, it seems your
> logic is pretty much the same as ours --- trim them up and improve the
> docs.
>
> So, that particular URL was an example of what _not_ to do. I have
> heard folks say they like the PHP comments a lot, but I wonder how much
> of that is the cutesy factor of users making comments compared to good
> documentation that actually has useful, well structured information ---
> it doesn't have the cutesy factor, but it does seem more useful. :-)
>
> ---
>
> Ronald Chmara wrote:
> > On Monday, February 3, 2003, at 04:39 AM, Bruce Momjian wrote:
> > > I looked at that URL, and it is good example of what _not_ to do with
> > > interactive docs, IMHO. The manual page is _very_ short, and shows no
> > > examples. The comments have various examples/cases, with corrections
> > > later to earlier postings. I would think this is not what we want. We
> > > want a longer manual page, with _correct_ examples that show typical
> > > usage.
> > >
> > > I know folks like those comments, but isn't it showing cases where the
> > > curt documentation just doesn't cut it?
> >
> > Well, I happen to have some erm... "experience" with the PHP system. I
> > can offer a bit of history in this conversation about what seems to
> > have worked, and what doesn't work.
> >
> > What is *supposed* to happen with the pages and notes works like this:
> > 1. Manual page goes online. Almost all manual pages begin with a bare
> > skeleton, derived from the raw code itself. Some developers are nice
> > enough to, oh, explain what it means. :-)
> > 2. Comments, corrections, and additional examples are submitted.
> > 3. Notes and doc editors go through all the notes, roll all of the best
> > ones *into* the docs, delete redundancies (2 similar examples is
> > silly), fix errors in the page *and* other notes, and do other garbage
> > cleanup.
> > 4. Notes are removed when they are no longer relevant. A note that
> > duplicates current documentation would not be relevant. A note that
> > pertains to a version or behavior that is no longer supported is not
> > relevant.
> > 5. If a page has a lot of notes, that means it should be re-documented.
> > There have been days when I've cleared hundreds of "notes" with ten
> > lines of text, and a four line code example.
> >
> > After working with it (php's notes system) off and on for about 2 (3?)
> > years, here are some of the major *problems* in the PHP system:
> > 1. Silly slashdot mentality, were every opinion and "tip" imaginable
> > gets submitted into the notes. ("If running PHP on a TRS-80 tweaked out
> > as a hobby project, don't forget to make sure your error logs are
> > written to a faster device than cassette!!.")
> > 2. People are using the doc notes to submit bug reports. Constantly.
> > Annoyingly. So frequently that we automated their rejection.
> > 3. People are using the doc notes to submit coding questions.
> > Constantly. Annoyingly. So frequently that we automated their rejection.
> > 4. Keeping up with the submissions. PHP can get hundreds a day, of
> > which 98% or so are useless. There are people who read a "php-notes"
> > mailing list all day long, and at the bottom of each email is a set of
> > one-click URLs to take action... "reject", "edit", and "delete" (the
> > automation mentioned above). And yet, bad notes still get published,
> > because there's only so many a person can read...
> > 5. Keeping notes editors motivated. Talk about a thankless job. :-)
> > 6. Editing the manual page code is _much_ more complex than editing the
> > notes. As a result, rather than updating the manual, th
Re: [HACKERS] [ADMIN] Cannot connect to the database (PG 7.3)
On Tue, 28 Jan 2003, Tom Lane wrote: > I wrote: > > Michiel Lange <[EMAIL PROTECTED]> writes: > >> It is, somehow, not possible to connect as a user which name is completely > >> numeric. > > > I muttered "nonsense!" to myself, but darned if you're not right: > > > regression=# create user "12345"; > > CREATE USER > > regression=# \q > > $ psql -U 12345 regression > > psql: FATAL: SET SESSION AUTHORIZATION: permission denied > > > Will look into it. > > After some looking, it appears the culprit is > assign_session_authorization() in commands/variable.c, which is assuming > that a numeric-looking parameter string should be taken as a numeric > user sysid, rather than an actual user name. > > The reason this was done was to avoid the need to do catalog lookups > when restoring a prior setting during error recovery. That's still a > valid concern, so right offhand I don't see an easy fix. Any ideas? How about throwing an error if an all digit user name is given to create user as already alluded to? Seems that would be simple, not that I know anything about the parser, but does that break any standards? -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Yaarrgh! CVS remote buffer overflow
On 21 Jan 2003, Doug McNaught wrote: > It's all over Slashdot: > > http://security.e-matters.de/advisories/012003.html > That bit about 'This does not apply to :pserver: only' (probably slightly paraphrased) is very confusing. I gather from later on in the page that it means that the flaw only applies to the pserver method. -- 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] Indexes
On Thu, 16 Jan 2003, Tom Lane wrote: > > mlw said: > >>> Does anyone think it would be a good idea, or is it even practical, to > >>> have a 'indx' subdirectory along side of the 'base' directory? > >>> > >>> I was thinking that, if it were an easy modification, that it could be > >>> an easy way to separate data and indexes to different hard disks. > > This and other quick hacks have been discussed before. I think the > consensus has been to do nothing until someone gets around to writing > a general-purpose tablespace implementation. Wasn't someone just about done with a tablespace implementation? I certainly remember some discussion on this subject a few months ago. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] I feel the need for speed. What am I doing wrong?
Added -general list so that the next followup can remove -hackers and everyone
there will have had notice.
On Mon, 6 Jan 2003, Dann Corbit wrote:
>
> I have a query using two postgres tables.
> One is called "CNX_DS_53_SIS_STU_OPT_FEE_TB" and the other is called
> "CNX_DS2_53_SIS_STU_OPT_FEE_TB".
>
> I am getting 3 times slower performance than Microsoft Access when
> performing a left outer join.
>
> ...
>
> Here is the query:
> select a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" from
> "CNX_DS_53_SIS_STU_OPT_FEE_TB" a left outer join
> "CNX_DS2_53_SIS_STU_OPT_FEE_TB" b on ( a."RT_REC_KEY" = b."RT_REC_KEY"
> and a."cnxarraycolumn" = b."cnxarraycolumn") where b.oid is null ;
>
>
> Creating the following index had no effect on performance!
> create unique index i1 on "CNX_DS2_53_SIS_STU_OPT_FEE_TB" ("RT_REC_KEY",
> "cnxarraycolumn", "CRC");
>
> Both tables had 6139062 rows of data.
>
> In this query ... all rows of data match perfectly, so no results are
> returned.
I suspect you get no results because it's unlikely b.oid will be null. Are you
sure the query is how it should be since you seem to be expecting no rows to be
returned and yet your reason for that doesn't match the query as shown. Without
the oid test I'd bet you get a result set of 6139062 rows.
> Is there a way to reformulate this query so that it will use the index?
Given the above comment I'd say no since the entirety of both tables will be
tested to make the result set.
Alternatively, if the query is right try something along the lines of:
SELECT a.blah, a.foo,
FROM a, b
WHERE a.blah = b.blah AND a.foo = b.foo AND b.oid IS NULL
if that doesn't use a query try pushing the null test into a subselect like:
SELECT a.blah, a.foo,
FROM a, (SELECT * FROM b WHERE oid IS NULL) b
WHERE a.blah = b.blah AND a.foo = b.foo
After that let's hope I haven't embarrassed myself.
--
Nigel J. Andrews
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgres Security Expert???
FWIW, a search on Google gives some hits for the name on the lists this year. First impressions are that it's not Sir Mondred (or whatever the spelling was). On Tue, 26 Nov 2002, Justin Clift wrote: > Hi Chris, > > Just received this from them. Look like he was trying to claim stuff > that wasn't true. > > :-/ > > Thanks for pointing this out Chris. :) > > Regards and best wishes, > > Justin Clift > > > *** > > Original Message > Subject: Re: Demande de renseignements Defi SYSDOOR > Date: Tue, 26 Nov 2002 11:04:47 +0100 > From: "Vergoz Michael (SYSDOOR)" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > References: <200211260205.gAQ25GTK009595@jenna> > > Dear Clift, > > > > > Justin Clift PostgreSQL Global Development Group demande des informations > > son adresse : > > > > Son e-mail : [EMAIL PROTECTED] > > Son téléphone : +61.393631313 > > Son message : Hi, > > > > Just noticed your website mentioning that "Michael Vergoz" is well known > to created security patches for PostgreSQL: > > > > http://kernel.sysdoor.com/eng/ > > > > Can you please point us in their direction, as we don't know him by name. > > Right, it's true that i never make "_security_ patches" for > PostGreSQL... > > > > > As a side thought, would you please be able to correct the spelling of > PostgreSQL on the same page. Presently it's spelt "PostGreSQL", which > is > incorrect. > > Better way, i'v remove postgresql name in the site, as i think you want. > > > > > Regards and best wishes, > > > > Justin Clift > > > > -- > - > > Source IP : 203.173.161.124 (p378-tnt1.mel.ihug.com.au) > > Secure ID : [EMAIL PROTECTED] > > -- > - > > > > Best Regards, > Vergoz Michael > SYSDOOR > Founder > > *** > ---(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] Need Quote for 7.3
On Tue, 19 Nov 2002, Josh Berkus wrote: > Folks, > > We need a quote from a major code contributor to PostgreSQL about the > upcoming 7.3 release -- something about how great the new release is, > or some of the features in the release. We need this for the 7.3 > press release, which will be drafted in 2 days. > > If you have something to say, please e-mail me, Marc ([EMAIL PROTECTED]) > and Justin ([EMAIL PROTECTED]) off-list so we can quote you! I think it's great - but don't quote me on that. :) -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Debian build prob
On Thu, 14 Nov 2002, Patrick Welche wrote: > Believe it or not, I'm trying to compile today's cvs pgsql on a > Debian 2.2.19 system. Compilation dies while compiling pg_dump with > > ../../../src/interfaces/libpq/libpq.so: undefined reference to `atexit' > > In the mail archives there is a mention of upgrading libc to > libc6-dev_2.2.5-3_i386.deb. As far as I can tell, that should read > libc6_2.2.5-3_i386.deb, and again AFAICT this system already has > libc6_2.2.5-6_i386.deb on it. I can see atexit is undefined in libpq, and it > is defined in /usr/lib/libc.a. For some reason /lib/libc*.so are stripped, > so it is hard to tell, but I assume it must be the same as for > /usr/lib/libc.a. > > Have any of you managed to compile postgresql on an oldstable Debian system? > The latest I've built was from somewhere like the beta 3 mark but yes, built it on a Debian 2.2 installation with no library upgrades or anything. Now of course one would need a new bison. -- Nigel J. Andrews ---(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] RC1?
On Wed, 13 Nov 2002, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > FWIW, gmake check and gmake bigcheck pass on: > > FreeBSD 3.3-RELEASE #3: Thu Feb 3 23:48:56 GMT 2000 > > > with the expection of: > > [snipped] > > in the float8 test. > > Okay, looks like we need to use float8-fp-exception.out on your > platform. This is a bit surprising since resultmap presently shows > > float8/i.86-.*-freebsd=float8-small-is-zero > > How shall we distinguish your version of freebsd from the ones that > need the other comparison file? > > regards, tom lane > Is it necessary, I mean really necessary to distinguish this system? It's quite an old installation [that ain't broke so I ain't fixed it] and the difference is only the error message. I hadn't even looked to see if there was a better expected output file, just accepted it as a normal, acceptable variation in the regression tests. I don't know anything about how the tests are put together so I'd have to look into that before suggesting a way to differentiate my system. Having said that wouldn't the 3.3-RELEASE string be sufficient? -- Nigel J. Andrews ---(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] RC1?
On Tue, 12 Nov 2002, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Bruce Momjian writes: > >> Are we ready for RC1 yet? > > > Questionable. We don't even have 50% confirmation coverage for the > > supported platforms yet. > > We can't just wait around indefinitely for port reports that may or may > not ever appear. In any case, most of the "<7.3" entries in the list > seem to be various flavors of *BSD; I think it's unlikely we broke > those ... > FWIW, gmake check and gmake bigcheck pass on: FreeBSD 3.3-RELEASE #3: Thu Feb 3 23:48:56 GMT 2000 using: gcc -v gcc version 2.7.2.3 and ld -v GNU ld version 2.9.1 (with BFD 2.9.1) with: ./configure --prefix=/usr/local/pgsql-7.2.1 --enable-multibyte --with-perl --with-tcl --enable-odbc --with-pam --enable-syslog --with-tclconfig=/usr/local/lib/tcl8.0 --with-tkconfig=/usr/local/lib/tk8.0 --with-includes=/usr/local/include/tcl8.0:/usr/local/include/tk8.0 with the expection of: *** 214,220 SET f1 = FLOAT8_TBL.f1 * '-1' WHERE FLOAT8_TBL.f1 > '0.0'; SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; ! ERROR: Bad float8 input format -- overflow SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; ERROR: pow() result is out of range SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ; --- 214,220 SET f1 = FLOAT8_TBL.f1 * '-1' WHERE FLOAT8_TBL.f1 > '0.0'; SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; ! ERROR: floating point exception! The last floating point operation either exceeded legal ranges or was a divide by zero SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; ERROR: pow() result is out of range SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ; in the float8 test. -- Nigel J. Andrews Logictree Systems Limited ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Memory leaks
On Tue, 22 Oct 2002, Tom Lane wrote: > Greg Copeland <[EMAIL PROTECTED]> writes: > > > Interesting. Having not looked at memory management schemes used in the > > pl implementations, can you enlighten me by what you mean by "integrate > > the memory-context notion"? Does that mean they are not using > > palloc/pfree stuff? > > Not everywhere. plpgsql is full of malloc's and I think the other PL > modules are too --- and that's not to mention the allocation policies of > the perl, tcl, etc, language interpreters... I was going to make the suggestion that malloc et al. could be replaced with palloc etc but then that raises too many complications without just shooving everything into a long lived context anyway. Also I think we've got to rely on, i.e. it is sensible to do so, the underlying language handling memory correctly. Hmmm...there do seem to be a few mallocs in plpython.c . I haven't looked very closely but nothing jumped out at me as being obviously wrong from the grep output. -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Memory leaks
On 22 Oct 2002, Greg Copeland wrote: > On Tue, 2002-10-22 at 17:09, Tom Lane wrote: > > > plpgsql has some issues too, I suspect, but not as bad as pltcl etc. > > Possibly the best answer is to integrate the memory-context notion into > > those modules; if they did most of their work in a temp context that > > could be freed once per PL statement or so, the problems would pretty > > much go away. > > Interesting. Having not looked at memory management schemes used in the > pl implementations, can you enlighten me by what you mean by "integrate > the memory-context notion"? Does that mean they are not using > palloc/pfree stuff? I saw use of a couple of malloc (or Python specific malloc) calls the other day but I also seem to recall that, after consideration, I decided the memory needed to survive for the duration of the backend. Should I have created a new child of the top context and changed these malloc calls? I was going to ask about thoughts on redirecting malloc etc to palloc etc and thereby intercepting memory allocation within the languages and automatically bringing them into the memory context realm. However, that would just be making life way too awkward, bearing in mind the above paragraph. Can't we get Sir Mongle (or whatever the name was) to test these things under the auspices of them being DoS attacks? -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Freeing plan memory
On Sat, 19 Oct 2002, Tom Lane wrote:
> "Nigel J. Andrews" <[EMAIL PROTECTED]> writes:
> > The leak is that memory is grabbed in SPI_prepare() for a plan within
> > whatever context is current when it does the palloc(). It may be the
> > caller's or it may be the relevent SPI one. The plan is then copied
> > out of this memory [and context] into a child of the procedure's
> > context and forgotten about, or just plain forgotten.
>
> Au contraire: SPI_prepare builds the plan in its "execCxt", which is
> reset before returning (look at _SPI_begin_call and _SPI_end_call).
> So I see no leak there.
Ah, yes, I see that now.
> I'm not sure where the leak is in your plpython example, but I'd be
> inclined to look to plpython itself, perhaps even just the string
> concatenation expression in
> plan = plpy.prepare("SELECT " + repr(a))
Well it's not that string operation.
> plpgsql used to have terrible intra-function memory leaks, and only by
> dint of much hard work has it been brought to the point where you can
> expect a long loop in a plpgsql function not to chew up memory. AFAIK,
> no one has yet done similar work for the other PL languages.
Hmmm...my test case should boil down to a fairly small number of other calls in
the SPI_prepare wrapper and a quick looks doesn't show anything
interesting. Not sure I've got the time to dedicate to investigating this but
I'll look at it as and when I can.
I'm sending a patch for plpython.c to -patches which fixes a mistake I made in
the previous patch.
--
Nigel J. Andrews
---(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] Freeing plan memory
I notice there's a leak of memory in SPI_prepare().
The full fix is nontrival and I don't want to submit a half solution so I
thought I'd check whether people think it's worth worrying about.
The leak is that memory is grabbed in SPI_prepare() for a plan within whatever
context is current when it does the palloc(). It may be the caller's or it may
be the relevent SPI one. The plan is then copied out of this memory [and
context] into a child of the procedure's context and forgotten about, or just
plain forgotten. Obviously the intention is that this memory is freed when the
context is deleted and is probably not a problem unless someone does something
like:
i = 10;
while (i--)
{
plan = SPI_prepare("SELECT 1", 0, (Oid *)NULL);
SPI_freeplan(plan); /* SPI_freeplan() is not just for SPI_saveplan() */
}
Is this worth worrying about?
Any busy person can stop reading now as the above defines the problem while the
below only shows an easily reproducable example.
FWIW, I found it while testing something like, which is a little less daft
than the above example:
create function atest1 ( ) returns int as '
a = 0
while a < 1:
plan = plpy.prepare("SELECT " + repr(a))
a = a + 1
' language 'plpython';
Here the plpython code uses SPI_freeplan to release the context holding the
plan memory when each plan object returned by plpy.prepare() is garbage
collected. This seems sensibly to happen when the plan variable is
reassigned. However I was baffled why the process still had an obvious memory
leak so looked a little closer at SPI.
--
Nigel J. Andrews
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Little note to php coders
On Tue, 8 Oct 2002, Sir Mordred The Traitor wrote: > Check out this link, if you need something to laugh at: > http://www.postgresql.org/idocs/index.php?1' > > Keeping in mind, that there are bunch of overflows in PostgreSQL(really?), > it is > very dangerous i guess. Right? I'm not sure what list this really fits onto so I've left as hackers. The old argument about data validation and whose job it is. However, is there a reason why all CGI parameters aren't scanned and rejected if they contain any punctuation. I was going to say if they contain anything non alphanumeric but then I'm not sure about internationalisation and that test. -- Nigel J. Andrews ---(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] anoncvs and diff
On Thu, 3 Oct 2002, Bruce Momjian wrote: > Nigel J. Andrews wrote: > > cvs diff -r HEAD pltcl.c > > > > gave me differences against revision 1.64 > > > > and cvs update pltcl.c > > > > said it was merging changes between 1.64 and 1.61 > > > > and a plain cvs diff now shows me differences against 1.64 > > > > I think this is probably just a short fall in my fairly basic knowledge of how > > cvs works. > > What does 'cvs log' say about the file, especially the top stuff? It gave me the log all the way up to the 1.64 revision with the REL7_3_STABLE label assigned to revision 1.64.0.2 Revision 1.64 apparently backing out my patch which made 1.63. I had a brain wave and did the cvs log command which was what lead me to try specifying revisions. As I say it looks like a lack of knowledge about how cvs works for these things. I always thought it worked like RCS and gave a diff against the latest checked in but obviously not. BTW, I've found Neil Conway's patch for this file, email dated 25th Sept., I can forward it or apply it and include the changes along with whatever I do for my next submission, which ever you'd prefer. I'd suggest it's easy to let me apply and submit it due to overlaps. -- 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] anoncvs and diff
On Thu, 3 Oct 2002, Bruce Momjian wrote: > Nigel J. Andrews wrote: > > > > > > I've been waiting to see how a patched file differs from my version. > > > > The patch was added to the to apply list last week I think (it wasn't mine btw) > > and I've been doing cvs diff to view the differences so I can tell when the > > patch has been applied. Additional information given by this is the revision > > number the comparison is against of course. This has stayed at 1.61 all the > > time I've been doing this cvs diff operation. Looking at the web interface to > > cvs I see the file has a revision number of 1.64. I use the anoncvs server for > > my operations. Am I being daft or is there a problem with the anoncvs archive? > > That is strange. anoncvs and the web interface should have the same > version number. What file are you looking at? src/pl/tcl/pltcl.c However, since writing that I've tried some other things. cvs diff -r HEAD pltcl.c gave me differences against revision 1.64 and cvs update pltcl.c said it was merging changes between 1.64 and 1.61 and a plain cvs diff now shows me differences against 1.64 I think this is probably just a short fall in my fairly basic knowledge of how cvs works. -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] anoncvs and diff
I've been waiting to see how a patched file differs from my version. The patch was added to the to apply list last week I think (it wasn't mine btw) and I've been doing cvs diff to view the differences so I can tell when the patch has been applied. Additional information given by this is the revision number the comparison is against of course. This has stayed at 1.61 all the time I've been doing this cvs diff operation. Looking at the web interface to cvs I see the file has a revision number of 1.64. I use the anoncvs server for my operations. Am I being daft or is there a problem with the anoncvs archive? -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Fwd: [HACKERS] int type problem in 7.3
On Wed, 2 Oct 2002, Mario Weilguni wrote: > >> But oracle accepts this one: > >> SQL> select * from re_eintraege where id=''; > >> no rows selected > >> because oracle treats the empty string as NULL > > > >Oracle does that for string data, but it doesn't do it for numerics > >does it? In any case, that behavior is surely non-compliant with > >the SQL spec. > > No, oracle accepts this and works correctly with number() datatype. > However I did not know that in postgres '' was treated as '0'. So what would I be selecting in Oracle if I did: SELECT * FROM mytable WHERE myfield = '' where myfield is of VARCHAR type? If you want to select on NULL, whether or not you think the database is more intelligent than you in determining what you really want, then write your query to select on NULL. The chances are your database is not actually a mind reader. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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] 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] pltcl.so patch
In answer to the question posed at the end of the message below:
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).
Briefly following through to check this value shows that as long as I have
CLOBBER_FREED_MEMORY defined, which I presume I do having configured with
--debug, this value is also consistent with the tuptable having been freed
before this faulting invocation.
I haven't looked too closely yet but at a glance I can't see what could be
going wrong with the exception that the tuptable is freed even if zero rows are
returned by SPI_exec. That and I'm not sure what that $T(id) thing is doing in
the SQL submited to pltcl_SPI_exec. Oh 'eck, I've been reading that test
function wrong, it's got a level of nesting.
Unfortunately, I am currently trying to throw together a quick demo of
something at the moment so can't investigate too fully for the next day or so.
If someone wants to pick this up feel free otherwise I'll look into it later.
--
Nigel J. Andrews
On Tue, 24 Sep 2002, Ian Harding wrote to me:
> First, thank you very much for working on this issue. Pltcl is extremely important
>to me right now, and this memory leak is cramping my style a bit.
>
> I applied the patch you sent to my pltcl.c (I am at version 7.2.1, but it seems to
>apply fine...) It builds fine, psql starts fine, but my test function still blows up
>dramatically.
>
> Here is the script I am using:
>
> drop function memleak();
> create function memleak() returns int as '
>
> for {set i 1} {$i < 100} {incr i} {
> set sql "select ''foo''"
> spi_exec "$sql"
> }
>
>
> ' language 'pltcl';
>
> drop table testable;
> create table testable (
> id int,
> data text);
>
> insert into testable values (1, 'foobar');
> insert into testable values (2, 'foobar');
> insert into testable values (3, 'foobar');
> insert into testable values (4, 'foobar');
> insert into testable values (5, 'foobar');
> insert into testable values (6, 'foobar');
>
> drop function memleak(int);
> create function memleak(int) returns int as '
>
> set sql "select * From testable"
> spi_exec -array T "$sql" {
>
> for {set i 1} {$i < 100} {incr i} {
> set sql "select * from testable where id = $T(id)"
> spi_exec "$sql"
> }
> }
> ' language 'pltcl';
>
> Here is what happens:
>
> bash-2.05# psql -U iharding test < testfunction
> DROP
> CREATE
> ERROR: table "testable" does not exist
> CREATE
> INSERT 118942676 1
> INSERT 118942677 1
> INSERT 118942678 1
> INSERT 118942679 1
> INSERT 118942680 1
> INSERT 118942681 1
> DROP
> CREATE
> bash-2.05# psql -U iharding test
> Welcome to psql, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
>\h for help with SQL commands
>\? for help on internal slash commands
>\g or terminate with semicolon to execute query
>\q to quit
>
> test=# select memleak();
> memleak
> -
>0
> (1 row)
>
> test=# select memleak(1);
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !#
>
>
> Here is the end of the log:
>
> DEBUG: server process (pid 1992) was terminated by signal 11
> DEBUG: terminating any other active server processes
> DEBUG: all server processes terminated; reinitializing shared memory and semaphores
> IpcMemoryCreate: shmget(key=5432001, size=29769728, 03600) failed: Cannot allocate
>memory
>
> This error usually means that PostgreSQL's request for a shared
> ...
>
>
> Do you have similar results?
---(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 Sun, 22 Sep 2002, Tom Lane wrote: > > It was pretty clear that Thomas' original patch lost the vote, or > would have lost if we'd bothered to hold a formal vote. Hasn't there just been a formal vote on this? > I don't > see anyone arguing against the notion of making XLOG location more > easily configurable --- it was just the notion of making it depend > on environment variables that scared people. And it's obvious it was centred on the use of an environment variable from the subject line, it's still got PGXLOG in capitals in it. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Memory Errors...
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.
Index: src/pl/plpython/feature.expected
===
RCS file: /projects/cvsroot/pgsql-server/src/pl/plpython/feature.expected,v
retrieving revision 1.4
diff -c -r1.4 feature.expected
*** src/pl/plpython/feature.expected2002/03/06 18:50:31 1.4
--- src/pl/plpython/feature.expected2002/09/20 22:12:36
***
*** 29,35
(1 row)
SELECT import_fail();
! WARNING: ('import socket failed -- untrusted dynamic module: _socket',)
import_fail
failed as expected
--- 29,35
(1 row)
SELECT import_fail();
! NOTICE: ('import socket failed -- untrusted dynamic module: _socket',)
import_fail
failed as expected
Index: src/pl/plpython/plpython.c
===
RCS file: /projects/cvsroot/pgsql-server/src/pl/plpython/plpython.c,v
retrieving revision 1.22
diff -c -r1.22 plpython.c
*** src/pl/plpython/plpython.c 2002/09/04 22:51:23 1.22
--- src/pl/plpython/plpython.c 2002/09/20 22:12:40
***
*** 408,414
--- 408,416
else
PLy_restart_in_progress += 1;
if (proc)
+ {
Py_DECREF(proc->me);
+ }
RERAISE_EXC();
}
***
*** 1841,1847
--- 1843,1856
Re: [HACKERS] [GENERAL] Memory Errors...
On 20 Sep 2002, Greg Copeland wrote: > I'll try to have a look-see by the end of the weekend. Any code that > can reproduce it or is it ANY code that uses SPI? > > Greg > > > On Fri, 2002-09-20 at 11:39, Peter Eisentraut wrote: > > Tom Lane writes: > > > > > On looking a little more closely, it's clear that pltcl_SPI_exec() > > > should be, and is not, calling SPI_freetuptable() once it's done with > > > the tuple table returned by SPI_exec(). This needs to be done in all > > > the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. > > > > There's a note in the PL/Python documentation that it's leaking memory if > > SPI plans are used. Maybe that's related and someone could take a look at > > it. I've added the call to free the tuptable just as in the pltcl patch I submited earlier (which I can't remember if I've seen in the list so I may well resend). However, the comments in the code imply there might be another leak with prepared plans. I'm looking into that so I won't be sending this patch just yet. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Improving speed of copy
On Fri, 20 Sep 2002, Shridhar Daithankar wrote: > In select test where approx. 15 rows where reported with query on index field, > mysql took 14 sec. and psotgresql took 17.5 sec. Not bad but other issues > eclipse the result.. I don't know about anyone else but I find this aspect strange. That's 1 second (approx.) per row retrieved. That is pretty dire for an index scan. The data/index must be very non unique. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SCSI Error
On Fri, 20 Sep 2002, Ricardo Fogliati wrote: > Hiya Lists > > Somebody could help me? I am with an error when the Postgresql makes Insert, > Delete or Update > > kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 > kernel: I/O error: dev 08:08, sector 47938856 > kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 > kernel: I/O error: dev 08:08, sector 47938800 > kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 > kernel: I/O error: dev 08:08, sector 47938864 > kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 > kernel: I/O error: dev 08:08, sector 47938872 > kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 > kernel: I/O error: dev 08:08, sector 47938808 > > Version: > > postgresql-7.2.1-5 > [deleted]... > Kernel: > > Linux version 2.4.7-10custom (gcc version 2.96 2731 (Red Hat Linux 7.1 > 2.96-98)) #9 Mon > Sep 16 17:50:13 BRT 2002 Not sure what you're asking for. That's a hardware error. Back up immediately, if you haven't already got decent backups, and fix the disk/controller. Could possibly be a filesystem error but even if so it's still casting doubt on the hardware. On the other hand I do believe I saw a message recently saying that some of the 2.4 series kernels had file system bugs. I don't know which, someone else might be able to expand. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Memory Errors...
On Thu, 19 Sep 2002, Joe Conway wrote: > Tom Lane wrote: > > I said: > > > >>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? > > > > > > On looking a little more closely, it's clear that pltcl_SPI_exec() > > should be, and is not, calling SPI_freetuptable() once it's done with > > the tuple table returned by SPI_exec(). This needs to be done in all > > the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. > > pltcl_SPI_execp() has a similar problem, and there may be comparable > > bugs in other pltcl routines (not to mention other sources of memory > > leaks, but I think this is the problem for your example). > > > > I have no time to work on this right now; any volunteers out there? > > > > I can give it a shot, but probably not until the weekend. > > I haven't really followed this thread closely, and don't know tcl very well, > so it would help if someone can send me a minimal tcl function which triggers > the problem. I can probably take a look at this tomorrow, already started by looking at the pltcl_SPI_exec routine. I think a quick glance at ...init_unknown() also shows a lack of tuptable freeing. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Memory Errors...
> "Ian Harding" <[EMAIL PROTECTED]> writes: > > It is pltcl [not plpgsql] Quick, minor point, in the manner of a question: Why is the pltcl directory called tcl where all the other pls are pl? That's in src/pl of course. Also in my anoncvs fetch which is a few weeks old now being from the day before beta freeze. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGXLOG variable worthwhile?
On Tue, 17 Sep 2002, Bruce Momjian wrote: > Dave Page wrote: > > Which in this case is what puzzles me. We are only talking about a > > simple GUC variable after all - I don't know for sure, but I'm guessing > > it's not a huge effort to add one? > > Can we get agreement on that? A GUC for pg_xlog location? Much cleaner > than -X, doesn't have the problems of possible accidental use, and does > allow pg_xlog moving without symlinks, which some people don't like? > > If I can get a few 'yes' votes I will add it to TODO and do it for 7.4. GUC instead of -X or PGXLOG : yes. However, how is that going to work if tablespaces are introduced in 7.4. Surely the same mechanism for tablespaces would be used for pg_xlog. As the tablespace mechanism hasn't been determined yet, as far as I know, wouldn't it be best to see what happens there before creating the TODO item for the log? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] SRF and pg_group
I realise that this has already been done, by Joe Conway I think. Indeed I was
looking at this just before beta1 when I happened to notice the post giving the
plpgsql function. However, as I had started work on it and I was interested in
seeing how things should be done I continued, only not in so much of a rush.
In the interests on finding out if I have approached this the right way, or the
way a more experienced backend programmer would, I'd appreciate any comments on
the attached .c file. In particular, I'm not sure what I'm doing with regard to
memory contexts, I think I may have one unnecessary switch in there, and in
general I seem to be doing a lot of work just to find out tidbits of
information.
I based this on, i.e. started by editing, Joe Conway's tablefunc.c but I think
there's very little of the original left in there.
I've also attached the .h, Makefile and .sql.in files to make this work if
anyone is interested in giving it a run. The .sql.in shows the usage. I did
this in a directory called pggrouping, for the sake of a better name, under the
contrib directory in my tree, so that's probably the best place to build it.
Thanks, and sorry for adding to people's email and work load.
--
Nigel J. Andrews
Director
---
Logictree Systems Limited
Computer Consultants
/*
* Derived from tablefunc.c, a sample to demonstrate C functions which
* return setof scalar and setof composite by Joe Conway <[EMAIL PROTECTED]>
*
* Copyright 2002 by PostgreSQL Global Development Group
*
* Permission to use, copy, modify, and distribute this software and its
* documentation for any purpose, without fee, and without a written agreement
* is hereby granted, provided that the above copyright notice and this
* paragraph and the following two paragraphs appear in all copies.
*
* IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
* DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
* LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
* DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
* INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
* AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
* ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
* PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
*
*/
#include
#include
#include "postgres.h"
#include "fmgr.h"
#include "funcapi.h"
#include "executor/spi.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
#include "pggrouping.h"
typedef struct unpack_array_fctx
{
SPITupleTable *spi_tuptable; /* sql results from user query */
TupleDesc tupdesc; /* TupleDesc for results */
int unpack_attrnum; /* attribute number to be unpacked */
int lastcall_cntr; /* previous call_cntr, invlaid = -1 */
int lastindex; /* index of the last array item sent, invalid < 1 */
} unpack_array_fctx;
#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
static Datum expandArray_SRF(FunctionCallInfo info,
char *sql,
int unpackAttrNum);
static Datum expandArray_SRF_FirstCall(FunctionCallInfo fcinfo,
FuncCallContext *funcctx,
const char *sql,
const int unpackAttrNum);
static Datum expandArray_SRF_GetTuple(FunctionCallInfo fcinfo,
FuncCallContext *funcctx);
static TupleDesc makeUnpackedTupleDesc(TupleDesc src_tupdesc,
int unpack_attrnum);
static bool similarTupleDescs(TupleDesc ret_tupdesc,
TupleDesc sql_tupdesc);
/*
* pg_group_expandusers
*
* Return pg_group where each tuple has grolist attribute of int4[] type
* changed to be of type int4 and to hold only one user id.
*/
PG_FUNCTION_INFO_V1(pg_group_long);
Datum
pg_group_long(PG_FUNCTION_ARGS)
{
return expandArray_SRF(fcinfo,
"select groname,grosysid,grolist from pg_group",
3);
}
/*
* expand_array_srf
*
* Return tuples such that the elements of an array attribute are
* extracted in turn and placed into the output instead of the array.
* Declared to fmgr as:
* CREATE FUNCTION the_name(text,integer) RETURNS SETOF RECORD ...
*
* where the text argument is the query string to obtain the source
* data and the integer argument gives the column number of the array
* to expand.
*
* Note, despite checking number of arguments this is in no way safe
* from some one creating a fmgr function which us
Re: [HACKERS] Memory management question
On Tue, 3 Sep 2002, Karel Zak wrote: > On Tue, Sep 03, 2002 at 12:28:37PM +0100, Nigel J. Andrews wrote: > > > > > > It's probably a pretty basic question explained in some document I haven't seen > > but...if I do something like a CreateTupleDescCopy() how do I know my memory > > context owns everything allocated without following the code all the way > > through until it returns to me? > > If some code doesn't call MemoryContextSwitchTo() all is allocated in > current memory context. You can check if CurrentMemoryContext is same > before and after call that is important for you - but this check say > nothing, bacuse some code can switch to other context and after usage > switch back to your context. IMHO is not common way how check it. > (Ok, maybe check all contexts size before/after call...) > > Suggestion: add to memory managment counter that handle number > of MemoryContextSwitchTo() calls. IMHO it can be compile > only if MEMORY_CONTEXT_CHECKING is define. I quite like that idea. Only thing is it doesn't full address the issue of identifying if my context owns memory allocated by other functions I've used. For example: A called procedure could be doing (psuedo code obviously): SwitchContext() mem=palloc(anumber) /* use mem */ pfree(mem) SwitchContectBack() retmem=palloc(anothersize) There, net effect is that I do own retmem but the test on context switch counters would indicate that I may not. I think the problem is that I don't fully understand why [and when] is context switch is or should be done. > But I think there is not to much places which switching between > contexts and all are good commented (I hope, I wish :-) As someone pointed out my example wasn't very complex so checking the source wasn't onerous. Checking something like heap_modifytuple() is more time consuming. I was hoping there was some sort of 'rule of thumb'. In general I can't see how it could be sensibly known without such a rule and without tracing through the source. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Memory management question
It's probably a pretty basic question explained in some document I haven't seen but...if I do something like a CreateTupleDescCopy() how do I know my memory context owns everything allocated without following the code all the way through until it returns to me? -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Impending freeze
On Sun, 1 Sep 2002, Marc G. Fournier wrote: > On Sun, 1 Sep 2002, Nigel J. Andrews wrote: > > > > > On Mon, 2 Sep 2002, Gavin Sherry wrote: > > > > > On Sun, 1 Sep 2002, Nigel J. Andrews wrote: > > > > > > > When is the beta freeze? > > > > > > Today. > > > > > > > Oops, my fault for being imprecise. > > > > I was wondering what time of day with timezone. Someone suggested end of today > > but that means different times to different people. > > 8:30am ADT on Tuesday morning is when I'm going to freeze everything ... > so you effectively have all day on Monday to get it in ... Thanks all that replied. I haven't read the messages between Friday and when I posted yet so I didn't know about the Tuesday morning thing. I was not going to bother having realised the silliness of trying to rush a patch into place but this sounds more realistic. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Impending freeze
On Mon, 2 Sep 2002, Gavin Sherry wrote: > On Sun, 1 Sep 2002, Nigel J. Andrews wrote: > > > When is the beta freeze? > > Today. > Oops, my fault for being imprecise. I was wondering what time of day with timezone. Someone suggested end of today but that means different times to different people. -- Nigel Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Impending freeze
When is the beta freeze? I've just started looking at a ToDo list item and hope it won't take too long. However, I've got other things to do and this is the first I've looked in this area. An idea about time left to complete it would be good so I decide if I'm wasting my time and effort at the moment. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] source code indexer
On Fri, 30 Aug 2002, Laurette Cisneros wrote: > > HI all, > > Sorry to interrupt your busy list. > > I was wondering if you could recomend a good source code db/indexer that > could be used to search through the postgresql code? I think I must be one of those 'old school' types. I use find | xargs grep often followed by tags in Emacs. It isn't perfect but then I'm not either. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Fulltextindex
On Fri, 30 Aug 2002, Christopher Kings-Lynne wrote:
>
>
>
> struct varlena *data;
> char*word = "john";
> char*cur_pos= NULL;
> int cur_pos_length = 0;
>
> data = (struct varlena *) palloc(VARHDRSZ + column_length + 1);
> word_length = strlen(word);
> cur_pos = &word[word_length - 2];
>
> while(cur_pos > word)
> {
> cur_pos_length = strlen(cur_pos);
> /* Line below causes seg fault on SECOND iteration */
> data->vl_len = cur_pos_length + sizeof(int32);
> memcpy(VARDATA(data), cur_pos, cur_pos_length);
> values[0] = PointerGetDatum(data);
> values[1] = 0;
> values[2] = oid;
>
> ret = SPI_execp(*(plan->splan), values, NULL, 0);
> if(ret != SPI_OK_INSERT)
> elog(ERROR, "Full Text Indexing: error executing plan in insert\n");
>
> cur_pos--;
> }
>
That would imply the SPI_execp call is trashing the value of data. Have you
confirmed that? (Sometimes it helps to confirm exactly where a pointer is
getting hammered.)
column_length is something sensible like word_length I presume.
That sizeof(int32) should really be VARHDRSZ imo, but I can't see how that's
breaking it.
Disclaimer: I have no idea what I'm doing here.
--
Nigel J. Andrews
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] TODO Done. Superuser backend slot reservations
On Mon, 26 Aug 2002, Bruce Momjian wrote: > Tom Lane wrote: > > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > > I was taking the line that the last slots in the array are > > > reserved. Those are not going to be taken by non su connections. > > > > But that doesn't do the job, does it? My view of the feature is that > > when there are at least MaxBackends - ReservedBackends slots in use (by > > either su or non-su connections) then no new non-su jobs should be let > > in. For example, if the system is full (with a mix of su and non-su > > jobs) and one non-su job quits, don't we want to hold that slot for a > > possible su connection? > > > > Your approach does have the advantage of being very cheap to test > > (I think my semantics would require counting the active backends), > > but I'm not sure that it really does what we want. > > Tom is right. If the last two slots are held by two long-running > super-user backends, and the slots fill, there will be no reserved > slots. The trick is that when the maximum number of backends is almost > exceeded, only let the supuer-user in. Okay, it's not how I was thinking as you know but I've got nothing against it other than the backend slot scan time. I don't think that would be a significant drain of cpu time so I'll implement that scheme and resubmit. Got some other stuff to do first so it won't be done immediately but will in the next day or so; in time for beta assuming it doesn't fall foul of any patch review interval required. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] TODO Done. Superuser backend slot reservations
On Mon, 26 Aug 2002, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > + if (!superuser() && MyBackendId > MaxBackends - ReservedBackends) > > + elog(ERROR, "Normal user limit exceeded"); > > This coding is wrong on its face: the slot number you happen to find has > no relationship to the number of slots remaining free, except as an > existence proof that the number of slots free was > 0 before you took > one. Yes. I was taking the line that the last slots in the array are reserved. Those are not going to be taken by non su connections. Therefore, if MyBackendId is under the lower limit it doesn't matter if it's the only slot free since the 'safety' measure has already been used in restricting access to the last free slots and it just so happens that those sessions are still active. I take Neil's point about the order of the tests. That's my stupidity when rearranging stuff after noticing in tests that the user information wasn't available where I was [also stupidly] expecting it to be first time around. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] TODO Done. Superuser backend slot reservations
TODO item:
Administration -
Reserve last few process slots for super-user if max_connections
reached
Notes:
Added GUC superuser_reserved_connections such that non-superuser connections
are only acceptable in the first
(max_connections - superuser_reserved_connections) backend slots.
Superuser connections within these first n slots count towards this
non-superuser connection limit. Therefore there can be at most this number
of non-superuser connections but may be less.
In addition, this limit is only checked on initialisation of a backend
process. So reserved slots can be taken by connections that subsequently
lose superuser priviledges thus evading the lower limit on backends.
Passed regression tests, not that it was likely not to.
Behaved as expected in a manual test.
--
Nigel J. Andrews
Director
---
Logictree Systems Limited
Computer Consultants
? config.log
? GNUmakefile
? config.status
? src/Makefile.global
? src/include/pg_config.h
? src/include/stamp-h
Index: src/backend/postmaster/postmaster.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.285
diff -c -r1.285 postmaster.c
*** src/backend/postmaster/postmaster.c 2002/08/18 03:03:25 1.285
--- src/backend/postmaster/postmaster.c 2002/08/25 22:27:47
***
*** 151,156
--- 151,168
*/
int MaxBackends = DEF_MAXBACKENDS;
+ /*
+ * ReservedBackends is the number of backends reserved for superuser use.
+ * This number is taken out of the pool size given by MaxBackends so
+ * number of backend slots available to none super users is
+ * (MaxBackends - ReservedBackends). Note, existing super user
+ * connections are not taken into account once this lower limit has
+ * been reached, i.e. superuser connections made before the lower limit
+ * is reached always count towards that limit and are not taken from
+ * ReservedBackends.
+ */
+ int ReservedBackends = 2;
+
static char *progname = (char *) NULL;
***
*** 566,571
--- 578,591
SetDataDir(potential_DataDir);
ProcessConfigFile(PGC_POSTMASTER);
+
+ /*
+* Force ReservedBackends is less than MaxBackends if need be.
+* A cluster only allowing superuser connections seems silly whereas
+* a cluster reserving none for superusers doesn't.
+*/
+ if (ReservedBackends >= MaxBackends)
+ ReservedBackends = MaxBackends - 1;
/*
* Now that we are done processing the postmaster arguments, reset
Index: src/backend/utils/init/postinit.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/init/postinit.c,v
retrieving revision 1.109
diff -c -r1.109 postinit.c
*** src/backend/utils/init/postinit.c 2002/07/20 05:16:59 1.109
--- src/backend/utils/init/postinit.c 2002/08/25 22:27:48
***
*** 402,407
--- 402,417
/* close the transaction we started above */
if (!bootstrap)
CommitTransactionCommand();
+
+ /*
+* Check a normal user hasn't connected to a superuser reserved slot.
+* Do this here since we need the user information and that only happens
+* after we've started bringing the shared memory online. So we wait
+* until we've registered exit handlers and potentially shut an open
+* transaction down for an as safety conscious rejection as possible.
+*/
+ if (!superuser() && MyBackendId > MaxBackends - ReservedBackends)
+ elog(ERROR, "Normal user limit exceeded");
}
/*
Index: src/backend/utils/misc/guc.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.83
diff -c -r1.83 guc.c
*** src/backend/utils/misc/guc.c2002/08/18 03:03:25 1.83
--- src/backend/utils/misc/guc.c2002/08/25 22:27:51
***
*** 537,547
/*
* Note: There is some postprocessing done in PostmasterMain() to make
* sure the buffers are at least twice the number of backends, so the
!* constraints here are partially unused.
*/
{
{ "max_connections", PGC_POSTMASTER }, &MaxBackends,
DEF_MAXBACKENDS, 1, INT_MAX, NULL, NULL
},
{
--- 537,553
/*
* Note: There is some postprocessing done in PostmasterMain() to make
* sure the buffers are at least twice the number of backends, so the
!* constraints here are partially unused. Also the super user reserved
!* n
Re: [HACKERS] A configure.in patch check (fwd)
On Sun, 25 Aug 2002, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > + AC_MSG_CHECKING([for default superuser reserved number of connections]) > > + PGAC_ARG_REQ(with, reservedbackends, [ --with-reservedbackends=Nset default >superuser reserved number of connections [2]], > > + [], > > + [with_reservedbackends=2]) > > This will be rejected anyway; what you want is to set up > reserved_backends as a GUC parameter, not as something that has to be > hard-wired at configure time. I can't see any reason to make it > hard-wired... It is a GUC. It's exactly like max_backends. I took the easy route out and just followed where DEF_MAXBACKENDS was being set rather than hard wiring the value any where. Rather distressingly in order to get this new value into where it's needed I had to hit quite a few files, more than I would have expected. Again I just followed how MaxBackends was being sent to where it was needed but is there any particular reason why storage/ipc/sinvaladt.c:SIBackendInit() can't access MaxBackends and my new ReservedBackends directly? The are global variables afterall, I think #include "miscadmin.h" would need to be added but is that bad? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] A configure.in patch check (fwd)
Helps if I attach the patch... -- Forwarded message -- Date: Sun, 25 Aug 2002 14:36:19 +0100 (BST) From: Nigel J. Andrews <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: A configure.in patch check Would someone apply the attached patch to the development source and let me know if the autoconf step fails or works. I've only got autoconf 2.13 available and the file needs 2.53 apparently. If it works could I also have a copy of the resulting configure script, or patch, please. For the record, this is related to reserving the last few backend slots for the superuser and I just need to test what I've done. TIA -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants Index: configure.in === RCS file: /projects/cvsroot/pgsql-server/configure.in,v retrieving revision 1.197 diff -c -r1.197 configure.in *** configure.in2002/08/22 22:43:08 1.197 --- configure.in2002/08/25 13:31:31 *** *** 213,218 --- 213,230 # + # Number of connections reserved for superuser (--with-reservedbackends), default 2 + # + AC_MSG_CHECKING([for default superuser reserved number of connections]) + PGAC_ARG_REQ(with, reservedbackends, [ --with-reservedbackends=Nset default +superuser reserved number of connections [2]], + [], + [with_reservedbackends=2]) + AC_MSG_RESULT([$with_reservedbackends]) + AC_DEFINE_UNQUOTED([DEF_RESERVEDBACKENDS], [$with_reservedbackends], +[The default number of concurrent connection slots reserved for +superusers only]) + + + # # Option to disable shared libraries # PGAC_ARG_BOOL(enable, shared, yes, ---(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] A configure.in patch check
Would someone apply the attached patch to the development source and let me know if the autoconf step fails or works. I've only got autoconf 2.13 available and the file needs 2.53 apparently. If it works could I also have a copy of the resulting configure script, or patch, please. For the record, this is related to reserving the last few backend slots for the superuser and I just need to test what I've done. TIA -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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] Proposal: make "opaque" obsolete
On Wed, 21 Aug 2002, Tom Lane wrote: > Joe Conway <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> ... Then you could actually do something interesting with > >> a function taking anyarraytype. > > > This sounds very cool. I'd vote for that. > > Um, am I hearing a volunteer to make it happen? I have other problems > I need to deal with ... Tom, I saw something in the other thread suggesting that you might be working on this. Is that so? If not I have had a little poke around the cash type but I'm no where near up to speed on the internals. Your proposal is that cstring etc. get entries like record on pg_type? That presumably means we'd need in and out functions defined for these, which in the case of cstring would just be copying the input to output? (As you can see I may not be the best person to work on this if it is to be available for the beta) -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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] @(#) Mordred Labs advisory 0x0001: Buffer overflow in
On Tue, 20 Aug 2002, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > >> I'd like to see something done about this fairly soon, but it's not > >> happening for 7.3 ... > > > Does anyone have an idea about what other functions are affected by this? > > As a first approximation, every output function for a built-in > pass-by-reference datatype will show this same behavior. cash_out is > just getting picked on because it was the one mentioned in the first > complaint. For that matter, every input function for any datatype > has the same problem: > regression=# select cash_in(2); > server closed the connection unexpectedly > > ... But going back to the idea that it seems that the only problem being publicised in the 'outside world' is the cash_out(2) version can we not do the restriction on acceptable input type in order to claim that the fix? Obviously this is only a marketing ploy but on the basis that a real fix seems unlikely before beta in 11 days time (I'm still trying to work out what Tom's suggestion is) perhaps one worth implementing. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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] @(#) Mordred Labs advisory 0x0001: Buffer overflow in
On Mon, 19 Aug 2002, Tom Lane wrote: > Justin Clift <[EMAIL PROTECTED]> writes: > > From the info still around, this looks to mean that the cash_words() > > problem was fixed, but the cash_out() problem was harder to fix. > > > Tom/Bruce, is that correct? > > The cash_out problem can't really be fixed until we do something about > subdividing type "opaque" into multiple pseudo-types with more carefully > defined meanings. cash_out is declared cash_out(opaque) which does not > really mean that it accepts any input type ... but one of the several > meanings of "opaque" is "accepts any type", so the parser doesn't reject > cash_out(2). > > I'd like to see something done about this fairly soon, but it's not > happening for 7.3 ... Does anyone have an idea about what other functions are affected by this? As a stop gap measure to remove the *known* DoS issue how about changing the pg_proc entry to restrict input types, i.e. not cash_out(opaque)? cash_words is already listed as only taking the money type is cash_out really that different? On a related topic cash_out() is listed in pg_proc as returning an int4 but doesn't the code clearly show that is incorrect? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Open 7.3 issues
On Fri, 16 Aug 2002, Bruce Momjian wrote: > Peter Eisentraut wrote: > > Bruce Momjian writes: > > > > > Socket permissions - only install user can access db by default > > > unix_socket_permissions in postgresql.conf > > > > This is dead. > > Removed, still on TODO. Daft question but isn't this an administrator's issue? Forcing a complete override of umask isn't usually considered a decent, friendly thing to do and the sys admin can change the permission bits. Besides, what was the proposed scheme? user/group writable world not I presume. I wouldn't even know how to port that to un unixy systems like Win32. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.3 items
On Wed, 14 Aug 2002, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I have no personal preference between period and @ or whatever. See if > > you can get some other votes for @ because most left @ when the ORDER BY > > idea came up from Marc. > > FWIW, I still lean to username@database, so I think we're roughly at a > tie. It would be good to get more votes ... Seeing as this is rumbling on I'll throw in my fraction of a vote. I too like the user@database form, partly because it 'reads'. On the other hand I can see the the reasons to like database.user and it does match the style of database.schema.object. Unfortunately for this second form, as '.' is a valid character in a database name then I can see this causing problems, especially with the behind the scenes combination of the two names. I don't see this problem with the '@' form because I can't see that character being used in a 'unqualified' user name. Hmmm...not sure that makes a terribly good arguement for my vote for 'user@db', is there a third choice for us confused folks to go for? A compromise: database@username ? [BTW, I did check and '@' seems to be a valid character in database and user names.] -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [HACKERS] Linux Largefile Support In Postgresql RPMS
On Fri, 9 Aug 2002, Helge Bahmann wrote: > > As far as I can make out from the libc docs, largefile support is > > automatic if the macro _GNU_SOURCE is defined and the kernel supports > > large files. > > > > Is that a correct understanding? or do I actually need to do something > > special to ensure that pg_dump supports large files? > > in this case you still have to use large file functions in the code > explicitly > > the easiest way to get large file support is to pass > -D_FILE_OFFSET_BITS=64 to the preprocessor, and I think I remember doing > this once for pg_dump > > see /usr/include/features.h There is some commentary on this in my /usr/doc/libc6/NOTES.gz, which I presume Oliver has already found since I found it after reading his posting. It gives a bit more detail that the header file for those who want to check this out. I for one was completely unaware of those 64 bit functions. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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] postgresql performance tuning document ?
Again, I'm not sure this doesn't belong on -general but have left the -hackers in the distribution list so it can be seen there. On Fri, 9 Aug 2002, Oleg Bartunov wrote: > I see files truncated at 1Gb on my Linux server: > > -rw---1 postgres users855490560 Aug 6 20:53 795261707.2 > -rw---1 postgres users943259648 Aug 8 23:34 823049708 > -rw---1 postgres users1073741824 Aug 6 20:53 795261707.1 > -rw---1 postgres users1073741824 Aug 6 20:53 795261707 > > I'm wondering if postgresql doesn't have LARGE_FILES support ? Those look like PostgreSQL's data files (from the context of the email) is that so? And if so, have you got a requirement for having large data files? It seems an odd requirement especially as I'd say you have approaching 3GB in one table so you're not too far off getting to 4GB and can the host system handle such files? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
