[HACKERS] update syntax
Hello, Are update statements like: update t1 set (f1, f2, f3) = (select t1, t2, t3 from tab1 where id=5) where id=3 standard. Any hope of supporting this in Postgres? -Edwin S. Ramirez- ---(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] cache control?
On Fri, Jan 16, 2004 at 12:00:08PM -0500, Michael Brusser wrote: Is there a way to force database to load a frequently-accessed table into cache and keep it there? If it is frequently accessed, I guess it would be in the cachke permanently -- __ Nothing is as subjective as reality Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] What's planned for 7.5?
Any chance we'll see the VACUUM delay patch (throttle) get into 7.5? I only had the chance to try the first patch by Tom Lane and it was very good already. I was hoping it gets into 7.4.1 but it didn't. :-( I really need the VACUUM delay patch because my servers are begging to die every time VACUUM runs. Please let it be in 7.5. Thanks. Stephen Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] ow [EMAIL PROTECTED] writes: Is this all that's planned for 7.5? (based on current TODO list) If you think the TODO list has *anything* to do with release planning, you fundamentally misunderstand the way things are done around here. The TODO list is a list of things that are widely agreed to be problems (though sometimes it only means Bruce thinks this is a problem) and therefore will probably get worked on at some point in the future. What actually gets done for 7.5 will depend on which itches bother which developers enough to get scratched in the next few months. We do not have any central planning. It is a safe bet that 7.5 will include some things mentioned in the present TODO, as well as many things not mentioned in it; and that many items mentioned in the present TODO will still remain undone. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] VACUUM delay (was Re: What's planned for 7.5?)
The vacuum delay patch is not the ideal solution but it worked like a charm on my servers. I really need the vacuum delay patch or a better solution in 7.5. I'm getting millions of requests a month and running VACUUM without the patch makes PostgreSQL useless for many consecutive hours. Not quite the 24/7 system I was hopping for. :-( Unfortunately, it's rather difficult to patch so many machines as my entire system runs on Redhat RPMs. I'm really hopping to see a solution to this VACUUM problem in 7.5. I've been waiting for this fix for over 3 years and now it's almost there. Will this problem get addressed in the not so official TODO list? Thanks and keep up the good work! Stephen Jan Wieck [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Tom Lane wrote: Christopher Browne [EMAIL PROTECTED] writes: Stephen [EMAIL PROTECTED] writes: Any chance we'll see the VACUUM delay patch (throttle) get into 7.5? The hope, in 7.5, is to have ARC, which is the super-duper-duper version, working. Actually, I'm not sure that ARC should be considered to supersede the usefulness of a per-page delay in VACUUM. ARC should prevent VACUUM from trashing the contents of Postgres' shared buffer arena, but it won't do much of anything to prevent VACUUM from trashing the kernel buffer contents. And it definitely won't do anything to help if the real problem is that you're short of disk bandwidth and VACUUM's extra I/O demand pushes your total load over the knee of the response-time curve. What you need then is a throttle. The original patch I posted was incomplete for a number of reasons, but I think it may still be worth working on. Jan, any comments? I agree that there is considerable value in IO distribution. As such I already have the basics of the Background Writer in there. I left out the vacuum delay since I thought it was good enough to proove that there is low hanging fruit, but that it was far from what I'd call a solution. Ideally Vacuum would coordinate it's IO not only against some GUC variables, but also against the BGWriter+BufStrategy combo. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Documentation: Fast Backward/Forward
A simple documentation enhancement request: please provide Fast Backward/Forward links at the bottom of the page as well. ---(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
[HACKERS] feature request... case sensitivity without double quotes
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. Perhaps a feature, which is not set by default so it doesn't break current functionality, can be set so that when creating the database you can set a flag that will let postgresql know to parse column and table names that don't have double quotes and still keep the case information. I'm sure this will help spure more adoption of Postgresql, because people with serious databases concerns can't use MySQL and for real production and large scale projects I've purchased commercial databases because the migration from MySQL to them was easier because of the different approach to parsing. Thanks for your time, Pete ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] where shall i send my he.po file
hello, i am in the middle of translating this ru.po file into hebrew messages.i simply change the garbage with hebrew equivalent. where shall i send the result to ? Cheers. M. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] How to retrieve functional index column names
As of 7.4, this is a requirement badly in need of reconsideration. What makes you think there is any function name involved? Consider something like create index i on t ((col + 2)); Getting the column names is still a sensible operation though. I'd suggest looking in pg_depend to see which columns of the table the index depends on. Thank you for your help Tom! Regards Jakub ---(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] feature request... case sensitivity without double quotes
On Thu, Jan 15, 2004 at 10:02:34PM -0500, 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 What exactly is the problem? I can only see a problem when you create two fields/table that only differ in case. Being case insensitive should make it easier to port to postgresql rather than harder. Kurt ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] where shall i send my he.po file
Maxim Kovgan wrote: hello, i am in the middle of translating this ru.po file into hebrew messages.i simply change the garbage with hebrew equivalent. where shall i send the result to ? Read this for instructions: http://developer.postgresql.org/~petere/nlsinfo/ You should probably translate the English file rather than the Russian one to keep the lossage low. Also, do we even have right-to-left support in PostgreSQL? I'm not sure how that would work. ---(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] 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] feature request... case sensitivity without double quotes
Pete wrote: 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. Why would this be a problem, considering that all uses of the same mixed-case identifier are converted to the same lower-case identifier? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Defined Functions/AM's inherently slow?
Theory B would be that there's some huge overhead in calling non-built-in functions on your platform. I've done some profiling and convinced myself that indeed there's pretty steep overhead involved in fmgr_info() for a C-language function. Much of it isn't platform-dependent either --- as best I can tell, the lion's share of the time is being eaten in expand_dynamic_library_name(). In scenarios where a function is called many times per query, we cache the results of fmgr_info() ... but we do not do so for operations like ambeginscan that are done just once per query. Every other function language uses shortcuts or caching to reduce the cost of fmgr_info() lookup; external C language is the only one that hasn't been optimized in this way. I shall see what I can do about that. ISTM we can have a hash table that maps function OID to function address using the same sorts of techniques that plpgsql et al use. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] User Defined Functions/AM's inherently slow?
On Jan 18, 2004, at 7:28 PM, Tom Lane wrote: Theory B would be that there's some huge overhead in calling non-built-in functions on your platform. I've done some profiling and convinced myself that indeed there's pretty steep overhead involved in fmgr_info() for a C-language function. Much of it isn't platform-dependent either --- as best I can tell, the lion's share of the time is being eaten in expand_dynamic_library_name(). In scenarios where a function is called many times per query, we cache the results of fmgr_info() ... but we do not do so for operations like ambeginscan that are done just once per query. Wow, thanks for spending the time on this. What about for gettuple? Do calls to it take advantage of the cache? If not, this likely explains some of my custom am's performance troubles. Every other function language uses shortcuts or caching to reduce the cost of fmgr_info() lookup; external C language is the only one that hasn't been optimized in this way. I shall see what I can do about that. ISTM we can have a hash table that maps function OID to function address using the same sorts of techniques that plpgsql et al use. If there's anything I can do to help, let me know. I'll be happy to test any patches you might come up with too. eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Defined Functions/AM's inherently slow?
Eric B. Ridge [EMAIL PROTECTED] writes: Wow, thanks for spending the time on this. What about for gettuple? Do calls to it take advantage of the cache? If not, this likely explains some of my custom am's performance troubles. gettuple is looked up once at the start of a scan, so there's no per-tuple overhead involved there. As I said before, we're usually pretty good about avoiding per-tuple overheads --- the cost you identified here is a per-query overhead. If there's anything I can do to help, let me know. I'll be happy to test any patches you might come up with too. I have committed a patch into CVS HEAD --- give it a try. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
