[HACKERS] Buffer miss ratio
Dear all: A quick question about shared buffers in PostgreSQL: I want to get the number of misses for the buffer pool of PostgreSQL. Is there any methods or performance tools I can use to get the information? Best Wishes yyan
Re: [HACKERS] Buffer miss ratio
崔岩ccuiy...@sina.com wrote: I want to get the number of misses for the buffer pool of PostgreSQL. Is there any methods or performance tools I can use to get the information? Yes. Please see the manual chapters on "Monitoring database activity". The pgstatio_* views in particular. PS. This mailing list is for discussion on development of PostgreSQL. Questions on usage belong to pgsql-general, or one of the other user mailing lists. -- Heikki Linnakangas EnterpriseDB 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: [HACKERS] lazy_truncate_heap()
Greg Stark wrote: On 31 Dec 2008, at 13:21, Simon Riggs wrote: Both of these bugs are minor, but the effect of either/both of them is to cause more AccessExclusiveLocks than we might expect. For Hot Standby this means that many VACUUMs take AccessExclusiveLocks on relations, which would potentially lead to having queries cancelled for no reason at all. Well by default it would just cause wal to pause briefly until the queries with those locks finish, no? Wait a minute. Why does an AccessExclusiveLock lead to cancelled queries or pausing WAL application? I thought it'd just block other queries trying to acquire a conflicting lock in the standby, just like holding an AccessExclusiveLock on the primary does. It's unrelated to the xmin horizon issue. There is a noteworthy point though. In the primary, vacuum trying to truncate takes AccessExclusiveLock conditionally, so that it doesn't disturb queries accessing the table, and only truncates the table if it got the lock. But in standby, we have to truncate the table, and therefore have to acquire the lock, waiting until we get it. I guess we have to stop applying WAL while waiting. -- Heikki Linnakangas EnterpriseDB 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: [HACKERS] TODO items for window functions
Hi, Happy new year! Le 31 déc. 08 à 17:04, Tom Lane a écrit : However, it seems kind of inconsistent to do this for window functions unless we also make \df start putting parens around the argument lists for regular functions. Comments? A way to distinguish between window functions "seeing" frames vs. partitions, if possible, would sound quite useful after a docs reading session. AKA OVER(... order by ...) effect in some other thread. -- dim -- 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] TODO items for window functions
> I am not thrilled about inventing a new column for this, but how about > a display like so: > > regression=# \df nth_value >List of functions > Schema | Name| Result data type | Argument data types > +---+--+- > pg_catalog | nth_value | anyelement | anyelement, integer OVER window > > or some other addition that only shows up when needed. I think this whole idea is a bad one. In the current release, you can do DROP FUNCTION Name ( Argument data types ) ...and it will work. Maybe you will say that no one is doing this via a script (which I wouldn't bet on, but it's possible) but I'm sure people are doing it via cut and paste, because I have done exactly this thing. Any of the various proposals for hacking up Argument data types will make this no longer true, and somebody will get confused. I think you should bite the bullet and add a "type" column (f for regular function and w for window? could there be others in the future?). ...Robert -- 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] lazy_truncate_heap()
On Wed, 2008-12-31 at 14:45 -0500, Greg Stark wrote: > On 31 Dec 2008, at 13:21, Simon Riggs wrote: > > > > Both of these bugs are minor, but the effect of either/both of them is > > to cause more AccessExclusiveLocks than we might expect. > > > > For Hot Standby this means that many VACUUMs take AccessExclusiveLocks > > on relations, which would potentially lead to having queries cancelled > > for no reason at all. > > Well by default it would just cause wal to pause briefly until the > queries with those locks finish, no? Yes, but why allow pointless actions in the first place? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] lazy_truncate_heap()
On Wed, 2008-12-31 at 21:45 +0200, Heikki Linnakangas wrote: > > Can I fix? > > Yes please. Fix attached. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support Index: src/backend/commands/vacuumlazy.c === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/commands/vacuumlazy.c,v retrieving revision 1.114 diff -c -r1.114 vacuumlazy.c *** src/backend/commands/vacuumlazy.c 17 Dec 2008 09:15:02 - 1.114 --- src/backend/commands/vacuumlazy.c 1 Jan 2009 17:45:29 - *** *** 183,190 * number of pages. Otherwise, the time taken isn't worth it. */ possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages; ! if (possibly_freeable >= REL_TRUNCATE_MINIMUM || ! possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION) lazy_truncate_heap(onerel, vacrelstats); /* Vacuum the Free Space Map */ --- 183,192 * number of pages. Otherwise, the time taken isn't worth it. */ possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages; ! if (vacrelstats->tuples_deleted > 0 && ! (possibly_freeable >= REL_TRUNCATE_MINIMUM || ! (possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION && ! possibly_freeable > 0))) lazy_truncate_heap(onerel, vacrelstats); /* Vacuum the Free Space Map */ -- 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] lazy_truncate_heap()
On Thu, 2009-01-01 at 12:00 +0200, Heikki Linnakangas wrote: > Greg Stark wrote: > > > > On 31 Dec 2008, at 13:21, Simon Riggs wrote: > >> > >> Both of these bugs are minor, but the effect of either/both of them is > >> to cause more AccessExclusiveLocks than we might expect. > >> > >> For Hot Standby this means that many VACUUMs take AccessExclusiveLocks > >> on relations, which would potentially lead to having queries cancelled > >> for no reason at all. > > > > Well by default it would just cause wal to pause briefly until the > > queries with those locks finish, no? > > Wait a minute. Why does an AccessExclusiveLock lead to cancelled queries > or pausing WAL application? I thought it'd just block other queries > trying to acquire a conflicting lock in the standby, just like holding > an AccessExclusiveLock on the primary does. It's unrelated to the xmin > horizon issue. Yes, it is unrelated to the xmin horizon issue. There are two reasons for delaying WAL apply: * locks * xmin horizon When a lock is acquired on the primary it almost always precedes an action which cannot occur concurrently. For example, if VACUUM did truncate a table then queries could get errors because parts of their table disappear from under them. Others are drop table etc.. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Kerberos options requiring restart
The kerberos configuration options (krb_caseinsens_users, krb_realm, krb_server_keyfile, krb_srvname, krb_server_hostname) are all set as PGC_POSTMASTER. From what I can see, the only places where kerberos code is called is from ClientAuthentication(). That means these parameters shouldn't need to be PGC_POSTMASTER, to they? Or am I missing something here? //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Copyright update
I have updated all the source files for a 2009 copyright; seems the commit message was suppressed due to its size. Tom found a few more and I have adjusted for those as well. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Kerberos options requiring restart
Magnus Hagander writes: > The kerberos configuration options (krb_caseinsens_users, krb_realm, > krb_server_keyfile, krb_srvname, krb_server_hostname) are all set as > PGC_POSTMASTER. From what I can see, the only places where kerberos code > is called is from ClientAuthentication(). > That means these parameters shouldn't need to be PGC_POSTMASTER, to > they? Or am I missing something here? Hmmm ... I think at one time there was some kerberos initialization done at postmaster start, but it sure doesn't look like there is anymore. So we could probably make all of these SIGHUP. 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] Reformat permissions in \l+ (like \z does)
On Wed, 31 Dec 2008 13:08:20 -0500 Tom Lane wrote: > "Andreas 'ads' Scherbaum" writes: > > On Sun, 28 Dec 2008 18:19:48 -0500 Tom Lane wrote: > >> If we're going to do this, shouldn't it happen uniformly for *all* > >> ACL displays in describe.c? > > > Makes sense, imho. > > Done. Oh, thanks. The updated patch was on my todo. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors -- 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] Copyright update
Is that actually legal if we haven't modified the files? Or is the whole source tree considiered one work? -- Greg On 1 Jan 2009, at 13:25, Bruce Momjian wrote: I have updated all the source files for a 2009 copyright; seems the commit message was suppressed due to its size. Tom found a few more and I have adjusted for those as well. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Copyright update
Greg Stark wrote: > Is that actually legal if we haven't modified the files? Or is the > whole source tree considiered one work? One work, I assume. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Copyright update
Bruce Momjian wrote: Greg Stark wrote: Is that actually legal if we haven't modified the files? Or is the whole source tree considiered one work? One work, I assume. I am not a lawyer, but if its one work, why is there a notice in every source file? ISTM that if it were one work there would only have to be one notice. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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: [HACKERS] Copyright update
Andrew Chernow wrote: > Bruce Momjian wrote: > > Greg Stark wrote: > >> Is that actually legal if we haven't modified the files? Or is the > >> whole source tree considiered one work? > > > > One work, I assume. > > > > I am not a lawyer, but if its one work, why is there a notice in every source > file? ISTM that if it were one work there would only have to be one notice. Because people often take source files and copy them for use in other projects. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Copyright update
On Thu, 2009-01-01 at 14:47 -0500, Bruce Momjian wrote: > Andrew Chernow wrote: > > Bruce Momjian wrote: > > > Greg Stark wrote: > > >> Is that actually legal if we haven't modified the files? Or is the > > >> whole source tree considiered one work? > > > > > > One work, I assume. > > > > > > > I am not a lawyer, but if its one work, why is there a notice in every > > source > > file? ISTM that if it were one work there would only have to be one notice. > > Because people often take source files and copy them for use in other > projects. I think the correct resolution to the question is to ask legal. Yes? Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Copyright update
Greg Stark writes: > Is that actually legal if we haven't modified the files? Or is the > whole source tree considiered one work? [ shrug... ] We've always done it this way. 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] Copyright update
Joshua D. Drake wrote: > On Thu, 2009-01-01 at 14:47 -0500, Bruce Momjian wrote: > > Andrew Chernow wrote: > > > Bruce Momjian wrote: > > > > Greg Stark wrote: > > > >> Is that actually legal if we haven't modified the files? Or is the > > > >> whole source tree considiered one work? > > > > > > > > One work, I assume. > > > > > > > > > > I am not a lawyer, but if its one work, why is there a notice in every > > > source > > > file? ISTM that if it were one work there would only have to be one > > > notice. > > > > Because people often take source files and copy them for use in other > > projects. > > I think the correct resolution to the question is to ask legal. Yes? So I can get three different answers? It is not a priority for me. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] posix_fadvise v22
I tried this on my laptop running FC9, and because I forgot to run autoconf, I got this error message when I tried to turn on posix_fadvise. rhaas=# set effective_io_concurrency to 3; ERROR: could not determine if this system has a working posix_fadvise DETAIL: Check configure.log produced by configure for more information Am I correct in thinking that the only thing we're really checking for here is whether a trivial posix_fadvise() call returns success? If so, is this test really worth doing? It seems to me that since users can always switch off the feature by leaving effective_io_concurrency set to the default value of 1, there is not much value in having a configure test that forcibly disables it. If the user has a broken posix_fadvise() and later fixes it, they shouldn't have to recompile PostgreSQL to use this feature, especially in this day when the build system and the run system are often different. A user who somehow ends up with RPMs that generate this error message will be utterly at a loss as to what to do about it. One minor nit: If we're going to keep this test, we should change the detail string to say config.log rather than configure.log, as that is the actual file name. ...Robert On Thu, Dec 11, 2008 at 4:35 PM, Gregory Stark wrote: > Here's the update > > I also skimmed through and cleaned a couple other things. There's *still* a > function prototype which I don't see what header file to put it in, that's the > one in port/posix_fadvise.c which contains one function with one caller, > guc.c. > > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's 24x7 Postgres support! > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > > -- 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] posix_fadvise v22
"Robert Haas" writes: > Am I correct in thinking that the only thing we're really checking for > here is whether a trivial posix_fadvise() call returns success? If > so, is this test really worth doing? Runtime tests performed during configure are generally a bad idea to start with --- it's impossible to do any such thing in a cross-compilation scenario, for example. 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] SQL/MED compatible connection manager
Peter Eisentraut wrote: > Well, what this function essentially does is a text transformation of the > options, something like this: > > peter=# SELECT array_to_string(fdwoptions || srvoptions || umoptions, ' ') > FROM pg_foreign_data_wrapper fdw, pg_foreign_server srv, pg_user_mappings um > WHERE fdw.oid = srv.srvfdw AND srv.oid = um.srvid; >array_to_string > - > host=localhost port=5432 user=peter password=seKret > (1 row) > > (You can enhance this with quoting etc., but that's the essence.) Essentially yes. Additional things include USAGE check on the server and user mapping lookup (use public if no explicit mapping is specified). Without those I'm not really sure this deserves a separate function at all. The main goal is to provide standard semantics for the connection lookup, so that dblink, plproxy, pl rpc etc. would not have to reinvent it. > So, we could add a function whose job it is to convert all options to a > PostgreSQL connection string. I wouldn't worry about dealing with other > wrappers specifically. They could still use the function, but the result > would not make much sense. > This works for me. I'd implement this as a C function so it is directly callable from other C modules. Another option is to implement it as a SRF, similar to what was initially in the dummy wrapper. Just return all of the options for fdw, server and user mapping. This is probably worth doing if there are any users for this. So far I haven't noticed any enthusiasm, so it might be better to start with just the connection string. > I would call it something like > > pg_postgresql_fdw_options_string(server, user) returns text Hmm, it is probably a good idea to avoid the fdw abbreviation -- the term "foreign data wrapper" is already confusing enough. My suggestion: pg_foreign_server_conninfo(server) pg_foreign_server_conninfo(server,user) If there are no objections, I'll whack those functions out, and bring the dblink patch up to date. regards, Martin -- 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] Enable pl/python to return records based on multiple OUT params
Hannu Krosing writes: > On Mon, 2008-11-03 at 19:07 -0500, Tom Lane wrote: >> The status of this patch isn't clear --- are you still working on it? >> There certainly appear to be a lot of debug leftovers that need to >> be removed, error messages to clean up, etc. > It passes all existing regression tests and works fine for "correct" > use, I'm returning this patch for rework. You still have done nothing about the above complaints (useless noise added to elog messages, elog(NOTICE) debug messages that should have been removed, etc). I started to clean this up myself but got too annoyed when I found that the patch had removed security-critical checks that reject pseudotype result types. I have other things to do than clean up WIP patches. 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] Copyright update
Andrew Chernow wrote: Bruce Momjian wrote: Greg Stark wrote: Is that actually legal if we haven't modified the files? Or is the whole source tree considiered one work? One work, I assume. I am not a lawyer, but if its one work, why is there a notice in every source file? ISTM that if it were one work there would only have to be one notice. "Would only have to be one notice" is correct. You do not need a notice in every file. You put a notice in every file as extra unnecessary effort to make sure that people cannot possibly miss it. It is not a requirement for copyright that every file have a copyright comment on top. That it is in every source file is similar to putting extra parens around expressions or embedding documentation in an API. It does not indicate that the work is not a single work. It is simply making the terms more explicit and easily accessible. Most importantly, the *lack* of a copyright notice, does not indicate that there is no copyright rights defined. If 10 files have a copyright notice, and the 11th file does not, this does not indicate that the 11th file has more or less copyright restrictions than the other 10 that are explicit. The implicit copyright may be "All rights reserved" whereas the explicit copyright may say "You may use this software for free provided that you do not hold the authors responsible for any damages caused by use of the software". Which is more restrictive? Cheers, mark -- Mark Mielke -- 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] Copyright update
Bruce Momjian wrote: Andrew Chernow wrote: I am not a lawyer, but if its one work, why is there a notice in every source file? ISTM that if it were one work there would only have to be one notice. Because people often take source files and copy them for use in other projects. As per my previous message, although people do this, it is not "safer" to copy a file without an explicit copyright embedded within the file, than to copy a file without an explicit copyright embedded within the file. The explicit copyright embedded serves more of a warning for people that don't know better to guilt them into thinking twice before doing whatever they are doing, than an actual legal requirement for enforcement of copyright restrictions. Cheers, mark -- Mark Mielke
Re: [HACKERS] contrib/pg_stat_statements 1226
"Alex Hunsaker" writes: > ... So Im going to mark it as > ready for commmiter. Has this patch been tested on Windows? (Or more generally, with EXEC_BACKEND?) The reason I ask is that eyeballing the code suggests a couple of major problems in that area: * the startup/shutdown hooks will be installed in the postmaster process, but the patch expects them to be executed in a child process. I think nothing will happen. * in an EXEC_BACKEND situation, we re-execute process_shared_preload_libraries() when starting a fresh backend (but not in other kinds of child processes, which is why the other problem is a problem). This means re-executing the _PG_init function, which will try to redefine the custom GUC variables, which will fail. I don't think this is really a bug in this patch per se, it's a bug in the custom-GUC support; but nonetheless it looks like a problem. 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] posix_fadvise v22
On Thu, Jan 1, 2009 at 3:55 PM, Tom Lane wrote: > "Robert Haas" writes: >> Am I correct in thinking that the only thing we're really checking for >> here is whether a trivial posix_fadvise() call returns success? If >> so, is this test really worth doing? > > Runtime tests performed during configure are generally a bad idea to > start with --- it's impossible to do any such thing in a > cross-compilation scenario, for example. OK, here's an update of Greg's patch with the runtime configure test ripped out, some minor documentation tweaks, and a few unnecessary whitespace diff hunks quashed. I think this is about ready for committer review. The only thing I haven't been able to do is demonstrate that this change actually produces a performance improvement. Either I'm testing the wrong thing, or it just doesn't provide any benefit on a single-spindle system. However, I believe that Greg has previously posted some fairly impressive performance results, so I'm not sure that my shortcomings in this area should be a bar to having a committer pick this one up. If more testing is needed, it would at least be helpful to have a committer specify what areas they are concerned about. ...Robert posix_fadvise_v23_rh1.diff.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] contrib/pg_stat_statements 1226
On Thu, Jan 1, 2009 at 17:28, Tom Lane wrote: > "Alex Hunsaker" writes: >> ... So Im going to mark it as >> ready for commmiter. > > Has this patch been tested on Windows? (Or more generally, with > EXEC_BACKEND?) I was under the impression thats where Itagaki-san develops.You'll note some other specific windows changes: pgstat_track_activity_query_size gains PGDLLIMPORT process_shared_preload_libraries() also seems of intreset: http://archives.postgresql.org/pgsql-hackers/2008-12/msg01416.php varoius carriage returns in the patch... I could be wrong though. > The reason I ask is that eyeballing the code suggests a couple of major > problems in that area: > > * the startup/shutdown hooks will be installed in the postmaster > process, but the patch expects them to be executed in a child process. > I think nothing will happen. I dunno about this one, not very familiar with EXEC_BACKEND > * in an EXEC_BACKEND situation, we re-execute > process_shared_preload_libraries() when starting a fresh backend > (but not in other kinds of child processes, which is why the other > problem is a problem). This means re-executing the _PG_init function, > which will try to redefine the custom GUC variables, which will fail. > I don't think this is really a bug in this patch per se, it's a bug > in the custom-GUC support; but nonetheless it looks like a problem. I see 3 options: - add a GUC_CUSTOM_REDEFINE flag - ignore redefines of custom gucs -change the define_custom_variable() to return a bool (or something) true if it got added false if it was already there Seems to me we could probably just ignore any redefines of custom gucs outright. Im not to worried about some other module picking the same custom guc. And frankly the op should notice. Especially when they go to add it to custom_variable_classes. -- 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] contrib/pg_stat_statements 1226
I wrote: > * in an EXEC_BACKEND situation, we re-execute > process_shared_preload_libraries() when starting a fresh backend > (but not in other kinds of child processes, which is why the other > problem is a problem). This means re-executing the _PG_init function, > which will try to redefine the custom GUC variables, which will fail. > I don't think this is really a bug in this patch per se, it's a bug > in the custom-GUC support; but nonetheless it looks like a problem. Oh, never mind that part. I was thinking that the child process would already know the real definition of the custom variable at the time it tries to load the shared library, but actually the mechanism for pushing GUC values into EXEC_BACKEND child processes doesn't transfer the whole variable definition. It causes any such values to be loaded as placeholders, and then the later load of the shared library converts the placeholder to a regular variable. So it all works, or nearly anyway: the code fails on a custom variable class whose name alphabetically precedes "custom_variable_class". http://archives.postgresql.org/pgsql-committers/2009-01/msg8.php 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] contrib/pg_stat_statements 1226
On Thu, Jan 1, 2009 at 19:59, Tom Lane wrote: > I wrote: >> * in an EXEC_BACKEND situation, we re-execute >> process_shared_preload_libraries() when starting a fresh backend >> (but not in other kinds of child processes, which is why the other >> problem is a problem). This means re-executing the _PG_init function, >> which will try to redefine the custom GUC variables, which will fail. >> I don't think this is really a bug in this patch per se, it's a bug >> in the custom-GUC support; but nonetheless it looks like a problem. > > Oh, never mind that part. I was thinking that the child process would > already know the real definition of the custom variable at the time it > tries to load the shared library, but actually the mechanism for pushing > GUC values into EXEC_BACKEND child processes doesn't transfer the whole > variable definition. It causes any such values to be loaded as > placeholders, and then the later load of the shared library converts the > placeholder to a regular variable. > So it all works, or nearly anyway: > the code fails on a custom variable class whose name alphabetically > precedes "custom_variable_class". Cool! Err interesting... > http://archives.postgresql.org/pgsql-committers/2009-01/msg8.php Yeah I saw your commits just shortly after hitting send on my reply :) -- 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] posix_fadvise v22
In theory there should be no benefit on a single spindle system. There could be a slight benefit due to reordering of I/o but only on a raid array would you see a significant speedup -- which should be about equal to the number of spindles. What would be interesting is whether you see a noticable speed *decrease* from having prefetching enabled when it isn't helping. Either due to having everything fit in shared buffers or everything fit in the filesystem cache (the latter should be more of a hit) Even if there is it doesn't really worry me. By default the feature is disabled and you should only really turn it on if ulu do have a raid array and want an individual query to make use if it. Now that there's an actual run-time sysconf check for the buggy glibc called by the guc function we arguably don't need the autoconf check_run check anymore anyways. -- Greg On 1 Jan 2009, at 21:43, "Robert Haas" wrote: On Thu, Jan 1, 2009 at 3:55 PM, Tom Lane wrote: "Robert Haas" writes: Am I correct in thinking that the only thing we're really checking for here is whether a trivial posix_fadvise() call returns success? If so, is this test really worth doing? Runtime tests performed during configure are generally a bad idea to start with --- it's impossible to do any such thing in a cross-compilation scenario, for example. OK, here's an update of Greg's patch with the runtime configure test ripped out, some minor documentation tweaks, and a few unnecessary whitespace diff hunks quashed. I think this is about ready for committer review. The only thing I haven't been able to do is demonstrate that this change actually produces a performance improvement. Either I'm testing the wrong thing, or it just doesn't provide any benefit on a single-spindle system. However, I believe that Greg has previously posted some fairly impressive performance results, so I'm not sure that my shortcomings in this area should be a bar to having a committer pick this one up. If more testing is needed, it would at least be helpful to have a committer specify what areas they are concerned about. ...Robert -- 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] Copyright update
On Thursday 01 January 2009 15:28:51 Bruce Momjian wrote: > Joshua D. Drake wrote: > > On Thu, 2009-01-01 at 14:47 -0500, Bruce Momjian wrote: > > > Andrew Chernow wrote: > > > > Bruce Momjian wrote: > > > > > Greg Stark wrote: > > > > >> Is that actually legal if we haven't modified the files? Or is the > > > > >> whole source tree considiered one work? > > > > > > > > > > One work, I assume. > > > > > > > > I am not a lawyer, but if its one work, why is there a notice in > > > > every source file? ISTM that if it were one work there would only > > > > have to be one notice. > > > > > > Because people often take source files and copy them for use in other > > > projects. > > > > I think the correct resolution to the question is to ask legal. Yes? > > So I can get three different answers? It is not a priority for me. > Nor does it need to be... copyright for organizations runs ~ 100 years, so a year here or there is unlikely to make much difference to any of us. (Though for future generations, we'd probably have been better off not having a copyright notice at all). -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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: [HACKERS] Proposed Patch to Improve Performance of Multi-BatchHash Join for Skewed Data Sets
On Tue, Dec 30, 2008 at 12:29 AM, Bryce Cutt wrote: > Here is the next patch version. Thanks for posting this update. This is definitely getting better, but I still see some style issues. We can work on fixing those once the rest of the details have been finalized. However, one question in this area - isn't ExecHashFreezeNextMCVPartition actually a most common TUPLE partition, rather than a most common VALUE partition (and similarly for ExecHashGetMCVPartition)? I'm not quite sure what to do about this as the names are already quite long - is there some better name for the functions and structure members than MostCommonTuplePartition? Maybe we could call it the in-memory partition and abbreviate it IMPartition throughout. I think that might make things more clear. > The code can now find the the MCVs in more cases. Even if the probe > side is an operator other than a seq scan (such as another hashjoin) > the code can now find the stats tuple for the underlying relation. You're using varnoold in a way that directly contradicts the comment in primnodes.h (essentially, that it's not used for anything other than debugging). I don't think this is a bad thing, but you have to patch the comment. Have you done any performance testing on the impact of this change? > The new idea of limiting the number of MCVs to a percentage of memory > has not been added yet. That's a pretty important change, I think, though it would be nice to have one of the committers chime in here. For those who may not have been following the thread closely, the current implementation's memory usage can go quite a bit higher than work_mem - the in-memory open hash table can be up to 1MB or so (if statistics_target = 10K) plus it can contain up to work_mem of tuples plus each batch can contain another work_mem of tuples. The proposal is to carve out 1-3% of work_mem for the in-memory hash table and leave the rest for the batches, thus hopefully not affecting the # of batches very much. If it doesn't look like the whole MCV list will fit, we'll take a shot at guessing what length prefix of it will. ...Robert -- 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] posix_fadvise v22
> Now that there's an actual run-time sysconf check for the buggy glibc called > by the guc function we arguably don't need the autoconf check_run check > anymore anyways. Isn't that the check I just removed for you, or are you talking about some other check that can also be removed? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Significantly larger toast tables on 8.4?
I just did a fresh import of my alpha database into 8.4 and noticed
that the size of the database had increased significantly:
8.4: 6.3G
8.3: 4.9G
Tracking it down the main difference seems to a toast tables namely this one:
ls -ltrh 8.3/base/16516/430156
-rw--- 1 postgres postgres 145M 2009-01-01 00:12 8.3/base/16516/430156
and the same toast table in 8.4:
ls -ltrh 8.4/base/16513/347706
-rw--- 1 postgres postgres 967M 2009-01-01 20:56 8.4/base/16513/347706
This table consists mainly of perl Storable binary blobs in a bytea
column schema looks like:
Column | Type |
Modifiers
---+--+-
action| text |
die_id | integer | not null default
nextval('dies_die_id_seq'::regclass)
cparam| bytea|
date_created | timestamp with time zone | not null default now()
db_profile| bytea|
debug | bytea|
defunct | smallint | not null default 0
env | bytea|
login | bytea|
msg | text |
open_user_id | integer |
page_load_id | integer |
session_id| integer |
state | bytea|
state_action | bytea|
user_id | integer |
whoops| bytea|
Indexes:
"dies_pkey" PRIMARY KEY, btree (die_id)
My hunch is its related to
http://git.postgresql.org/?p=postgresql.git;a=commit;h=3b6942b3f2fe733572c05a71cb2d12e5ece60cdbits
or for the CVS inclined
http://archives.postgresql.org/pgsql-committers/2008-03/msg00121.php
But if anything that looks like it should help reduce size...
Ideas?
--
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] Significantly larger toast tables on 8.4?
On Thu, Jan 1, 2009 at 21:30, Alex Hunsaker wrote: > http://git.postgresql.org/?p=postgresql.git;a=commit;h=3b6942b3f2fe733572c05a71cb2d12e5ece60cdbits ... typoed that its http://git.postgresql.org/?p=postgresql.git;a=commit;h=3b6942b3f2fe733572c05a71cb2d12e5ece60cd -- 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] posix_fadvise v22
Sorry for top-posting -- phone mail client sucks. I thought the autoconf ac_run_check was the test that people were questioning. That calls posix_fadvise to see if it crashes at configure time. The guc run-time check is checking for known-buggy versions of glibc using sysconf to check what version of glibc you have. -- Greg On 1 Jan 2009, at 23:11, "Robert Haas" wrote: Now that there's an actual run-time sysconf check for the buggy glibc called by the guc function we arguably don't need the autoconf check_run check anymore anyways. Isn't that the check I just removed for you, or are you talking about some other check that can also be removed? ...Robert -- 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] posix_fadvise v22
On Thu, Jan 1, 2009 at 11:49 PM, Greg Stark wrote: > Sorry for top-posting -- phone mail client sucks. > > I thought the autoconf ac_run_check was the test that people were > questioning. That calls posix_fadvise to see if it crashes at configure > time. Yes, that's what I removed. > The guc run-time check is checking for known-buggy versions of glibc using > sysconf to check what version of glibc you have. Right - that check is still in my updated patch. I think the confusion may stem from the fact that Tom and I used the word "runtime" to refer to the ac_run_check thing, because it is checking something about the runtime environment (namely, whether posix_fadvise works or not) at configure-time. In any event, it seems as though we are all on the same page. ...Robert -- 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] Significantly larger toast tables on 8.4?
On Thu, Jan 1, 2009 at 21:30, Alex Hunsaker wrote: > My hunch is its related to > http://git.postgresql.org/?p=postgresql.git;a=commit;h=3b6942b3f2fe733572c05a71cb2d12e5ece60cd > or for the CVS inclined > http::/archives.postgresql.org/pgsql-committers/2008-03/msg00121.php > > But if anything that looks like it should help reduce size... Looking at the patch we dont compress things > 1M anymore so I thought maybe I was hitting that. But no luck there are only 39 rows where the row size > 1M... With those 39 being about 22M each. Unless I calculated something wrong. Oh and CLUSTER and VACUUM dont seem to help. select count(1) from dies; count --- 52010 select count(1) from ( select coalesce(pg_column_size(action), 0) + coalesce(pg_column_size(die_id), 0) + coalesce(pg_column_size(cparam), 0) + coalesce(pg_column_size(date_created), 0) + coalesce(pg_column_size(db_profile), 0) + coalesce(pg_column_size(debug), 0) + coalesce(pg_column_size(defunct), 0) + coalesce(pg_column_size(env), 0) + coalesce(pg_column_size(login), 0) + coalesce(pg_column_size(msg), 0) + coalesce(pg_column_size(open_user_id), 0) + coalesce(pg_column_size(page_load_id), 0) + coalesce(pg_column_size(session_id), 0) + coalesce(pg_column_size(state), 0) + coalesce(pg_column_size(state_action), 0) + coalesce(pg_column_size(user_id), 0) + coalesce(pg_column_size(whoops), 0) as row_size from dies ) as foo where foo.row_size > 1024*1024; count --- 39 BTW is there a "cooler" way to do this? -- 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] SQL/MED compatible connection manager
On Thu, Jan 01, 2009 at 11:10:38PM +0200, Martin Pihlak wrote: > Peter Eisentraut wrote: > > Well, what this function essentially does is a text transformation of the > > options, something like this: > > > > peter=# SELECT array_to_string(fdwoptions || srvoptions || umoptions, ' ') > > FROM pg_foreign_data_wrapper fdw, pg_foreign_server srv, pg_user_mappings > > um > > WHERE fdw.oid = srv.srvfdw AND srv.oid = um.srvid; > >array_to_string > > - > > host=localhost port=5432 user=peter password=seKret > > (1 row) > > > > (You can enhance this with quoting etc., but that's the essence.) > > Essentially yes. Additional things include USAGE check on the server and user > mapping lookup (use public if no explicit mapping is specified). Without those > I'm not really sure this deserves a separate function at all. The main goal > is to provide standard semantics for the connection lookup, so that dblink, > plproxy, pl rpc etc. would not have to reinvent it. > > > So, we could add a function whose job it is to convert all options to a > > PostgreSQL connection string. I wouldn't worry about dealing with other > > wrappers specifically. They could still use the function, but the result > > would not make much sense. > > > This works for me. I'd implement this as a C function so it is > directly callable from other C modules. > > Another option is to implement it as a SRF, similar to what was > initially in the dummy wrapper. Just return all of the options for > fdw, server and user mapping. This is probably worth doing if there > are any users for this. So far I haven't noticed any enthusiasm, so > it might be better to start with just the connection string. The connection string could be pretty different if it's not a PostgreSQL database, so +1 on the SRF option :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Significantly larger toast tables on 8.4?
On Thu, Jan 1, 2009 at 22:44, Alex Hunsaker wrote: > Looking at the patch we dont compress things > 1M anymore so I thought > maybe I was hitting that. But no luck there are only 39 rows where > the row size > 1M... With those 39 being about 22M each. Oh my... 25 * 40 = 1000M So I guess my question is are we going to recommend to people that they manually compress their data just for 8.4? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
