Re: [HACKERS] Patch for reserved connections for replication users
On 2013-08-02 08:16:15 -0400, Robert Haas wrote: On Tue, Jul 30, 2013 at 3:10 AM, Gibheer gibh...@zero-knowledge.org wrote: here is an update off my patch based on the discussion with Marko Tiikkaja and Andres Freund. Marko and I had the idea of introducing reserved connections based on roles as it would create a way to garantuee specific roles to connect when other roles use up all connections for whatever reason. But Andreas said, that it would make connecting take much too long. So to just fix the issue at hand, we decided that adding max_wal_senders to the pool of reserved connections is better. With that, we are sure that streaming replication can connect to the master. So instead of creating a new configuration option I added max_wal_senders to the reserved connections and changed the check for new connections. The test.pl is a small script to test, if the patch does what it should. Hmm. It seems like this match is making MaxConnections no longer mean the maximum number of connections, but rather the maximum number of non-replication connections. I don't think I support that definitional change, and I'm kinda surprised if this is sufficient to implement it anyway (e.g. see InitProcGlobal()). I don't think the implementation is correct, but why don't you like the definitional change? The set of things you can do from replication connections are completely different from a normal connection. So using separate pools for them seems to make sense. That they end up allocating similar internal data seems to be an implementation detail to me. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Saturday, August 03, 2013 12:53 AM Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: * Josh Berkus (j...@agliodbs.com) wrote: A much simpler solution to the issue Stephen proposes is to have a way to start up the server with all settings from ALTER SYSTEM SET disabled, just like some software allows you to start it up in safe mode. See above for why I'm not thrilled wih this approach, unless it was set up to happen automatically, but you couldn't simply ignore *all* the ALTER SYSTEM SET parameters because then you might not be able to connect in due to some ALTER SYSTEM SET parameter being necessary for remote connectivity or authentication. Yeah, this approach is a nonstarter because there's no reason to assume that a postmaster started with default parameters will start successfully, or will be connectable-to if it does start. Maybe there's another postmaster hogging the default port, for instance. Okay, but user will always have option to start server with different value of parameter (pg_ctl -o -p 5434). Now as a summarization we have below ways to move forward: 1. Provide a way for user to start server if not able to start due to in-appropriate value of unsafe parameter a. already user has an option that he can mention value of any particular parameter with which sever can start b. keep one backup copy of parameters, so that user can option to start with that copy, else if that also doesn't work he can use point 'a'. 2. Don't allow unsafe parameters to be modified by ALTER SYSTEM a. List of un-safe parameters b. mechanism so that ALTER SYSTEM throws error for non-modifiable parameters c. user can view non-modifiable parameters (may be in pg_settings) d. some way such that if user wants to take risk of server not getting started, he should allow to modify such parameters. may be server is started with some specific option. This can reduce the fear Josh had regarding this command to be not of much use. I think if we choose Option-2, then one of the initial difficulty will be to get an agreement on list of un-safe parameters. I believe even if we want to go with Option-2, then in first cut the work should be minimized. With Regards, Amit Kapila. -- 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] query_planner() API change
Robert Haas robertmh...@gmail.com writes: On Sun, Aug 4, 2013 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think it's time to bite the bullet and *not* pass back completed paths. What's looking more attractive now is to just pass back the top-level RelOptInfo (final_rel in query_planner()). I tend to think this is a pretty good plan. I looked around a little more and noted that this would complicate the special-case handling of an empty join tree (viz, SELECT 2+2). Right now query_planner() just has to make the appropriate Result path and it's done. We'd have to create a dummy RelOptInfo representing an empty set of relations, which is a bit weird but probably not too unreasonable when all's said and done. I think this is reasonable. Thanks, Best regards, Etsuro Fujita -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bottlenecks with large number of relation segment files
Hello, I am looking the effect of having large number of relation files under $PGDATA/base/ (for example, in cases where I choose lower segment size using --with-segsize). Consider a case where I am working with a large database with large relations, for example a database similar in size to what pgbench -i -s 3500 would be. May the routines in fd.c become bottleneck with a large number of concurrent connections to above database, say something like pgbench -j 8 -c 128? Is there any other place I should be paying attention to? -- Amit Langote -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
Amit Kapila amit.kap...@huawei.com writes: On Saturday, August 03, 2013 12:53 AM Tom Lane wrote: Yeah, this approach is a nonstarter because there's no reason to assume that a postmaster started with default parameters will start successfully, or will be connectable-to if it does start. Maybe there's another postmaster hogging the default port, for instance. Okay, but user will always have option to start server with different value of parameter (pg_ctl -o -p 5434). You're assuming that the user starts the postmaster manually. On most modern installations there's a few layers of scripting in there, which might not be that easy to hack to add some command-line parameters, even assuming that the DBA has sufficient wits about him to think of this solution. (When your postmaster unexpectedly fails to restart at four in the morning, having to think of such an approach isn't what you want to be doing.) My point here is just that we should keep the parameter values in plain text files, so that one possible solution is reverting a bogus change with vi/emacs/your-weapon-of-choice. If we improve matters so that the only possible way to fix the parameter setting is via a running postmaster, we've narrowed the number of escape routes that a frantic DBA will have. And what would we have bought for that? Not much. 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] query_planner() API change
While we could complicate query_planner()'s API even more to add some understanding of unnecessary resjunk items, I think this is probably the straw that breaks the camel's back for the current approach here. There is already a comment like this in query_planner(): * This introduces some undesirable coupling between this code and * grouping_planner, but the alternatives seem even uglier; we couldn't * pass back completed paths without making these decisions here. I agree with the idea,but am trying to understand why adding understanding of resjunk columns is a bad idea. Just for understanding purpose, could you please elaborate a bit on it? Regards, Atri -- Regards, Atri l'apprenant -- 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] query_planner() API change
On Mon, Aug 5, 2013 at 3:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: I've been looking at what it would take to do proper cost estimation for the recently-discussed patch to suppress calculation of unnecessary ORDER BY expressions. Can you please mention the subject of the thread? I tried to locate the thread based on this description, but couldn't locate it. Are you referring to the discussion related to aggregation with specified ordering? A doubt at the end ... It turns out that knowledge of that would have to propagate into query_planner(), because the place where we do the cost comparison between unsorted and presorted paths is in there (planmain.c lines 390ff in HEAD). As it stands, query_planner() will actually refuse to return the presorted path to grouping_planner() at all if it thinks it's a loser cost-wise, meaning grouping_planner() would have no opportunity to override the decision. So there's no way to fix this without some API change for query_planner(). While we could complicate query_planner()'s API even more to add some understanding of unnecessary resjunk items, I think this is probably the straw that breaks the camel's back for the current approach here. There is already a comment like this in query_planner(): * This introduces some undesirable coupling between this code and * grouping_planner, but the alternatives seem even uglier; we couldn't * pass back completed paths without making these decisions here. I think it's time to bite the bullet and *not* pass back completed paths. What's looking more attractive now is to just pass back the top-level RelOptInfo (final_rel in query_planner()). We could remove all three output parameters of query_planner(), and essentially just move lines 265-420 (pretty much everything after the make_one_rel() call) into planner.c. Since that code is almost all about grouping-related choices, this seems like it'll be a net improvement modularity-wise, even though it'll make grouping_planner() even bigger. We could probably ameliorate the latter problem by putting the calculation of num_groups and adjustment of tuple_fraction into a subroutine. Can we change the query_planner() to return both the paths (presorted and unsorted) irrespective of the cost of presorted path, and let grouping_planner() (or any caller of query_planner()) handle which of them to pick up? Objections, better ideas? 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 -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Postgres Database Company
[HACKERS] FOR UPDATE/SHARE incompatibility with GROUP BY, DISTINCT, HAVING and window functions
Hi all, By having a look at the documentation of SELECT, it is not specified that FOR SHARE/UPDATE and friends are incompatible with the clauses in $subject http://www.postgresql.org/docs/9.2/static/sql-select.html This restriction is explicitly cited for INTERSECT and UNION though, so people can get easily confused IMHO for the other clauses. Does it make sense to mention in the documentation those limitations like in the patch attached? If yes, perhaps this should be back-patched, after removing the part about FOR NO KEY UPDATE and FOR KEY SHARE of course from the patch attached for 9.2 and prior versions... Regards, -- Michael 20130805_forlock_doc.patch Description: Binary 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] query_planner() API change
I agree with the idea,but am trying to understand why adding understanding of resjunk columns is a bad idea. Just for understanding purpose, could you please elaborate a bit on it? Although I may not have understood your question correctly, I think it is good to see http://www.postgresql.org/message-id/14993.1354552...@sss.pgh.pa.us Best regards, Etsuro Fujita -- 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] query_planner() API change
On Mon, Aug 5, 2013 at 3:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: I've been looking at what it would take to do proper cost estimation for the recently-discussed patch to suppress calculation of unnecessary ORDER BY expressions. Can you please mention the subject of the thread? I tried to locate the thread based on this description, but couldn't locate it. Please see http://www.postgresql.org/message-id/6543.1375470...@sss.pgh.pa.us Best regards, Etsuro Fujita -- 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] [GENERAL] Bottlenecks with large number of relation segment files
Hi Amit, (2013/08/05 15:23), Amit Langote wrote: May the routines in fd.c become bottleneck with a large number of concurrent connections to above database, say something like pgbench -j 8 -c 128? Is there any other place I should be paying attention to? What kind of file system did you use? When we open file, ext3 or ext4 file system seems to sequential search inode for opening file in file directory. And PostgreSQL limit FD 1000 per process. It seems too small. Please change src/backend/storage/file/fd.c at max_files_per_process = 1000; If we rewrite it, We can change limit of FD per process. I have already created fix-patch about this problem in postgresql.conf, and will submit next CF. Regards, -- Mitsumasa KONDO NTT Open Source Software Center -- 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] [GENERAL] Bottlenecks with large number of relation segment files
On Mon, Aug 5, 2013 at 5:01 PM, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: Hi Amit, (2013/08/05 15:23), Amit Langote wrote: May the routines in fd.c become bottleneck with a large number of concurrent connections to above database, say something like pgbench -j 8 -c 128? Is there any other place I should be paying attention to? What kind of file system did you use? When we open file, ext3 or ext4 file system seems to sequential search inode for opening file in file directory. And PostgreSQL limit FD 1000 per process. It seems too small. Please change src/backend/storage/file/fd.c at max_files_per_process = 1000; If we rewrite it, We can change limit of FD per process. I have already created fix-patch about this problem in postgresql.conf, and will submit next CF. Thank you for replying Kondo-san. The file system is ext4. So, within the limits of max_files_per_process, the routines of file.c should not become a bottleneck? -- Amit Langote -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Monday, August 05, 2013 11:57 AM Tom Lane wrote: Amit Kapila amit.kap...@huawei.com writes: On Saturday, August 03, 2013 12:53 AM Tom Lane wrote: Yeah, this approach is a nonstarter because there's no reason to assume that a postmaster started with default parameters will start successfully, or will be connectable-to if it does start. Maybe there's another postmaster hogging the default port, for instance. Okay, but user will always have option to start server with different value of parameter (pg_ctl -o -p 5434). You're assuming that the user starts the postmaster manually. On most modern installations there's a few layers of scripting in there, which might not be that easy to hack to add some command-line parameters, even assuming that the DBA has sufficient wits about him to think of this solution. (When your postmaster unexpectedly fails to restart at four in the morning, having to think of such an approach isn't what you want to be doing.) My point here is just that we should keep the parameter values in plain text files, Here by text files, do you mean to say you are expecting file-per-guc-setting? so that one possible solution is reverting a bogus change with vi/emacs/your-weapon-of-choice. If we improve matters so that the only possible way to fix the parameter setting is via a running postmaster, we've narrowed the number of escape routes that a frantic DBA will have. And what would we have bought for that? Not much. Although it is not advisable to edit this file manually, but I think in such situations (postmaster doesn't start up due to inappropriate parameter value) it can help user to come out of situation much easily. My only point was to address the concerns regarding un-safe parameter values. With Regards, Amit Kapila. -- 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] [GENERAL] Bottlenecks with large number of relation segment files
(2013/08/05 17:14), Amit Langote wrote: So, within the limits of max_files_per_process, the routines of file.c should not become a bottleneck? It may not become bottleneck. 1 FD consumes 160 byte in 64bit system. See linux manual at epoll. Regards, -- Mitsumasa KONDO NTT Open Source Software Center -- 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] [GENERAL] Bottlenecks with large number of relation segment files
On 2013-08-05 18:40:10 +0900, KONDO Mitsumasa wrote: (2013/08/05 17:14), Amit Langote wrote: So, within the limits of max_files_per_process, the routines of file.c should not become a bottleneck? It may not become bottleneck. 1 FD consumes 160 byte in 64bit system. See linux manual at epoll. That limit is about max_user_watches, not the general cost of an fd. Afair they take up a a good more than that. Also, there are global limits to the amount of filehandles that can simultaneously opened on a system. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
Amit Kapila amit.kap...@huawei.com writes: On Monday, August 05, 2013 11:57 AM Tom Lane wrote: My point here is just that we should keep the parameter values in plain text files, Here by text files, do you mean to say you are expecting file-per-guc-setting? No, I don't think this argument really says much one way or the other about one-file vs one-file-per-GUC. I'm just lobbying against the put them in a catalog idea. 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] [GENERAL] Bottlenecks with large number of relation segment files
Andres Freund and...@2ndquadrant.com writes: ... Also, there are global limits to the amount of filehandles that can simultaneously opened on a system. Yeah. Raising max_files_per_process puts you at serious risk that everything else on the box will start falling over for lack of available FD slots. (PG itself tends to cope pretty well, since fd.c knows it can drop some other open file when it gets EMFILE.) We more often have to tell people to lower that limit than to raise 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] [v9.4] row level security
On Tue, Jul 23, 2013 at 11:30:14AM -0700, Josh Berkus wrote: Greg, It's more than the available experienced reviewers are willing to chew on fully as volunteers. The reward for spending review time is pretty low right now. Short of paying for review time, I don't think we have another solution for getting the big patches reviewed, except to rely on the major contributors who are paid full-time to hack Postgres. You know as well as me that, as consultants, we can get clients to pay for 10% extra time for review in the course of developing a feature, but the kind of time which patches like Row Security, Changesets, or other big patches need nobody is going to pay for on a contract basis. And nobody who is doing this in their spare time has that kind of block. So I don't think there's any good solution for the big patches. Let me echo Josh's comments above --- in the early years, we had trouble creating new features that required more than 1-2 weekends of development. We now have enough full-time developers that this is not a problem, but now it seems features requiring more than a weekend to _review_ are a problem, so full-time folks are again required here. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] query_planner() API change
Atri Sharma atri.j...@gmail.com writes: While we could complicate query_planner()'s API even more to add some understanding of unnecessary resjunk items, I think this is probably the straw that breaks the camel's back for the current approach here. There is already a comment like this in query_planner(): * This introduces some undesirable coupling between this code and * grouping_planner, but the alternatives seem even uglier; we couldn't * pass back completed paths without making these decisions here. I agree with the idea,but am trying to understand why adding understanding of resjunk columns is a bad idea. Just for understanding purpose, could you please elaborate a bit on it? It's just that doing it that way would require making both planner.c and planmain.c intimately involved in the decision about whether suppressing resjunk ORDER BY targets is a win. Really, anything to do with ordering/grouping implementation decisions is grouping_planner's business. So putting chunks of that logic in a completely different file doesn't seem like a great design, especially not if it requires weighing down query_planner()'s API even more. query_planner should only be concerned with scan/join planning. Basically, we'd be moving knowledge of how to dig the best paths out of a RelOptInfo from query_planner to grouping_planner --- which when you think about it seems like mostly a wash from a modularity standpoint, anyway. Having done that, we can get query_planner's fingers out of a number of issues that are really grouping_planner's business. Returning the RelOptInfo also eliminates the baked-into-the-API assumption that only one of the presorted path(s) could be of interest to grouping_planner, which is something I've long suspected would become a problem someday. On balance I'm feeling like this is a win even without considering the proposed changes for resjunk targets. 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] query_planner() API change
Ashutosh Bapat ashutosh.ba...@enterprisedb.com writes: Can we change the query_planner() to return both the paths (presorted and unsorted) irrespective of the cost of presorted path, and let grouping_planner() (or any caller of query_planner()) handle which of them to pick up? That's exactly the result this change would have, since all the potential Paths are attached to the top-level RelOptInfo. 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
[HACKERS] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Fri, Aug 2, 2013 at 4:05 PM, Stephen Frost sfr...@snowman.net wrote: I'm not even clear we do want this in /etc since none of our GUC options are repeatable things like Apache virtual servers. It actually makes *more* sense for pg_hba than it does for gucs. I think we can assume that in the future we'll have something like it however. I tend to agree with this also, though I can imagine wanting to separate things in a conf.d directory ala exim's conf.d directories, to allow tools like puppet to manage certain things environment-wide (perhaps krb_server_keyfile) while other configuration options are managed locally. Extensions are actually a pretty good argument for why conf.d in /etc (or wherever the non-auto-config is) is pretty important useful. That's the kind of thing conf.d directories are meant for. A user can install a package containing an extension and the extension would automatically drop in the config entries needed in that directory. -- greg -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
* Tom Lane (t...@sss.pgh.pa.us) wrote: Amit Kapila amit.kap...@huawei.com writes: On Monday, August 05, 2013 11:57 AM Tom Lane wrote: My point here is just that we should keep the parameter values in plain text files, Here by text files, do you mean to say you are expecting file-per-guc-setting? No, I don't think this argument really says much one way or the other about one-file vs one-file-per-GUC. I'm just lobbying against the put them in a catalog idea. imv there are things which should be in a catalog and things which shouldn't. I'm not sure that can the postmaster start up if its wrong is necessairly the right test to use but the results tend to line up with my expectations. postgresql.conf seems to have a number of items which don't even make a lot of sense to set server-side: enable_* being a good example. I know it *works* to set those in postgresql.conf, but I dislike using postgresql.conf as documentation for what options exist. I keep thinking that postgresql.conf should really be those items that define how PG interacts with the OS (which tends to also line up with things the postmaster needs correct to start) and the rest of the options should go somewhere else, perhaps as things which can be configured through this ALTER SYSTEM option, which starts to sound more like default GUC values for those items which take effect on individual backend startup. I've some more thoughts on this and will try to formulate them later today. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
* Greg Stark (st...@mit.edu) wrote: On Fri, Aug 2, 2013 at 4:05 PM, Stephen Frost sfr...@snowman.net wrote: I'm not even clear we do want this in /etc since none of our GUC options are repeatable things like Apache virtual servers. It actually makes *more* sense for pg_hba than it does for gucs. I think we can assume that in the future we'll have something like it however. I tend to agree with this also, though I can imagine wanting to separate things in a conf.d directory ala exim's conf.d directories, to allow tools like puppet to manage certain things environment-wide (perhaps krb_server_keyfile) while other configuration options are managed locally. Extensions are actually a pretty good argument for why conf.d in /etc (or wherever the non-auto-config is) is pretty important useful. That's the kind of thing conf.d directories are meant for. A user can install a package containing an extension and the extension would automatically drop in the config entries needed in that directory. Agreed, though I think there should be a difference between shared library load being added-to for extensions, and random extension-specific GUC.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] getting rid of SnapshotNow
On Thu, Jul 25, 2013 at 07:24:53PM -0400, Robert Haas wrote:
- /* Used by pre-9.0 binary upgrades */
- if (tuple-t_infomask HEAP_MOVED_OFF)
- {
- TransactionId xvac = HeapTupleHeaderGetXvac(tuple);
-
- if (TransactionIdIsCurrentTransactionId(xvac))
- return false;
- if (!TransactionIdIsInProgress(xvac))
- {
- if (TransactionIdDidCommit(xvac))
- {
- SetHintBits(tuple, buffer,
HEAP_XMIN_INVALID,
-
InvalidTransactionId);
- return false;
- }
- SetHintBits(tuple, buffer, HEAP_XMIN_COMMITTED,
- InvalidTransactionId);
- }
- }
- /* Used by pre-9.0 binary upgrades */
- else if (tuple-t_infomask HEAP_MOVED_IN)
- {
- TransactionId xvac = HeapTupleHeaderGetXvac(tuple);
-
- if (!TransactionIdIsCurrentTransactionId(xvac))
- {
- if (TransactionIdIsInProgress(xvac))
- return false;
- if (TransactionIdDidCommit(xvac))
- SetHintBits(tuple, buffer,
HEAP_XMIN_COMMITTED,
-
InvalidTransactionId);
- else
- {
- SetHintBits(tuple, buffer,
HEAP_XMIN_INVALID,
-
InvalidTransactionId);
- return false;
- }
- }
- }
One interesting aspect of this patch is that the backend code is no
longer even checking HEAP_MOVED_OFF and HEAP_MOVED_IN. However, we
can't reuse those bits because they could be set from pre-9.0 rows.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] getting rid of SnapshotNow
On 2013-08-05 11:17:08 -0400, Bruce Momjian wrote: One interesting aspect of this patch is that the backend code is no longer even checking HEAP_MOVED_OFF and HEAP_MOVED_IN. However, we can't reuse those bits because they could be set from pre-9.0 rows. The other tqual.c .satisfies routines still check it - and have to do so. It'd be nice to get rid of that, but this patch doesn't seem to get us nearer towards it :( Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] [COMMITTERS] pgsql: Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.
Some of the issues raised by Andres and Noah have been addressed. These all seemed simple and non-controversial, so I've just applied the suggested fixes. Some issues remain, such as how best to create the temp table used for the diff data, and the related simplification of the security context swapping that might become possible with a change there. Review of that area has raised a couple other questions that I'm looking into. These all probably amount to enough that I will add the patch(es) to address them to the next CF. Andres Freund and...@2ndquadrant.com wrote: On 2013-07-23 09:27:34 -0700, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: The loop over indexes in refresh_by_match_merge should index_open(ExclusiveLock) the indexes initially instead of searching the syscache manually. They are opened inside the loop in many cases anyway. There probably aren't any hazards currently, but I am not even sure about that. The index_open() in the loop at the very least processes the invalidation messages other backends send... Fixed. I'd even suggest using BuildIndexInfo() or such on the indexes, then you could use -ii_Expressions et al instead of peeking into indkeys by hand. Given that the function is in a source file described as containing code to create and destroy POSTGRES index relations and the comments for that function say that it stores the information about the index that's needed by FormIndexDatum, which is used for both index_build() and later insertion of individual index tuples, and we're not going to create or destroy an index or call FormIndexDatum or insert individual index tuples from this code, it would seem to be a significant expansion of the charter of that function. What benefits do you see to using that level? I'd prevent you from needing to peek into indkeys. Note that it's already used in various places. I looked at where it was and wasn't used, and continue to be skeptical. For example, both techniques are used in tablecmds.c; the technique you suggest is used where an index is being created, dropped, or index tuples are being manipulated, while the Form_pg_index structure is being used when the definition of the index is being examined without directly manipulating it. Compare what is being done in my code with the existing code for ATExecDropNotNull(), for example. [while comparison of indexed columns used OPERATOR() correctly, comparison of tid and rowvar values did not] I wasn't aware that people could override the equality operators for tid and RECORD [example proving the possibility] I think for the cases where you're comparing tids it's fine to just to hardcode the operator to OPERATOR(pg_catalog.=). Done. * I'd strongly suggest more descriptive table aliases than x, y, d. Those make the statements rather hard to parse. I guess. Those are intended to be internal, but I guess there's no reason not to be more verbose in the aliases. Well, for one, other people will read that code every now and then. I am not 100% convinced that all the statements are correct, and it's more effort to do so right now. Also, those statements will show up in error messages. Done. What I am thinking of is that you'll get a successfull REFRESH CONCURRENTLY but it will later error out at COMMIT time because there were constraint violations. Afaik we don't have any such behaviour for existing DDL and I don't like introducing it. REFRESH MATERIALIZED VIEW CONCURRENTLY is definitely not DDL. It is DML, and behavior should be consistent with that. (Note that the definition of the matview remains exactly the same after the statement executes as it was before; only the data is modified.) Without the CONCURRENTLY clause it's in the same sort of gray area as TRUNCATE TABLE, where it is essentially DML, but the implementation details are similar to that of DDL, so it may sometimes be hard to avoid DDL-like behaviors, even though it would be best to do so. We have no such problem here. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] StrategyGetBuffer optimization, take 2
My $company recently acquired another postgres based $company and migrated all their server operations into our datacenter. Upon completing the move, the newly migrated database server started experiencing huge load spikes. *) Environment description: Postgres 9.2.4 RHEL 6 32 cores virtualized (ESX) but with a dedicated host 256GB ram shared_buffers: 32G 96 application servers configured to max 5 connections each very fast i/o database size: ~ 200GB HS/SR: 3 slaves *) Problem description: The server normally hums along nicely with load 1.0 and no iowait -- in fact the server is massively over-provisioned. However, on semi-random basis (once every 1-2 days) load absolutely goes through the roof to 600+, no iowait, 90-100% (70%+ sys) cpu. It hangs around like that for 5-20 minutes then resolves as suddenly as it started. There is nothing interesting going on application side (except the application servers are all piling on) but pg_locks is recording lots of contention on relation 'extension locks'. One interesting point is that the slaves are also affected, but the precise point of the high load affects happens some seconds after the master. *) Initial steps taken: RhodiumToad aka (Andrew G) has seen this in the wild several times and suggested dropping shared_buffers significantly might resolve the situation short term. That was done on friday night, and so far problem has not re-occurred. *) What I think is happening: I think we are again getting burned by getting de-scheduled while holding the free list lock. I've been chasing this problem for a long time now (for example, see: http://postgresql.1045698.n5.nabble.com/High-SYS-CPU-need-advise-td5732045.html) but not I've got a reproducible case. What is happening this: 1. in RelationGetBufferForTuple (hio.c): fire LockRelationForExtension 2. call ReadBufferBI. this goes down the chain until StrategyGetBuffer() 3. Lock free list, go into clock sweep loop 4. while holding clock sweep, hit 'hot' buffer, spin on it 5. get de-scheduled 6. now enter the 'hot buffer spin lock lottery' 7. more/more backends pile on, linux scheduler goes bezerk, reducing chances of winning #6 8. finally win the lottery. lock released. everything back to normal. *) what I would like to do to fix it: see attached patch. This builds on the work of Jeff Janes to remove the free list lock and has some extra optimizations in the clock sweep loop: optimization 1: usage count is advisory. it is not updated behind the buffer lock. in the event there are a large sequences of buffers with 0 usage_count, this avoids spamming the cache_line lock; you decrement and hope for the best optimization 2: refcount is examined during buffer allocation without a lock. if it's 0, buffer is assumed pinned (even though it may not in fact be) and sweep continues optimization 3: sweep does not wait on buf header lock. instead, it does 'try lock' and bails if the buffer is determined pinned. I believe this to be one of the two critical optimizations optimization 4: remove free list lock (via Jeff Janes). This is the other optimization: one backend will no longer be able to shut down buffer allocation *) what I'm asking for Is the analysis and the patch to fix the perceived problem plausible without breaking other stuff.. If so, I'm inclined to go further with this. This is not the only solution on the table for high buffer contention, but IMNSHO it should get a lot of points for being very localized. Maybe a reduced version could be tried retaining the freelist lock but keeping the 'trylock' on the buf header. *) further reading: https://www.google.com/url?sa=trct=jq=esrc=ssource=webcd=1cad=rjaved=0CC8QFjAAurl=http%3A%2F%2Fpostgresql.1045698.n5.nabble.com%2FHigh-SYS-CPU-need-advise-td5732045.htmlei=hsb_Uc6pB4Ss9ASN7YHoAgusg=AFQjCNEefMxOvjvW3Alg4TiXqCSAUmDR7Asig2=EyPOQa9XbVEND5kwzTeBJgbvm=bv.50165853,d.eWU http://www.postgresql.org/message-id/cahyxu0x47d4n6edpynyadshxqqxkohelv2cbrgr_2ngrc8k...@mail.gmail.com http://postgresql.1045698.n5.nabble.com/Page-replacement-algorithm-in-buffer-cache-td5749236.html merlin buffer2.patch Description: Binary 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] [COMMITTERS] pgsql: Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.
Hi On 2013-08-05 08:37:57 -0700, Kevin Grittner wrote: Some of the issues raised by Andres and Noah have been addressed. These all seemed simple and non-controversial, so I've just applied the suggested fixes. Cool! I'd even suggest using BuildIndexInfo() or such on the indexes, then you could use -ii_Expressions et al instead of peeking into indkeys by hand. Given that the function is in a source file described as containing code to create and destroy POSTGRES index relations and the comments for that function say that it stores the information about the index that's needed by FormIndexDatum, which is used for both index_build() and later insertion of individual index tuples, and we're not going to create or destroy an index or call FormIndexDatum or insert individual index tuples from this code, it would seem to be a significant expansion of the charter of that function. What benefits do you see to using that level? I'd prevent you from needing to peek into indkeys. Note that it's already used in various places. I looked at where it was and wasn't used, and continue to be skeptical. For example, both techniques are used in tablecmds.c; the technique you suggest is used where an index is being created, dropped, or index tuples are being manipulated, while the Form_pg_index structure is being used when the definition of the index is being examined without directly manipulating it. Compare what is being done in my code with the existing code for ATExecDropNotNull(), for example. The RelationGetIndexExpressions() you mentioned in the commit sounds like a good plan to me. Didn't remember that existed. Don't think the ATExecDropNotNull() comparison is really valid, we need to know more details there, but anyway, you've found something a good bit better. What I am thinking of is that you'll get a successfull REFRESH CONCURRENTLY but it will later error out at COMMIT time because there were constraint violations. Afaik we don't have any such behaviour for existing DDL and I don't like introducing it.0 REFRESH MATERIALIZED VIEW CONCURRENTLY is definitely not DDL. It is DML, and behavior should be consistent with that. (Note that the definition of the matview remains exactly the same after the statement executes as it was before; only the data is modified.) Without the CONCURRENTLY clause it's in the same sort of gray area as TRUNCATE TABLE, where it is essentially DML, but the implementation details are similar to that of DDL, so it may sometimes be hard to avoid DDL-like behaviors, even though it would be best to do so. We have no such problem here. But there's no usecase that makes deferred checks and similar useful afaics. And it seems to me like it certainly will confuse users that a second RMVC fails (via CheckTableNotInUse()) because there's still a deferred trigger queue. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] getting rid of SnapshotNow
On Mon, Aug 5, 2013 at 05:22:28PM +0200, Andres Freund wrote: On 2013-08-05 11:17:08 -0400, Bruce Momjian wrote: One interesting aspect of this patch is that the backend code is no longer even checking HEAP_MOVED_OFF and HEAP_MOVED_IN. However, we can't reuse those bits because they could be set from pre-9.0 rows. The other tqual.c .satisfies routines still check it - and have to do so. It'd be nice to get rid of that, but this patch doesn't seem to get us nearer towards it :( Oh, sorry, thanks for pointing that out --- somehow I missed it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Thu, Aug 1, 2013 at 03:40:22PM +0100, Greg Stark wrote: Why isn't it enough to just dump out all variables with a source of alter system to a text file? You can either have a single global lock around that operation or write it to a new file and move it into place. -- greg On 1 Aug 2013 15:19, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-01 15:17:04 +0100, Greg Stark wrote: We don't need per guc locking. This is the whole objection Tom had about this patch being more complex than it has to be. IIRC he objected to using locking *at all* because a simple one-file-per-setting approach should be used. I am unclear why we don't need a lock around _each_ GUC, i.e. if two sessions try to modify the same GUC at the same time. And if we need a lock, seems we can have just one and write all the settings to one file --- it is not like we have trouble doing locking, though this is cluster-wide locking. How would users handle renamed GUC variables, as we have done in the past? Would pg_dumpall dump the settings out? Would unrecognized settings throw an error, causing pg_upgrade to fail? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] StrategyGetBuffer optimization, take 2
On 2013-08-05 10:49:08 -0500, Merlin Moncure wrote: optimization 4: remove free list lock (via Jeff Janes). This is the other optimization: one backend will no longer be able to shut down buffer allocation I think splitting off the actual freelist checking into a spinlock makes quite a bit of sense. And I think a separate one should be used for the actual search for the clock sweep. I don't think the unlocked increment of nextVictimBuffer is a good idea though. nextVictimBuffer jumping over NBuffers under concurrency seems like a recipe for disaster to me. At the very, very least it will need a good wad of comments explaining what it means and how you're allowed to use it. The current way will lead to at least bgwriter accessing a nonexistant/out of bounds buffer via StrategySyncStart(). Possibly it won't even save that much, it might just increase the contention on the buffer header spinlock's cacheline. Greetings, Andres Freund -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
All, To limit the argument here, let's please not argue about things which people already agree on. So: We seem to have consensus around: A) the inadvisability of storing GUCs in a system catalog. B) the utility of a conf.d in /etc/ which may have nothing to do with ALTER SYSTEM SET C) that any file for ALTER SYSTEM SET go in $PGDATA somewhere. What we are still arguing about: D) one-big-file vs. file-per-setting E) whether unsafe settings or restart settings should be allowed in ALTER SYSTEM SET. F) whether admins need the ability to disable ALTER SYSTEM SET. Since each of D, E and F issues are completely orthagonal to each other, I suggest that maybe we argue them each out on their own threads? I'll start. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] CREATE EVENT TRIGGER syntax
On Fri, Jul 19, 2013 at 05:12:16PM -0700, Dimitri Fontaine wrote: Joe Abbate j...@freedomcircle.com writes: What is the purpose of the [ AND ... ] at the end of the WHEN clause? Is that for later releases, when presumably additional filter_variables will be introduced? Right now, if I add AND tag IN ... I get an Yes. I had other filter variables in some versions of the patch, but we're yet to agree on a design for the things I wanted to solve with them. See http://www.postgresql.org/message-id/m2txrsdzxa@2ndquadrant.fr for some worked out example of the CONTEXT part of the Event Trigger proposal. So do we want to keep that AND in the 9.3beta and 9.4 documentation? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 2013-08-05 12:18:25 -0400, Bruce Momjian wrote: On Thu, Aug 1, 2013 at 03:40:22PM +0100, Greg Stark wrote: Why isn't it enough to just dump out all variables with a source of alter system to a text file? You can either have a single global lock around that operation or write it to a new file and move it into place. -- greg On 1 Aug 2013 15:19, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-01 15:17:04 +0100, Greg Stark wrote: We don't need per guc locking. This is the whole objection Tom had about this patch being more complex than it has to be. IIRC he objected to using locking *at all* because a simple one-file-per-setting approach should be used. I am unclear why we don't need a lock around _each_ GUC, i.e. if two sessions try to modify the same GUC at the same time. And if we need a lock, seems we can have just one and write all the settings to one file --- it is not like we have trouble doing locking, though this is cluster-wide locking. If you have two sessions modifying the same variable, one is going to win and overwrite the other's setting with or without locking around GUCs unless you error out if somebody else holds the lock. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] StrategyGetBuffer optimization, take 2
optimization 2: refcount is examined during buffer allocation without a lock. if it's 0, buffer is assumed pinned (even though it may not in fact be) and sweep continues +1. I think this shall not lead to much problems, since a lost update cannot,IMO, lead to disastrous result. At most, a buffer page can survive for an extra clock sweep. optimization 3: sweep does not wait on buf header lock. instead, it does 'try lock' and bails if the buffer is determined pinned. I believe this to be one of the two critical optimizations +1 again. I believe the try lock will be a sort of filter before the actual check, hence reducing the contention. Regards, Atri -- Regards, Atri l'apprenant -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
Andres Freund and...@2ndquadrant.com writes: On 2013-08-05 12:18:25 -0400, Bruce Momjian wrote: I am unclear why we don't need a lock around _each_ GUC, i.e. if two sessions try to modify the same GUC at the same time. And if we need a lock, seems we can have just one and write all the settings to one file --- it is not like we have trouble doing locking, though this is cluster-wide locking. If you have two sessions modifying the same variable, one is going to win and overwrite the other's setting with or without locking around GUCs unless you error out if somebody else holds the lock. The point of a lock is just to ensure that the end result is one valid state or the other, and not something corrupt. We would certainly need a lock if we write to a single file. With file-per-GUC, we could possibly dispense with a lock if we depend on atomic file rename(); though whether it's wise to assume that for Windows is unclear. (Note that we ought to write a temp file and rename it into place anyway, to avoid possibly corrupting the existing file on out-of-disk-space. The only thing that needs discussion is whether to add an explicit lock around that.) 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] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
Stephen, all: (forked from main ALTER SYSTEM discussion. this thread is meant to discuss only this question: E) whether unsafe settings or restart settings should be allowed in ALTER SYSTEM SET.) On 08/02/2013 01:48 PM, Stephen Frost wrote: Reflecting on this a bit more, I'm curious what your list-of-15 is. Based on serving literally hundreds of clients, the below are the settings we change on client servers 50% or more of the time. Other settings I touch maybe 10% of the time. THese are also, in general, the settings which I modify when we create Puppet/Chef/Salt scripts for clients. listen_addresses*@ shared_buffers*@ work_mem maintenance_work_mem effective_cache_size synchronous_commit (because of amazon) wal_buffers*@ checkpoint_segments*@ checkpoint_completion_target* (because of ext3) autovacuum* (turn off for data warehouses, turn back on for very mistaken users) stats_file_directory*@ replication/archiving settings as a set*@ wal_level, max_wal_senders, wal_keep_segments, hot_standby, archive_mode and archive_command logging settings as a set logging_collector* everything else * = requires a cold start to change @ potentially can cause failure to restart Note that two of the settings, shared_buffers and wal_buffers, become much less of an issue for restarting the system in 9.3. Also, it's possible that Heikki's automated WAL log management might deal with out-of-disk-space better than currently, which makes that less of a risk. However, you'll see that among the 11 core settings, 7 require a full restart, and 5 could potentially cause failure to restart. That means that from my perspective, ALTER SYSTEM SET is at least 45% useless if it can't touch unsafe settngs, and 63% useless if it can't touch any setting which requires a restart. Adding the replication settings into things makes stuff significantly worse that way, although ALTER SYSTEM SET would be very useful for logging options provided that logging_collector was turned on. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Mon, Aug 5, 2013 at 12:53:24PM -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-08-05 12:18:25 -0400, Bruce Momjian wrote: I am unclear why we don't need a lock around _each_ GUC, i.e. if two sessions try to modify the same GUC at the same time. And if we need a lock, seems we can have just one and write all the settings to one file --- it is not like we have trouble doing locking, though this is cluster-wide locking. If you have two sessions modifying the same variable, one is going to win and overwrite the other's setting with or without locking around GUCs unless you error out if somebody else holds the lock. The point of a lock is just to ensure that the end result is one valid state or the other, and not something corrupt. We would certainly need a lock if we write to a single file. With file-per-GUC, we could possibly dispense with a lock if we depend on atomic file rename(); though whether it's wise to assume that for Windows is unclear. (Note that we ought to write a temp file and rename it into place anyway, to avoid possibly corrupting the existing file on out-of-disk-space. The only thing that needs discussion is whether to add an explicit lock around that.) So my larger question is why a single-guc-per-file avoids corruption while having all the gucs in a single file does not. It seems the later reduces the probability of lost updates, but does not eliminate it. Also, should I be concerned that everyone removed my pg_upgrade question in their replies. :-O -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 2013-08-05 12:53:24 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-08-05 12:18:25 -0400, Bruce Momjian wrote: I am unclear why we don't need a lock around _each_ GUC, i.e. if two sessions try to modify the same GUC at the same time. And if we need a lock, seems we can have just one and write all the settings to one file --- it is not like we have trouble doing locking, though this is cluster-wide locking. If you have two sessions modifying the same variable, one is going to win and overwrite the other's setting with or without locking around GUCs unless you error out if somebody else holds the lock. The point of a lock is just to ensure that the end result is one valid state or the other, and not something corrupt. Sure. That's what I tried to explain. I've completely missed to mention in this mail why the safe rename dance is sufficient to guarantee a correct file with one-file-per-guc... We would certainly need a lock if we write to a single file. With file-per-GUC, we could possibly dispense with a lock if we depend on atomic file rename(); though whether it's wise to assume that for Windows is unclear. Afaik it should be safe on anything NT based via Replacefile. Anything else/earlier isn't supported anyways... I think there's already a custom rename() implementation on windows? Don't we already rely on atomic renames working for the control file? (Note that we ought to write a temp file and rename it into place anyway, to avoid possibly corrupting the existing file on out-of-disk-space. The only thing that needs discussion is whether to add an explicit lock around that.) Yes, agreed. All versions of the patch that I've read have done so luckily. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] mvcc catalo gsnapshots and TopTransactionContext
On Fri, Jul 12, 2013 at 11:42:23AM +0200, Andres Freund wrote: On 2013-07-11 15:09:45 -0400, Tom Lane wrote: It never has been, and never will be, allowed to call the catcache code without being in a transaction. What do you think will happen if the requested row isn't in cache? A table access, that's what, and that absolutely requires being in a transaction. I'd like to add an Assert like in the attached patch making sure we're in a transaction. Otherwise it's far too easy not to hit an error during development because everything is cached - and syscache usage isn't always obvious from the outside to the naive or the tired. The following test case reliably hits this new assertion: create table t (c int); begin; create index on t (c); savepoint q; insert into t values (1); select 1/0; Stack trace: #2 0x00761159 in ExceptionalCondition (conditionName=value optimized out, errorType=value optimized out, fileName=value optimized out, lineNumber=value optimized out) at assert.c:54 #3 0x0074fc53 in SearchCatCache (cache=0xc53048, v1=139479, v2=0, v3=0, v4=0) at catcache.c:1072 #4 0x0075c011 in SearchSysCache (cacheId=0, key1=369539, key2=6, key3=18446744073709551615, key4=0) at syscache.c:909 #5 0x00757e57 in RelationReloadIndexInfo (relation=0x7f2dd5fffea0) at relcache.c:1770 #6 0x0075817b in RelationClearRelation (relation=0x7f2dd5fffea0, rebuild=1 '\001') at relcache.c:1926 #7 0x007588c6 in RelationFlushRelation (relationId=139479) at relcache.c:2076 #8 RelationCacheInvalidateEntry (relationId=139479) at relcache.c:2138 #9 0x0075185d in LocalExecuteInvalidationMessage (msg=0xcde778) at inval.c:546 #10 0x00751185 in ProcessInvalidationMessages (hdr=0xc0d390, func=0x7517d0 LocalExecuteInvalidationMessage) at inval.c:422 #11 0x00751a3f in AtEOSubXact_Inval (isCommit=0 '\000') at inval.c:973 #12 0x004cb986 in AbortSubTransaction () at xact.c:4250 #13 0x004cbf05 in AbortCurrentTransaction () at xact.c:2863 #14 0x006968f6 in PostgresMain (argc=1, argv=0x7fff7cf71610, dbname=0xc13b60 test, username=0xbedc88 nm) at postgres.c:3848 #15 0x0064c2b5 in BackendRun () at postmaster.c:4044 #16 BackendStartup () at postmaster.c:3733 #17 ServerLoop () at postmaster.c:1571 #18 0x0064fa8d in PostmasterMain (argc=1, argv=0xbe84a0) at postmaster.c:1227 #19 0x005e2dcd in main (argc=1, argv=0xbe84a0) at main.c:196 When we call AtEOSubXact_Inval() or AtEOXact_Inval() with a relation still open, we can potentially get a relcache rebuild and therefore a syscache lookup as shown above. CommitSubTransaction() is also potentially affected, though I don't have an SQL-level test case for that. It calls CommandCounterIncrement() after moving to TRANS_COMMIT. That CCI had better find no invalidations of open relations, or we'll make syscache lookups. (In simple tests, any necessary invalidations tend to happen at the CCI in CommitTransactionCommand(), so the later CCI does in fact find nothing to do. I have little confidence that should be counted upon, though.) How might we best rearrange things to avoid these hazards? Thanks, nm -- Noah Misch 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
Bruce Momjian br...@momjian.us writes: So my larger question is why a single-guc-per-file avoids corruption while having all the gucs in a single file does not. If it's file-per-GUC, then when two sessions try to write different GUCs, there is no conflict. When they try to write the same GUC, the end result will be one value or the other (assuming use of atomic rename). Which seems fine. If it's single-file, and we don't lock, then when two sessions try to write different GUCs, one's update can be lost altogether, because whichever one renames second didn't see the first one's update. That doesn't seem acceptable. 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] Comma Comma Comma 8601
On Jul 23, 2013, at 6:24 PM, David E. Wheeler da...@justatheory.com wrote: I kind of suspect not, since this fails: david=# select '12:24:53 654'::time; ERROR: invalid input syntax for type time: 12:24:53 654 LINE 1: select '12:24:53 654'::time; ^ I would have guessed that the time parser gets to a state where it knows it is dealing with a ISO-8601-style time. But I have not looked at the code, of course. I added this to the To Dos so it won’t get lost. https://wiki.postgresql.org/wiki/Todo#Dates_and_Times Best, David -- 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] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
(this discussion concerns issue (D), file-per-setting vs. one-big-file) On 08/05/2013 10:16 AM, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: So my larger question is why a single-guc-per-file avoids corruption while having all the gucs in a single file does not. If it's file-per-GUC, then when two sessions try to write different GUCs, there is no conflict. When they try to write the same GUC, the end result will be one value or the other (assuming use of atomic rename). Which seems fine. If it's single-file, and we don't lock, then when two sessions try to write different GUCs, one's update can be lost altogether, because whichever one renames second didn't see the first one's update. That doesn't seem acceptable. I'll also point out that some of our settings only really work in combinations of two or more settings. For example, one doesn't want to set archive_mode = on unless one is setting archive_command as well. And generally if one sets sequential_page_cost, one is changing the other cost parameters as well. And logging parameters are generally managed as a set. So the case of two sessions both modifying ALTER SYSTEM SET, and one succeeding for some-but-all-GUCS, and the other succeeding for some-but-not-all-GUCs, would not be user-friendly or pretty, even if each setting change succeeded or failed atomically. Also, one of the reasons Amit went to one-big-file was the question of: if each setting is changed independantly, how do we know when to send the backend a reload()? IIRC, anyway. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] query_planner() API change
On Mon, Aug 5, 2013 at 6:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: Atri Sharma atri.j...@gmail.com writes: While we could complicate query_planner()'s API even more to add some understanding of unnecessary resjunk items, I think this is probably the straw that breaks the camel's back for the current approach here. There is already a comment like this in query_planner(): * This introduces some undesirable coupling between this code and * grouping_planner, but the alternatives seem even uglier; we couldn't * pass back completed paths without making these decisions here. I agree with the idea,but am trying to understand why adding understanding of resjunk columns is a bad idea. Just for understanding purpose, could you please elaborate a bit on it? It's just that doing it that way would require making both planner.c and planmain.c intimately involved in the decision about whether suppressing resjunk ORDER BY targets is a win. Really, anything to do with ordering/grouping implementation decisions is grouping_planner's business. So putting chunks of that logic in a completely different file doesn't seem like a great design, especially not if it requires weighing down query_planner()'s API even more. query_planner should only be concerned with scan/join planning. Basically, we'd be moving knowledge of how to dig the best paths out of a RelOptInfo from query_planner to grouping_planner --- which when you think about it seems like mostly a wash from a modularity standpoint, anyway. Having done that, we can get query_planner's fingers out of a number of issues that are really grouping_planner's business. Returning the RelOptInfo also eliminates the baked-into-the-API assumption that only one of the presorted path(s) could be of interest to grouping_planner, which is something I've long suspected would become a problem someday. On balance I'm feeling like this is a win even without considering the proposed changes for resjunk targets. Thanks a ton for such a detailed explanation. So, query_planner() returns both,the unsorted and presorted paths and lets grouping_planner() decide between them, and grouping_planner() ignores unnecessary ORDER BY columns,right? Sorry if I am being naive here, I am just trying to assimilate the overall process for my understanding. Thanks, atri -- Regards, Atri l'apprenant -- 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] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Mon, Aug 5, 2013 at 01:16:10PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: So my larger question is why a single-guc-per-file avoids corruption while having all the gucs in a single file does not. If it's file-per-GUC, then when two sessions try to write different GUCs, there is no conflict. When they try to write the same GUC, the end result will be one value or the other (assuming use of atomic rename). Which seems fine. If it's single-file, and we don't lock, then when two sessions try to write different GUCs, one's update can be lost altogether, because whichever one renames second didn't see the first one's update. That doesn't seem acceptable. Yes, I understand now --- with file-per-GUC, you can say one was later than the other, but when changing two different GUCs, a single file implementation doesn't have that logical clarity. FYI, we will need to use create-rename even without the problem of creating corrupted files because we need it to avoid backends reading partially-written files. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Mon, Aug 5, 2013 at 10:21:56AM -0700, Josh Berkus wrote: (this discussion concerns issue (D), file-per-setting vs. one-big-file) On 08/05/2013 10:16 AM, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: So my larger question is why a single-guc-per-file avoids corruption while having all the gucs in a single file does not. If it's file-per-GUC, then when two sessions try to write different GUCs, there is no conflict. When they try to write the same GUC, the end result will be one value or the other (assuming use of atomic rename). Which seems fine. If it's single-file, and we don't lock, then when two sessions try to write different GUCs, one's update can be lost altogether, because whichever one renames second didn't see the first one's update. That doesn't seem acceptable. I'll also point out that some of our settings only really work in combinations of two or more settings. For example, one doesn't want to set archive_mode = on unless one is setting archive_command as well. And generally if one sets sequential_page_cost, one is changing the other cost parameters as well. And logging parameters are generally managed as a set. So the case of two sessions both modifying ALTER SYSTEM SET, and one succeeding for some-but-all-GUCS, and the other succeeding for some-but-not-all-GUCs, would not be user-friendly or pretty, even if each setting change succeeded or failed atomically. Wow, that is a good point. I just mentioned in a previous email that sessions are going to be created while this is going on and they can't see partially-written files, so we need the create/rename dance. One new twist is that certain settings have to be _all_ set, or the backend is going to throw an error. Also, one of the reasons Amit went to one-big-file was the question of: if each setting is changed independantly, how do we know when to send the backend a reload()? IIRC, anyway. Remember the backends starting up during this too. A global lock is looking unavoidable. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
Josh, * Josh Berkus (j...@agliodbs.com) wrote: Based on serving literally hundreds of clients, the below are the settings we change on client servers 50% or more of the time. Other settings I touch maybe 10% of the time. THese are also, in general, the settings which I modify when we create Puppet/Chef/Salt scripts for clients. And that is actually half my point with all of this- these things are often set by change management systems like puppet or chef and you can be almost guaranteed that the folks running those pieces of the infrastructure will be *very* unhappy if the DBA tries to adjust those parameters independently because it'll almost certainly *not work*. In particular, that goes for things like listen_addresses and port, but also shared_buffers, checkpoint_segments, wal_keep_segments, etc. When it comes to things like the stats_file_directory, the admin will need to be doing things on the host server anyway to set that up (directories, permissions, etc), so it's unclear to me why they would care to have the ability to modify these things through ALTER SYSTEM. Also, to reiterate, what this patch is primairly adding, from my pov anyway, is a way for DBAs to modify settings where they don't have access to do so directly. Today, you can prevent that from happening while giving the DBA superuser (which is required generally because we don't have sufficiently fine-grained control to avoid it) by making postgresql.conf root-owned. To be clear, I'm not suggesting that this is some malicious act on the part of the DBA. In the end, I'd prefer that we shrink up the config file to just those items which really need to be in a config file, have a conf.d structure which allows tools like puppet/chef to more easily break up and have different components (eg: logging) configured specially for certain classes of systems, and then move all of the internal-to-PG type of config (enable_*, etc) into a catalog which is modified through an 'ALTER CLUSTER SET' type command, allowing DBAs to set parameters across the entire cluster instead of just for a given database or role. Josh, I really have to ask- are these people who are implementing puppet to control these configs really clamoring to have an 'ALTER SYSTEM' PG command to have to code against instead of dealing with text files? I feel like you're arguing for these parameters to be modifiable through ALTER SYSTEM on the grounds that these parameters need to be set at some point and in some way and not because having them set through ALTER SYSTEM actually makes any *sense*. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] Re: File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
Josh Berkus wrote (this discussion concerns issue (D), file-per-setting vs. one-big-file) So the case of two sessions both modifying ALTER SYSTEM SET, and one succeeding for some-but-all-GUCS, and the other succeeding for some-but-not-all-GUCs, would not be user-friendly or pretty, even if each setting change succeeded or failed atomically. Can the final file write occur only at COMMIT; with anything inside a transaction simply staged up for later saving (or rollback). The file write phase as a whole then needs to be atomic and not just a single GUC-file. Could the system read the last update timestamp of each GUC-file when the original statement is executed and then re-read all of them at commit and fail with some kind of serialization error if the last-update timestamp on any of the files has changed? I dislike the idea of any kind of automatic reload. That said some kind of have their been any configuration changes since last reload? query/function makes sense. In can be plugged into Nagios or similar to warn if these changes are occurring but made live. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Proposal-for-Allow-postgresql-conf-values-to-be-changed-via-SQL-tp5729917p5766338.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
Josh Berkus j...@agliodbs.com writes: I'll also point out that some of our settings only really work in combinations of two or more settings. For example, one doesn't want to set archive_mode = on unless one is setting archive_command as well. And generally if one sets sequential_page_cost, one is changing the other cost parameters as well. And logging parameters are generally managed as a set. So the case of two sessions both modifying ALTER SYSTEM SET, and one succeeding for some-but-all-GUCS, and the other succeeding for some-but-not-all-GUCs, would not be user-friendly or pretty, even if each setting change succeeded or failed atomically. That is a killer point. So really the value of the global lock is to ensure serializability when transactions are updating multiple GUCs. 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] Re: File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Mon, Aug 5, 2013 at 10:52:52AM -0700, David Johnston wrote: Josh Berkus wrote (this discussion concerns issue (D), file-per-setting vs. one-big-file) So the case of two sessions both modifying ALTER SYSTEM SET, and one succeeding for some-but-all-GUCS, and the other succeeding for some-but-not-all-GUCs, would not be user-friendly or pretty, even if each setting change succeeded or failed atomically. Can the final file write occur only at COMMIT; with anything inside a transaction simply staged up for later saving (or rollback). The file write phase as a whole then needs to be atomic and not just a single GUC-file. Yes, the writes would only happen at commit, but I see of no way to have the renames happen atomically. Could the system read the last update timestamp of each GUC-file when the original statement is executed and then re-read all of them at commit and fail with some kind of serialization error if the last-update timestamp on any of the files has changed? That sounds like a mess --- clearly a global lock and a single file for all GUCs would be perferrable. I dislike the idea of any kind of automatic reload. That said some kind of have their been any configuration changes since last reload? query/function makes sense. In can be plugged into Nagios or similar to warn if these changes are occurring but made live. Who talked about automatic reload? The bigger problem is new backends starting and seeing partial state during the renames. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
* Josh Berkus (j...@agliodbs.com) wrote: I'll also point out that some of our settings only really work in combinations of two or more settings. For example, one doesn't want to set archive_mode = on unless one is setting archive_command as well. And one doesn't want to set archive_command w/o doing some kind of setup work on the server in question, perhaps setting up SSH keys or putting a decent shell script in place to be called from archive_command. That's part of my issue w/ this- the parameters which can't be set up correctly through sole use of ALTER SYSTEM should be excluded from it and instead set up through a config file. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
On 08/05/2013 10:49 AM, Stephen Frost wrote: Josh, I really have to ask- are these people who are implementing puppet to control these configs really clamoring to have an 'ALTER SYSTEM' PG command to have to code against instead of dealing with text files? I feel like you're arguing for these parameters to be modifiable through ALTER SYSTEM on the grounds that these parameters need to be set at some point and in some way and not because having them set through ALTER SYSTEM actually makes any *sense*. Nope. ALTER SYSTEM, from my POV, is mainly for folks who *don't* use Puppet/Chef/whatever. Here's where I see ALTER SYSTEM being useful: * invididually managed servers with out centralized management (i.e. one DBA, one server). * developer machines (i.e. laptops and vms) * automated testing of tweaking performance parameters * setting logging parameters temporarily on systems under centralized management For that reason, the only way in which I think it makes sense to try to make ALTER SYSTEM set work together with Puppet/Chef is in the rather limited context of modifying the logging settings for limited-time data collection. Mostly, ALTER SYSTEM SET is for systems were people *aren't* using Puppet/Chef. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Mon, Aug 5, 2013 at 02:02:50PM -0400, Stephen Frost wrote: * Josh Berkus (j...@agliodbs.com) wrote: I'll also point out that some of our settings only really work in combinations of two or more settings. For example, one doesn't want to set archive_mode = on unless one is setting archive_command as well. And one doesn't want to set archive_command w/o doing some kind of setup work on the server in question, perhaps setting up SSH keys or putting a decent shell script in place to be called from archive_command. That's part of my issue w/ this- the parameters which can't be set up correctly through sole use of ALTER SYSTEM should be excluded from it and instead set up through a config file. Yes, but many setting changes don't require file system changes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 08/05/2013 11:02 AM, Stephen Frost wrote: * Josh Berkus (j...@agliodbs.com) wrote: I'll also point out that some of our settings only really work in combinations of two or more settings. For example, one doesn't want to set archive_mode = on unless one is setting archive_command as well. And one doesn't want to set archive_command w/o doing some kind of setup work on the server in question, perhaps setting up SSH keys or putting a decent shell script in place to be called from archive_command. That's part of my issue w/ this- the parameters which can't be set up correctly through sole use of ALTER SYSTEM should be excluded from it and instead set up through a config file. I'm really trying to make progress on this discussion by separating it into the distinct, orthagonal, arguments which need to be resolved individually. You are NOT helping by then intermixing the threads. We are already discussing that on unsafe GUCs, please keep it there. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
On 08/05/2013 08:02 PM, Josh Berkus wrote: On 08/05/2013 10:49 AM, Stephen Frost wrote: Josh, I really have to ask- are these people who are implementing puppet to control these configs really clamoring to have an 'ALTER SYSTEM' PG command to have to code against instead of dealing with text files? I feel like you're arguing for these parameters to be modifiable through ALTER SYSTEM on the grounds that these parameters need to be set at some point and in some way and not because having them set through ALTER SYSTEM actually makes any *sense*. Nope. ALTER SYSTEM, from my POV, is mainly for folks who *don't* use Puppet/Chef/whatever. Here's where I see ALTER SYSTEM being useful: * invididually managed servers with out centralized management (i.e. one DBA, one server). * developer machines (i.e. laptops and vms) * automated testing of tweaking performance parameters * setting logging parameters temporarily on systems under centralized management overridding the configuration system, that will just lead to very confused sysadmins why something that was configurated now behaves differently and I cause operational hazards because people _WILL_ forget changing those temporary only settings back? For that reason, the only way in which I think it makes sense to try to make ALTER SYSTEM set work together with Puppet/Chef is in the rather limited context of modifying the logging settings for limited-time data collection. Mostly, ALTER SYSTEM SET is for systems were people *aren't* using Puppet/Chef. I tend to disagree, the current approach of ALTER SYSTEM requiring superuser basically means: * in a few years from now people will just use superuser over the network for almost all stuff because its easy and I can click around in $gui, having potential unsafe operations available over the network will in turn cause a lot of actual downtime (in a lot of cases the reason why people want remote management is because the don't have physical/shell access - so if they break stuff they cannot fix) * for classic IaaS/SaaS/DBaaS the ALTER SYSTEM seems to be mostly useless in the current form - because most of them will not or cannot hand out flat out superuser (like if you run a managed service you might want customers to be able to tweak some stuff but say not archive/pitr/replication stuff because the responsibility for backups is with the hosting company) Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Autovacuum different in 9.2.4?
Hello, I seem to recall autovacuum changes landing for 9.2.4. Can someone please describe what those changes were and how they could affect usage? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
All: (this thread concerns argument (F) whether or not admins need a way to disable ALTER SYSTEM SET) On 08/01/2013 04:03 PM, Dimitri Fontaine wrote: If only we could trigger some actions when a command is about to be executed, in a way that it's easy for the user to implement whatever policy he fancies… Oh, maybe I should finish preparing those patches for Event Triggers to be fully usable in 9.4 then ;) I don't see this as a solution at all. Mr. Sysadmin, we've given the DBAs a new tool which allows them to override your version-controlled database parameter settings. You can turn it off, though, by using this incredibly complicated, brand-new Event Trigger tool which requires writing lots of SQL code to make work. Per Stephen Frost's arguments, some system owners are going to be opposed to allowing ALTER SYSTEM SET at all because it can mess systems up and cause downtime. Yes, that's already true of ALTER ROLE and ALTER DATABASE, but ALTER SYSTEM SET expands this the ability of the DBA to change setting substantially. That's obviously its benefit, but it's also clearly a misfeature for some system owners. Also, to be blunt, most DBAs/DEVs don't *know* about ALTER ROLE/DATABASE. I don't think this is a small thing. I really think we'll get a LOT of blowback from sysadmins -- and maybe even refusals to upgrade -- if we add ALTER SYSTEM SET in 9.4.0 with no easy way to disable it. Having an easy way to disable ALTER SYSTEM SET is *also* a good way to get out of the whole situation of I set shared_buffers to 100GB using ALTER SYSTEM SET and now PostgreSQL won't start problem. As I've said before, I think the disable switch can be a follow-up patch to the main ALTER SYSTEM SET patch. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
On 08/05/2013 07:01 PM, Josh Berkus wrote: Stephen, all: (forked from main ALTER SYSTEM discussion. this thread is meant to discuss only this question: E) whether unsafe settings or restart settings should be allowed in ALTER SYSTEM SET.) On 08/02/2013 01:48 PM, Stephen Frost wrote: Reflecting on this a bit more, I'm curious what your list-of-15 is. Based on serving literally hundreds of clients, the below are the settings we change on client servers 50% or more of the time. Other settings I touch maybe 10% of the time. THese are also, in general, the settings which I modify when we create Puppet/Chef/Salt scripts for clients. listen_addresses*@ shared_buffers*@ work_mem maintenance_work_mem effective_cache_size synchronous_commit (because of amazon) wal_buffers*@ checkpoint_segments*@ checkpoint_completion_target* (because of ext3) autovacuum* (turn off for data warehouses, turn back on for very mistaken users) stats_file_directory*@ replication/archiving settings as a set*@ wal_level, max_wal_senders, wal_keep_segments, hot_standby, archive_mode and archive_command logging settings as a set logging_collector* everything else * = requires a cold start to change @ potentially can cause failure to restart Note that two of the settings, shared_buffers and wal_buffers, become much less of an issue for restarting the system in 9.3. Also, it's possible that Heikki's automated WAL log management might deal with out-of-disk-space better than currently, which makes that less of a risk. However, you'll see that among the 11 core settings, 7 require a full restart, and 5 could potentially cause failure to restart. That means that from my perspective, ALTER SYSTEM SET is at least 45% useless if it can't touch unsafe settngs, and 63% useless if it can't touch any setting which requires a restart. Adding the replication settings into things makes stuff significantly worse that way, although ALTER SYSTEM SET would be very useful for logging options provided that logging_collector was turned on. not sure at all I agree with our % useless measure but we need to consider that having all of those available over remote means they will suddenly become action at a distance thingies, people will play with them more and randomly change stuff, and a lot of those can break the entire system because of say overrunning system resources. The same thing can happen now just as well, but having them available from remote will also result in tools doing this and people that have less information about the hardware and system or what else is going on on that box. Also we have to keep in mind that in most scenarios the logfile and potentially reported errors/warnings there will be useless because people will never see them... Stefan -- 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] whichever ALTER SYSTEM thread is the right one
* Bruce Momjian (br...@momjian.us) wrote: On Mon, Aug 5, 2013 at 02:02:50PM -0400, Stephen Frost wrote: * Josh Berkus (j...@agliodbs.com) wrote: I'll also point out that some of our settings only really work in combinations of two or more settings. For example, one doesn't want to set archive_mode = on unless one is setting archive_command as well. And one doesn't want to set archive_command w/o doing some kind of setup work on the server in question, perhaps setting up SSH keys or putting a decent shell script in place to be called from archive_command. That's part of my issue w/ this- the parameters which can't be set up correctly through sole use of ALTER SYSTEM should be excluded from it and instead set up through a config file. Yes, but many setting changes don't require file system changes. Agreed, and those are the changes which would make sense to allow through ALTER SYSTEM (or ALTER CLUSTER, or whatever)- settings which don't require file system, network, etc, changes. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
On 08/05/2013 11:14 AM, Stefan Kaltenbrunner wrote: * in a few years from now people will just use superuser over the network for almost all stuff because its easy and I can click around in $gui, having potential unsafe operations available over the network will in turn cause a lot of actual downtime (in a lot of cases the reason why people want remote management is because the don't have physical/shell access - so if they break stuff they cannot fix) See thread Disabling ALTER SYSTEM SET. * for classic IaaS/SaaS/DBaaS the ALTER SYSTEM seems to be mostly useless in the current form - because most of them will not or cannot hand out flat out superuser (like if you run a managed service you might want customers to be able to tweak some stuff but say not archive/pitr/replication stuff because the responsibility for backups is with the hosting company) 100% in agreement. If someone thought we were serving DBAAS with this, they haven't paid attention to the patch. However, there are other places where ALTER SYSTEM SET will be valuable. For example, for anyone who wants to implement an autotuning utility. For example, I'm writing a network utility which checks bgwriter stats and tries adjusting settings over the network to improve checkpoint issues. Not having to SSH configuration files into place (and make sure they're not overridden by other configuration files) would make writing that script a *lot* easier. Same thing with automated performance testing. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Should we remove not fast promotion at all?
Hi all, We discussed the $SUBJECT in the following threads: http://www.postgresql.org/message-id/ca+tgmozbr+wl8e7mf_krp6fy4fd2pmr11tpiuyjmfx_vtg1...@mail.gmail.com http://www.postgresql.org/message-id/CAHGQGwEBUvgcx8X+Z0Hh+VdwYcJ8KCuRuLt1jSsxeLxPcX=0...@mail.gmail.com Our consensus seems to remove not fast promotion at all because there is no use case for that promotion. Attached patch removes not fast promotion. Barring any objections, I will commit this patch. Regards, On Sat, Aug 3, 2013 at 4:31 PM, Tomonari Katsumata t.katsumata1...@gmail.com wrote: Hi, I made a patch for REL9_3_STABLE which gets rid of old promote processing. please check it. This patch make PostgreSQL do fast promoting(*) always. (*) which means skipping long checkpoint before increasing timeline. And after this, I'll do make another patch for unlinking files which are created by user as a trigger_file or pg_ctl promote command. --- Tomonari Katsumata 2013/7/30 Fujii Masao masao.fu...@gmail.com On Sat, Jul 27, 2013 at 6:57 PM, Tomonari Katsumata t.katsumata1...@gmail.com wrote: Hi, Yes, it prevents PROMOTE_SIGNAL_FILE from remaining even if both promote files exist. The command(unlink(PROMOTE_SIGNAL_FILE)) here is for unusualy case. Because the case is when done both procedures below. - user create promote file on PGDATA - user issue pg_ctl promote I understand the reason. But I think it's better to unlink(PROMOTE_SIGNAL_FILE) before unlink(FAST_PROMOTE_SIGNAL_FILE). Because FAST_PROMOTE_SIGNAL_FILE is definetly there but PROMOTE_SIGNAL_FILE is sometimes there or not there. I could not understand why that's better. Could you elaborate that? I'm sorry for less explanation. I've thought that errno would be set ENOENT and this may lead something wrong. I checked this and I know it's not problem. sorry for confusing you. And I have another question linking this behavior. I think TriggerFile should be removed too. This is corner-case but it will happen. How do you think of it ? I don't have strong opinion about that. I've never heard the complaint about that current behavior so far. For example, please imagine the cascading replication environment and using old master as a standby without copying the timeline history file to new standby. --- 1. replicating 3 servers(A,B,C) A-B-C (trigger_file = /tmp/trig is set in recovery_recovery.conf on B and C.) 2. stop server A and promoting server B with touch /tmp/trig;pg_ctl promote Why do you need to both create the trigger file and run pg_ctl promote? Anyway, if the patch is useful for fail-safe and it doesn't break the current behavior, I'd be happy to apply it. You are suggesting that we should remove the trigger file in CheckForStandbyTrigger() even if pg_ctl promote is executed. But there can be some cases where we can get out of the WAL replay loop, for example, reach the recovery_target_xxx. So ISTM we should try to remove both the trigger file and promote file at the end of recovery instead. Thought? B-C (/tmp/trig file remains on server B) 4. stop server B and promoting server C with pg_ctl promote C 5. making server B connect for standby of server C C-B - In step5 server B will promote as soon as it starts, because /tmp/trig is stil there. One question is that: we really still need to support normal promote? pg_ctl promote provides only way to do fast promotion. If we want to do normal promotion, we need to create PROMOTE_SIGNAL_FILE and send the SIGUSR1 signal to postmaster by hand. This seems messy. I think that we should remove normal promotion at all, or change pg_ctl promote so that provides also the way to do normal promotion. I think he merit of fast promote is - allowing quick connection by skipping checkpoint and its demerit is - taking little bit longer when crash-recovery If it is seldom to happen its crash soon after promoting and fast promte never breaks consistency of database cluster, I think we don't need normal promotion. You can execute checkpoint after fast promotion for that. OK. Then I think we should do below things. - removing normal promotion at all from source - adding the know-how you suggest on document IMO either is necessary. Regards, -- Fujii Masao -- Fujii Masao remove_not_fast_promote_v1.patch Description: Binary 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Mon, Aug 5, 2013 at 11:16:24AM -0700, Josh Berkus wrote: I don't see this as a solution at all. Mr. Sysadmin, we've given the DBAs a new tool which allows them to override your version-controlled database parameter settings. You can turn it off, though, by using this incredibly complicated, brand-new Event Trigger tool which requires writing lots of SQL code to make work. Per Stephen Frost's arguments, some system owners are going to be opposed to allowing ALTER SYSTEM SET at all because it can mess systems up and cause downtime. Yes, that's already true of ALTER ROLE and ALTER DATABASE, but ALTER SYSTEM SET expands this the ability of the DBA to change setting substantially. That's obviously its benefit, but it's also clearly a misfeature for some system owners. Also, to be blunt, most DBAs/DEVs don't *know* about ALTER ROLE/DATABASE. I don't think this is a small thing. I really think we'll get a LOT of blowback from sysadmins -- and maybe even refusals to upgrade -- if we -- Really? Is that a reasonable statement? add ALTER SYSTEM SET in 9.4.0 with no easy way to disable it. Having an easy way to disable ALTER SYSTEM SET is *also* a good way to get out of the whole situation of I set shared_buffers to 100GB using ALTER SYSTEM SET and now PostgreSQL won't start problem. As I've said before, I think the disable switch can be a follow-up patch to the main ALTER SYSTEM SET patch. You are mixing the behaviors of disabling the ALTER SYSTEM SET command with recognizing ALTER SYSTEM SET settings already made --- the later would be to fix a problem with the server not starting. However, frankly, these are flat files, so I don't see a problem with having the administrator modify the flat file. Would disabling the ALTER SYSTEM SET command also disable recognizing any ALTER SYSTEM SET commands already performed? Maybe that was already understood, but I missed that point. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
* Josh Berkus (j...@agliodbs.com) wrote: Nope. ALTER SYSTEM, from my POV, is mainly for folks who *don't* use Puppet/Chef/whatever. Ok, that's fine, but let's try to avoid making life difficult for those who *do* use puppet/chef/whatever. This capability runs a very high risk of that by allowing a DBA who *isn't* a sysadmin to go modifying things that depend on external-to-PG factors. Here's where I see ALTER SYSTEM being useful: * invididually managed servers with out centralized management (i.e. one DBA, one server). * developer machines (i.e. laptops and vms) The above strikes me as being already dealt with through pgAdmin and the 'admin pack', if the user wants a GUI to use for modifying these parameters (which seems like what they'd primairly get out of ALTER SYSTEM SET- pgAdmin, or whatever $gui wouldn't have to depend on the admin pack). * automated testing of tweaking performance parameters This sounds like you'd need tooling around it to make it work anyway, which could probably handle modifying a text file, but even if not, these paremeters may be on the 'safe' list. * setting logging parameters temporarily on systems under centralized management This is the kind of argument that I could get behind- in an environment where logs are shipped to a server where DBAs can view and analyze them, being able to modify the logging parameters on the fly could be useful (eg: log_min_duration_statement or similar). As I tried to get at up-thread, my concern is primairly around those parameters which can't be set sensibly through ALTER SYSTEM because they depend on other activities happening. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
On 08/05/2013 08:21 PM, Josh Berkus wrote: On 08/05/2013 11:14 AM, Stefan Kaltenbrunner wrote: * in a few years from now people will just use superuser over the network for almost all stuff because its easy and I can click around in $gui, having potential unsafe operations available over the network will in turn cause a lot of actual downtime (in a lot of cases the reason why people want remote management is because the don't have physical/shell access - so if they break stuff they cannot fix) See thread Disabling ALTER SYSTEM SET. * for classic IaaS/SaaS/DBaaS the ALTER SYSTEM seems to be mostly useless in the current form - because most of them will not or cannot hand out flat out superuser (like if you run a managed service you might want customers to be able to tweak some stuff but say not archive/pitr/replication stuff because the responsibility for backups is with the hosting company) 100% in agreement. If someone thought we were serving DBAAS with this, they haven't paid attention to the patch. However, there are other places where ALTER SYSTEM SET will be valuable. For example, for anyone who wants to implement an autotuning utility. For example, I'm writing a network utility which checks bgwriter stats and tries adjusting settings over the network to improve checkpoint issues. Not having to SSH configuration files into place (and make sure they're not overridden by other configuration files) would make writing that script a *lot* easier. Same thing with automated performance testing. seems like an excessively narrow usecase to me - people doing that kind of specific testing can easily do automation over ssh, and those are very few vs. having to maintain a fairly complex piece of code in postgresql core. Nevertheless my main point is that people _WILL_ use this as a simple convinience tool not fully understanding all the complex implications, and in a few years from now running people with superuser by default (because people will create cool little tools say to change stuff from my tray or using $IOS app that have a little small comment make sure to create the user WITH SUPERUSER and people will follow like lemmings. Stefan -- 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] comment for fast promote
On Sat, Aug 3, 2013 at 4:31 PM, Tomonari Katsumata t.katsumata1...@gmail.com wrote: Hi, I made a patch for REL9_3_STABLE which gets rid of old promote processing. please check it. This patch make PostgreSQL do fast promoting(*) always. (*) which means skipping long checkpoint before increasing timeline. Thanks for the patch! I fixed the bug that your patch accidentally makes archive recovery skip end-of-recovery checkpoint, fixed some typos, refactored the source code and posted the updated version of the patch in http://www.postgresql.org/message-id/CAHGQGwGYkF+CvpOMdxaO=+anazc1oo9o4lqwo50mxpvfj+0...@mail.gmail.com Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] don't own lock of type?
Hello, What exactly causes this? WARNING: you don't own a lock of type ExclusiveLock Does this mean the user calling the lock doesn't own the object? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- 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] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
On 08/05/2013 11:28 AM, Stephen Frost wrote: * Josh Berkus (j...@agliodbs.com) wrote: Nope. ALTER SYSTEM, from my POV, is mainly for folks who *don't* use Puppet/Chef/whatever. Ok, that's fine, but let's try to avoid making life difficult for those who *do* use puppet/chef/whatever. This capability runs a very high risk of that by allowing a DBA who *isn't* a sysadmin to go modifying things that depend on external-to-PG factors. See thread Disabling ALTER SYSTEM SET. In short, I agree with you. Here's where I see ALTER SYSTEM being useful: * invididually managed servers with out centralized management (i.e. one DBA, one server). * developer machines (i.e. laptops and vms) The above strikes me as being already dealt with through pgAdmin and the 'admin pack', if the user wants a GUI to use for modifying these parameters (which seems like what they'd primairly get out of ALTER SYSTEM SET- pgAdmin, or whatever $gui wouldn't have to depend on the admin pack). Except that forcing developers to install the admin pack and pgadmin to get this functionality is a high barrier to entry exactly where we don't want one. * automated testing of tweaking performance parameters This sounds like you'd need tooling around it to make it work anyway, which could probably handle modifying a text file, but even if not, these paremeters may be on the 'safe' list. Well, frankly, it's the main reason why *I* want ALTER SYSTEM SET. It makes my job writing automated testing scripts easier. Certainly it was possible before, but there's value in easier. And that's the reason I don't want you to take away the ability to modify shared_buffers et. al. ;-) On 08/05/2013 11:30 AM, Stefan Kaltenbrunner wrote: Nevertheless my main point is that people _WILL_ use this as a simple convinience tool not fully understanding all the complex implications, and in a few years from now running people with superuser by default (because people will create cool little tools say to change stuff from my tray or using $IOS app that have a little small comment make sure to create the user WITH SUPERUSER and people will follow like lemmings. Most of our users not on Heroku are running with superuser as the app user now. Like, 95% of them based on my personal experience (because our object permissions management sucks). In that this feature will further discourage people from having a separate application user, there's some argument. However, it's really an argument for not having ALTER SYSTEM SET *at all* rather than restricting it to safe GUCs, no? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] HeapTupleSatisfiesDirty fails to test HEAP_XMAX_IS_LOCKED_ONLY for TransactionIdIsInProgress(...)
On Fri, Aug 2, 2013 at 5:25 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: The attached test case runs under isolationtester to exersise the problem. I've tested it against 9.2, 9.3, and HEAD, but Andres looked over the code and says the underlying bug goes back to the commit of MVCC, it's just become easier to trigger. To reliably test this with isolationtester I had to horribly abuse pg_advisory_lock(...) so that I could force the first SELECT ... FOR UPDATE to acquire its snapshot before the UPDATE runs. I didn't apply the test case. I think if we want to test tqual.c behavior we will need to introduce a large battery of tests. I would like to see more opinions on whether that's something we want. I haven't read this particular test, but I do think we could get a lot of mileage out of applying the isolation tester stuff to more things, and am generally in favor of that. It has the advantages of (1) allowing tests that require more than one session and (2) being run regularly the buildfarm; but it's not something developers typically run before every commit, so the run time of the test suite shouldn't be a big issue for anyone. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] don't own lock of type?
On Mon, Aug 5, 2013 at 2:32 PM, Joshua D. Drake j...@commandprompt.com wrote: Hello, What exactly causes this? WARNING: you don't own a lock of type ExclusiveLock Does this mean the user calling the lock doesn't own the object? It means there's a bug. Either in PostgreSQL, or some loadable module you're using. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET
* Josh Berkus (j...@agliodbs.com) wrote: On 08/05/2013 11:14 AM, Stefan Kaltenbrunner wrote: * in a few years from now people will just use superuser over the network for almost all stuff because its easy and I can click around in $gui, having potential unsafe operations available over the network will in turn cause a lot of actual downtime (in a lot of cases the reason why people want remote management is because the don't have physical/shell access - so if they break stuff they cannot fix) See thread Disabling ALTER SYSTEM SET. I'm really not thrilled with this solution. However, there are other places where ALTER SYSTEM SET will be valuable. For example, for anyone who wants to implement an autotuning utility. For example, I'm writing a network utility which checks bgwriter stats and tries adjusting settings over the network to improve checkpoint issues. Not having to SSH configuration files into place (and make sure they're not overridden by other configuration files) would make writing that script a *lot* easier. Same thing with automated performance testing. I've done a fair bit of this myself and find templating postgresql.conf isn't really all that hard and comes in handy for a lot things, not to mention that it can then be integrated into a configuration management system more easily.. Still, I'm fine w/ parameters which don't depend on external things happening, which I think covers a lot of this use-case. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
* Bruce Momjian (br...@momjian.us) wrote: On Mon, Aug 5, 2013 at 11:16:24AM -0700, Josh Berkus wrote: I don't think this is a small thing. I really think we'll get a LOT of blowback from sysadmins -- and maybe even refusals to upgrade -- if we -- Really? Is that a reasonable statement? I don't believe Josh was saying that in jest.. I certainly believe that it could happen. As I've said before, I think the disable switch can be a follow-up patch to the main ALTER SYSTEM SET patch. Even if it's a different patch, it should go in at the same time, imv.. You are mixing the behaviors of disabling the ALTER SYSTEM SET command with recognizing ALTER SYSTEM SET settings already made --- the later would be to fix a problem with the server not starting. That's a good point. However, frankly, these are flat files, so I don't see a problem with having the administrator modify the flat file. Admins on Ubuntu or Debian or a host of their derivatives aren't going to be looking in $PGDATA for config files that they have to hand-modify to fix something the DBA did. When they eventually figure it out, they're going to be *very* unhappy. Would disabling the ALTER SYSTEM SET command also disable recognizing any ALTER SYSTEM SET commands already performed? Maybe that was already understood, but I missed that point. I don't have an answer to that one, though I do like the idea of a switch that says only read the settings from my postgresql.conf file. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pass-through queries to foreign servers
For those who don't want to go to the link to see what I'm talking
about with query rewrites, I thought I'd give a brief description.
Foreign data wrappers currently do all of their work in the planning
phase but I claim that isn't the right place to optimize foreign
queries with aggregates and GROUP BY because optimizing those things
would involve collapsing multiple plan node back into a single node
for a foreign call. I propose to do these optimizations as query
rewrites instead. So for example suppose t is a foreign table on the
foreign server named fs. Then the query
SELECT count(*) FROM t
is rewritten to
SELECT count FROM fs('select count(*) from t') fs(count bigint)
where ts() is the pass-through query function for the server fs. To
implement this optimization as a query rewrite, all of the elements of
the result have to be real source-language constructs so the
pass-through query has to be available in Postgresql SQL.
My current implementation of this uses a plugin that hooks into
planner_hook, but I'm hoping that I can get some support for adding
the query rewriting as callback functions for the FDW system.
Regards,
David Gudeman
http://unobtainabol.blogspot.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] pass-through queries to foreign servers
Hello
2013/8/5 David Gudeman dave.gude...@gmail.com:
For those who don't want to go to the link to see what I'm talking
about with query rewrites, I thought I'd give a brief description.
Foreign data wrappers currently do all of their work in the planning
phase but I claim that isn't the right place to optimize foreign
queries with aggregates and GROUP BY because optimizing those things
would involve collapsing multiple plan node back into a single node
for a foreign call. I propose to do these optimizations as query
rewrites instead. So for example suppose t is a foreign table on the
foreign server named fs. Then the query
SELECT count(*) FROM t
is rewritten to
SELECT count FROM fs('select count(*) from t') fs(count bigint)
where ts() is the pass-through query function for the server fs. To
implement this optimization as a query rewrite, all of the elements of
the result have to be real source-language constructs so the
pass-through query has to be available in Postgresql SQL.
why you introduce new API? There is still dblink.
Regards
Pavel
My current implementation of this uses a plugin that hooks into
planner_hook, but I'm hoping that I can get some support for adding
the query rewriting as callback functions for the FDW system.
Regards,
David Gudeman
http://unobtainabol.blogspot.com
--
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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Mon, Aug 5, 2013 at 02:52:40PM -0400, Stephen Frost wrote: However, frankly, these are flat files, so I don't see a problem with having the administrator modify the flat file. Admins on Ubuntu or Debian or a host of their derivatives aren't going to be looking in $PGDATA for config files that they have to hand-modify to fix something the DBA did. When they eventually figure it out, they're going to be *very* unhappy. Well, can you assume that if you have a problem with one of your ALTER SYSTEM SET commands, that disabling _all_ of them is going to get you a running system? I question that, e.g. port. With postgresql.conf, you can modify the bad entry, but how would that happen with ALTER SYSTEM SET? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Autovacuum different in 9.2.4?
On Mon, Aug 5, 2013 at 11:16 AM, Joshua D. Drake j...@commandprompt.com wrote: Hello, I seem to recall autovacuum changes landing for 9.2.4. Can someone please describe what those changes were and how they could affect usage? Those landed in 9.2.3, see release notes for that version: Fix performance problems with autovacuum truncation in busy workloads (Jan Wieck) Fix error in vacuum_freeze_table_age implementation (Andres Freund) There should be no change in usage, unless you were taking some heroic methods to overcome the problems and can now discontinue them. Cheers, Jeff -- 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] pass-through queries to foreign servers
David Gudeman dave.gude...@gmail.com writes:
For those who don't want to go to the link to see what I'm talking
about with query rewrites, I thought I'd give a brief description.
Foreign data wrappers currently do all of their work in the planning
phase but I claim that isn't the right place to optimize foreign
queries with aggregates and GROUP BY because optimizing those things
would involve collapsing multiple plan node back into a single node
for a foreign call.
I'm not sure what the best implementation for that is, but what you
propose here would still involve such collapsing, so this argument
seems rather empty.
I propose to do these optimizations as query
rewrites instead. So for example suppose t is a foreign table on the
foreign server named fs. Then the query
SELECT count(*) FROM t
is rewritten to
SELECT count FROM fs('select count(*) from t') fs(count bigint)
where ts() is the pass-through query function for the server fs. To
implement this optimization as a query rewrite, all of the elements of
the result have to be real source-language constructs so the
pass-through query has to be available in Postgresql SQL.
I don't believe in any part of that design, starting with the pass
through query function. For one thing, it seems narrowly targeted to the
assumption that the FDW is a frontend for a foreign server that speaks
SQL. If the FDW's infrastructure doesn't include some kind of textual
query language, this isn't going to be useful for it at all. For another,
a query rewrite system is unlikely to be able to cost out the alternatives
and decide whether pushing the aggregation across is actually a win or
not.
The direction I think we ought to be heading is to generate explicit Paths
representing the various ways in which aggregation can be implemented.
The logic in grouping_planner is already overly complex, and hard to
extend, because it's all hard-wired comparisons of alternatives. We'd be
better off with something more like the add_path infrastructure. Once
that's been done, maybe we can allow FDWs to add Paths representing remote
aggregation.
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] Autovacuum different in 9.2.4?
Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Aug 5, 2013 at 11:16 AM, Joshua D. Drake j...@commandprompt.com wrote: I seem to recall autovacuum changes landing for 9.2.4. Can someone please describe what those changes were and how they could affect usage? Those landed in 9.2.3, see release notes for that version: Fix performance problems with autovacuum truncation in busy workloads (Jan Wieck) Fix error in vacuum_freeze_table_age implementation (Andres Freund) There should be no change in usage, unless you were taking some heroic methods to overcome the problems and can now discontinue them. Well, that was the intent, but there was an unintended increase in the frequency with which an autovacuum which attempts to truncate a heap may fail to set new statistics, and the logging around truncation got a bit too chatty. These issues will be corrected with the next minor release, but until then some users may need to run ANALYZE commands in some cases to prevent tables with large swings in size from developing stale statistics, and there may be some new LOG entries which users wonder about -- they can safely be ignored. Overall, the autovacuum changes in 9.2.3 put an end to some debilitating problems with blocking and load related to overly aggressive and eager autovacuum runs. Jan's fix addressed problems with tables used for queues, as in slony and some JMS implementations. Andres fixed a bug which caused wraparound prevention autovacuum runs to occur too frequently. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Autovacuum different in 9.2.4?
On 08/05/2013 12:13 PM, Jeff Janes wrote: On Mon, Aug 5, 2013 at 11:16 AM, Joshua D. Drake j...@commandprompt.com wrote: Hello, I seem to recall autovacuum changes landing for 9.2.4. Can someone please describe what those changes were and how they could affect usage? Those landed in 9.2.3, see release notes for that version: Fix performance problems with autovacuum truncation in busy workloads (Jan Wieck) Fix error in vacuum_freeze_table_age implementation (Andres Freund) There should be no change in usage, unless you were taking some heroic methods to overcome the problems and can now discontinue them. That is what is confusing me, I could be cracked but messages like these: automatic vacuum of table pg_catalog.pg_attribute: could not (re)acquire exclusive lock for truncate scan Seem to be new? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Moving 'hot' pages from buffer pool to heap
Hi all, I was experimenting with the idea of moving hot buffer pages from the buffer pool to heap, thus allowing for normal removal of the hot buffer page from the buffer pool and freeing the corresponding buffer pool slot. This shouldnt be too hard to implement, we just need to keep track of the number of times a page was accessed(or the duration of pins held, I am not sure). The point is to not hold a buffer pool slot for more than a fixed duration of time, to ensure constant flow in the buffer pool. Has there been discussion about it done previously? I couldnt find an existing implementation, nor a discussion for the same. If I missed anything, please point me to it. Please let me know your feedback and comments. Thanks and Regards, Atri -- Regards, Atri l'apprenant -- 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] don't own lock of type?
On 08/05/2013 11:38 AM, Robert Haas wrote: On Mon, Aug 5, 2013 at 2:32 PM, Joshua D. Drake j...@commandprompt.com wrote: Hello, What exactly causes this? WARNING: you don't own a lock of type ExclusiveLock Does this mean the user calling the lock doesn't own the object? It means there's a bug. Either in PostgreSQL, or some loadable module you're using. I am getting this rather frequently, I will check modules but I don't believe we are running any. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Bruce Momjian br...@momjian.us writes: Well, can you assume that if you have a problem with one of your ALTER SYSTEM SET commands, that disabling _all_ of them is going to get you a running system? I question that, e.g. port. With postgresql.conf, you can modify the bad entry, but how would that happen with ALTER SYSTEM SET? I think we already have consensus that the settings will be in text files, so that system breakage can be handled by editing the files. What Josh seems to be concerned with in this thread is the question of whether we should support an installation *policy decision* not to allow ALTER SYSTEM SET. Not because a particular set of parameters is broken, but just because somebody is afraid the DBA might break things. TBH I'm not sure I buy that, at least not as long as ALTER SYSTEM is a superuser feature. There is nothing in Postgres that denies permissions to superusers, and this doesn't seem like a very good place to start. 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] Moving 'hot' pages from buffer pool to heap
Atri Sharma atri.j...@gmail.com writes: I was experimenting with the idea of moving hot buffer pages from the buffer pool to heap, thus allowing for normal removal of the hot buffer page from the buffer pool and freeing the corresponding buffer pool slot. Uh ... what? Why in the world would you want to force a hot page out of shared buffers? I fail to conceive of any scenario where that'd be a good idea. 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] Autovacuum different in 9.2.4?
Joshua D. Drake escribió: On 08/05/2013 12:13 PM, Jeff Janes wrote: There should be no change in usage, unless you were taking some heroic methods to overcome the problems and can now discontinue them. That is what is confusing me, I could be cracked but messages like these: automatic vacuum of table pg_catalog.pg_attribute: could not (re)acquire exclusive lock for truncate scan Seem to be new? Yeah, those are new. In the old code, trying to truncate the free pages at the end of a table (which requires an ACCESS EXCLUSIVE lock on the table) could lock other processes out of that table. The new code instead tries to handle this gracefully by giving up the lock if some other process is trying to access the table. The result is that a few free pages might be left over after vacuuming the table. Not a big deal, normally, unless you're really short on disk space. There was discussion about autovacuum being tweaked so that it would reattempt to truncate those free pages in a future pass. I don't know if this was done or not. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Tom Lane escribió: What Josh seems to be concerned with in this thread is the question of whether we should support an installation *policy decision* not to allow ALTER SYSTEM SET. Not because a particular set of parameters is broken, but just because somebody is afraid the DBA might break things. TBH I'm not sure I buy that, at least not as long as ALTER SYSTEM is a superuser feature. There is nothing in Postgres that denies permissions to superusers, and this doesn't seem like a very good place to start. Someone made an argument about this on IRC: GUI tool users are going to want to use ALTER SYSTEM through point-and-click, and if all we offer is superuser-level access to the feature, we're going to end up with a lot of people running with superuser privileges just so that they are able to tweak inconsequential settings. This seems dangerous. The other issue is that currently you can only edit a server's config if you are logged in to it. If we permit SQL-level access to that, and somebody who doesn't have access to edit the files blocks themselves out, there is no way for them to get a working system *at all*. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Moving 'hot' pages from buffer pool to heap
On Mon, Aug 5, 2013 at 12:36 PM, Atri Sharma atri.j...@gmail.com wrote: Hi all, I was experimenting with the idea of moving hot buffer pages from the buffer pool to heap, Which heap do you mean here? Alas, half the data structures used in CS are called heap. I can't think of any of them that are good candidates for this, though. Cheers, Jeff -- 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] pg_basebackup vs. Windows and tablespaces
On Thu, Aug 01, 2013 at 06:44:41PM +0200, Dimitri Fontaine wrote: Noah Misch n...@leadboat.com writes: 2. Add a pg_basebackup option like --destdir or --sysroot, meaningful only with -Fp; tablespace backups will be stored relative to it. So if the actual tablespace path is c:/foo, --destdir=c:/backups/today would backup that tablespace to c:/backups/today/c/foo. This facilitates same-server use of -Fp on all platforms. My understanding is that the second option here would be useful also when you want to create a standby with a different file layout than the master, which in some cases is what you want to do (not HA strictly). The way I was envisioning it, you would still need to place the tablespace directories in their ordinary locations before recovering the base backup. This was just a way to relocate the backup itself. I can see value in both capabilities, though. Another defect of pg_basebackup is its lack of shandling of tablespaces mounted within $PGDATA, which happens often enough at customers sites, whatever we think about that option. Would your work be extended to cover that too? Not that I had in mind. My latest thinking on that topic is along the lines of helping folks stop doing it, not making it work better: http://www.postgresql.org/message-id/flat/20121205010442.ga16...@tornado.leadboat.com Thanks, nm -- Noah Misch 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] pg_basebackup vs. Windows and tablespaces
On Thu, Aug 01, 2013 at 01:04:42PM -0400, Andrew Dunstan wrote: On 08/01/2013 12:15 PM, Noah Misch wrote: 1. Include in the base backup a file listing symbolic links/junction points, then have archive recovery recreate them. This file would be managed like the backup label file; exclusive backups would actually write it to the master data directory, and non-exclusive backups would incorporate it on the fly. pg_basebackup could also omit the actual links from its backup. Nearly any tar or file copy utility would then suffice. 2. Add a pg_basebackup option like --destdir or --sysroot, meaningful only with -Fp; tablespace backups will be stored relative to it. So if the actual tablespace path is c:/foo, --destdir=c:/backups/today would backup that tablespace to c:/backups/today/c/foo. This facilitates same-server use of -Fp on all platforms. I like #1, it seems nice and workable. Agreed. I'll lean in that direction for resolving the proximate problem. I also like the concept of #2, but I think we need to think about it a bit more. One of the things I like about barman backups is that on recovery you can map where tablespaces go, on a per tablespace basis (it's not very well documented, or wasn't when I last looked, but it does work). I think something like that would be awesome to have for pg_basebackup. So allowing multiple options of the form --map-tablespace c:/foo/bar=d:/baz/blurfl or some such would be great. Good point. I see now that the syntax I floated covered just one slice of a whole range of things folks might want in that area. Thanks, nm -- Noah Misch 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] [9.4 CF 1]Commitfest ... over!
On Sun, Aug 4, 2013 at 11:41 PM, Amit Kapila amit.kap...@huawei.com wrote: First of all Thank you very much for running a wonderful Commit Fest. Yes. Thanks, Josh. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Moving 'hot' pages from buffer pool to heap
Sent from my iPad On 06-Aug-2013, at 1:14, Tom Lane t...@sss.pgh.pa.us wrote: Atri Sharma atri.j...@gmail.com writes: I was experimenting with the idea of moving hot buffer pages from the buffer pool to heap, thus allowing for normal removal of the hot buffer page from the buffer pool and freeing the corresponding buffer pool slot. Uh ... what? Why in the world would you want to force a hot page out of shared buffers? I fail to conceive of any scenario where that'd be a good idea. Just experimenting though.I was thinking of scenarios where a page is pinned for long period of time.My concern was that it would lead to blocking of a buffer pool slot for that entire duration. The idea is to allocate a separate data structure for such hot pages in memory,and maintain them there. Sorry if it is naive though. Regards, Atri -- 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] Moving 'hot' pages from buffer pool to heap
Sent from my iPad On 06-Aug-2013, at 1:24, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Aug 5, 2013 at 12:36 PM, Atri Sharma atri.j...@gmail.com wrote: Hi all, I was experimenting with the idea of moving hot buffer pages from the buffer pool to heap, Which heap do you mean here? Alas, half the data structures used in CS are called heap. I can't think of any of them that are good candidates for this, though. My bad.By heap, I meant the memory.The idea is to allocate a new slot in memory for holding that page,so as to free the buffer pool slot. Regards, Atri -- 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] make --enable-depend the default
On Thu, 2013-08-01 at 11:10 +0200, Andres Freund wrote: People, including me, every now and then forget to pass --enable-depend to configure (when not using my own environment). Which then leads to strange errors that cost time to track down... One argument against that is that we only support dependency tracking with GCC, so we would have to either fail configure or use a different default depending on the compiler. It's also unreasonable to assume, I think, that just turning on --enable-depend by default will eliminate forgot-a-configure-option mistakes. In most cases, you want to turn on most or all --enable-* and --with-* options to get good build and test coverage. -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Hi, I'm now completely lost in the current threads. Is there a single valid use case for the feature we're trying to design? Who is the target audience of this patch? Josh Berkus j...@agliodbs.com writes: I don't see this as a solution at all. Mr. Sysadmin, we've given the DBAs a new tool which allows them to override your version-controlled database parameter settings. You can turn it off, though, by using this incredibly complicated, brand-new Event Trigger tool which requires writing lots of SQL code to make work. Well, given what has been said already and very recently again by Tom, it's superuser only and installing a precedent wherein superuser is not allowed to use a feature looks like a dead-end. You would have to make a case that it's comparable to allow_system_table_mods. If you believe that revoking ALTER SYSTEM SET privileges to superusers isn't going to be accepted, I know of only two other paths to allow you to implementing your own policy, including per-GUC policy and non-superuser granting of ALTER SYSTEM SET in a controled fashion: - add per GUC GRANT/REVOKE capabilities to SETTINGs, - implement the same thing within an Event Trigger. The former has been talked about lots of time already in the past and I'm yet to see any kind of progress made about it despite plenty of user support for the feature, the latter requires a shared catalog for global object Event Triggers and maybe a flexible Extension that you can manage by just dropping a configuration file into the PostgreSQL conf.d. So when trying to be realistic the answer is incredibly complicated because it involves a stored procedure to implement the local policy and a command to enable the policy, really, I wonder who you're addressing there. Certainly not DBA, so that must be sysadmins, who would be better off without the feature in the first place if I'm understanding you. Again, what are we trying to achieve?! Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Autovacuum different in 9.2.4?
Alvaro Herrera alvhe...@2ndquadrant.com wrote: Joshua D. Drake escribió: That is what is confusing me, I could be cracked but messages like these: automatic vacuum of table pg_catalog.pg_attribute: could not (re)acquire exclusive lock for truncate scan Seem to be new? Yeah, those are new. Yeah, they started appearing in 9.2.3 and concurrent releases; but since they seem to be causing more confusion than enlightenment, I removed them in a subsequent patch. They should be gone again in 9.2.5. In the old code, trying to truncate the free pages at the end of a table (which requires an ACCESS EXCLUSIVE lock on the table) could lock other processes out of that table. Right, prior to 9.2.3 all other access to the table could be blocked by an autovacuum truncation attempt for the duration that deadlock_timeout was set to. Those who set deadlock_timeout to something longer than the default 1 second could experience a lot of pain. The new code instead tries to handle this gracefully by giving up the lock if some other process is trying to access the table. The result is that a few free pages might be left over after vacuuming the table. Not a big deal, normally, unless you're really short on disk space. Actually, the old code wound up not truncating anything when the autovacuum process was killed by the deadlock checker when deadlock_timeout expired, and needed to start all over for each attempt; with each attempt coming quickly on the heals of the last. So you could have an indefinitely repeating series of AccessExclusiveLocks on the table, with heavy load making no progress. In 9.2.3 and 9.2.4, it will truncate to the point it has checked and release the AccessExclusiveLock if any other process is waiting for 20 ms. It will attempt to reacquire the lock to continue the truncation every 50 ms for 5 seconds, at which point it will give up without the drama of the deadlock checker killing it. Either way, an incomplete truncation attempt would result in no statistics update, which would cause another autovacuum on that table to be attempted soon. Significantly, the old code *would* update statistics if the truncation attempt was not able to *begin* due to lock contention, while that behavior was lost in 9.2.3 and 9.2.4. This is where people with certain types of workloads are seeing an increase in stale statistics with the new code. This will be fixed in 9.2.5. We will then update statistics regardless of any problems in the truncation phase. There was discussion about autovacuum being tweaked so that it would reattempt to truncate those free pages in a future pass. I don't know if this was done or not. Well, the old code did not reattempt the truncation very quickly if it was not able to acquire the initial AccessExclusiveLock, but would fire again soon if killed by the deadlock checker after it started. The new code has a cycle of quick detection of blocked processes, incremental truncate and sleep, and retry up to 100 times before giving up. In 9.2.3 and 9.2.4 it *also* reschedules quickly like the old aborted truncation; in 9.2.5 it will just try again if it seems needed at the next normally scheduled autovacuum. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane escribió: What Josh seems to be concerned with in this thread is the question of whether we should support an installation *policy decision* not to allow ALTER SYSTEM SET. Not because a particular set of parameters is broken, but just because somebody is afraid the DBA might break things. TBH I'm not sure I buy that, at least not as long as ALTER SYSTEM is a superuser feature. There is nothing in Postgres that denies permissions to superusers, and this doesn't seem like a very good place to start. Someone made an argument about this on IRC: GUI tool users are going to want to use ALTER SYSTEM through point-and-click, and if all we offer is superuser-level access to the feature, we're going to end up with a lot of people running with superuser privileges just so that they are able to tweak inconsequential settings. This seems dangerous. Agreed, but what else are you going to do? You can't have random unprivileged users changing settings that affect other users, even if those settings are somehow safe. And what is more to the point in this thread, having an additional shutoff that prevents even superusers from doing it doesn't reduce the temptation for everyone to make themselves superuser all the time. The other issue is that currently you can only edit a server's config if you are logged in to it. If we permit SQL-level access to that, and somebody who doesn't have access to edit the files blocks themselves out, there is no way for them to get a working system *at all*. True. So, if they were smart enough to prevent themselves from changing any settings remotely, they have no feature. And if they weren't, having such a blocking capability didn't really help them either. These are both valid worries, but what design is going to make them better? Other than forgetting about ALTER SYSTEM entirely? 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] Moving 'hot' pages from buffer pool to heap
Atri Sharma atri.j...@gmail.com writes: Just experimenting though.I was thinking of scenarios where a page is pinned for long period of time.My concern was that it would lead to blocking of a buffer pool slot for that entire duration. The idea is to allocate a separate data structure for such hot pages in memory,and maintain them there. You can't do that; such a copy could easily become stale, leading to wrong query answers. Perhaps more to the point, long-term pins (as opposed to locks) aren't that problematic. What problem do you think you're solving? 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Mon, 5 Aug 2013 15:53:01 -0400 Alvaro Herrera alvhe...@2ndquadrant.com wrote: The other issue is that currently you can only edit a server's config if you are logged in to it. If we permit SQL-level access to that, and somebody who doesn't have access to edit the files blocks themselves out, there is no way for them to get a working system *at all*. This is not a valid point, at least for mebasically all OSs should disable any change to firewall if you are connected remotely cause you can block yourselfgiving power to users does not mean being their babysitterwe should smart enough to use the power we receive...if we are not smart...we should start thinking about other profession And excuse my English, I hope the point is clear... Best regards Rodrigo Gonzalez -- 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] CREATE EVENT TRIGGER syntax
Bruce Momjian br...@momjian.us writes: So do we want to keep that AND in the 9.3beta and 9.4 documentation? The grammar as in gram.y still allows the AND form, and I think we're used to maintain documentation that matches the code here. So I think it makes sense to remove both capabilities as we failed to deliver any other filter. But if we wanted to clean that, what about having the grammar check for the only one item we support rather than waiting until into CreateEventTrigger() to ereport a syntax error? -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On 08/05/2013 10:18 PM, Dimitri Fontaine wrote: Hi, I'm now completely lost in the current threads. Is there a single valid use case for the feature we're trying to design? Who is the target audience of this patch? wonder about that myself... Josh Berkus j...@agliodbs.com writes: I don't see this as a solution at all. Mr. Sysadmin, we've given the DBAs a new tool which allows them to override your version-controlled database parameter settings. You can turn it off, though, by using this incredibly complicated, brand-new Event Trigger tool which requires writing lots of SQL code to make work. Well, given what has been said already and very recently again by Tom, it's superuser only and installing a precedent wherein superuser is not allowed to use a feature looks like a dead-end. You would have to make a case that it's comparable to allow_system_table_mods. If you believe that revoking ALTER SYSTEM SET privileges to superusers isn't going to be accepted, I know of only two other paths to allow you to implementing your own policy, including per-GUC policy and non-superuser granting of ALTER SYSTEM SET in a controled fashion: - add per GUC GRANT/REVOKE capabilities to SETTINGs, realistically I think this is what we want(fsvo) for this feature as a prerequisite, however that also will make it fairly complex to use for both humans and tools so not sure we would really gain anything... - implement the same thing within an Event Trigger. The former has been talked about lots of time already in the past and I'm yet to see any kind of progress made about it despite plenty of user support for the feature, the latter requires a shared catalog for global object Event Triggers and maybe a flexible Extension that you can manage by just dropping a configuration file into the PostgreSQL conf.d. So when trying to be realistic the answer is incredibly complicated because it involves a stored procedure to implement the local policy and a command to enable the policy, really, I wonder who you're addressing there. Certainly not DBA, so that must be sysadmins, who would be better off without the feature in the first place if I'm understanding you. Again, what are we trying to achieve?! no idea - wondering about that myself... Stefan -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On 08/05/2013 09:53 PM, Alvaro Herrera wrote: Tom Lane escribió: What Josh seems to be concerned with in this thread is the question of whether we should support an installation *policy decision* not to allow ALTER SYSTEM SET. Not because a particular set of parameters is broken, but just because somebody is afraid the DBA might break things. TBH I'm not sure I buy that, at least not as long as ALTER SYSTEM is a superuser feature. There is nothing in Postgres that denies permissions to superusers, and this doesn't seem like a very good place to start. Someone made an argument about this on IRC: GUI tool users are going to want to use ALTER SYSTEM through point-and-click, and if all we offer is superuser-level access to the feature, we're going to end up with a lot of people running with superuser privileges just so that they are able to tweak inconsequential settings. This seems dangerous. indeed it is The other issue is that currently you can only edit a server's config if you are logged in to it. If we permit SQL-level access to that, and somebody who doesn't have access to edit the files blocks themselves out, there is no way for them to get a working system *at all*. thinking more about that - is there _ANY_ prerequisite of an application that can be completely reconfigured over a remote access protocol and solved the reliability and security challenges of that to a reasonable degree? Stefan -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Stefan, thinking more about that - is there _ANY_ prerequisite of an application that can be completely reconfigured over a remote access protocol and solved the reliability and security challenges of that to a reasonable degree? Good question! I also think that, given the issues raised in discussion of ALTER SYSTEM SET, we should poll a bit of the wider community. I'll put something up on my blog, unless someone can think of a better way. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
