Re: [HACKERS] libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable
On Oct8, 2011, at 23:07 , Christopher Browne wrote: > General purpose queries are nowhere near so predetermined. Indeed, whether a > column is nullable may not be at all visible, as the value of a column may be > computed by a function and thereby be quite opaque to static analysis. I don't agree. To me, nullability is part of a column's type, just as the type's OID and TYPMOD are. We do static analysis on the TYPMOD, so I don't see why we shouldn't or couldn't do that on nullability. > That makes me think that guessing which attributes of a query may be null > seems like a pretty futile exercise. At first blush, we could simplify to > PQnullable() always returning true, but that's not terribly revealing. > However, often, there mayn't be a much better solution that isn't really > tough to implement. Coming up with a reasonable algorithm isn't *that* hard. Here's what I think would be reasonable A) All result columns which are not simple column references are nullable B) All result columns which are simple references to nullable columns are nullable C) All result columns which are simple references to column from the nullable side of an outer join are nullable (i.e., columns from the "right" side of a LEFT JOIN, "left" side of a RIGHT JOIN, or any side of a FULL OUTER JOIN) D) All others are nullable (i.e. simple column references to non-nullable columns from the non-nullable side of a join) If someone cared enough, (A) could be improved upon further. CASE constructs are an obvious candidate for deeper inspection (i.e., a CASE construct is non-nullable if all WHEN branches are non-nullable and a non-nullalbe ELSE branch exists), as is COALESCE (similar rule). This is mostly how it works for typmod I think - we do some analysis, but at some point we give up and just return "-1". As I see it, the hardest part of this feature is getting the information to the client. I don't think the reply to a DESCRIBE message is currently extensible, so we'd probably need to add a new version of the message. That might be a rather tough sell, as least as long as there's isn't a clear use-case for this. Which, unfortunately, nobody has provided so far. > I'd not be keen on people putting much effort into futile exercises ; better > to work on things that are "less futile." Again, I think "futile" is the wrong word here. This is all perfectly doable, the question is simply whether one values to feature enough to put in the word. I certainly won't, because I don't really see the benefit. But since most of our competitors seem to support this, and since Sun even put this into the JDBC spec, I guess a whole lot of people disagree. 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] WIP: Join push-down for foreign tables
Hanada-san, The proposed patch put an invocation of PlanForeignJoin on the create_foreignjoin_path() being also called by match_unsorted_outer(). Is it a suitable position to make a decision whether a join can be pushed-down? I think; it needs an additional functionality to provide higher priority on the foreign-join plan that other plans, when fdw determind a particular join can be pushed-down. (Sorry, I have no idea right now.) Let's see the following result. postgres=# EXPLAIN SELECT * FROM ft1 , ft2, lt3 WHERE ft1.a = ft2.x AND ft1.a = lt3.s; QUERY PLAN -- Merge Join (cost=205.08..758.83 rows=30750 width=108) Merge Cond: (ft1.a = lt3.s) -> Merge Join (cost=119.66..199.66 rows=5000 width=72) Merge Cond: (ft1.a = ft2.x) -> Sort (cost=59.83..62.33 rows=1000 width=36) Sort Key: ft1.a -> Foreign Scan on ft1 (cost=10.00..10.00 rows=1000 width=36) Remote SQL: DECLARE pgsql_fdw_cursor_0 SCROLL CURSOR FOR SELECT ft1.a, ft1.b FROM public.ft1 ft1 -> Sort (cost=59.83..62.33 rows=1000 width=36) Sort Key: ft2.x -> Foreign Scan on ft2 (cost=10.00..10.00 rows=1000 width=36) Remote SQL: DECLARE pgsql_fdw_cursor_1 SCROLL CURSOR FOR SELECT ft2.x, ft2.y FROM public.ft2 ft2 -> Sort (cost=85.43..88.50 rows=1230 width=36) Sort Key: lt3.s -> Seq Scan on lt3 (cost=0.00..22.30 rows=1230 width=36) (15 rows) Then, I turned off the enable_mergejoin. postgres=# EXPLAIN SELECT * FROM ft1 , ft2, lt3 WHERE ft1.a = ft2.x AND ft1.a = lt3.s; QUERY PLAN - Hash Join (cost=37.67..1126.42 rows=30750 width=108) Hash Cond: (ft1.a = lt3.s) -> Foreign Scan on multiple foreign tables (cost=0.00..0.00 rows=5000 width=72) Remote SQL: SELECT ft1.a, ft1.b, ft2.x, ft2.y FROM public.ft1 ft1, public.ft2 ft2 WHERE (ft1.a = ft2.x) -> Hash (cost=22.30..22.30 rows=1230 width=36) -> Seq Scan on lt3 (cost=0.00..22.30 rows=1230 width=36) (6 rows) Probably, the basic design is correct. However, the planner gives higher priority on the join plan between local and foreign than pushing-down foreign relations. Does it make sense not to consider any other possible plans when FDW decided a particular join can be pushed down? Thanks, 2011年10月7日18:06 Kohei KaiGai : > 2011年10月4日12:08 Shigeru Hanada : >>> In my opinion, FdwRoutine should have an additional API to inform the core >>> its >>> supported features; such as inner-join, outer-join, order-by, >>> group-by, aggregate >>> functions, insert, update, delete, etc... in the future version. >> >> Sure, so in my design PlanForeignJoin is optional. >> >> The lack of capability is informed from FDW with setting function >> pointer in FdwRoutine to NULL. If PlanForeignJoin was NULL, core >> (planner) will give up to consider join push-down, and use one of local >> join methods such as NestLoop and MergeJoin for those foreign tables. >> As you say, other push-down-able features would also have optional >> handler function for each. >> > Sorry, I overlooked it was already implemented at create_foreignjoin_path(). > > I additionally tried several cases using pgsql_fdw. > In some cases, it seems to me the planner don't push down the join tree > as you probably expected. > Please see the following example: > > I defined three foreign tables: ft1(a int, b text), ft2(x int, y > text), ft3(s int, t text), > and lt1, lt2, lt3 are regular local tables. > > postgres=# explain SELECT * FROM (ft1 join ft2 on a = x join ft3 on a = s); > > QUERY PLAN > --- > Foreign Scan on multiple foreign tables (cost=0.00..0.00 rows=25000 > width=108) > Remote SQL: SELECT ft1.a, ft1.b, ft2.x, ft2.y, ft3.s, ft3.t FROM > public.ft1 ft1, public.ft2 ft2, public.ft3 ft3 WHERE (ft1.a = ft3.s) > AND (ft1.a = ft2.x) > (2 rows) > > It works good. > (P.S. I noticed that pgsql_fdw has incorrect Assert(). Please fix > pgsql_fdw.c:730) > > However, an existence of local relation makes planner confused. > It seems to me you expect "ft1 join ft2 on a = x" > > postgres=# explain SELECT * FROM (ft1 join ft2 on a = x join lt3 on a = s); > QUERY PLAN > -- > Merge Join (cost=205.08..758.83 rows=30750 width=108) > Merge Cond: (ft1.a = lt3.s) > -> Merge Join (cost=119.66..199.66 rows=5000 width=72) > Merge Cond: (ft1.a = ft2.x) > ->
Re: [HACKERS] libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable
Florian Pflug wrote: > Coming up with a reasonable algorithm isn't *that* hard. Agreed. Our shop has used a home-grown framework for over a decade where we parse queries using ANTLR ( http://www.antlr.org/ ) and we tracked this trough all expressions. There really weren't that many situations where we had to punt. > D) All others are nullable I think you meant "All others are not nullable." > As I see it, the hardest part of this feature is getting the > information to the client. Ay, there's the rub. > I don't think the reply to a DESCRIBE message is currently > extensible, so we'd probably need to add a new version of the > message. Or a new protocol version. I've been thinking that the next *big* project I look at here might be a new version of the protocol, since I see mentions of protocol limitations preventing things people want with some regularity. We should be keeping a list, and this should be on it. > That might be a rather tough sell, as least as long as there's > isn't a clear use-case for this. Which, unfortunately, nobody has > provided so far. Yeah. It would be nice to see at least one use case. The only comment I recall is a vague suggestion that that people might want to select data from a table and infer table attributes from the result set metadata. That seems marginal. > the question is simply whether one values to feature enough to put > in the word. ... or fund the work. There are people for hire in the community. > I certainly won't, because I don't really see the benefit. Yeah, it wouldn't be hard to produce a long list of things which would take about the same effort which seem more beneficial to me. It's a matter of whether this is causing someone enough bother to want to devote the resources to changing it. I really think it's that simple. -Kevin -- 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] libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable
On Oct9, 2011, at 14:20 , Kevin Grittner wrote: > Florian Pflug wrote: > >> Coming up with a reasonable algorithm isn't *that* hard. > > Agreed. Our shop has used a home-grown framework for over a decade > where we parse queries using ANTLR ( http://www.antlr.org/ ) and we > tracked this trough all expressions. There really weren't that many > situations where we had to punt. Sounds cool. What was your use-case for doing that? >> D) All others are nullable > > I think you meant "All others are not nullable." Ups, yeah, right, that was supposed to read *non*-nullable. >> That might be a rather tough sell, as least as long as there's >> isn't a clear use-case for this. Which, unfortunately, nobody has >> provided so far. > > Yeah. It would be nice to see at least one use case. The only > comment I recall is a vague suggestion that that people might want to > select data from a table and infer table attributes from the result > set metadata. That seems marginal. Well, there is one other, namely SQL standards compliance. It does mandate that "CREATE TABLE ... AS SELECT" creates NOT NULL constraints on non-nullable columns I think (I didn't re-check, though). I'm not sure I see the value in that either, but, hey, standards compliance ought to be a value it in itself, right? >> the question is simply whether one values to feature enough to put >> in the word. > > ... or fund the work. There are people for hire in the community. And that was, of course, supposed to read "put in the *work*". Alas, just putting in the *word* is probably not going to be enough ;-) 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] WIP: Join push-down for foreign tables
On Oct9, 2011, at 13:35 , Kohei KaiGai wrote: > I think; it needs an additional functionality to provide higher priority > on the foreign-join plan that other plans, when fdw determind a particular > join can be pushed-down. > (Sorry, I have no idea right now.) > > Probably, the basic design is correct. However, the planner gives > higher priority on the join plan between > local and foreign than pushing-down foreign relations. The textbook approach to that is to factor the cost of transferring the rows over the network into the plan costs. That, of course, only works once we have statistics for the foreign tables. But AFAIK we eventually want to have those, so I'd say punt this until that time. > Does it make sense not to consider any other possible plans when FDW > decided a particular join can be > pushed down? I think in the long run we're going to want a cost-based decision there. Pushing down a join is only a win if the join selectivity is low. For a selectivity close to 1.0, it may very well be many times more efficient to fetch the tables separately and join them locally. You'll be fetching only |A| + |B| rows for a local join, instead of |A| * |B| rows for a remote join (assuming A,B are tables with cardinality |A|, |B|). 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] alter table only ... drop constraint broken in HEAD
On Fri, Oct 7, 2011 at 5:45 PM, Alex Hunsaker wrote: > If I find the time maybe Ill submit something along these lines for > the next commit fest. > So i just picked up the non-inherited constraints patch and quickly ran into the same problem and found this thread. I think it makes sense to hold off on this patch until these issues are resolved. Because we really do need to test the cases when adding or removing child tables that have constraints with the same name as non-inherited parent tables. And I'm not sure what will happen in these cases once these issues are resolved. -- 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] libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable
"Kevin Grittner" writes: > Florian Pflug wrote: >> I don't think the reply to a DESCRIBE message is currently >> extensible, so we'd probably need to add a new version of the >> message. > Or a new protocol version. Exactly --- this *would* require a protocol version bump. >> That might be a rather tough sell, as least as long as there's >> isn't a clear use-case for this. Which, unfortunately, nobody has >> provided so far. > Yeah. It would be nice to see at least one use case. The only > comment I recall is a vague suggestion that that people might want to > select data from a table and infer table attributes from the result > set metadata. That seems marginal. Yes. We need a pretty convincing use-case to seriously consider such a thing. > Yeah, it wouldn't be hard to produce a long list of things which > would take about the same effort which seem more beneficial to me. > It's a matter of whether this is causing someone enough bother to > want to devote the resources to changing it. The problem with something like a protocol bump is that the coding required to make it happen (in the backend and libpq, that is) is only a small part of the total distributed cost. So even if someone stepped up with a patch, it'd likely get rejected outright, unless there's significant community buy-in to the need for it. I agree with Kevin's comment that the right thing to be doing now would be to be keeping a list of things we might want to change the protocol for. It's just about certain that no single element on that list will be sufficient reason to change, but once there are enough of them maybe we'll have critical mass to do them all together. (Actually, isn't there such a page on the wiki already? Or a subsection of the TODO list?) 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] libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable
Florian Pflug writes: > On Oct9, 2011, at 14:20 , Kevin Grittner wrote: >> Yeah. It would be nice to see at least one use case. The only >> comment I recall is a vague suggestion that that people might want to >> select data from a table and infer table attributes from the result >> set metadata. That seems marginal. > Well, there is one other, namely SQL standards compliance. It does > mandate that "CREATE TABLE ... AS SELECT" creates NOT NULL constraints > on non-nullable columns I think (I didn't re-check, though). I'm not sure > I see the value in that either, but, hey, standards compliance ought > to be a value it in itself, right? Um ... but that case has nothing to do with protocol changes. 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: Optimizing box_penalty
On 08.10.2011 21:51, Kevin Grittner wrote: I tried to review the "Optimizing box_penalty" patch: Thanks! On the other hand, this patch leaves the code a few lines shorter and eliminates some unnecessary Datum wrapping, PG_FUNCTION_ARGS parameters on a static function, and allows that function to be called directly rather than using DirectFunctionCall2(). I find the resulting code a little cleaner and easier to read. I would prefer to see it applied on that basis, personally. Agreed, committed. -- 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] Range Types - typo + NULL string constructor
Jeff Davis writes: > On Sat, 2011-10-08 at 12:44 -0700, Jeff Janes wrote: >> When I apply this to head, "make check" fails with: >> >> create type textrange_en_us as range(subtype=text, collation="en_US"); >> + ERROR: collation "en_US" for encoding "SQL_ASCII" does not exist > Thank you for pointing that out. I think I need to remove those before > commit, but I just wanted them in there now to exercise that part of the > code. > Is there a better way to test collations like that? You could add some code that assumes particular collations are present into collate.linux.utf8.sql. It's not going to work to depend on anything beyond C locale being present in a mainline regression test case. 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] Schema grants for creating and dropping objects
Marc Munro writes: > It seems that in order to create an object in a given schema, I must > have been granted create privilege on the schema. But in order to drop > that object I require usage privilege. > This means that with the right privilege settings I can create objects > that I cannot subsequently drop, or can drop an object that I cannot > recreate. Yeah. So? You can get similar effects with read-only or write-only directories in Unix filesystems. Don't see why you find this surprising. 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] libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable
On Oct9, 2011, at 17:56 , Tom Lane wrote: > Florian Pflug writes: >> On Oct9, 2011, at 14:20 , Kevin Grittner wrote: >>> Yeah. It would be nice to see at least one use case. The only >>> comment I recall is a vague suggestion that that people might want to >>> select data from a table and infer table attributes from the result >>> set metadata. That seems marginal. > >> Well, there is one other, namely SQL standards compliance. It does >> mandate that "CREATE TABLE ... AS SELECT" creates NOT NULL constraints >> on non-nullable columns I think (I didn't re-check, though). I'm not sure >> I see the value in that either, but, hey, standards compliance ought >> to be a value it in itself, right? > > Um ... but that case has nothing to do with protocol changes. No, that was meant as a use-case for the deduction of nullability, not for it's transmission to the client. While those are obviously two distinct things, I figured we'd probably tackle them at the same time (if ever). It'd be strange to infer NOT NULL constraints for CREATE TABLE ... AS SELECT, yet provide no way for clients to obtain that information for simple SELECT statements. And you're right, the Wiki already contains a wish list for the next protocol version, and that wish list includes an entry for extending Describe to report the nullability of columns. The entry, BTW, was added by one Tom Lane ;-) The wish list can be found on http://wiki.postgresql.org/wiki/Todo under "Wire Protocol Changes". The referenced thread on -hackers includes a rather interesting use-case. The idea presented there is to infer the type of a statement's result columns at application compile-time, and inject the result into the compiler's type checking and deduction algorithm. Since most statically types languages don't have a general concept of "undefined" (i.e., there's no special "undefined" value included in the domain of every type), there's a lot of value in knowing that a columns cannot be null. It allows you to map the column directly to a string, int or whatever on the client side, instead of going through some intermediate type which adds "undefined" to the list of possible values. (That "intermediate type" is the "Maybe" monad in Haskell, in C++ it'd be boost::optional or something similar) 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] alter table only ... drop constraint broken in HEAD
On Sun, Oct 9, 2011 at 09:17, Greg Stark wrote: > On Fri, Oct 7, 2011 at 5:45 PM, Alex Hunsaker wrote: >> If I find the time maybe Ill submit something along these lines for >> the next commit fest. >> > > So i just picked up the non-inherited constraints patch and quickly > ran into the same problem and found this thread. > > I think it makes sense to hold off on this patch until these issues > are resolved. Because we really do need to test the cases when adding > or removing child tables that have constraints with the same name as > non-inherited parent tables. And I'm not sure what will happen in > these cases once these issues are resolved. Doesn't someone just need to commit Roberts patch? I suppose it could do with a better review than my eyeballing... Maybe thats where the hang up is? -- 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] Online base backup from the hot-standby
I created a patch corresponding FPW. Fujii's patch (ver 9) is based. Manage own FPW in shared-memory (on master) * startup and walwriter process update it. startup initializes it after REDO. walwriter updates it when started or received SIGHUP. Insert WAL including a value of current FPW (on master) * In the the same timing as update, they insert WAL (is named XLOG_FPW_CHANGE). XLOG_FPW_CHANGE has a value of the changed FPW. * When it creates CHECKPOINT, it adds a value of current FPW to the CHECKPOINT WAL. Manage master's FPW in local-memory in startup (on standby) * It takes a value of the master's FPW by reading XLOG_FPW_CHANGE at REDO. Check when pg_start_backup/pg_stop_backup (on standby) * It checks to use these two value. * master's FPW at latest CHECKPOINT * current master's FPW by XLOG_FPW_CHANGE Regards. Jun Ishizuka NTT Software Corporation TEL：045-317-7018 E-Mail: ishizuka@po.ntts.co.jp standby_online_backup_09base_01fpw.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] libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable
On Sun, Oct 9, 2011 at 17:51, Tom Lane wrote: > "Kevin Grittner" writes: >> Yeah, it wouldn't be hard to produce a long list of things which >> would take about the same effort which seem more beneficial to me. >> It's a matter of whether this is causing someone enough bother to >> want to devote the resources to changing it. > > The problem with something like a protocol bump is that the coding > required to make it happen (in the backend and libpq, that is) is only a > small part of the total distributed cost. So even if someone stepped up > with a patch, it'd likely get rejected outright, unless there's > significant community buy-in to the need for it. > > I agree with Kevin's comment that the right thing to be doing now would > be to be keeping a list of things we might want to change the protocol > for. It's just about certain that no single element on that list will > be sufficient reason to change, but once there are enough of them maybe > we'll have critical mass to do them all together. > > (Actually, isn't there such a page on the wiki already? Or a subsection > of the TODO list?) There is. Currently section 27.3 (seems not to have an anchor to link, and might change numbers when other things change, but that's what it's called now). Heading "wire protocol changes". And I think this is on there already? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Online base backup from the hot-standby
2011/10/9 Jun Ishiduka : > Insert WAL including a value of current FPW (on master) > * In the the same timing as update, they insert WAL (is named > XLOG_FPW_CHANGE). XLOG_FPW_CHANGE has a value of the changed FPW. > * When it creates CHECKPOINT, it adds a value of current FPW to the > CHECKPOINT WAL. I can't see a reason why we would use a new WAL record for this, rather than modify the XLOG_PARAMETER_CHANGE record type which was created for a very similar reason. The code would be much simpler if we just extend XLOG_PARAMETER_CHANGE, so please can we do that? The log message "full_page_writes on master is set invalid more than once during online backup" should read "at least once" rather than "more than once". lastFpwDisabledLSN needs to be initialized. Is there a reason to add lastFpwDisabledLSN onto the Control file? If we log parameters after every checkpoint then we'll know the values when we startup. If we keep logging parameters this way we'll end up with a very awkward and large control file. I would personally prefer to avoid that, but that thought could go either way. Let's see if anyone else thinks that also. Looks good. -- 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] Schema grants for creating and dropping objects
On Sun, 2011-10-09 at 11:58 -0400, Tom Lane wrote: > Marc Munro writes: > > It seems that in order to create an object in a given schema, I must > > have been granted create privilege on the schema. But in order to drop > > that object I require usage privilege. > > > This means that with the right privilege settings I can create objects > > that I cannot subsequently drop, or can drop an object that I cannot > > recreate. > > Yeah. So? You can get similar effects with read-only or write-only > directories in Unix filesystems. Don't see why you find this surprising. It's just that ordinarily as the owner of an object, I can do what I like with it. In this case, I can't. In fact, once I've created the table I can't access it. I guess the surprising thing to me is that I'm allowed to create it without usage privilege. However, it is what it is, and as it is intended behaviour I will happily work with it. Perhaps some extra notes in the documentation of the sql-grants section might be useful. As a side note: creating a file in a write-only directory on Unix doesn't work for me. > regards, tom lane Thanks for the response. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] unite recovery.conf and postgresql.conf
On Tue, Sep 27, 2011 at 10:34 AM, Fujii Masao wrote: > On Mon, Sep 26, 2011 at 7:45 PM, Peter Eisentraut wrote: >> On sön, 2011-09-25 at 12:58 -0400, Tom Lane wrote: >>> And it's not like we don't break configuration file >>> contents in most releases anyway, so I really fail to see why this one >>> has suddenly become sacrosanct. >> >> Well, there is a slight difference. Changes in postgresql.conf >> parameter names and settings are adjusted automatically for me by my >> package upgrade script. If we, say, changed the names of recovery.conf >> parameters, I'd have to get a new version of my $SuperReplicationTool. >> That tool could presumably look at PG_VERSION and put a recovery.conf >> with the right spellings in the right place. >> >> But if we completely change the way the replication configuration >> interacts, it's not clear that a smooth upgrade is possible without >> significant effort. That said, I don't see why it wouldn't be possible, >> but let's design with upgradability in mind, instead of claiming that we >> have never supported upgrades of this kind anyway. > > Currently recovery.conf has two roles: > > #1. recovery.conf is used as a trigger file to enable archive recovery. > At the end of recovery, recovery.conf is renamed to recovery.done. > > #2. recovery.conf is used as a configuration file for recovery parameters. > > Which role do you think we should support in 9.2 because of the backward > compatibility? Both? Unless I misunderstand the discussion so far, Tom and > Robert (and I) agree to get rid of both. Simon seems to agree to remove > only the former role, but not the latter. How about you? If you agree to > remove the former, too, let's focus on the discussion about whether the > latter role should be supported in 9.2. Tatsuo/Josh/Robert also discussed how recovery.conf can be used to provide parameters solely for recovery. That is difficult to do without causing all downstream tools to make major changes in the ways they supply parameters. Keeping our APIs relatively stable is important to downstream tools. I have no objection to a brave new world, as long as you don't chuck out the one that works right now. Breaking APIs needs a good reason and I've not seen one discussed anywhere. No problem with immediately deprecating the old API and declare is planned to be removed in release 10.0. -- 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] index-only scans
I wrote: > I believe that we should rejigger things so that when an index-only scan > is selected, the executor *always* works from the data supplied by the > index. Even if it has to visit the heap --- it will do that but just to > consult the tuple's visibility data, and then use what it got from the > index anyway. This means we'd build the plan node's filter quals and > targetlist to reference the index tuple columns not the underlying > table's. I've been studying this a bit. The key decision is how to represent Vars that reference columns of the index. We really have to have varattno equal to the index column number, else ExecEvalVar will pull the wrong column from the tuple. However, varno is not so clear cut. There are at least four things we could do: 1. Keep varno = table's rangetable index. The trouble with this is that a Var referencing index column N would look exactly like a Var referencing table column N; so the same Var would mean something different in an index-only scan node than it does in any other type of scan node for the same table. We could maybe make that work, but it seems confusing and fragile as heck. The executor isn't going to care much, but inspection of the plan tree by e.g. EXPLAIN sure will. 2. Set varno = OUTER (or maybe INNER). This is safe because there's no other use for OUTER/INNER in a table scan node. We would have to hack things so that the index tuple gets put into econtext->ecxt_outertuple (resp. ecxt_innertuple) at runtime, but that seems like no big problem. In both setrefs.c and ruleutils.c, it would be desirable to have a TargetEntry list somewhere representing the index columns, which setrefs would want so it could set up the special Var nodes with fix_upper_expr, and ruleutils would want so it could interpret the Vars using existing machinery. I'm not sure whether to hang that list on the index-only plan node or expect EXPLAIN to regenerate it at need. 3. Invent another special varno value similar to OUTER/INNER but representing an index reference. This is just about like #2 except that we could still put the index tuple into econtext->ecxt_scantuple, and ExecEvalVar would do the right thing as it stands. 4. Create a rangetable entry specifically representing the index, and set varno equal to that RTE's number. This has some attractiveness in terms of making the meaning of the Vars clear, but an RTE that represents an index rather than a table seems kind of ugly otherwise. It would likely require changes in unrelated parts of the code. One point here is that we have historically used solution #1 to represent the index keys in index qual expressions. We avoid the ambiguity issues by not asking EXPLAIN to try to interpret the indexqual tree at all: it works from indexqualorig which contains ordinary Vars. So one way to dodge the disadvantages of solution #1 would be to add untransformed "targetlistorig" and "qualorig" fields to an index-only plan node, and use those for EXPLAIN. However, those fields would be totally dead weight if the plan were never EXPLAINed, whereas indexqualorig has a legitimate use for rechecking indexquals against the heap tuple in case of a lossy index. (BTW, if we go with any solution other than #1, I'm strongly inclined to change the representation of indexqual to match. See the comments in fix_indexqual_operand.) At the moment I'm leaning to approach #3, but I wonder if anyone has a different opinion or another idea altogether. 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] index-only scans
On Sun, Oct 9, 2011 at 9:03 PM, Tom Lane wrote: > At the moment I'm leaning to approach #3, but I wonder if anyone has > a different opinion or another idea altogether. > Would any of these make it more realistic to talk about the crazy plans Heikki suggested like doing two index scans, doing the join between the index tuples, and only then looking up the visibility information and remaining columns for the tuple on the matching rows? -- 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] index-only scans
Greg Stark writes: > On Sun, Oct 9, 2011 at 9:03 PM, Tom Lane wrote: >> At the moment I'm leaning to approach #3, but I wonder if anyone has >> a different opinion or another idea altogether. > Would any of these make it more realistic to talk about the crazy > plans Heikki suggested like doing two index scans, doing the join > between the index tuples, and only then looking up the visibility > information and remaining columns for the tuple on the matching rows? I don't think it's particularly relevant --- we would not want to use weird representations of the Vars outside the index scan nodes. Above the scan they'd be just like any other upper-level Vars. (FWIW, that idea isn't crazy; I remember having discussions of it back in 2003 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] index-only scans
On Sun, Oct 9, 2011 at 10:54 PM, Tom Lane wrote: > I don't think it's particularly relevant --- we would not want to use > weird representations of the Vars outside the index scan nodes. Above > the scan they'd be just like any other upper-level Vars. I can't say I fully understand the planner data structures and the implications of the options. I guess what I was imagining was that being able to reference the indexes as regular rangetable entries would make it more convenient for the rest of the planner to keep working as if nothing had changed when working with values extracted from index tuples. -- 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] index-only scans
I wrote: > There are at least four things we could do: ... > 2. Set varno = OUTER (or maybe INNER). This is safe because there's no > other use for OUTER/INNER in a table scan node. We would have to hack > things so that the index tuple gets put into econtext->ecxt_outertuple > (resp. ecxt_innertuple) at runtime, but that seems like no big problem. > In both setrefs.c and ruleutils.c, it would be desirable to have a > TargetEntry list somewhere representing the index columns, which setrefs > would want so it could set up the special Var nodes with fix_upper_expr, > and ruleutils would want so it could interpret the Vars using existing > machinery. I'm not sure whether to hang that list on the index-only > plan node or expect EXPLAIN to regenerate it at need. > 3. Invent another special varno value similar to OUTER/INNER but > representing an index reference. This is just about like #2 except that > we could still put the index tuple into econtext->ecxt_scantuple, and > ExecEvalVar would do the right thing as it stands. I have mostly-working code for approach #3, but I haven't tried to make EXPLAIN work yet. While looking at that I realized that there's a pretty good argument for adding the above-mentioned explicit TargetEntry list representing the index columns to index-only plan nodes. Namely, that if we don't do it, EXPLAIN will have to go to the catalogs to find out what's in that index, and this will fall down for "hypothetical" indexes injected into the planner by index advisors. We could imagine adding some more hooks to let the advisor inject bogus catalog data at EXPLAIN time, but on the whole it seems easier and less fragile to just have the planner include a data structure it has to build anyway into the finished plan. The need for this additional node list field also sways me in a direction that I'd previously been on the fence about, namely that I think index-only scans need to be their own independent plan node type instead of sharing a node type with regular indexscans. It's just too weird that a simple boolean indexonly property would mean completely different contents/interpretation of the tlist and quals. I've run into one other thing that's going to need to be hacked up a bit: index-only scans on "name" columns fall over with this modified code, because there's now tighter checking of the implied tuple descriptors: regression=# select relname from pg_class where relname = 'tenk1'; ERROR: attribute 1 has wrong type DETAIL: Table has type cstring, but query expects name. The reason for this is the hack we put in some time back to conserve space in system catalog indexes by having "name" columns be indexed as though they were "cstring", cf commit 5f6f840e93a3649e0d07e85bad188d163e96ec0e. We will probably need some compensatory hack in index-only scans, unless we can think of a less klugy way of representing that optimization. (Basically, the index-only code is assuming that btrees don't have storage type distinct from input type, and that's not the case for the name opclass. I had kind of expected the original patch to have some issues with that too, and I'm still not fully convinced that there aren't corner cases where it'd be an issue even with the currently committed 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] index-only scans
On Mon, Oct 10, 2011 at 2:47 AM, Tom Lane wrote: > The need for this additional node list field also sways me in a > direction that I'd previously been on the fence about, namely that > I think index-only scans need to be their own independent plan node type > instead of sharing a node type with regular indexscans At a superficial PR level it'll go over quite well to have a special plan node be visible in the explain output. People will love to see Fast Index Scan or Covering Index Scan or whatever you call it in their plans. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] What is known about PostgreSQL HP-UX support?
[ Thanks all for the very productive discussion in the thread "libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable" which I originated. Very useful. Now on something different. ] About two years ago, I had to research some PostgreSQL failures on HP-UX on a lame PA-RISC box. Looking at the PostgreSQL source code then, I got an impression that running PostgreSQL on HP-UX was an open question -- HP-UX didn't seem like a seriously targeted platform. Was I wrong in my assessment? Does anybody have a good experience running PostgreSQL on HP-UX? What version of both? PA-RISC? IA64? Thanks, -- Alex -- alex-goncha...@comcast.net -- -- 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] What is known about PostgreSQL HP-UX support?
Alex Goncharov writes: > About two years ago, I had to research some PostgreSQL failures on > HP-UX on a lame PA-RISC box. Looking at the PostgreSQL source code > then, I got an impression that running PostgreSQL on HP-UX was an open > question -- HP-UX didn't seem like a seriously targeted platform. Well, HP hasn't exactly been forthcoming with support on their own end, but we do have an HPUX 11.31 IA64 machine in the buildfarm, and I still routinely test on a personal 10.20 HPPA box, so it's not like the platform doesn't get coverage at all. If you'd like to see some other cases covered, feel free to contribute a buildfarm member. 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] What is known about PostgreSQL HP-UX support?
Thanks... ,--- You/Tom (Sun, 09 Oct 2011 22:29:19 -0400) * | Well, HP hasn't exactly been forthcoming with support on their own end, | but we do have an HPUX 11.31 IA64 machine in the buildfarm, Should I read the above as: 1. The PostgreSQL server will build on HPUX 11.31 IA64. 2. The server will run all right (speaking on the test cases you personally covered only) on HPUX 11.31 IA64. 3. Both PostgreSQL v. 8.4 and 9.1. | and I still routinely test on a personal 10.20 HPPA box, so it's not | like the platform doesn't get coverage at all. and the same three items on HPPA 10.20. Or anything of the items 1 to 3 is not true or not certain? Thanks again! -- Alex -- alex-goncha...@comcast.net -- -- 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] alter table only ... drop constraint broken in HEAD
On Sun, Oct 9, 2011 at 1:56 PM, Alex Hunsaker wrote: >> So i just picked up the non-inherited constraints patch and quickly >> ran into the same problem and found this thread. >> >> I think it makes sense to hold off on this patch until these issues >> are resolved. Because we really do need to test the cases when adding >> or removing child tables that have constraints with the same name as >> non-inherited parent tables. And I'm not sure what will happen in >> these cases once these issues are resolved. > > Doesn't someone just need to commit Roberts patch? Yeah, I've just been mostly AFK for ~53 hours. It's committed now. -- 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] SET variable - Permission issues
On Sat, Oct 8, 2011 at 12:30 PM, Tom Lane wrote: > Josh writes: >> [ unhappy about users being able to freely adjust work_mem etc ] > > Really, if you're letting users issue arbitrary SQL queries, there > simply isn't any way to prevent them from beating your server into > the ground. I don't think that inserting a hack to prevent specific > configuration variables from being adjusted is going to help you > against an uncooperative user. You'd be better off to rethink the > "let them issue SQL queries directly" part of your design. > > The reason that the specific variables you mention (as well as some > others that bear on such things) are USERSET and not SUSET is precisely > that we are not trying to constrain the amount of resources an > uncooperative user can consume. If we did try to do that, quite a > lot of design decisions would have to be revisited, and there would > be a number of unpleasant tradeoffs to be made. GUC privilege levels > are just the tip of the iceberg. Yeah. For example, if somebody writes a complicated query against a gigantic table that runs for a long time, is that because they're trying to DOS the server, or because they have a legitimate need for the results of that query, and it just so happens to be an expensive query? That's not really a question a computer can answer, and if you restrict users to running only queries that are so short and simple that they can't be used to DOS the box, you'll likely also be locking out a significant percentage of legitimate user needs. Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault (which is probably futile) but as a way for DBAs to enforce system policy. But even that seems like a lot of work for a fairly marginal benefit -- 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] SET variable - Permission issues
Robert Haas writes: > On Sat, Oct 8, 2011 at 12:30 PM, Tom Lane wrote: >> The reason that the specific variables you mention (as well as some >> others that bear on such things) are USERSET and not SUSET is precisely >> that we are not trying to constrain the amount of resources an >> uncooperative user can consume. If we did try to do that, quite a >> lot of design decisions would have to be revisited, and there would >> be a number of unpleasant tradeoffs to be made. GUC privilege levels >> are just the tip of the iceberg. > Yeah. For example, if somebody writes a complicated query against a > gigantic table that runs for a long time, is that because they're > trying to DOS the server, or because they have a legitimate need for > the results of that query, and it just so happens to be an expensive > query? That's not really a question a computer can answer, and if you > restrict users to running only queries that are so short and simple > that they can't be used to DOS the box, you'll likely also be locking > out a significant percentage of legitimate user needs. Rereading the original message, I see that Josh raised an independent point which I failed to see was independent. Namely, that if one has a SECURITY DEFINER function that relies on random() producing unpredictable values, then an unprivileged user might be able to compromise the behavior of the function by issuing SET SEED just before calling the function. That does seem like a potential security issue --- not a very big one, but still undesirable. And unlike other GUCs that might affect query behavior, a SECURITY DEFINER function can't protect itself against such a thing by adding SET clauses, because setting the seed to a known value is exactly what it doesn't want. I still don't think that a specialized GUC assignment privilege is worth the trouble, but I could see an argument for just changing the seed GUC from USERSET to SUSET. 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] patch: fix a regress tests
Hello
there is fix a locale dependency of regress tests
Last time I forgot to attach a patch
Regards
Pavel Stehule
*** ./src/test/regress/expected/foreign_data.out.orig 2011-10-04 13:56:41.0 +0200
--- ./src/test/regress/expected/foreign_data.out 2011-10-06 14:06:20.0 +0200
***
*** 631,637
-- CREATE FOREIGN TABLE
CREATE SCHEMA foreign_schema;
! CREATE SERVER sc FOREIGN DATA WRAPPER dummy;
CREATE FOREIGN TABLE ft1 ();-- ERROR
ERROR: syntax error at or near ";"
LINE 1: CREATE FOREIGN TABLE ft1 ();
--- 631,637
-- CREATE FOREIGN TABLE
CREATE SCHEMA foreign_schema;
! CREATE SERVER s0 FOREIGN DATA WRAPPER dummy;
CREATE FOREIGN TABLE ft1 ();-- ERROR
ERROR: syntax error at or near ";"
LINE 1: CREATE FOREIGN TABLE ft1 ();
***
*** 641,655
CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR
NOTICE: CREATE FOREIGN TABLE will create implicit sequence "ft1_c1_seq" for serial column "ft1.c1"
ERROR: default values on foreign tables are not supported
! CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS;-- ERROR
ERROR: syntax error at or near "WITH OIDS"
! LINE 1: CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
! ) SERVER sc OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
--- 641,655
CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR
NOTICE: CREATE FOREIGN TABLE will create implicit sequence "ft1_c1_seq" for serial column "ft1.c1"
ERROR: default values on foreign tables are not supported
! CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS;-- ERROR
ERROR: syntax error at or near "WITH OIDS"
! LINE 1: CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
! ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
***
*** 659,665
c1 | integer | not null | ("param 1" 'val1') | plain| ft1.c1
c2 | text| | (param2 'val2', param3 'val3') | extended |
c3 | date| || plain|
! Server: sc
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
Has OIDs: no
--- 659,665
c1 | integer | not null | ("param 1" 'val1') | plain| ft1.c1
c2 | text| | (param2 'val2', param3 'val3') | extended |
c3 | date| || plain|
! Server: s0
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
Has OIDs: no
***
*** 667,673
List of foreign tables
Schema | Table | Server | FDW Options | Description
+---++-+-
! public | ft1 | sc | (delimiter ',', quote '"', "be quoted" 'value') | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
--- 667,673
List of foreign tables
Schema | Table | Server | FDW Options | Description
+---++-+-
! public | ft1 | s0 | (delimiter ',', quote '"', "be quoted" 'value') | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
***
*** 717,723
c8 | text| | (p2 'V2') | extended |
c9 | integer | || plain|
c10| integer | | (p1 'v1') | plain|
! Server: sc
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
Has OIDs: no
--- 717,723
c8 | text| | (p2 'V2') | extended |
c9 | integer | || plain|
c10| integer | | (p1 'v1') | plain|
! Server: s0
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
Has OIDs: no
***
*** 760,766
c7 | integer | | (p1 'v1', p2 'v2')
c8 | text| | (p2 'V2')
c10 | integer | | (p1 'v1')
! Server: sc
FDW Optio
