Re: [HACKERS] A modest proposal: get rid of GUC's USERLIMIT
On Thu, 2004-11-11 at 23:05 -0500, Bruce Momjian wrote: Andrew McMillan wrote: -- Start of PGP signed section. On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote: Andrew McMillan [EMAIL PROTECTED] writes: When tracking down gnarly problems in heavily multi-user applications enabling higher log levels at selective points has the potential to help _a lot_ with diagnostic detail, without smothering you in _every_ detail. Sure. As I pointed out in the other thread, if you want to allow an app to do this, you can make available a SECURITY DEFINER function that performs the desired SET on its behalf. By setting execute permissions on the function and/or including restrictions in the function's code, you can make this as tight or as loose a loophole as you like. So it's certainly possible to do what you want in any case. I think the issue at hand is what's appropriate to provide as hard-wired functionality. That sounds excellent - I hadn't realised that this workaround would be possible, and indeed with this in place that will provide even better control over the facility. OK, here is one vote for the ALTER USER/remove USERLIMIT croud, and you were the person who originally mentioned the problem. You don't think the function creation is hard. Perhaps that's the way to go then. Yes, I agree - it seems good. Also, I don't see that this function would need to be written under stress as ISTR you suggested elsewhere - any analysis like this is going to be following on from review of other statistics - I think it would normally be a well-planned process. Cheers, Andrew. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 How many things I can do without! -- Socrates - signature.asc Description: This is a digitally signed message part
Re: [HACKERS] A modest proposal: get rid of GUC's USERLIMIT
On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote: Andrew McMillan [EMAIL PROTECTED] writes: When tracking down gnarly problems in heavily multi-user applications enabling higher log levels at selective points has the potential to help _a lot_ with diagnostic detail, without smothering you in _every_ detail. Sure. As I pointed out in the other thread, if you want to allow an app to do this, you can make available a SECURITY DEFINER function that performs the desired SET on its behalf. By setting execute permissions on the function and/or including restrictions in the function's code, you can make this as tight or as loose a loophole as you like. So it's certainly possible to do what you want in any case. I think the issue at hand is what's appropriate to provide as hard-wired functionality. That sounds excellent - I hadn't realised that this workaround would be possible, and indeed with this in place that will provide even better control over the facility. Regards, Andrew. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 The secret of being a bore is to say everything -- Voltaire - signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [PATCHES] CVS should die
On Fri, 2004-11-05 at 15:37 -0500, Tom Lane wrote: One of the reasons I'm disinclined to move is that none of the proposed alternatives seem especially, um, mature. AFAIK this project has never had CVS lose any data in the eight years we've used it. I'd want a comparable level of trust in any replacement SCM, and I haven't got it. A very sane reason. I've lost my share of stuff with SVN in trialling it, but we are switching our company over to Arch, which seems to offer significantly more benefits. From our trialling of it, I think it has a more robust and mature repository structure too. Watching the PostgreSQL team developing I would think that Arch would provide much better support for the developers than SVN would. Switching to Arch is more work, but it also offers a lot more benefits - including the opportunity for individuals to maintain their own trees, and be able to work out which patchsets from someone else's tree have not been applied. If anything is going to become the open-source BitKeeper it will be this, I think. Cheers, Andrew. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Planning an election? Call us! - signature.asc Description: This is a digitally signed message part
Re: [HACKERS] LinuxTag wrapup
On Sun, 2004-07-04 at 13:11 +0200, Andreas Pflug wrote: That's right, and initially they will only serve MySQL, but it will be extendable to support any db system. It will be GPL (or licenseable, but since it's a tool and not a platform IMHO GPL is ok). If things work out as they seem, I'd contribute the pgsql stuff. The fact that it is written in Kylix might make this harder. I was looking at it last night, after your post, to see if I could package it for Debian, but that Kylix requirement just kind of killed any ideas I had in that direction, since I've never been able to get the environment to even install on a Debian system :-( Regards, Andrew McMillan. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Whereof one cannot speak, thereon one must remain silent. -- Wittgenstein - signature.asc Description: This is a digitally signed message part
Re: [HACKERS] casting for dates
On Thu, 2001-09-27 at 08:30, Vince Vielhaber wrote: I'm trying to use an integer from a table to add/subtract time in months. IOW: create table foo(nummonths int); select now() - nummonths months; newsroom=# select now() - interval( text(3) || ' months'); ?column? 2001-06-27 08:56:27+12 (1 row) Crude, but hey: it works :-) Cheers, SAndrew. -- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7217MOB: +64(21)635-694OFFICE: +64(4)499-2267 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Link to bug webpage
Vince Vielhaber wrote: What who thinks of what has actually become irrelevant. The following is clear: o No tool will replace the mailing lists o The mailing lists are where discussion will be held o Many/most maintainers have no desire to update bug reports If anyone is interested, I am willing to undertake to be the link between the bugs mailing list and a bugs database. This should allow developers to continue to deal with the mailing list, just CCing a special e-mail address whenever a bug was fixed. I would then take care of finding the appropriate bug(s) in the database and marking them as fixed. There are two large, well-used bugs databases that I am aware of with somewhat different strengths: - The Debian Bug Tracking System - Bugzilla there are a gazillion others, of course, but let's just consider those two for the moment. In some ways the Debian bug tracking system is a closer fit to the way PostgreSQL currently works, since it drives into a mailing list, bug submission is via e-mail and bug control is via e-mail as well. Bugzilla is probably a closer fit in reality, since it is more focused around bugs for a single application. If Bugzilla were installed I'm sure some functionality could be added into it along the lines of the Debian BTS too. Regards, Andrew. -- _ Andrew McMillan, e-mail: Andrew @ catalyst . net . nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64(21)635-694, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Postgresql log analyzer
Gilles DAROLD wrote: Hi all, Here is a first draft generated by a log analyzer for postgres I've wrote today: http://www.samse.fr/GPL/log_report/ In all this html report there is what I'm able to extract minus the statistics. I need to know what people want to see reported to have a powerfull log analyzer, I like what you have there so far. For my own use I would like to see the ability to turn some of these off, and also perhaps a summary page that you would click through to the more detailed reports. The 'query' page is kind of complicated too. Would it be possible to put that into a table layout as well? +---+ |select... | +++++---+ |stat|stat|stat|stat ...| | +++++---+ sort of layout. It would be nice to see an EXPLAIN on the query page, but you would want this to be an option, I guess. I imagine you could do this by getting the EXPLAIN at log analysis time if it isn't in the logs. Cheers, Andrew. -- _ Andrew McMillan, e-mail: Andrew @ catalyst . net . nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64(21)635-694, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709 ---(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] Incomplete idea about views and INSERT...RETURNING
Tom Lane wrote: While this all seems good at first glance, I am wondering just how useful it really would be in practice. The problem is: how do you know which rows to return in the RETURNS query? If you don't qualify the selection then you'll get all the rows in the view, which is surely not what you want. You could restrict the select with clauses like WHERE col1 = NEW.col1, but this is not necessarily going to be efficient, and what's worse it only works for columns that are supplied by the initial insert into the view. For example, suppose an underlying table has a SERIAL primary key that's generated on the fly when you insert to it. The RETURNS query has no way to know what that serial number is, and so no way to select the right row. It seems like the rule author is up against the very same problem that we wanted INSERT RETURNING to solve. So I'm still baffled, unless someone sees a way around that problem. Could we get away with restricting INSERT RETURNING to work only on inserts directly to tables (no ON INSERT DO INSTEAD allowed)? Or is that too much of a kluge? Isn't it likely that the person writing the RULE would want to internally use an INSERT ... RETURNING query and that the RETURNS ... should either use values from that, or use a SELECT clause keyed on values from that? Cheers, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64(27)246-7091, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709 ---(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] SAPDB Open Souce
Bruce Momjian wrote: I downloaded it. The directories are two characters in length, the files are numbers, and it is a mixture of C++, Python, and Pascal. Need I say more. :-) OK, I'll bite: you need to say more. What is it like at handling transactions? What sort of full-text indexing does it have? Can I have transactions within transactions? What sort of tools are available for managing database extents? How compliant is it with the various SQL standards? How does performance compare with PostgreSQL and others? Does it have an extensible type system? Does it have an 'internal' language to compare with PL/SQL or PL/PGSQL? How well does it scale on SMP systems? Can I perform a single query across multiple databases? What performance monitoring tools does it come with? Hell, in a statement like that you don't even indicate if those directories are so-named within the source code, or in an installed data environment. Whichever environment they do apply to, however, I'm sure there are good systems in place for dealing with them. And of course C++, Python and Pascal are all languages with plenty of proponents, so there's no problem with those. Your statement is so light on utility that it persuades me to download it for myself and try it - but that is presumably exactly the effect you were after, wasn't it? Regards, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64(21)635-694, Fax: +64(4)499-5596, Office: +64(4)499-2267xtn709 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] --tuning compile and runtime option (?)
Bruce Momjian wrote: Well, again, I will write a performance tuning article this month, which hopefyully will help people. My recommendation on shared memory is that if you have a machine that is going to be used only for PostgreSQL, the shared memory should be increased to the point where you are not seeing any swap page-ins during normal use. I know you have the kernel buffer cache for all unused memory, but those pages are copied in and out of the PostgreSQL buffer cache for processing, which can be an expensive operation. Now how do you automate something to increase shared memory until there are no page swap-ins under normal use. I think the administrator will have to be involved because a script has no idea what a normal load looks like. The best we could do is to monitor swap-ins as part of the running server and report to the administrator that there is extra memory around that could be used for shared memory. Brilliant. Thanks for that - it's exactly the sort of information / statistics stuff that it is useful to know. I use Progress RDBMS on a few sites. On a Progress database I get this sort of information which can help me tune things: Activity - Sampled at 04/11/01 12:32 for 892:23:25. EventTotal Per SecEventTotal Per Sec Commits 50518 0.0 Undos24 0.0 Record Updates 72407 0.0Record Reads 121294681 37.7 Record Creates 37065 0.0 Record Deletes 19807 0.0 DB Writes 25720 0.0DB Reads 1551040 0.4 BI Writes 14701 0.0BI Reads 14534 0.0 AI Writes 0 0.0 Record Locks645952 0.2Record Waits 0 0.0 Checkpoints62 0.0 Buffers Flushed 13102 0.0 Rec Lock Waits0 %BI Buf Waits 0 %AI Buf Waits 0 % Writes by APW 0 %Writes by BIW 0 %Writes by AIW 0 % Buffer Hits 16 % DB Size 96 MB BI Size3192 KAI Size 0 K FR chain 0 blocks RM chain 1 blocks Shared Memory 29864 KSegments 1 8 Servers, 7 Users (0 Local, 7 Remote, 0 Batch),0 Apws Or, for a more reasonable length of sample: Activity - Sampled at 04/11/01 12:42 for 0:09:26. EventTotal Per SecEventTotal Per Sec Commits14 0.0 Undos 0 0.0 Record Updates 7 0.0Record Reads 90488159.8 Record Creates 1 0.0 Record Deletes 0 0.0 DB Writes38 0.0DB Reads 1636 2.8 BI Writes 5 0.0BI Reads 0 0.0 AI Writes 0 0.0 Record Locks69 0.1Record Waits 0 0.0 Checkpoints 0 0.0 Buffers Flushed 0 0.0 Rec Lock Waits0 %BI Buf Waits 0 %AI Buf Waits 0 % Writes by APW 0 %Writes by BIW 0 %Writes by AIW 0 % Buffer Hits 99 % DB Size 96 MB BI Size3192 KAI Size 0 K FR chain 0 blocks RM chain 1 blocks Shared Memory 29864 KSegments 1 8 Servers, 9 Users (0 Local, 9 Remote, 0 Batch),0 Apws I find this is quite a straightforward and useful set of statistics. Just having this sort of functionality easily available gets me used to the sorts of numbers I can expect in different hardware environments. It is then simple to conduct basic tuning by running reports (or other operations) and seeing the sorts of numbers you get for the sample period. Of course Progress has a bunch more stuff you can tune, including separate processes for asynchronously writing database pages, or their after-image and before-image files. I don't have any databases that get that arcane though, hence the APW, BIW and AIW statistics are zero above. Regards, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Speaking of Indexing... (Text indexing)
Poet/Joshua Drake wrote: Good day, I've been experimenting a bit with Full Text Indexing in PostgreSQL. I have found several conflicting sites various places on the net pertaining to whether or not PostgreSQL supports FTI, and I was hoping I could find an authoritative answer here - I tried searching the website's archives, but the search seems to be having some problems. At any rate, I am running a CVS snapshot of 7.1, and I have been trying to create a full text index on a series of resumes. Some of these exceed 8k in size, which is no longer a storage problem of course with 7.1, but I seem to have run into the wicked 8k once again. Specifically: ERROR: index_formtuple: data takes 9344 bytes, max is 8191 Furthermore, after trying to just index on a 8191-character long substring of the resume, I run into the following: ERROR: btree: index item size 3948 exceeds maximum 2713 The only way I could actually get the index created was to substring the body of the resumes down to 2k. I also later tried using HASH rather than BTREE, which worked, but none of these solutions really appreciably increased performance in the way we were hoping. Are these known and accepted limitations of the current 7.1 implementation, or am I doing something terribly wrong? ;) On Tue, 10 Apr 2001, Thomas Lockhart wrote: You need to use the 'contrib' code for full-text indexing. The indexing you are trying to do with that is just using the whole content of the string as the index value. Close to useless. The contrib code is in contrib/fulltextindex. I have a hacked version of that which changes it to keyword indexing, if you're interested. Regards, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267 ---(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] Re: xlog loose ends, continued
Tom Lane wrote: "Mikheev, Vadim" [EMAIL PROTECTED] writes: That'll be true in any case, unless we refuse to start up at all upon detecting xlog corruption (which doesn't seem like the way to fly). Not sure what we can do about that. What I would refuse in the event of log corruption is continuing normal database operations. Hmm. We could do that if we had some notion of a read-only operating mode, perhaps. But we don't have one now and I don't want to add it for 7.1. Can we agree to look at this more for 7.2? I'd love to see PostgreSQL have a read-only mode of some kind that let enquiry function against a possibly otherwise corrupted database, without the stress of worrying that you might be making things worse. I know other DB servers that have this sort of thing, and it has been a life-saver for me on occasion to allow critical information to be extracted before you nuke it all and start over. Cheers, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267 ---(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] How to shoot yourself in the foot: kill -9 postmaster
Vadim Mikheev wrote: Nevertheless, subj is rised. BTW, does anybody know results of kill -9 in Oracle/Informix/etc? Just curious -:) Progress has no problem with it that I have ever seen. Regards, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] mailing list messages
Bruce Momjian wrote: I wonder if the new Tips at the bottom of email messages can be enabled for users during their first 30 days of mailing list subscription, then not appear? What about having some basic _PostgreSQL_ tips in there? This would be especially cute for -novice, I think. We must all be able to come up with 100 or so little one or two liners about PostgreSQL can't we? Just a thought, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267 ---(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] Upper limit on number of buffers?
Michael J Schout wrote: On Sun, 24 Dec 2000, Joe Conway wrote: On redhat 6.2 I know that you can use /etc/sysctl.conf to do this as well. Just add this to /etc/sysctl.conf. kernel.shmall = 134217728 kernel.shmmax = 134217728 After this, your tunables will be restored every time that the system boots. I can confirm that that applies under Debian as well, although there is also some stuff in the startup script for Debian to configure files max specifically for PostgreSQL. Cheers, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
Re: [HACKERS] Why vacuum?
Tim Allen wrote: On Thu, 14 Dec 2000, Christopher Kings-Lynne wrote: Plenty of other databases need to be 'vacuumed'. For instance, if you have an ms access database with 5 MB of data in it, and then delete all the data, leaving only the forms, etc - you will be left with a 5MB mdb file still! If you then run 'Compact Database' (which is another word for 'vacuum'), the mdb file will be reduced down to 500k... Ooh... Hope MS Access isn't going to be taken seriously as a benchmark here :-). The same is also true of MapInfo, by the way, but I'm not holding that up as a benchmark either ;-). :-) I think that the non-overwriting storage manager actually bought a lot more for PostgreSQL than it does for MS Access. In earlier versions of PostgreSQL it was possible to "time travel" your database and so run your query agains the database as it was at a particular time / date. This advanced feature turns out to be useful in very few situations, and is very expensive in terms of storage. Still, "if it works, don't fix it" also applies. The PostgreSQL storage manager is quite efficient as it is now, and most of us do have quiet periods when we can safely vacuum the database, which is why it has had to wait until now. This will be quite a big change for 7.2, and getting the performance right will no doubt challenge these hackers whom we are all greatly indebted to. Cheers, Andrew. -- _____ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
Re: [HACKERS] Import text field
Carlos Jacobs wrote: Hi: I have a MS Access database with tables containing TEXT fields. I need import that info in a postgres 7 table. How to do it? If I use copy from, dont work. I have a perl program which will import this sort of multi-line CSV data that is not handled by the COPY ... DELIMITER ... sort of mechanism in PostgreSQL. E-mail me privately if you want a copy. Regards, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
Re: [HACKERS] Full text indexing (Question/request)
Bruce Momjian wrote: See contrib/fulltextindex. An easy answer, but not a very good solution in the real world. contrib/fulltextindex requires you to jump through hoops in developing queries to retrieve your data. It's also very space-inefficient in that a table with a fulltextindex on a field needs another table with a complete set of values for that field, as well as any substrings of that field, and then it wants two indexes on that table. Add that up! It would be nice to see a true index which was full text. It would be nice to see a true index which allowed an individual field to index to many entries through a function interface. This would straightforwardly allow people to create their own simple functions to perform full-text, keyword or other indexing schemes quite simply. It naively appears to me that the function interface is moving closer to achieving this with the enhancements in 7.1 to the use of setof() returns combined with the earlier enhancement to indexing on function results. If a function fulltextindex(text) returned a setof() the substrings in its text argument, how hard will it be to index on that return value and allow WHERE field=fulltextindex('substring') to use that index? Of course such a fulltextindex() function would have to know not to do any processing on the string when called in the second situation. Is it possible for functions to do this sort of trick? It seems a bit beyond the pale! I would _love_ to see full-text or keyword indexing natively in PostgreSQL. Regards, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267