Re: [HACKERS] psql feature thought
Tom Lane wrote: Quite aside from the compatibility and how-useful-is-it-really arguments, I think this'd be a bad idea in the abstract. SQL is not one of those languages that assigns semantic significance to the shape of whitespace [1]. We should NOT introduce any such concept into psql, because it'd fundamentally break the lexical structure of the language. +1 Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] psql feature thought
Tom Lane wrote: Michael Glaesemann <[EMAIL PROTECTED]> writes: What use case are you envisioning? Just saving ... *counts ... 14 keystrokes in this case? (I'm not saying there *isn't* a use case. I just don't see a big benefit here.) Quite aside from the compatibility and how-useful-is-it-really arguments, I think this'd be a bad idea in the abstract. SQL is not one of those languages that assigns semantic significance to the shape of whitespace [1]. We should NOT introduce any such concept into psql, because it'd fundamentally break the lexical structure of the language. To take just one example of the difficulties you'd get into, consider select 1; select 2; select 3; How many transactions is that exactly? And on what grounds are you deciding? Well, my use case was that I was doing a quick batch process that had about half a dozen statements in a row that were insert/update/delete. If I had forgotten to type by begin; , I could have a problem with consistency of various things if one in the middle failed. I understand that this is completely a sugar feature and I am not actually arguing for as much as I am saying, "Hey this was an interesting thought". (No, I don't like python. Why do you ask? ;-)) Heh... I happen to like Python quite a bit: But even in Python you can do: select 1; select 2; select 3; From an implementation perspective I would suggest that if the line has a carriage return then it doesn't behave as a single transaction.. e.g; BEGIN; SELECT 1; SELECT 2; COMMIT; Would be the same as: SELECT 1; SELECT 2; But not the same as: SELECT 1; SELECT 2; Egad... forget I even mentioned it... These looks like a whole lot of pgsql-novice posts having to be answerd. Thanks for entertaining me though. Sincerely, Joshua D. Drake regards, tom lane [1] Mostly. There's the infamous continued-string-literal construct... -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql feature thought
Michael Glaesemann <[EMAIL PROTECTED]> writes: > What use case are you envisioning? Just saving ... *counts ... 14 > keystrokes in this case? (I'm not saying there *isn't* a use case. I > just don't see a big benefit here.) Quite aside from the compatibility and how-useful-is-it-really arguments, I think this'd be a bad idea in the abstract. SQL is not one of those languages that assigns semantic significance to the shape of whitespace [1]. We should NOT introduce any such concept into psql, because it'd fundamentally break the lexical structure of the language. To take just one example of the difficulties you'd get into, consider select 1; select 2; select 3; How many transactions is that exactly? And on what grounds are you deciding? (No, I don't like python. Why do you ask? ;-)) regards, tom lane [1] Mostly. There's the infamous continued-string-literal construct... ---(end of broadcast)--- TIP 1: 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] psql feature thought
On May 16, 2006, at 13:42 , Joshua D. Drake wrote: Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: What if single line statements that were seperated by ; within psql were implicitly within a transaction? Seems like this would risk breaking a lot of scripts. I wouldn't assume that it would be a default feature of course. Perhaps something that can be passed from the command line or from a .psqlrc ? What use case are you envisioning? Just saving ... *counts ... 14 keystrokes in this case? (I'm not saying there *isn't* a use case. I just don't see a big benefit here.) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] psql feature thought
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: What if single line statements that were seperated by ; within psql were implicitly within a transaction? Seems like this would risk breaking a lot of scripts. I wouldn't assume that it would be a default feature of course. Perhaps something that can be passed from the command line or from a .psqlrc ? Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Creating a Pseudocolumn
"Jonah H. Harris" <[EMAIL PROTECTED]> writes: > I'm reworking hierarchical queries and am adding a LEVEL pseudocolumn. > As it's a totally calculated attribute, what's the best way to handle > it keeping in mind that LEVEL is only used in a hierarchical query? Perhaps you should start by explaining what the heck you're talking about ;-). I can find nothing in the SQL2003 spec that sounds anything like a LEVEL function or pseudocolumn. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Why use " != "
As Qingqing Zhou said, i misread "exist". Thanks. - Original Message - From: "Gurjeet Singh" <[EMAIL PROTECTED]> To: "ipig" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, May 16, 2006 11:36 AM Subject: Re: [HACKERS] Why use " != " It done so, because InitProcess() is supposed to be called only once per backend, because it allocates a PGPROC from a LIST OF free PGPROCs. So with this test, and calling elog( ERROR, ... ) we are stopping the caller from doing a second initialization for this backend. On 5/16/06, ipig <[EMAIL PROTECTED]> wrote: > > > Hi, > >in postgresql-8.1.3/src/backend/storage/lmgr/proc.c >there are 2 function InitProcess and InitDummyProcess, both use > " if (MyProc != NULL) > elog(ERROR, "you already exist"); " (line 215 and 315) > >why use " != " ? > > Best regards. > > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Compression and on-disk sorting
[EMAIL PROTECTED] wrote: > The real question - and I believe Tom and others have correctly harped > on it in the past is - is it worth it? Until somebody actually pulls > up their sleeves, invests a month or more of their life to it, and > does it, we really won't know. And even then, the cost of maintenance > would have to be considered. Who is going to keep up-to-date on > theoretical storage models? What happens when generic file system > levels again surpass the first attempt? > > Personally, I believe it would be worth it - but only to a few. And > these most of these few are likely using Oracle. So, no gain unless > you can convince them to switch back... :-) We do know that the benefit for commercial databases that use raw and file system storage is that raw storage is only a few percentage points faster. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Compression and on-disk sorting
Oh come on, Sorry to troll but this is too easy. On 5/15/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: You guys have to kill your Windows hate - in jest or otherwise. It's zealous, and blinding. [snip] Why would it be assumed, that a file system designed for use from a desktop, would be optimal at all for database style loads? It wouldn't. Why would someone use a desktop OS for a database? Why would you call the result of answering the previous question zealous and blinding? PG's use of the OS's block cache is a good move because it makes PG tend to 'just work' where the alternatives require non-trivial tuning (sizing their caches not to push the OS into swap). The advantages of this are great enough that if additional smarts are needed in the OS cache it might well be worth the work to add it there and to ask for new fadvise flags to get the desired behavior. That's something that would be easy enough for a dedicated hacker to do, or easy enough to collaborate with the OS developers if the need could be demonstrated clearly enough. What reasonable OS couldn't you do that with? :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] BUG #2429: Explain does not report object's schema
"Gurjeet Singh" <[EMAIL PROTECTED]> writes: > I don't think Cristiano is asking for the schema_name in the > EXPLAIN o/p. The request is for the table ALIASes to be shown in the > o/p, which makes more sense than schema_name+table_name, since the > same table can be used in the same query more than once. But EXPLAIN has always shown the aliases. Possibly a reasonable compromise would be for EXPLAIN to act like rule reverse-listing does, that is, consult the schema search path and print a qualified name only if the table wouldn't be found without it. regards, tom lane ---(end of broadcast)--- TIP 1: 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] [BUGS] BUG #2429: Explain does not report object's schema
I don't think Cristiano is asking for the schema_name in the EXPLAIN o/p. The request is for the table ALIASes to be shown in the o/p, which makes more sense than schema_name+table_name, since the same table can be used in the same query more than once. Gurjeet. On 5/15/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: Moving to -hackers On Wed, May 10, 2006 at 11:26:30AM -0300, Cristiano Duarte wrote: > > Don't know whether this would help in your situation, but you can make the > > output of EXPLAIN disambiguous by using table aliases: > > > > EXPLAIN > > SELECT * > >FROM schema1.mytable AS mt1, schema2.mytable AS mt2 > > WHERE 1=0 > > > > (The AS keyword is optional.) > > > > The aliases will be included in the query plan output. > > > Thanks for the notice, but to do this, I would have to raise errors on > queries having tables with the same name, which is not what I meant. > > But, if there is no other way, I'll do it. So you actually write out schemaname.tablename.fieldname for every field in the SELECT clause? Yikes! In any case, I agree that there should be a way to have EXPLAIN (and other things) show schema names. But since this isn't an itch that any of the developers have felt like scratching, it's unlikely this will happen unless someone sponsors it. :/ Unless folks think it's specifically a bad idea, can we at least get it on the TODO so if someone's bored it might get done? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Compression and on-disk sorting
On Mon, May 15, 2006 at 05:42:53PM -0700, Joshua D. Drake wrote: > >Windows98? No, those decisions predate any thought of running Oracle > >on Windows, probably by decades. But I think the thought process was > >about as above whenever they did make it; they were running on some > >pretty stupid OSes way back when. > Windows XP? > runs You guys have to kill your Windows hate - in jest or otherwise. It's zealous, and blinding. I'm picking on you Joshua, only because your message is the one I saw last. Sorry... Writing your own block caching layer can make a lot of sense. Why would it be assumed, that a file system designed for use from a desktop, would be optimal at all for database style loads? Why would it be assumed that a file system to be used for many different smaller files would be optimal at all for database style loads? It's always going to be true, that the more specific the requirements, the more highly optimized one can design a system. The Linux block caching layer, or file system layout can be beat *for sure* for database loads. The real question - and I believe Tom and others have correctly harped on it in the past is - is it worth it? Until somebody actually pulls up their sleeves, invests a month or more of their life to it, and does it, we really won't know. And even then, the cost of maintenance would have to be considered. Who is going to keep up-to-date on theoretical storage models? What happens when generic file system levels again surpass the first attempt? Personally, I believe it would be worth it - but only to a few. And these most of these few are likely using Oracle. So, no gain unless you can convince them to switch back... :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Why use " != "
It done so, because InitProcess() is supposed to be called only once per backend, because it allocates a PGPROC from a LIST OF free PGPROCs. So with this test, and calling elog( ERROR, ... ) we are stopping the caller from doing a second initialization for this backend. On 5/16/06, ipig <[EMAIL PROTECTED]> wrote: Hi, in postgresql-8.1.3/src/backend/storage/lmgr/proc.c there are 2 function InitProcess and InitDummyProcess, both use " if (MyProc != NULL) elog(ERROR, "you already exist"); " (line 215 and 315) why use " != " ? Best regards. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Why use " != "
""ipig"" <[EMAIL PROTECTED]> wrote > >in postgresql-8.1.3/src/backend/storage/lmgr/proc.c >there are 2 function InitProcess and InitDummyProcess, both use > " if (MyProc != NULL) > elog(ERROR, "you already exist"); " (line 215 and 315) > >why use " != " ? > I guess you misread "exist" to "exit"? Try to change "!=" to whatever you think of, and see what will happen. Regards, Qingqing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] psql feature thought
""Joshua D. Drake"" <[EMAIL PROTECTED]> wrote > > What if single line statements that were seperated by ; within psql were > implicitly within a transaction? > > E.g; > > postgres=# select * from foo; update foo set bar = 'baz'; delete from bing; > > Would be a single transaction ? The begin/commit would be implicit. > This is not in a single transaction currently. This is not a back compatible behavior. Also, I am not sure how difficult to implement it in current psql code. Think of this case: begin; select; update; delete; end; Line 2 is already in a transaction, so psql must be able to tell it (maybe we already be able to do so?). Not to speak of the SAVEPOINT control points. Regards, Qingqing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] psql feature thought
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > What if single line statements that were seperated by ; within psql were > implicitly within a transaction? Seems like this would risk breaking a lot of scripts. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] psql feature thought
Hello, I was dinking around wand came across something that may (or may not be useful). What if single line statements that were seperated by ; within psql were implicitly within a transaction? E.g; postgres=# select * from foo; update foo set bar = 'baz'; delete from bing; Would be a single transaction ? The begin/commit would be implicit. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] audit table containing Select statements submitted
Having tinkered a little with PQA, yes, actually. The issue is that the "message" text can easily be multi-line and contain a vast variety of special characters. The issue is figuring out where the prefix, the tag and the message begin and end. And our text log format makes that a PITA. Try pgfouine... ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Why use " != "
Hi, in postgresql-8.1.3/src/backend/storage/lmgr/proc.c there are 2 function InitProcess and InitDummyProcess, both use " if (MyProc != NULL) elog(ERROR, "you already exist"); " (line 215 and 315) why use " != " ? Best regards.
Re: [HACKERS] Compression and on-disk sorting
Tom Lane wrote: Ron Mayer <[EMAIL PROTECTED]> writes: I think the real reason Oracle and others practically re-wrote their own VM-system and filesystems is that at the time it was important for them to run under Windows98; where it was rather easy to write better filesystems than your customer's OS was bundled with. Windows98? No, those decisions predate any thought of running Oracle on Windows, probably by decades. But I think the thought process was about as above whenever they did make it; they were running on some pretty stupid OSes way back when. Windows XP? runs regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Compression and on-disk sorting
Ron Mayer <[EMAIL PROTECTED]> writes: > I think the real reason Oracle and others practically re-wrote > their own VM-system and filesystems is that at the time it was > important for them to run under Windows98; where it was rather > easy to write better filesystems than your customer's OS was > bundled with. Windows98? No, those decisions predate any thought of running Oracle on Windows, probably by decades. But I think the thought process was about as above whenever they did make it; they were running on some pretty stupid OSes way back when. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Compression and on-disk sorting
Jim C. Nasby wrote: There's an fadvise that tells the OS to compress the data if it actually makes it to disk? Compressed-filesystem extension (like e2compr, and I think either Fat or NTFS) can do that. I think the reasons against adding this feature to postgresql are largely the same as the reasons why compressed filesystems aren't very popular. Has anyone tried running postgresql on a compressing file-system? I'd expect the penalties to outweigh the benefits (or they'd be more common); but if it gives impressive results, it might add weight to this feature idea. Ron M I think the real reason Oracle and others practically re-wrote their own VM-system and filesystems is that at the time it was important for them to run under Windows98; where it was rather easy to write better filesystems than your customer's OS was bundled with. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Iterating generator from C (PostgreSQL's
Ühel kenal päeval, E, 2006-05-15 kell 17:21, kirjutas Tom Lane: > Hannu Krosing <[EMAIL PROTECTED]> writes: > >> Sven Suursoho wrote: > >>> As for testing in actual pl/python build environment, we had objections > >>> from > >>> leading postgresql Tom Lane that even if we do test it at build time, > >>> a determined DBA may substitute a buggy python.so later and still crash > >>> her DB instance. > > The above is a straw-man depiction of my point. Sure ;) > What I said was that just > because python is up-to-date on the system where plpython is *compiled* > does not mean it'll be up-to-date on the system where plpython is *used*. Would running an external program at pl init time and testing for its crash status be a good broken lib test for plpython ? > With the increasing popularity of prebuilt packages (rpm, deb, etc), > I think it's folly to use a build-time check for this. I guess most packaging systems can require some minimal version of dependencies. And in general newer versions are less buggy than old ones. So i guess that some combination of build-time/run-time tests, documentation and packager education should take care of 99% of concerns ? Hopefully we can just ignore the "determined DBA" failure mode and move forward with including the patch. Or do you think that trying to hack in the extra python frame to all/some builds to ward of potentially broken libs would still be something to go for ? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents. ---(end of broadcast)--- TIP 1: 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] Iterating generator from C (PostgreSQL's pl/python RETUN
Hannu Krosing <[EMAIL PROTECTED]> writes: >> Sven Suursoho wrote: >>> As for testing in actual pl/python build environment, we had objections >>> from >>> leading postgresql Tom Lane that even if we do test it at build time, >>> a determined DBA may substitute a buggy python.so later and still crash her >>> DB instance. The above is a straw-man depiction of my point. What I said was that just because python is up-to-date on the system where plpython is *compiled* does not mean it'll be up-to-date on the system where plpython is *used*. With the increasing popularity of prebuilt packages (rpm, deb, etc), I think it's folly to use a build-time check for this. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Iterating generator from C (PostgreSQL's pl/python RETUN
Sorry for cross-posting, but this IS a cross-platform issue. Christian Tismer tismer at stackless.com wrote: > Sven Suursoho wrote: > > >>> Is there any way to rewrite following program to handle returned > >>> generator without hitting this bug? > > The only way I can think of getting around this is to make > sure that there is always a running python frame. > This would be possible if you can control how the > extension is called. What would be the easiest way to hold a "always running" python frame ? The actual calls to iterator come through a pl/python framework, which can hold some state - like some inter-call dictionaries - so keeping also a simple outer frame there may be possible. What danger (apart from general uglyness) may lurk there in keeping this frame ? > > Unfortunately, this is not an option because I can't control used > > environment: I'm trying to improve PostgreSQL's stored procedure > > language PL/Python and this software can be used everywhere. > > Then there is no other way than to report the bug, get the > fix back-ported and nagging the PL/Python maintainers > to update things after the fix. Unfortunately there is no 'after the fix', as the fix must happen outside postgresql/plpython development and should also run on oldish systems. The reason we want to get some workaround for that bug is the need to overcome resistance from core postgreSQL developers to inclusion of our plpython enchancements to postgreSQLs bundled plpython due to one specific use of our generic enchancement (using a built-in generator, usually a function with yield) on buggy RedHat's bundled plpython.so causing crashes. Also, PL/Python has been in minimal maintenance mode for many years, with basically only immediate bugfixing going on. We at Skype (that is currently Sven Suursoho) are the first ones doing active development on it after Andrew Bosma dropped development many years ago once he got just the very basic stuff working. > Also a test should be added which is probably missing since a while. Test where ? In python.so build process, so that RedHat will spot it and fix their RPM builds ? As for testing in actual pl/python build environment, we had objections from leading postgresql Tom Lane that even if we do test it at build time, a determined DBA may substitute a buggy python.so later and still crash her DB instance. Do you have any ideas, how to test for buggy asserts in python runtime environment without actually triggering the assert ? Then we could introduce some (uglish) special handling for generators in pl/pythons iterator. > I'd put a warning somewhere that generators are broken in > debug mode, file an issue as well, but avoid trying to hack > around this. It would make the bug even more resistent :-) We have been trying to advocate such approach, but so far with modest results :( -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Compression and on-disk sorting
On Mon, May 15, 2006 at 10:09:47PM +0200, Martijn van Oosterhout wrote: > > In this case the problem is that we want to tell the OS "Hey, if this > > stuff is actually going to go out to the spindles then compress it. And > > by the way, we won't be doing any random access on it, either." But > > AFAIK there's no option like that in fopen... :) > > posix_fadvise(). We don't use it and many OSes don't support it, but it > is there. There's an fadvise that tells the OS to compress the data if it actually makes it to disk? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Compression and on-disk sorting
On Mon, May 15, 2006 at 03:02:07PM -0500, Jim C. Nasby wrote: > The problem is that it seems like there's never enough ability to clue > the OS in on what the application is trying to accomplish. For a long > time we didn't have a background writer, because the OS should be able > to flush things out on it's own before checkpoint. Now there's talk of a > background reader, because backends keep stalling on waiting on disk IO. Hmm, I thought the background writer was created to reduce the cost of checkpoint which has to write out modified pages in the buffers. The background writer tries to keep the number of dirty pages down. I don't know about Oracle, but with or without an OS, backends are going to block on I/O and you'd still need a background reader in both cases for precisely the same reason. We might be able to do without a background reader if we did asyncronous i/o, but that can't be done portably. > In this case the problem is that we want to tell the OS "Hey, if this > stuff is actually going to go out to the spindles then compress it. And > by the way, we won't be doing any random access on it, either." But > AFAIK there's no option like that in fopen... :) posix_fadvise(). We don't use it and many OSes don't support it, but it is there. The O/S is some overhead, but the benefits outweigh the costs IMHO. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Compression and on-disk sorting
On Mon, May 15, 2006 at 03:44:50PM -0400, Andrew Dunstan wrote: > Jim C. Nasby wrote: > >On Mon, May 15, 2006 at 02:18:03PM -0400, Tom Lane wrote: > > > >>"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > >> > >>>A recent post Tom made in -bugs about how bad performance would be if we > >>>spilled after-commit triggers to disk got me thinking... There are > >>>several operations the database performs that potentially spill to disk. > >>>Given that any time that happens we end up caring much less about CPU > >>>usage and much more about disk IO, for any of these cases that use > >>>non-random access, compressing the data before sending it to disk would > >>>potentially be a sizeable win. > >>> > >>Note however that what the code thinks is a spill to disk and what > >>actually involves disk I/O are two different things. If you think > >>of it as a spill to kernel disk cache then the attraction is a lot > >>weaker... > >> > > > >I'm really starting to see why other databases want the OS out of their > >way... > > > > Some of it is pure NIH syndrome. I recently heard of some tests done by > a major DB team that showed their finely crafted raw file system stuff > performing at best a few percent better than a standard file system, and > sometimes worse. I have often heard of the supposed benefits of our > being able to go behind the OS, but I am very dubious about it. What > makes people think that we could do any better than the OS guys? The problem is that it seems like there's never enough ability to clue the OS in on what the application is trying to accomplish. For a long time we didn't have a background writer, because the OS should be able to flush things out on it's own before checkpoint. Now there's talk of a background reader, because backends keep stalling on waiting on disk IO. In this case the problem is that we want to tell the OS "Hey, if this stuff is actually going to go out to the spindles then compress it. And by the way, we won't be doing any random access on it, either." But AFAIK there's no option like that in fopen... :) I agree, when it comes to base-level stuff like how to actually put the data on the physical media, there's not much to be gained in this day and age by using RAW storage, and in fact Oracle hasn't favored RAW for quite some time. Every DBA I've ever talked to says that the only reason to use RAW is if you're trying to eek every last ounce of performance out of the hardware that you can, which for 99.99% of installs makes absolutely no sense. But, there's a big range between writing your own filesystem and assuming that the OS should just handle everything for you. I think a lot of commercial databases lean too far towards not trusting the OS (which is understandable to a degree, givin how much OSes have improved), while in some areas I think we still rely too much on the OS (like read-ahead). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Compression and on-disk sorting
Jim C. Nasby wrote: On Mon, May 15, 2006 at 02:18:03PM -0400, Tom Lane wrote: "Jim C. Nasby" <[EMAIL PROTECTED]> writes: A recent post Tom made in -bugs about how bad performance would be if we spilled after-commit triggers to disk got me thinking... There are several operations the database performs that potentially spill to disk. Given that any time that happens we end up caring much less about CPU usage and much more about disk IO, for any of these cases that use non-random access, compressing the data before sending it to disk would potentially be a sizeable win. Note however that what the code thinks is a spill to disk and what actually involves disk I/O are two different things. If you think of it as a spill to kernel disk cache then the attraction is a lot weaker... I'm really starting to see why other databases want the OS out of their way... Some of it is pure NIH syndrome. I recently heard of some tests done by a major DB team that showed their finely crafted raw file system stuff performing at best a few percent better than a standard file system, and sometimes worse. I have often heard of the supposed benefits of our being able to go behind the OS, but I am very dubious about it. What makes people think that we could do any better than the OS guys? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Compression and on-disk sorting
On Mon, May 15, 2006 at 02:18:03PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > A recent post Tom made in -bugs about how bad performance would be if we > > spilled after-commit triggers to disk got me thinking... There are > > several operations the database performs that potentially spill to disk. > > Given that any time that happens we end up caring much less about CPU > > usage and much more about disk IO, for any of these cases that use > > non-random access, compressing the data before sending it to disk would > > potentially be a sizeable win. > > Note however that what the code thinks is a spill to disk and what > actually involves disk I/O are two different things. If you think > of it as a spill to kernel disk cache then the attraction is a lot > weaker... I'm really starting to see why other databases want the OS out of their way... I guess at this point the best we could do would be to have a configurable limit for when compression started. The first X number of bytes go out uncompressed, everything after that is compressed. I don't know of any technical reason why you couldn't switch in the middle of a file, so long as you knew exactly where you switched. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] audit table containing Select statements submitted
On Mon, May 15, 2006 at 12:37:34PM -0400, Douglas McNaught wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > On Mon, May 15, 2006 at 10:55:43AM -0500, Hogan, James F. Jr. wrote: > >> Only specific tables. > >> > >> Of the 150 plus existing there are only 8 or 10 that hold sensitive > >> data. > > > > In that case I'd definately go with the suggestion of creating access > > functions and logging to a table from within them. Just make sure to > > mark the functions as volatile. > > But what if the user calls the access function, sees the data, then > issues a ROLLBACK? The audit record would be rolled back as well (as > Tom pointed out earlier). > > You could use dblink to log to a separate audit database, I suppose. Ooops, forgot about that. Yeah, you'd have to use dblink. If it works with pgpool performance might not be too horrid. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Compression and on-disk sorting
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > A recent post Tom made in -bugs about how bad performance would be if we > spilled after-commit triggers to disk got me thinking... There are > several operations the database performs that potentially spill to disk. > Given that any time that happens we end up caring much less about CPU > usage and much more about disk IO, for any of these cases that use > non-random access, compressing the data before sending it to disk would > potentially be a sizeable win. Note however that what the code thinks is a spill to disk and what actually involves disk I/O are two different things. If you think of it as a spill to kernel disk cache then the attraction is a lot weaker... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [TODO] Allow commenting of variables ...
On Wed, May 10, 2006 at 08:46:41PM +0200, Zdenek Kotala wrote: > I would like to implement following item from TODO list: > * Allow commenting of variables in postgresql.conf to restore them to > defaults. Currently, if a variable is commented out, it keeps the > previous uncommented value until a server restarted. > Does anybody work on it? I have a patch that seems to work, it could need some more testing however. I'll send it to you per PM. Joachim -- Joachim Wieland [EMAIL PROTECTED] C/ Usandizaga 12 1°B ICQ: 37225940 20002 Donostia / San Sebastian (Spain) GPG key available ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] BUG #2429: Explain does not report object's schema
Moving to -hackers On Wed, May 10, 2006 at 11:26:30AM -0300, Cristiano Duarte wrote: > > Don't know whether this would help in your situation, but you can make the > > output of EXPLAIN disambiguous by using table aliases: > > > > EXPLAIN > > SELECT * > >FROM schema1.mytable AS mt1, schema2.mytable AS mt2 > > WHERE 1=0 > > > > (The AS keyword is optional.) > > > > The aliases will be included in the query plan output. > > > Thanks for the notice, but to do this, I would have to raise errors on > queries having tables with the same name, which is not what I meant. > > But, if there is no other way, I'll do it. So you actually write out schemaname.tablename.fieldname for every field in the SELECT clause? Yikes! In any case, I agree that there should be a way to have EXPLAIN (and other things) show schema names. But since this isn't an itch that any of the developers have felt like scratching, it's unlikely this will happen unless someone sponsors it. :/ Unless folks think it's specifically a bad idea, can we at least get it on the TODO so if someone's bored it might get done? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] Compression and on-disk sorting
A recent post Tom made in -bugs about how bad performance would be if we spilled after-commit triggers to disk got me thinking... There are several operations the database performs that potentially spill to disk. Given that any time that happens we end up caring much less about CPU usage and much more about disk IO, for any of these cases that use non-random access, compressing the data before sending it to disk would potentially be a sizeable win. On-disk sorts are the most obvious candidate, but I suspect there's others. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] audit table containing Select statements submitted
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Mon, May 15, 2006 at 10:55:43AM -0500, Hogan, James F. Jr. wrote: >> Only specific tables. >> >> Of the 150 plus existing there are only 8 or 10 that hold sensitive >> data. > > In that case I'd definately go with the suggestion of creating access > functions and logging to a table from within them. Just make sure to > mark the functions as volatile. But what if the user calls the access function, sees the data, then issues a ROLLBACK? The audit record would be rolled back as well (as Tom pointed out earlier). You could use dblink to log to a separate audit database, I suppose. -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] audit table containing Select statements submitted
On Mon, May 15, 2006 at 10:55:43AM -0500, Hogan, James F. Jr. wrote: > Only specific tables. > > Of the 150 plus existing there are only 8 or 10 that hold sensitive > data. In that case I'd definately go with the suggestion of creating access functions and logging to a table from within them. Just make sure to mark the functions as volatile. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] audit table containing Select statements submitted
Only specific tables. Of the 150 plus existing there are only 8 or 10 that hold sensitive data. This will grow over time but will always be in the minority. -Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED] Sent: Monday, May 15, 2006 10:42 AM To: Hogan, James F. Jr. Cc: Tom Lane; Joshua D. Drake; josh@agliodbs.com; Andrew Dunstan; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] audit table containing Select statements submitted How do you hope to avoid this overhead when you're looking to track information on every single SELECT statement? Or were you looking to only log access to some specific tables? On Mon, May 15, 2006 at 09:38:54AM -0500, Hogan, James F. Jr. wrote: > Thank you all for the effort you put into response. > > The biggest thing I want to avoid isn't so much having to parse through > the log files but to avoid turning on such extensive logging altogether. > > I am not sure what kind of additional load logging to this extent may > add. > > Looks like I am not going to have much in the way of alternative. > > Maybe some day. > > Good news is that most access is via Web Interface and I capture most > activity that way. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Creating a Pseudocolumn
On 5/15/06, Tom Lane <[EMAIL PROTECTED]> wrote: This could only work if LEVEL is guaranteed to have one and only one value per tuple. I'm not too sure about the spec but it seems like that'd probably fall down in join situations. Yes, this was another thing handled by Evgen's patch... FakeVar could only exist once and was ignored in joins. So, basically (like rownum), I have to create a pseudocolumn that isn't bound by joins. Do you know of any way around this? -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ "SQL will be the COBOL in the year 2020" -Mike Stonebraker ---(end of broadcast)--- TIP 1: 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] Creating a Pseudocolumn
Martijn van Oosterhout writes: > All you need to do is decide where you are going to store the level > number and add it as a system attribute (negative attribute number). This could only work if LEVEL is guaranteed to have one and only one value per tuple. I'm not too sure about the spec but it seems like that'd probably fall down in join situations. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] audit table containing Select statements submitted
How do you hope to avoid this overhead when you're looking to track information on every single SELECT statement? Or were you looking to only log access to some specific tables? On Mon, May 15, 2006 at 09:38:54AM -0500, Hogan, James F. Jr. wrote: > Thank you all for the effort you put into response. > > The biggest thing I want to avoid isn't so much having to parse through > the log files but to avoid turning on such extensive logging altogether. > > I am not sure what kind of additional load logging to this extent may > add. > > Looks like I am not going to have much in the way of alternative. > > Maybe some day. > > Good news is that most access is via Web Interface and I capture most > activity that way. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PORTS] Compiling on 8.1.3 on Openserver 5.05
Mark Campbell wrote: > Hi All > > I downloaded version 7.3 and compiled that. > > First I had to compile newer versions of "m4" and "bison" > > At least I now have an up and running postgres server > > Thanks for all the help > > Mark Campbell > > > Confidentiality Notice: http://ucs.co.za/conf.html ^^^ Now there is a great idea! Instead of a huge block of legal text at the bottom of the email, just link to the content. Seems that would keep the lawyers and the email readers happy. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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] Creating a Pseudocolumn
On Mon, May 15, 2006 at 11:17:41AM -0400, Jonah H. Harris wrote: > On 5/15/06, Martijn van Oosterhout wrote: > >tableoid is a pseudo-column like you mean, perhaps you should look how > >that works. > > I thought tableoid was a system column with a physical representation > on the tuple itself? I don't want any on-disk representation of my > pseudocolumn... just assigned at runtime similar to rownum. Tableoid is certainly not stored on disk (waste of space) but retreived specially whenever someone asks for the tableoid column. See heap_getsysattr in access/common/heaptuple.c. All you need to do is decide where you are going to store the level number and add it as a system attribute (negative attribute number). Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Creating a Pseudocolumn
On 5/15/06, Martijn van Oosterhout wrote: tableoid is a pseudo-column like you mean, perhaps you should look how that works. I thought tableoid was a system column with a physical representation on the tuple itself? I don't want any on-disk representation of my pseudocolumn... just assigned at runtime similar to rownum. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 1: 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] Creating a Pseudocolumn
On Mon, May 15, 2006 at 11:03:46AM -0400, Jonah H. Harris wrote: > I couldn't think of any pseudocolumns like this in PostgreSQL, but I > may just be brain-dead again. As it may be sorta similar, how were we > discussing handling rownum? tableoid is a pseudo-column like you mean, perhaps you should look how that works. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[HACKERS] Creating a Pseudocolumn
Hey everyone, I'm reworking hierarchical queries and am adding a LEVEL pseudocolumn. As it's a totally calculated attribute, what's the best way to handle it keeping in mind that LEVEL is only used in a hierarchical query? Looking at Evgen's patches, if he recognizes a hierarchical query and finds a LEVEL column in the target list, he creates a FakeVar (similar to Var) entry for it which is then passed throughout the system and modified on a per-tuple basis. While this works, it seems a bit kludgy and requires a good amount of special-case code. As such, I was wondering if you guys have some suggestions on how to use what's already there to accomplish the same thing. I couldn't think of any pseudocolumns like this in PostgreSQL, but I may just be brain-dead again. As it may be sorta similar, how were we discussing handling rownum? -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] audit table containing Select statements submitted
Thank you all for the effort you put into response. The biggest thing I want to avoid isn't so much having to parse through the log files but to avoid turning on such extensive logging altogether. I am not sure what kind of additional load logging to this extent may add. Looks like I am not going to have much in the way of alternative. Maybe some day. Good news is that most access is via Web Interface and I capture most activity that way. Again thank you all. Jim -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 8:03 PM To: Joshua D. Drake Cc: josh@agliodbs.com; Andrew Dunstan; Hogan, James F. Jr.; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] audit table containing Select statements submitted "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Hmmm... well why don't we add log_line_suffix :) Doesn't help; you'd also need logic in there to quote any XML tags appearing in the message text. At that point, adding a "log_line_suffix" is a transparent pretense of generality --- what you might as well do is just have a full-fledged "emit the log in XML" switch. (I concur with Andrew's comments that this is pretty silly, unless someone wants to go to the further work of XML-ifying the message contents to some reasonable extent. If you are going to have to write a parser to make sense of the message contents, it is really pretty lame to claim that you can't cope with parsing the current log format as-is.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
