Re: [HACKERS] Cached Query Plans (was: global prepared statements)
Another issue with plan caches, besides contention, in Oracle at least, is shared memory fragmentation (as plans aren't all the same size in memory ...) But this cache is very helpful for developments where every query is done via prepare/execute/deallocate. I've seen it a lot on java apps, the purpose being to benefit from the advantages of prepared statements, but without having to deal with storing those prepared statements somewhere. And of course, as said before, the statistics associated with those plans can be very helpful, mostly for all those very small queries that are run very frequently (a badly developped app most of the time, but that happens). Le Sunday 13 April 2008 06:21:41 Jonah H. Harris, vous avez écrit : On Sat, Apr 12, 2008 at 10:17 PM, Tom Lane [EMAIL PROTECTED] wrote: Yes, this is worthless on large active databases. The logging overhead alone starts to affect performance. But somehow, all that stuff with cached plans is free? Of course not. The first time you execute a query, it is cached... so you pay the same penalty you do in PG, but in many cases, only once. In regards to plan re-use, sure there's going to be some contention on the hash buckets... but that can be mitigated in a lot of ways. In addition to that, Oracle collects over two thousand other statistics in real-time... yet somehow Oracle is quite fast. So, I would say that the usual complaint about collecting stats should be more an issue of proper implementation than a complaint about the act of collection itself. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCHES] [HACKERS] Show INHERIT in \du
On Tue, Mar 25, 2008 at 2:41 AM, Tom Lane [EMAIL PROTECTED] wrote: Brendan Jurd [EMAIL PROTECTED] writes: This makes me wonder whether print.c could offer something a bit more helpful to callers wishing to DIY a table; we could have a table-building struct with methods like addHeader and addCell. What do you think? Overkill, or worthy pursuit? Once you have two occurrences of a pattern, it's reasonable to assume there will be more later. +1 for building a little bit of infrastructure. I've written a patch which implements the same \du behaviour as my previous patch, but using the new printTable API I submitted in [1]. If the printTable API patch is rejected or substantially changed, we will need to revisit this patch. The new patch constructs a table manually, in the same manner as describeOneTableDetails, so that we get the same outputs as the original patch but without any of the localisation issues identified by Tom and Alvaro. I have attached a patch against my printTable code, containing only the changes I made to describeRoles() (du-attributes_1.diff.bz2), and a combined patch against HEAD containing the full printTable API changes as well as the changes to describeRoles() (du-attributes-print-table_1.diff.bz2). No memory problems detected by valgrind, and all regression tests passed on x86_64 gentoo. I've added this item to the May CommitFest wiki page. Cheers, BJ [1[ http://archives.postgresql.org/message-id/[EMAIL PROTECTED] du-attributes_1.diff.bz2 Description: BZip2 compressed data du-attributes-print-table_1.diff.bz2 Description: BZip2 compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cached Query Plans
Would it be possible to store plans with an indication of the search path that was used to find tables, and for temp tables some snapshot of the statistics for the table if any? My suspicions are that: * where you have a lot of short-lived connections then actually they will often use the default search path - or a similar one * if a temp table is in use then normally these will be small or contain 'similar' data There is a danger that these heuristics will be poor if long-running connections are in play - but they have no excuse not to do their own preparation. Perhaps you could have named cache segments and connections could 'opt in' to a cache segment if they want such sharing? James -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cached Query Plans (was: global prepared statements)
On Fri, Apr 11, 2008 at 12:34 PM, PFC [EMAIL PROTECTED] wrote: Well, I realized the idea of global prepared statements actually sucked, so I set on another approach thanks to ideas from this list, this is caching query plans. Well, that's a blatantly bad realization. Perhaps you should do more research. No, what I meant is that the global prepared statements as I tried to implement them before weren't that good... I think simple caching based on the query text itself is preferable to having to name each of your queries, extract them from your programs and replace them by executes, issue a create statement command for each of them, etc. Few people would actually use that feature because it would mean lots of modifications to the application, so all the applications that have to be compatible with other databases would not use the feature (*) It could be useful for permissions and fine access control, though, but views and stored procs already provide that functionality... (*) = Note that caching the plans based on the query text (with $ params) from a parse message will not provide caching for oldskool queries with params inside in the form of escaped strings. This is good, because it means the safer solution (using $-quoted params) will also be the faster solution. And in the application, only a very small part of the code needs to be changed, that's the DB abstraction layer. Doesn't Oracle do this now transparently to clients? Of course it does, and it has since the late 80's I believe. Oracle keeps a statement/plan cache in its shared memory segment (SGA) that greatly improves its performance at running queries that don't change very often. Can we have more details on how Oracle does it ? For inspiration... Here is what I'm thinking about : Don't flame me too much about implementation issues, this is just throwing ideas in the air to see where they'll fall ;) * global plan cache in shared memory, implemented as hashtable, hash key being the (search_path, query_string) Doubt : Can a plan be stored in shared memory ? Will it have to be copied to local memory before being executed ? This stores : - the plans (not for all keys, see below) - the stats : - number of times this query has been executed, - total, min and max wallclock time and CPU time spent planning this query, - total, min and max wallclock time, CPU time and RAM spent executing this query, - total, min and max number of rows returned, - last timestamp of execution of this query, There should be separate GUCs to control this : - should the whole thing be activated ? - should the cache be active ? or just the stats ? and what stats ? There should be also a way to query this to display the statistics (ie what query is killing my server ?), and a way to purge old plans. * every time a Parse message comes up : - look if the (search_path, query_string) is in the cache - if it is in the cache : - if there is a cached plan, make the unnamed statement point to it, and we're done. - if there is no cached plan, prepare the query, and put it in the unnamed statement. Now, the query has been parsed, so we can decide if it is cacheable. Should this be done in Parse, in Bind, or somewhere else ? I have no idea. For instance, queries which contain VALUES() or IN( list of consts ) should not be cached, since the IN() is likely to change all the time, it would just trash the cache. Using =ANY( $1 ) instead will work with cached plans. Also, will a plan to be cached have to be prepared with or without the parameters ? That's also an interesting question... Perhaps the user should also be able to specify wether to cache a plan or not, or wether to use the params or not, with hint flags in the query string ? (like mysql, /* flags */ SELECT blah ) Now, if the query is cacheable, store it in the cache, and update the stats. If we decided to store the plan, do that too. For instance we might decide to store the plan only if this query has been executed a certain number of times, etc. * In the Execute message, if a cached plan was used, execute it and update the stats (time spent, etc). Now, about contention, since this is one shared hashtable for everyone, it will be fought for... However, the lock on it is likely to be held during a very small time (much less than a microsecond), so would it be that bad ? Also, GUC can be used to mitigate the contention, for instance if the user is not interested in the stats, the thing becomes mostly read-only -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [Pljava-dev] stack depth limit exceeded - patch possible?
On Sat, 12 Apr 2008, Alexander W?hrer wrote: I'm working on Windows XP SP2 (stack limit 3500 kb) and deployed successfully my application (doing some external Web service calling) inside PostGre 8.3.0. Unfortunatelly, the application needs at least 3 Threads and will run for quite some time. I found this comment http://pgfoundry.org/pipermail/pljava-dev/2005/000491.html by Thomas Hallgren where he mentioned that PostGre only defines one stack and therefor pl/java has no way of telling PostGre about multiple thread stack pointers. My question is now if there is a patched version available of PostGre 8.3.0 having this stack_depth check disabled? This was fixed in postgresql/pljava shortly after the referenced discussion. As requested, postgresql 8.1+ allows modification of stack_base_ptr so pljava can set it as desired. Kris Jurka -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cached Query Plans (was: global prepared statements)
On Sun, Apr 13, 2008 at 02:26:04PM +0200, PFC wrote: * global plan cache in shared memory, implemented as hashtable, hash key being the (search_path, query_string) Doubt : Can a plan be stored in shared memory ? Will it have to be copied to local memory before being executed ? Frankly, I think you're better off storing them in a table. Shared memory is a limited resource and you cannot change how much you've allocated after the server has started. It does mean you'll have to serialise/deserialise them, but this will be cheaper than replanning, right? I liked your global prepared statements idea much better. Named the statements is no problem: DB frontends do that for you anyway sometimes. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] Cached Query Plans (was: global prepared statements)
On Sun, Apr 13, 2008 at 2:26 PM, PFC [EMAIL PROTECTED] wrote: Oracle keeps a statement/plan cache in its shared memory segment (SGA) that greatly improves its performance at running queries that don't change very often. Can we have more details on how Oracle does it ? For inspiration... Why limit ourselves with Oracle? How all major proprietary RDBMSs do it. Here is a nice presentation I've found on DB2, they call it Dynamic Statement Cache: http://www.tbrug.com/TB%20UG%20Dynamic%20Statement%20Cache.ppt Here is what I'm thinking about : Don't flame me too much about implementation issues, this is just throwing ideas in the air to see where they'll fall ;) * global plan cache in shared memory, implemented as hashtable, hash key being the (search_path, query_string) Doubt : Can a plan be stored in shared memory ? Will it have to be copied to local memory before being executed ? Well, Oracle uses terms hard parse and soft parse, the former being preparing the whole query, the latter reusing query plan prepared by some other session. More or less. See this link for more detailed description: http://asktom.oracle.com/pls/asktom/f?p=100:11:0P11_QUESTION_ID:2588723819082 (this is quite interesting read) This stores : - the plans (not for all keys, see below) - the stats : [...] I am not too sure that plans and statistical counters should be stored together... Probably plans should go in one place, and statistics should go to the stats collector (I know he's not quite ready for this ;)). There should be also a way to query this to display the statistics (ie what query is killing my server ?), and a way to purge old plans. Hm, a limit on how much memory can be used for plans (query_plan_cache_size GUC?), and a LRU/LFU expiration of old plans? * every time a Parse message comes up : - look if the (search_path, query_string) is in the cache - if it is in the cache : - if there is a cached plan, make the unnamed statement point to it, and we're done. - if there is no cached plan, prepare the query, and put it in the unnamed statement. Now, the query has been parsed, so we can decide if it is cacheable. Should this be done in Parse, in Bind, or somewhere else ? I have no idea. For instance, queries which contain VALUES() or IN( list of consts ) should not be cached, since the IN() is likely to change all the time, it would just trash the cache. Using =ANY( $1 ) instead will work with cached plans. Perhaps a GUC for controlling query cache should heve three values: none -- don't cache any statement smart -- use heuristics for deciding whether to cache it all -- force caching all queries -- for uncommon/statistical/testing purposes. Also, will a plan to be cached have to be prepared with or without the parameters ? That's also an interesting question... Perhaps the user should also be able to specify wether to cache a plan or not, or wether to use the params or not, with hint flags in the query string ? (like mysql, /* flags */ SELECT blah ) I don't like the hint flags. They tend to haunt later on (when the database gets smarter, but application forces it to be dumb). I would say a GUC. GUC gives freedom of change to the application, and can also be set per user with ALTER USER. Now, if the query is cacheable, store it in the cache, and update the stats. If we decided to store the plan, do that too. For instance we might decide to store the plan only if this query has been executed a certain number of times, etc. Interesting idea. I think I like it. * In the Execute message, if a cached plan was used, execute it and update the stats (time spent, etc). Now, about contention, since this is one shared hashtable for everyone, it will be fought for... However, the lock on it is likely to be held during a very small time (much less than a microsecond), so would it be that bad ? Also, GUC can be used to mitigate the contention, for instance if the user is not interested in the stats, the thing becomes mostly read-only I would say: keep the stats separate. For evey plan cached generate some unique id (Perhaps OID? I am not convinced), and use this ID as the key for the statistics. I tend to think of it as a temporary table, and temporary table stats. :) Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgwin32_safestat weirdness
I'm confused about the current state of the pgwin32_safestat stuff. Cygwin is now building happily, but MinGW is now broken on libpq. It looks like libpq now needs dirmod.o or maybe libpgport.a. What I really don't understand though is why MinGW is broken but MSVC isn't. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgwin32_safestat weirdness
Andrew Dunstan wrote: I'm confused about the current state of the pgwin32_safestat stuff. Cygwin is now building happily, but MinGW is now broken on libpq. It looks like libpq now needs dirmod.o or maybe libpgport.a. What I really don't understand though is why MinGW is broken but MSVC isn't. I don't know why MSVC survives that without digging deeper, but the original patch had the redefine only if !defined(FRONTEND), but that seems to have been lost in Toms fix and it's now always redefined. Tom - was there a reason it now runs in FRONTEND as well, or was that an oversight? //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgwin32_safestat weirdness
Andrew Dunstan [EMAIL PROTECTED] writes: I'm confused about the current state of the pgwin32_safestat stuff. Me too. I tried to fix it a couple of days ago, but seem to have only moved the problem around :-( Cygwin is now building happily, but MinGW is now broken on libpq. It looks like libpq now needs dirmod.o or maybe libpgport.a. What I really don't understand though is why MinGW is broken but MSVC isn't. I don't think we should import dirmod.o into libpq; it's too big. I suggest either (1) Assume that we don't need safe stat for frontend code, and compile the safestat stuff only when !defined(FRONTEND) (2) Split safestat into its own file and include that in libpq. I'm not touching it myself though, since I have no way to test it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgwin32_safestat weirdness
Magnus Hagander [EMAIL PROTECTED] writes: Tom - was there a reason it now runs in FRONTEND as well, or was that an oversight? I did do that intentionally because I was worried about frontend code maybe expecting stat to work fully. Like pg_standby for example. I think the immediate problem is that libpq uses stat() as well, and depending on your link rules that might mean that safestat actually has to be bound into libpq. I would not have a problem with assuming that libpq will never care about st_size being right, but I'm a lot more nervous about making that presumption for all FRONTEND code. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgwin32_safestat weirdness
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Cygwin is now building happily, but MinGW is now broken on libpq. It looks like libpq now needs dirmod.o or maybe libpgport.a. What I really don't understand though is why MinGW is broken but MSVC isn't. I don't think we should import dirmod.o into libpq; it's too big. Is it really big enough to matter? Where would you in general draw the line for including? I suggest either (1) Assume that we don't need safe stat for frontend code, and compile the safestat stuff only when !defined(FRONTEND) (2) Split safestat into its own file and include that in libpq. Is there not a (3) which has it included in all frontend code *except* libpq? Do we have a define to do that off? Because I agree with your comments in the other mail that there may be other frontend stuff that might need it. In libpq, it's only used in one place to check if a file is present, and one then in the SSL code to determine permissions and such (which means it's being ignored on win32). //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgwin32_safestat weirdness
Magnus Hagander [EMAIL PROTECTED] writes: Is there not a (3) which has it included in all frontend code *except* libpq? Do we have a define to do that off? Offhand I can't think of one. In libpq, it's only used in one place to check if a file is present, and one then in the SSL code to determine permissions and such (which means it's being ignored on win32). Maybe we could finess the problem by tweaking libpq to not use stat() at all on Windows. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Remove lossy-operator RECHECK flag?
I've committed changes that move the determination of whether recheck is required into the index AMs. Right now, GIST and GIN just always set the recheck flag to TRUE. Obviously that control should be pushed down to the opclass consistent() functions, but I don't know that code well enough to be clear on exactly what should happen. Are you willing to do that part? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] printTable API (was: Show INHERIT in \du)
Brendan Jurd escribió: I'd like to submit my first version of this patch for review. I have introduced a new struct in print.h called printTableContent, which is used to compose the contents of a psql table. The methods exposed for this struct are as follows: Looks cool -- on a first read, I think you should add some more code comments at the top of each function specifying whether the texts need to be translated by the caller or done by the function itself. Also it would be good if it is consistent, too :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Commit fest queue
All, BTW, the lead developer for ReviewBoard stopped by the PostgreSQL booth at LUGRadio this weekend. He was interested in the possibility of us using ReviewBoard, but not very interested in adding an e-mail interface to the software. -- Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Commit fest queue
Tom, All: Well, I can provide an easy example: my first patch [1]. A second one would be Meredith's original QBE patch. While we wouldn't have ever included it in the core code, it would have been nice if she'd gotten a reply explaining why. More importantly, we *think* we haven't missed any patches ... but we can't *know* because we have no way to systematically search them. -- Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Remove lossy-operator RECHECK flag?
Teodor Sigaev [EMAIL PROTECTED] writes: If we do this, should we remove RECHECK from the CREATE OPERATOR CLASS syntax altogether, or leave it in but treat it as a no-op (probably with a warning)? I think, it should be a error, but not a syntax error - hint should point to use new version of module. Loading dump from previous versions with opclass definitions is not good action anyway. Here's a related issue: what should 8.4 pg_dump do when dumping from an older server version? Some possibilities include 1. Dump the CREATE OPERATOR CLASS command with a RECHECK phrase, same as before. Then the dump would still work with 8.3 ... at least until we make some other incompatible change ... while giving an error if loaded into 8.4. 2. Silently ignore amopreqcheck in older servers. Then the dump would not load correctly into the older server (but then again, it might not've anyway). It *would* load into 8.4, but whether it would work would of course depend on the opclass support functions having been updated. 3. Throw an error and refuse to dump if it finds amopreqcheck true. #3 seems just about useless behavior, though. For the moment I have it doing #1, but it strikes me that that is only useful if 8.4 gets to release without having made any backwards-incompatible changes in pg_dump output, which is probably not better than a fifty-fifty bet. Maybe we should do #2? Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to add objetct size on \d+ verbose output
On Sat, Apr 12, 2008 at 7:43 PM, Brendan Jurd [EMAIL PROTECTED] wrote: I was going to try this patch out, but it would not apply. Seems that where the patch should have , it has amp; instead. Has this somehow been HTML entity-ified? Hi Brendan, You are right, I don't now why this occurs. I'm sending it now gziped, You may test it again. Thank you. -- []s Dickson S. Guedes - Projeto Colmeia - Curitiba - PR (41) 3254-7130 ramal: 27 http://makeall.wordpress.com/ http://planeta.postgresql.org.br/ psql-object-size-on-describe-verbose.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgwin32_safestat weirdness
Tom Lane wrote: In libpq, it's only used in one place to check if a file is present, and one then in the SSL code to determine permissions and such (which means it's being ignored on win32). Maybe we could finess the problem by tweaking libpq to not use stat() at all on Windows. I would be quite happy with that, but before we go down that path I'd like to know why the MSVC builds aren't failing now from this when the MinGW builds are. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgwin32_safestat weirdness
Andrew Dunstan [EMAIL PROTECTED] writes: I would be quite happy with that, but before we go down that path I'd like to know why the MSVC builds aren't failing now from this when the MinGW builds are. Maybe the MSVC linker is willing to bind libpq's call to a safestat copy extracted from libpgport.a in the surrounding program --- IOW, it works only for calling programs that include libpgport, but all ours do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cached Query Plans (was: global prepared statements)
Why limit ourselves with Oracle? How all major proprietary RDBMSs do it. Thanks for the links. Very interesting. The DB2 document especially mentions an important point : in order to make their planner/optimizer smarter, they had to make it slower, hence it became crucial to cache the plans. Contrast this with MySQL where using prepared statements gains nothing : the optimizer does so little work that it actually doesn't matter. So, basically, Orcale : - Parses the query every time (identifies tables, permissions etc) (soft parse) - From that parsed query it looks up a cached plan (the lookup key could then be different depending on the schema etc) - If not, it must plan the query (hard parse). Also the Oracle doc mentions that the soft parsing should be avoided by using prepared statements in the application (ie Parse once and Bind lots of times) So, Oracle will redo the parsing + permissions check each time, unless prepared statements are used, in which case it's direct execution. And DB2 : Er, the document is not very clear about what it actually does, but the stats look nice ;) I liked your global prepared statements idea much better. Named the statements is no problem: DB frontends do that for you anyway sometimes. Hm. The global statements and the cache would complement each other actually. Why not. When the user wants to name the statements, he can do so (and perhaps control who can execute what, etc, like with stored procs) Permission checking overhead will be there at each execution. Should the plan be cached locally ? (RAM consumption times N bakends...) Cached per user once permissions have been checked ? (avoids the overhead of rechecking permissions) What about the search path ? (I'd force the global statements to use the default search path no matter what, being explicit is better than why does it stop working ?) Can the application or the database library name the statements ? I'm not so sure. This could work for compiled languages (what about when you run several applications ? or several versions of the same application ? do we need a uniqueness of statement names from all developers all over the world ?) Solution : make each application use a different user name, and global prepared statements only visible to the user that created them, perhaps. This conflicts with some desirable features, though. It needs more thinking. What about non-compiled languages ? It will not be possible to generate a list of statements beforehands... And queries are also constructed dynamically by frameworks such as Rails, which makes naming them impossible, but caching the plans would work well. So, some situations would benefit from a plan cache, Frankly, I think you're better off storing them in a table. Shared memory is a limited resource and you cannot change how much you've I'd say that unless you have a perverse application that will try all the permutations of column names just to make sure the query is different every time, how many different queries would you want to cache ?... probably less than 1000... so it wouldn't take more than a couple megabytes... allocated after the server has started. It does mean you'll have to serialise/deserialise them, but this will be cheaper than replanning, right? What would be the overhead of a catalog lookup to get a cached plan for a statement that returns 1 row ? Would the catalog cache make it fast enough ? And what about deserialization ?... I am not too sure that plans and statistical counters should be stored together... Not sure either. Probably plans should go in one place, and statistics should go to the stats collector (I know he's not quite ready for this ;)). That's the problem... Hm, a limit on how much memory can be used for plans (query_plan_cache_size GUC?), and a LRU/LFU expiration of old plans? Now it gets hairy ;) Yes memory size should be limited. But how to make a LRU cleaner which doesn't create lots of contention ?... Luckily, with a hash having a fixed number of buckets, it is easier (clean a bucket every N seconds for instance). Perhaps a GUC for controlling query cache should heve three values: none -- don't cache any statement smart -- use heuristics for deciding whether to cache it all -- force caching all queries -- for uncommon/statistical/testing purposes. I would not volunteer to write that heuristic ;) Although there would be a very simple solution : if time to parse some percentage of time to execute then cache. The hairiness is in the plan dependence (or independence) on parameter values, ideally we only want to cache plans that would be good for all parameter values, only the user knows that precisely. Although it could be possible to examine the column histograms... (like mysql, /* flags */ SELECT blah ) I don't like the hint flags. They tend to haunt
Re: [HACKERS] Patch to add objetct size on \d+ verbose output
On Mon, Apr 14, 2008 at 8:45 AM, Dickson dos Santos Guedes [EMAIL PROTECTED] wrote: On Sat, Apr 12, 2008 at 7:43 PM, Brendan Jurd [EMAIL PROTECTED] wrote: I was going to try this patch out, but it would not apply. Seems that where the patch should have , it has amp; instead. Has this somehow been HTML entity-ified? You are right, I don't now why this occurs. I'm sending it now gziped, You may test it again. Hi Dickson, I actually already corrected the 'amp;'s manually and tested your patch like that. It's nice -- I think this is a very worthwhile addition to \dt+ I note that your last email only went to -hackers; you might want to send the updated version of your patch to -patches as well. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
