[HACKERS] Issue with listing same tablenames from different schemas in the search_path
Hi, Consider the following sequence of commands in a psql session: postgres=#create table public.sample(x int); postgres=#create schema new; postgres=#create table new.sample(x int); postgres=#set search_path=public,new; postgres=#\dt Schema | Name | Type | Owner --- public | sample | table | postgres (1 row) We should have seen two entries in the above listing. So looks like a bug to me. The issue is with the call to pg_table_is_visible(). While scanning for the second entry, it breaks out because there is a matching entry with the same name in the first schema. What we need is a variation of this function which checks for visibility of the corresponding namespace in the search path and emit it out too if so. Thoughts? I can cook up a patch for this. Regards, Nikhils
Re: [HACKERS] Issue with listing same tablenames from different schemas in the search_path
On 02.10.2011 08:31, Nikhil Sontakke wrote: Consider the following sequence of commands in a psql session: postgres=#create table public.sample(x int); postgres=#create schema new; postgres=#create table new.sample(x int); postgres=#set search_path=public,new; postgres=#\dt Schema | Name | Type | Owner --- public | sample | table | postgres (1 row) We should have seen two entries in the above listing. So looks like a bug to me. No, that's the way it's designed to work. It shows the objects that are visible to you, without schema-qualifying them. See http://www.postgresql.org/docs/9.0/interactive/app-psql.html#APP-PSQL-PATTERNS : Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name appears earlier in the search path. This is equivalent to the statement that the object can be referenced by name without explicit schema qualification.) To see all objects in the database regardless of visibility, use *.* as the pattern. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Issue with listing same tablenames from different schemas in the search_path
postgres=#create table public.sample(x int); postgres=#create schema new; postgres=#create table new.sample(x int); postgres=#set search_path=public,new; postgres=#\dt Schema | Name | Type | Owner --**- public | sample | table | postgres (1 row) We should have seen two entries in the above listing. So looks like a bug to me. No, that's the way it's designed to work. It shows the objects that are visible to you, without schema-qualifying them. See http://www.postgresql.org/**docs/9.0/interactive/app-psql.** html#APP-PSQL-PATTERNShttp://www.postgresql.org/docs/9.0/interactive/app-psql.html#APP-PSQL-PATTERNS: Hmmm, ok. Makes sense after reading the documentation, but seems a bit surprising/confusing at first glance. Never mind. Regards, Nikhils Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name appears earlier in the search path. This is equivalent to the statement that the object can be referenced by name without explicit schema qualification.) To see all objects in the database regardless of visibility, use *.* as the pattern. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Range Types - typo + NULL string constructor
On Oct2, 2011, at 08:12 , Jeff Davis wrote: Done. Now range types more closely resemble records in parsing behavior. Patch attached. Cool! Looking at the patch, I noticed that it's possible to specify the default boundaries ([], [), (] or ()) per individual float type with the DEFAULT_FLAGS clause of CREATE TYPE .. AS RANGE. I wonder if that doesn't do more harm then good - it makes it impossible to deduce the meaning of e.g. numericrange(1.0, 2.0) without looking up the definition of numericrange. I suggest we pick one set of default boundaries, ideally '[)' since that is what all the built-in canonization functions produce, and stick with it. best regards, Florian Pflug -- 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_cancel_backend by non-superuser
On Sat, Oct 1, 2011 at 10:11 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: On 01-10-2011 17:44, Daniel Farina wrote: On Fri, Sep 30, 2011 at 9:30 PM, Tom Lanet...@sss.pgh.pa.us wrote: ISTM it would be reasonably non-controversial to allow users to issue pg_cancel_backend against other sessions logged in as the same userID. The question is whether to go further than that, and if so how much. In *every* case -- and there are many -- where we've had people express pain, this would have sufficed. I see. What about passing this decision to DBA? I mean a GUC can_cancel_session = user, dbowner (default is '' -- only superuser). You can select one or both options. This GUC can only be changed by superuser. Or how about making it a grantable database-level privilege? -- 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] pg_cancel_backend by non-superuser
I like this idea +1 Il giorno 02/ott/2011 12:56, Robert Haas robertmh...@gmail.com ha scritto: On Sat, Oct 1, 2011 at 10:11 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: On 01-10-2011 17:44, Daniel Farina wrote: On Fri, Sep 30, 2011 at 9:30 PM, Tom Lanet...@sss.pgh.pa.us wrote: ISTM it would be reasonably non-controversial to allow users to issue pg_cancel_backend against other sessions logged in as the same userID. The question is whether to go further than that, and if so how much. In *every* case -- and there are many -- where we've had people express pain, this would have sufficed. I see. What about passing this decision to DBA? I mean a GUC can_cancel_session = user, dbowner (default is '' -- only superuser). You can select one or both options. This GUC can only be changed by superuser. Or how about making it a grantable database-level privilege? -- 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] [REVIEW] pg_last_xact_insert_timestamp
On Fri, Sep 30, 2011 at 4:07 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Sep 30, 2011 at 8:57 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Sep 30, 2011 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote: If the feature could not be done another way, easily, I might agree. I don't see that you've offered a reasonable alternative. The alternative proposals that you proposed don't appear to me to be solving the same problem. AIUI, the requested feature is to be able to get, on the master, the timestamp of the last commit or abort, just as we can already get the timestamp of the last commit or abort replayed on the standby. Nothing you WAL log and no messages you send to the standby are going to accomplish that goal. The goal of the OP was to find out the replication delay. This function was suggested, but its not the only way. My alternative proposals solve the original problem in a better way. As far as I can see, they don't solve the problem at all. Your proposals involve sending additional information from the master to the slave, but the slave already knows both its WAL position and the timestamp of the transaction it has most recently replayed, because the startup process on the slave tracks that information and publishes it in shared memory. On the master, however, only the WAL position is centrally tracked; the transaction timestamps are not. -- 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] [REVIEW] pg_last_xact_insert_timestamp
On Thu, Sep 15, 2011 at 4:52 AM, Fujii Masao masao.fu...@gmail.com wrote: Okay, I've changed the patch in that way. It occurs to me that pgstat_report_xact_end_timestamp doesn't really need to follow the protocol of bumping the change count before and after bumping the timestamp. We elsewhere assume that four-byte reads and writes are atomic, so there's no harm in assuming the same thing here (and if they're not... then the change-count thing is pretty dubious anyway). I think it's sufficient to just set the value, full stop. Also, in pg_last_xact_insert_timestamp, the errhint() seems a little strange - this is not exactly a WAL *control* function, is it? In the documentation, for the short description of pg_last_xact_insert_timestamp(), how about something like returns the time at which a transaction commit or transaction about record was last inserted into the transaction log? Or maybe that's too long. But the current description doesn't seem to do much other than recapitulate the function name, so I'm wondering if we can do any better than that. I think that instead of hacking up the backend-status copying code to have a mode where it copies everything, you should just have a special-purpose function that computes the value you need directly off the backend status entries themselves. This approach seems like it both clutters the code and adds lots of extra data copying. -- 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] [REVIEW] pg_last_xact_insert_timestamp
On Sun, Oct 2, 2011 at 12:10 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Sep 30, 2011 at 4:07 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Sep 30, 2011 at 8:57 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Sep 30, 2011 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote: If the feature could not be done another way, easily, I might agree. I don't see that you've offered a reasonable alternative. The alternative proposals that you proposed don't appear to me to be solving the same problem. AIUI, the requested feature is to be able to get, on the master, the timestamp of the last commit or abort, just as we can already get the timestamp of the last commit or abort replayed on the standby. Nothing you WAL log and no messages you send to the standby are going to accomplish that goal. The goal of the OP was to find out the replication delay. This function was suggested, but its not the only way. My alternative proposals solve the original problem in a better way. As far as I can see, they don't solve the problem at all. Your proposals involve sending additional information from the master to the slave, but the slave already knows both its WAL position and the timestamp of the transaction it has most recently replayed, because the startup process on the slave tracks that information and publishes it in shared memory. On the master, however, only the WAL position is centrally tracked; the transaction timestamps are not. The problem is to find the replication delay, even when the system is quiet. What I have proposed finds the replication delay more accurately even than looking at the last commit, since often there are writes but no commits. If we focus on the problem, rather than the first suggested solution to that problem, we'll come out on top. -- Simon Riggs 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] pg_cancel_backend by non-superuser
On Sun, Oct 02, 2011 at 06:55:51AM -0400, Robert Haas wrote: On Sat, Oct 1, 2011 at 10:11 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: On 01-10-2011 17:44, Daniel Farina wrote: On Fri, Sep 30, 2011 at 9:30 PM, Tom Lanet...@sss.pgh.pa.us ?wrote: ISTM it would be reasonably non-controversial to allow users to issue pg_cancel_backend against other sessions logged in as the same userID. The question is whether to go further than that, and if so how much. In *every* case -- and there are many -- where we've had people express pain, this would have sufficed. +1 for allowing that unconditionally. I see. What about passing this decision to DBA? I mean a GUC can_cancel_session = user, dbowner (default is '' -- only superuser). You can select one or both options. This GUC can only be changed by superuser. Or how about making it a grantable database-level privilege? I think either is overkill. You can implement any policy by interposing a SECURITY DEFINER wrapper around pg_cancel_backend(). nm -- 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] Range Types - typo + NULL string constructor
On Sun, 2011-10-02 at 11:32 +0200, Florian Pflug wrote: Looking at the patch, I noticed that it's possible to specify the default boundaries ([], [), (] or ()) per individual float type with the DEFAULT_FLAGS clause of CREATE TYPE .. AS RANGE. I wonder if that doesn't do more harm then good - it makes it impossible to deduce the meaning of e.g. numericrange(1.0, 2.0) without looking up the definition of numericrange. I suggest we pick one set of default boundaries, ideally '[)' since that is what all the built-in canonization functions produce, and stick with it. That sounds reasonable to me. Unless someone objects, I'll make the change in the next patch. Regards, Jeff Davis -- 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.2] Fix Leaky View Problem
2011/9/30 Noah Misch n...@leadboat.com: On Sun, Sep 25, 2011 at 11:22:56PM -0400, Robert Haas wrote: On Sun, Sep 25, 2011 at 10:38 PM, Noah Misch n...@leadboat.com wrote: On Sun, Sep 25, 2011 at 11:22:03AM -0500, Kevin Grittner wrote: Robert Haas ?09/25/11 10:58 AM I'm not sure we've been 100% consistent about that, since we previously made CREATE OR REPLACE LANGUAGE not replace the owner with the current user. I think we've been consistent in *not* changing security on an object when it is replaced. [CREATE OR REPLACE FUNCTION does not change proowner or proacl] Good point. ?C-O-R VIEW also preserves column default values. ?I believe we are consistent to the extent that everything possible to specify in each C-O-R statement gets replaced outright. ?The preserved characteristics *require* commands like GRANT, COMMENT and ALTER VIEW to set in the first place. The analogue I had in mind is SECURITY DEFINER, which C-O-R FUNCTION reverts to SECURITY INVOKER if it's not specified each time. ?That default is safe, though, while the proposed default of security_barrier=false is unsafe. Even though I normally take the opposite position, I still like the idea of dedicated syntax for this feature. Not knowing what view options we might end up with in the future, I hate having to decide on what the general behavior ought to be. But it would be easy to decide that CREATE SECURITY VIEW followed by CREATE OR REPLACE VIEW leaves the security flag set; it would be consistent with what we're doing with owner and acl information and wouldn't back us into any unpleasant decisions down the road. I prefer the previous UI (WITH (security_barrier=...)) to this proposal, albeit for diffuse reasons. Both kinds of views can have the consequence of granting new access to data. One kind leaks tuples to untrustworthy code whenever it's convenient for performance, and the other does not. A non-security view would not mimic either of these objects; it would be a mere subquery macro. Using WITH (...) syntax attached to the CREATE VIEW command better evokes the similarity between the alternatives we're actually offering. I also find it mildly odd letting CREATE OR REPLACE VIEW update an object originating with CREATE SECURITY VIEW. My preference is still also WITH(security_barrier=...) syntax. The arguable point was the behavior when a view is replaced without explicit WITH clause; whether we should consider it was specified a default value, or we should consider it means the option is preserved. If we stand on the viewpoint that object's attribute related to security (such as ownership, acl, label, ...) should be preserved, the security barrier also shall be preserved. On the other hand, we can never know what options will be added in the future, right now. Thus, we may need to sort out options related to security and not at DefineVirtualRelation(). However, do we need to limit type of the options to be preserved to security related? It is the first case that object with arbitrary options can be replaced. It seems to me we have no matter, even if we determine object's options are preserved unless an explicit new value is provided. Any other ideas? Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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_cancel_backend by non-superuser
Noah Misch n...@leadboat.com writes: On Fri, Sep 30, 2011 at 9:30 PM, Tom Lanet...@sss.pgh.pa.us ?wrote: ISTM it would be reasonably non-controversial to allow users to issue pg_cancel_backend against other sessions logged in as the same userID. The question is whether to go further than that, and if so how much. In *every* case -- and there are many -- where we've had people express pain, this would have sufficed. +1 for allowing that unconditionally. +1 Or how about making it a grantable database-level privilege? I think either is overkill. You can implement any policy by interposing a SECURITY DEFINER wrapper around pg_cancel_backend(). I still like the idea of grant cancel and grant terminate. For another patch. 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
[HACKERS] build times
I have been investigating some build performance issues, and trying to narrow down causes of slowness, and observed an odd effect, which was suggested by a huge time difference between building from git and building from a tarball. If I do make -C src/port all and then wait 10 seconds or so and do make -j 3 or even just plain make the build finishes much much faster (like 1m vs 5m) than if I had not run the first command. I have seen this on a Fedora VM (VirtualBox, W7 host, Athlon II X2) and a ScientificLinux 6 machine (dual quad xeon E5620). The setup is a vpath build configured thus: ../pgsql/configure --enable-cassert --enable-debug --enable-integer-datetimes --with-perl --with-python --with-tcl --with-krb5 --with-includes=/usr/include/et --with-openssl --with-ldap --with-libxml --with-libxslt Can anyone with a bit more make-fu than I have suggest why this should be so? Can we tweak the make files so hacks like this aren't required to get a fast build? Can anyone replicate this odd result? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.2] DROP statement reworks
Hi, Kohei KaiGai kai...@kaigai.gr.jp writes: I've been reviewing those patches, that are all about code refactoring. I like what it's doing, generalizing ad-hoc code by adding some more knowledge about the source tree into some data structures. Typically, what catcache to use for a given object's class-id. I rebased the patches towards the latest git master, so I believe these are available to apply. Ok I needed `git apply' to apply the patches, now that I used that I can confirm that the 3 patches apply, compile, pass tests, and that I could play with them a little. I think I'm going to mark that ready for commiter. I don't have enough time for a more deep review but at the same time patch reading and testing both passed :) You might need to post a version that patch will be happy with, though, using e.g. git diff |filterdiff --format=context /tmp/foo.patch Then diffstat reports: 35 files changed, 932 insertions(+), 1913 deletions(-), 345 modifications(!) 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] REVIEW proposal: a validator for configuration files
Alexey Klyukin al...@commandprompt.com writes: Attached is v5. It should fix both problems you've experienced with v4. I've applied this patch after some additional hacking. One problem I'm not sure how to address is the fact that we require 2 calls of set_config_option for each option, one to verify the new value and another to actually apply it. Normally, this function returns true for a valid value and false if it is unable to set the new value for whatever reason (either the value is invalid, or the value cannot be changed in the context of the caller). However, calling it once per value in the 'apply' mode during reload produces false for every unchanged value that can only be changed during startup (i.e. shared_buffers, or max_connections). I thought that the most reasonable solution here was to extend set_config_option to provide a three-valued result: applied, error, or not-applied-for-some-non-error-reason. So I did that, and then ripped out the first set of calls to set_config_option. That should make things a bit faster, as well as eliminating a set of corner cases where the checking call succeeds but then the real apply step fails anyway. I also got rid of the changes to ParseConfigFile's API. I thought those were messy and unnecessary, because there's no good reason not to define the parse-error limit as being so many errors per file. It's really only there to prevent the config file contains War and Peace syndrome anyway. 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] Should we get rid of custom_variable_classes altogether?
During the discussion of Alexey Klyukin's rewrite of ParseConfigFile, considerable unhappiness was expressed by various people about the complexity and relative uselessness of the custom_variable_classes GUC. While working over his patch just now, I've come around to the side that was saying that this variable isn't worth its keep. We don't have any way to validate whether the second part of a qualified GUC name is correct, if its associated extension module isn't loaded, so how much point is there in validating the first part? And the variable is certainly a pain in the rear both to DBAs and to the GUC code itself. So at this point I'd vote for just dropping it and always allowing custom (that is, qualified) GUC names to be set, whether the prefix corresponds to any loaded module or not. Comments, other proposals? 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] pg_dump issues
Hi Andrew,
On 10/01/2011 09:46 PM, Andrew Dunstan wrote:
On 10/01/2011 05:48 PM, Joe Abbate wrote:
On 10/01/2011 05:08 PM, Andrew Dunstan wrote:
There is also this gem of behaviour, which is where I started:
p1p2
begin;
drop view foo;
pg_dump
commit;
boom.
with this error:
2011-10-01 16:38:20 EDT [27084] 30063 ERROR: could not open
relation with OID 133640
2011-10-01 16:38:20 EDT [27084] 30064 STATEMENT: SELECT
pg_catalog.pg_get_viewdef('133640'::pg_catalog.oid) AS viewdef
Of course, this isn't caused by having a large catalog, but it's
terrible nevertheless. I'm not sure what to do about it.
Couldn't you run pg_dump with --lock-wait-timeout?
How would that help? This isn't a lock failure.
I misinterpreted the error. I have confirmed the behavior you see. I'm
somewhat surprised there appears to be no ability to lock a database
exclusively for something like pg_dump/pg_restore, so you're not
surprised by concurrent activity against the catalogs. I'm guessing the
assumption is that MVCC will take care of that?
Joe
--
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] build times
Andrew Dunstan and...@dunslane.net writes: I have been investigating some build performance issues, and trying to narrow down causes of slowness, and observed an odd effect, which was suggested by a huge time difference between building from git and building from a tarball. If I do make -C src/port all and then wait 10 seconds or so and do make -j 3 or even just plain make the build finishes much much faster (like 1m vs 5m) than if I had not run the first command. Can't reproduce that here. What I do notice on a Fedora 14 machine is that ccache seems to be enabled by default, ie you get caching even when you just say gcc, and that makes a huge difference in build times. I see 70 seconds after rm -rf ~/.ccache, versus 4 seconds with it fully populated. Building src/port first saves nothing in either starting state. I wonder whether your experiments got affected by something similar. 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] pg_cancel_backend by non-superuser
Noah Misch n...@leadboat.com writes: On Sun, Oct 02, 2011 at 06:55:51AM -0400, Robert Haas wrote: On Sat, Oct 1, 2011 at 10:11 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: I see. What about passing this decision to DBA? I mean a GUC can_cancel_session = user, dbowner (default is '' -- only superuser). You can select one or both options. This GUC can only be changed by superuser. Or how about making it a grantable database-level privilege? I think either is overkill. You can implement any policy by interposing a SECURITY DEFINER wrapper around pg_cancel_backend(). I'm with Noah on this. If allowing same-user cancels is enough to solve 95% or 99% of the real-world use cases, let's just do that. There's no very good reason to suppose that a GUC or some more ad-hoc privileges will solve a large enough fraction of the rest of the cases to be worth their maintenance effort. In particular, I think both of the above proposals assume way too much about the DBA's specific administrative requirements. 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] [REVIEW] pg_last_xact_insert_timestamp
Robert Haas robertmh...@gmail.com writes: It occurs to me that pgstat_report_xact_end_timestamp doesn't really need to follow the protocol of bumping the change count before and after bumping the timestamp. We elsewhere assume that four-byte reads and writes are atomic, so there's no harm in assuming the same thing here (and if they're not... then the change-count thing is pretty dubious anyway). I think it's sufficient to just set the value, full stop. I agree you can read the value without that, but I think that setting it should still bump the change count. Otherwise there's no way for another process to collect the whole struct and be sure that it's self-consistent. We may not have a critical need for that right now, but it's silly to foreclose the possibility to save a cycle or so. 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] Should we get rid of custom_variable_classes altogether?
On Sun, Oct 2, 2011 at 10:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: During the discussion of Alexey Klyukin's rewrite of ParseConfigFile, considerable unhappiness was expressed by various people about the complexity and relative uselessness of the custom_variable_classes GUC. While working over his patch just now, I've come around to the side that was saying that this variable isn't worth its keep. We don't have any way to validate whether the second part of a qualified GUC name is correct, if its associated extension module isn't loaded, so how much point is there in validating the first part? And the variable is certainly a pain in the rear both to DBAs and to the GUC code itself. So at this point I'd vote for just dropping it and always allowing custom (that is, qualified) GUC names to be set, whether the prefix corresponds to any loaded module or not. Sounds sensible. One less thing to configure is a good thing. -- Simon Riggs 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] build times
On 10/02/2011 05:25 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: I have been investigating some build performance issues, and trying to narrow down causes of slowness, and observed an odd effect, which was suggested by a huge time difference between building from git and building from a tarball. If I do make -C src/port all and then wait 10 seconds or so and do make -j 3 or even just plain make the build finishes much much faster (like 1m vs 5m) than if I had not run the first command. Can't reproduce that here. What I do notice on a Fedora 14 machine is that ccache seems to be enabled by default, ie you get caching even when you just say gcc, and that makes a huge difference in build times. I see 70 seconds after rm -rf ~/.ccache, versus 4 seconds with it fully populated. Building src/port first saves nothing in either starting state. I wonder whether your experiments got affected by something similar. Yes, possibly, although SL6 (which is an RHEL clone) doesn't have ccache by default. I'm not sure what happened there, as now I can't reproduce it either. Sorry for the noise. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Separating bgwriter and checkpointer
2011/10/2 Simon Riggs si...@2ndquadrant.com: On Thu, Sep 15, 2011 at 11:53 PM, Simon Riggs si...@2ndquadrant.com wrote: Current patch has a bug at shutdown I've not located yet, but seems likely is a simple error. That is mainly because for personal reasons I've not been able to work on the patch recently. I expect to be able to fix that later in the CF. Full patch, with bug fixed. (v2) I'm now free to take review comments and make changes. Hi Simon, I'm trying your patch, it was applied cleanly to master and compiled ok. But since I started postgres I'm seeing a 99% of CPU usage: guedes@betelgeuse:/srv/postgres/bgwriter_split$ ps -ef | grep postgres guedes 14878 1 0 19:37 pts/000:00:00 /srv/postgres/bgwriter_split/bin/postgres -D data guedes 14880 14878 0 19:37 ?00:00:00 postgres: writer process guedes 14881 14878 99 19:37 ?00:03:07 postgres: checkpointer process guedes 14882 14878 0 19:37 ?00:00:00 postgres: wal writer process guedes 14883 14878 0 19:37 ?00:00:00 postgres: autovacuum launcher process guedes 14884 14878 0 19:37 ?00:00:00 postgres: stats collector process Best regards. -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br -- 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] WIP: SP-GiST, Space-Partitioned GiST
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 06.09.2011 20:34, Oleg Bartunov wrote: Here is the latest spgist patch, which has all planned features as well as all overhead, introduced by concurrency and recovery, so performance measurement should be realistic now. I'm ignoring the text suffix-tree part of this for now, because of the issue with non-C locales that Alexander pointer out. It seems to me that SP-GiST simply cannot work for full text comparisons in non-C locales, because it's critically dependent on the assumption that comparisons of strings are consistent with comparisons of prefixes of those strings ... an assumption that's just plain false for most non-C locales. We can dodge that problem in the same way that we did in the btree pattern_ops opclasses, namely implement the opclass only for the = operator and the special operators ~~ etc. I think I favor doing this for the first round, because it's a simple matter of removing code that's currently present in the patch. Even with only = support the opclass would be extremely useful. Something we could consider later is a way to use the index for the regular text comparison operators ( etc), but only when the operator is using C collation. This is not so much a matter for the index implementation as it is about teaching the planner to optionally consider collation when matching an operator call to the index. It's probably going to tie into the unfinished business of marking which operators are collation sensitive and which are not. In other news, I looked at the patch briefly, but I don't think I want to review it fully without some documentation. The absolute minimum requirement IMO is documentation comparable to what we have for GIN, ie a specification for the support methods and some indication of when you'd use this index type in preference to others. I'd be willing to help copy-edit and SGML-ize such documentation, but I do not care to reverse-engineer it from the code. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bug with pg_ctl -w/wait and config-only directories
On Sun, Oct 2, 2011 at 7:54 AM, Bruce Momjian br...@momjian.us wrote: What exactly is your question? You are not using a config-only directory but the real data directory, so it should work fine. No. He is using PGDATA (= /etc/postgresql-9.0) as a config-only directory, and DATA_DIR (= /var/lib/postgresql/9.0/data) as a real data directory. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION 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] Should we get rid of custom_variable_classes altogether?
Simon Riggs si...@2ndquadrant.com writes: On Sun, Oct 2, 2011 at 10:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: So at this point I'd vote for just dropping it and always allowing custom (that is, qualified) GUC names to be set, whether the prefix corresponds to any loaded module or not. Sounds sensible. One less thing to configure is a good thing. Attached is a draft patch for that. While working on this I got annoyed at our cheesy handling of the situation where a placeholder value has to be turned into a real setting, which happens when the corresponding extension gets loaded. There are several issues: 1. If it's a SUSET variable, a preceding attempt to set the value via SET will fail even if you're a superuser, for example regression=# set plpgsql.variable_conflict = use_variable; SET regression=# load 'plpgsql'; ERROR: permission denied to set parameter plpgsql.variable_conflict The reason for that is that define_custom_variable doesn't know whether the pre-existing value was set by a superuser, so it must assume the worst. Seems like we could easily fix that by having set_config_option set a flag in the GUC variable noting whether a SET was done by a superuser or not. 2. If you do get an error while re-assigning the pre-existing value of the variable, it's thrown as an ERROR. This is really pretty nasty because it'll abort execution of the extension module's init function; for example, a likely consequence is that other custom variables of the module don't set set up correctly, and it could easily be a lot worse if there are other things the init function hasn't done yet. I think we need to arrange that set_config_option only reports failures to apply such values as WARNING, not ERROR. There isn't anything in its present API that could be used for that, but perhaps we could add a new enum variant for action that commands it. 3. We aren't very careful about preserving the reset value of the variable, in case it's different from the active value (which could happen if the user did a SET and there's also a value from the postgresql.conf file). This seems like it just requires working a bit harder in define_custom_variable, to reapply the reset value as well as the current value of the variable. Any objections to fixing that stuff, while I'm looking at it? regards, tom lane diff --git a/doc/src/sgml/auth-delay.sgml b/doc/src/sgml/auth-delay.sgml index e377c980cab919a407294356f27ede0255e63897..91549ffe4f61e019645866fcd98a6f2fb2f52998 100644 *** a/doc/src/sgml/auth-delay.sgml --- b/doc/src/sgml/auth-delay.sgml *** *** 42,57 /variablelist para !In order to set these parameters in your filenamepostgresql.conf/ file, !you will need to add literalauth_delay/ to !xref linkend=guc-custom-variable-classes. Typical usage might be: /para programlisting # postgresql.conf shared_preload_libraries = 'auth_delay' - custom_variable_classes = 'auth_delay' auth_delay.milliseconds = '500' /programlisting /sect2 --- 42,55 /variablelist para !These parameters must be set in filenamepostgresql.conf/. !Typical usage might be: /para programlisting # postgresql.conf shared_preload_libraries = 'auth_delay' auth_delay.milliseconds = '500' /programlisting /sect2 diff --git a/doc/src/sgml/auto-explain.sgml b/doc/src/sgml/auto-explain.sgml index b16f9064ffc05d86637c054bc7b38221988b11db..6a8da566fbb200da0ab30f1cee5caee113645907 100644 *** a/doc/src/sgml/auto-explain.sgml --- b/doc/src/sgml/auto-explain.sgml *** LOAD 'auto_explain'; *** 158,173 /variablelist para !In order to set these parameters in your filenamepostgresql.conf/ file, !you will need to add literalauto_explain/ to !xref linkend=guc-custom-variable-classes. Typical usage might be: /para programlisting # postgresql.conf shared_preload_libraries = 'auto_explain' - custom_variable_classes = 'auto_explain' auto_explain.log_min_duration = '3s' /programlisting /sect2 --- 158,171 /variablelist para !These parameters must be set in filenamepostgresql.conf/. !Typical usage might be: /para programlisting # postgresql.conf shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '3s' /programlisting /sect2 diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 3282ab4f20303a986b6057c59a4bb979e20d497a..fbcd455694bfee1a17b8ebcc6d2ac504a09d0833 100644 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** dynamic_library_path = 'C:\tools\postgre *** 5940,5997 para This feature was designed to allow parameters not normally known to productnamePostgreSQL/productname to be added by add-on modules ! (such as procedural languages). This allows add-on modules to be configured in the standard ways. /para - variablelist - -
Re: [HACKERS] pg_dump issues
Andrew Dunstan and...@dunslane.net writes: While investigating a client problem I just observed that pg_dump takes a surprisingly large amount of time to dump a schema with a large number of views. The client's hardware is quite spiffy, and yet pg_dump is taking many minutes to dump a schema with some 35,000 views. Here's a simple test case: create schema views; do 'begin for i in 1 .. 1 loop execute $$create view views.v_$$ || i ||$$ as select current_date as d, current_timestamp as ts, $_$a$_$::text || n as t, n from generate_series(1,5) as n$$; end loop; end;'; On my modest hardware this database took 4m18.864s for pg_dump to run. It takes about that on my machine too ... with --enable-cassert. oprofile said that 90% of the runtime was going into AllocSetCheck, so I rebuilt without cassert, and the runtime dropped to 16 seconds. What were you testing? (Without cassert, it looks like LockReassignCurrentOwner is the next biggest time sink; I'm wondering if there's some sort of O(N^2) behavior in there.) 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] bug of recovery?
On Fri, Sep 30, 2011 at 3:57 PM, Simon Riggs si...@2ndquadrant.com wrote: I think we should issue PANIC if the source is a critical rmgr, or just WARNING if from a non-critical rmgr, such as indexes. Ideally, I think we should have a mechanism to allow indexes to be marked corrupt. For example, a file that if present shows that the index is corrupt and would be marked not valid. We can then create the file and send a sinval message to force the index relcache to be rebuilt showing valid set to false. This seems not to be specific to the invalid-page table problem. All error cases from a non-critical rmgr should be treated not-PANIC. So I think that the idea should be implemented separately from the patch I've posted. Anyway what if read-only query accesses the index marked invalid? Just emit ERROR? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION 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
