Re: [HACKERS] Closing some 8.4 open items
Hi, Reacting somewhat late, but maybe not too late? Le 11 avr. 09 à 17:13, Tom Lane a écrit : My own take on it is that actually I'd prefer one command for all of these. If I say \df sum it would be good if the output included the sum() aggregates; the reason being that I might be wondering if I can create a plain function named sum. If I have to check not only \df and \da but also \dw for conflicts, that's going to be a real PITA. [...] If we were designing in a green field I think you could make a real strong case for a single \df command with an output column type having the alternatives regular, aggregate, window, and maybe trigger. It seems this proposal got a consensus vote, and I'd like to add to it: what about having specialized \df views, per type, with an additional qualifier: \dfa list aggregate functions \dfw list window functions (and aggregates?) \dft list trigger functions ... you get the idea Nothing fundamentally new, just some more convenience to support for users wanting to list functions of a given known type: it allows not to have to \set ECHO_HIDDEN, \df, copy/paste/adapt where/launch again. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
2009/4/11 Andrew Gierth and...@tao11.riddles.org.uk: Tom == Tom Lane t...@sss.pgh.pa.us writes: Perhaps more to the point: the previous round of discussion about this already rejected the idea of treating window functions as a category fundamentally separate from plain functions --- that is, we are not following the aggregate model of having separate commands for aggregate functions. I hadn't seen any such a consensus. Tom We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION, Tom ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be Tom presenting a different world view than exists at the SQL level. I'm not sure why that would matter. The fact that it is CREATE FUNCTION ... WINDOW rather than CREATE WINDOW FUNCTION doesn't mean that window functions aren't a distinctly different animal to normal functions. The usage and syntax is different enough that putting them all together under \df seems forced. Yeah, but all the window functions are stored in pg_proc. Regards, -- Hitoshi Harada -- 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] Closing some 8.4 open items
2009/4/11 Tom Lane t...@sss.pgh.pa.us: Bruce Momjian br...@momjian.us writes: Yea, I thought we were going to do this: Please find enclosed one way to handle it, this being prepending WINDOW to the result types in \df. but I don't see this behavior in CVS. IIRC, my original proposal involved adding something to the argument list --- it seems more natural to regard window-ness as having something to do with the arguments than the result. But that was shot down on the grounds of not fitting in well unless we wanted to add more decoration, like parens around the regular argument list. And someone has claimed the argument column won't fit the syntax of DROP FUNCTION, which is not sure to be harmful or not. Another idea was to add a new column to the \df output to mark window-ness. Which, as I recall, *nobody* liked. But maybe if we only did it for \df+ it would be more tolerable? The only negative opinion of this is added column is useful for only window function so far. And nobody can find the future possible extension by this column. So I'm +1 for do nothing now, and let's wait for users reactions. The changes for this in the future seems not so painful. Regards, -- Hitoshi Harada -- 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] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote: 2009/4/11 Andrew Gierth and...@tao11.riddles.org.uk: Tom == Tom Lane t...@sss.pgh.pa.us writes: Perhaps more to the point: the previous round of discussion about this already rejected the idea of treating window functions as a category fundamentally separate from plain functions --- that is, we are not following the aggregate model of having separate commands for aggregate functions. I hadn't seen any such a consensus. Tom We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION, Tom ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be Tom presenting a different world view than exists at the SQL level. I'm not sure why that would matter. The fact that it is CREATE FUNCTION ... WINDOW rather than CREATE WINDOW FUNCTION doesn't mean that window functions aren't a distinctly different animal to normal functions. The usage and syntax is different enough that putting them all together under \df seems forced. Yeah, but all the window functions are stored in pg_proc. So are aggregate functions, and they have their own separate way of being addressed in psql :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
2009/4/11 David Fetter da...@fetter.org: On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote: Yeah, but all the window functions are stored in pg_proc. So are aggregate functions, and they have their own separate way of being addressed in psql :) Aggregate functions are stored in pg_aggregate. And they are combinations of plain function which is stored in pg_proc. Regards, -- Hitoshi Harada -- 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] Closing some 8.4 open items
On 11 Apr 2009, at 08:01, Hitoshi Harada wrote: 2009/4/11 David Fetter da...@fetter.org: On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote: Yeah, but all the window functions are stored in pg_proc. So are aggregate functions, and they have their own separate way of being addressed in psql :) Aggregate functions are stored in pg_aggregate. And they are combinations of plain function which is stored in pg_proc. Maybe trigger functions should be displayed separately too than ? -- 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] Closing some 8.4 open items
2009/4/11 Grzegorz Jaskiewicz g...@pointblue.com.pl: On 11 Apr 2009, at 08:01, Hitoshi Harada wrote: 2009/4/11 David Fetter da...@fetter.org: On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote: Yeah, but all the window functions are stored in pg_proc. So are aggregate functions, and they have their own separate way of being addressed in psql :) Aggregate functions are stored in pg_aggregate. And they are combinations of plain function which is stored in pg_proc. Maybe trigger functions should be displayed separately too than ? You don't catch the point. The aggregate entries in pg_proc have prosrc = 'aggregate_dummy', which means they're dummy and the entities are stored in pg_aggregate. Triggers in pg_proc are dummy? No, they are actually plain functions with trigger return type. Regards, -- Hitoshi Harada -- 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] Closing some 8.4 open items
On 11 Apr 2009, at 13:33, Hitoshi Harada wrote: Maybe trigger functions should be displayed separately too than ? You don't catch the point. The aggregate entries in pg_proc have prosrc = 'aggregate_dummy', which means they're dummy and the entities are stored in pg_aggregate. Triggers in pg_proc are dummy? No, they are actually plain functions with trigger return type. yes, that's from strictly insider's point of view. Based on the implementation of that in postgresql, but you guys talk about user perspective, after all - psql is for users, not only for postgresql hackers. So the better question would be, can you use window, aggregate, trigger functions the same way other procedures ? I guess the answer is no :) Hence, if classify - than I would suggest to do it completely and fair, and not judge it only from postgresql-hacker perspective. I am hardly the postgresql-hacker myself, so it is my opinion from user perspective, that also understands where your opinion comes from. -- 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] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 5:06 AM, Grzegorz Jaskiewicz g...@pointblue.com.pl wrote: On 11 Apr 2009, at 08:01, Hitoshi Harada wrote: 2009/4/11 David Fetter da...@fetter.org: On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote: Yeah, but all the window functions are stored in pg_proc. So are aggregate functions, and they have their own separate way of being addressed in psql :) Aggregate functions are stored in pg_aggregate. And they are combinations of plain function which is stored in pg_proc. Maybe trigger functions should be displayed separately too than ? We're up to at least four different categories of functions that people think might require special treatment: window, trigger, I/O, everything else. And then there are other categories you might want to include/exclude: conversion functions, referential integrity functions, operator functions, ... it quickly gets out of control. Maybe we should consider some sort of option syntax for blackslash commands. Like, \df -w to see just window functions, \df -c to see just conversion functions, \df -wc to see those two types but not anything else. Actually, I don't really like that syntax either, because it's just propagating the existing dubious design decision of identifying the behavior you want with longer and longer strings of inscrutable single-digit modifiers. But some sort of more powerful syntax would be good. This problem is not limited to searching either - for example, I'd like to be able to do \d foo, except don't show me the foreign-keys because there are a zillion of them and they make the output not fit on the screen. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 01:39:47PM +0100, Grzegorz Jaskiewicz wrote: On 11 Apr 2009, at 13:33, Hitoshi Harada wrote: Maybe trigger functions should be displayed separately too than ? You don't catch the point. The aggregate entries in pg_proc have prosrc = 'aggregate_dummy', which means they're dummy and the entities are stored in pg_aggregate. Triggers in pg_proc are dummy? No, they are actually plain functions with trigger return type. yes, that's from strictly insider's point of view. Based on the implementation of that in postgresql, but you guys talk about user perspective, after all - psql is for users, not only for postgresql hackers. That was my thought on this, too. So the better question would be, can you use window, aggregate, trigger functions the same way other procedures ? I guess the answer is no :) For the first two, it's no. For the third, it's what people are used to, including people who are extending a helping hand via our many help channels. Hence, if classify - than I would suggest to do it completely and fair, and not judge it only from postgresql-hacker perspective. I am hardly the postgresql-hacker myself, so it is my opinion from user perspective, that also understands where your opinion comes from. The amount of code I've gotten into the back end is absolutely minuscule. It's psql where I can currently help people see a new feature. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 08:52:31AM -0400, Robert Haas wrote: On Sat, Apr 11, 2009 at 5:06 AM, Grzegorz Jaskiewicz g...@pointblue.com.pl wrote: On 11 Apr 2009, at 08:01, Hitoshi Harada wrote: 2009/4/11 David Fetter da...@fetter.org: On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote: Yeah, but all the window functions are stored in pg_proc. So are aggregate functions, and they have their own separate way of being addressed in psql :) Aggregate functions are stored in pg_aggregate. And they are combinations of plain function which is stored in pg_proc. Maybe trigger functions should be displayed separately too than ? We're up to at least four different categories of functions that people think might require special treatment: window, trigger, I/O, everything else. The current psql has \da and \df, the latter of which now includes I/O functions. I contend that windowing functions are different enough that they require a separate category. You do bring up an interesting point for 8.5 or later, which would be a complete redo of psql from the ground up. Let's hash out a proposal for that in a separate thread once we get 8.4 out the door :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
2009/4/11 Grzegorz Jaskiewicz g...@pointblue.com.pl: On 11 Apr 2009, at 13:33, Hitoshi Harada wrote: Maybe trigger functions should be displayed separately too than ? You don't catch the point. The aggregate entries in pg_proc have prosrc = 'aggregate_dummy', which means they're dummy and the entities are stored in pg_aggregate. Triggers in pg_proc are dummy? No, they are actually plain functions with trigger return type. yes, that's from strictly insider's point of view. Based on the implementation of that in postgresql, but you guys talk about user perspective, after all - psql is for users, not only for postgresql hackers. So the better question would be, can you use window, aggregate, trigger functions the same way other procedures ? I guess the answer is no :) Hence, if classify - than I would suggest to do it completely and fair, and not judge it only from postgresql-hacker perspective. I am hardly the postgresql-hacker myself, so it is my opinion from user perspective, that also understands where your opinion comes from. It seems I that didn't catch the point. Still, I don't like such variable syntax for psql -- window, aggregate, plain function, trigger, i/o, cast and more -- I cannot remeber them all :( Regards, -- Hitoshi Harada -- 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] Closing some 8.4 open items
David Fetter da...@fetter.org writes: On Sat, Apr 11, 2009 at 08:52:31AM -0400, Robert Haas wrote: We're up to at least four different categories of functions that people think might require special treatment: window, trigger, I/O, everything else. The current psql has \da and \df, the latter of which now includes I/O functions. I contend that windowing functions are different enough that they require a separate category. I think the fact that aggregates have a separate command is somewhat historical. However, the fact remains that at the SQL level there is CREATE/DROP/etc AGGREGATE and CREATE/DROP/etc FUNCTION, and nothing else. If we don't hang psql's hat on that same hook then we are going to confuse users --- not to mention that this thread will never reach a resolution because there will be too many alternatives. 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] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 10:32:14AM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Sat, Apr 11, 2009 at 08:52:31AM -0400, Robert Haas wrote: We're up to at least four different categories of functions that people think might require special treatment: window, trigger, I/O, everything else. The current psql has \da and \df, the latter of which now includes I/O functions. I contend that windowing functions are different enough that they require a separate category. I think the fact that aggregates have a separate command is somewhat historical. However, the fact remains that at the SQL level there is CREATE/DROP/etc AGGREGATE and CREATE/DROP/etc FUNCTION, and nothing else. If we don't hang psql's hat on that same hook then we are going to confuse users --- not to mention that this thread will never reach a resolution because there will be too many alternatives. The do nothing solution is unacceptable because windowing functions behave in a way that's essentially different, from the user's perspective, from other functions including aggregates. Speaking of aggregates, they should probably show up in the windowing functions section too, as they behave differently there. For example, a sum() over a window with ordering is a *running* sum (to the extent that the ORDER BY clause causes unique values), a completely different behavior from its normal aggregate/non-ordered windowing behavior. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
David Fetter da...@fetter.org writes: The do nothing solution is unacceptable because windowing functions behave in a way that's essentially different, from the user's perspective, from other functions including aggregates. I don't like doing nothing either, but I disagree with your conclusion that window functions are as different from regular ones as aggregates are. Aggregates cause a sea-change in the behavior of the query around them; window functions do not. The call syntax of window functions is a bit odd (which is why \df needs to label them) but they still produce one output value where a regular function would produce one output value, and they don't have an impact on the semantics of the surrounding query. My own take on it is that actually I'd prefer one command for all of these. If I say \df sum it would be good if the output included the sum() aggregates; the reason being that I might be wondering if I can create a plain function named sum. If I have to check not only \df and \da but also \dw for conflicts, that's going to be a real PITA. Also, pity the poor newbie who is unclear on the distinctions between these different function-looking animals, and is just trying to find some documentation on rank(). If we were designing in a green field I think you could make a real strong case for a single \df command with an output column type having the alternatives regular, aggregate, window, and maybe trigger. 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] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 11:13:59AM -0400, Tom Lane wrote: My own take on it is that actually I'd prefer one command for all of these. If I say \df sum it would be good if the output included the sum() aggregates; the reason being that I might be wondering if I can create a plain function named sum. If I have to check not only \df and \da but also \dw for conflicts, that's going to be a real PITA. Also, pity the poor newbie who is unclear on the distinctions between these different function-looking animals, and is just trying to find some documentation on rank(). If we were designing in a green field I think you could make a real strong case for a single \df command with an output column type having the alternatives regular, aggregate, window, and maybe trigger. What would it do for triggers? Sounds like a general identifier search; there seem to be two big namespaces in PG at the moment, that of things that look like function calls and that of relations (and their types). CREATE TABLE foo ( i int, t text ); and CREATE TYPE foo AS ( t text); both go into the same namespace so would appear to be a similar symptom as above. I have a feeling this is going a bit further than you're thinking above. Not sure about the newbie argument; I'd expect them to be using google and wouldn't know much about the backslash commands in psql. -- Sam http://samason.me.uk/ -- 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] Closing some 8.4 open items
Sam Mason s...@samason.me.uk writes: On Sat, Apr 11, 2009 at 11:13:59AM -0400, Tom Lane wrote: If we were designing in a green field I think you could make a real strong case for a single \df command with an output column type having the alternatives regular, aggregate, window, and maybe trigger. What would it do for triggers? Well, I was just reacting to a comment upthread about triggers not being callable in the same contexts as other functions. I'm not hot to label them separately. The return type would be shown as trigger, which in theory is enough to tell you it's a trigger. 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] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 8:58 AM, David Fetter da...@fetter.org wrote: On Sat, Apr 11, 2009 at 08:52:31AM -0400, Robert Haas wrote: On Sat, Apr 11, 2009 at 5:06 AM, Grzegorz Jaskiewicz g...@pointblue.com.pl wrote: On 11 Apr 2009, at 08:01, Hitoshi Harada wrote: 2009/4/11 David Fetter da...@fetter.org: On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote: Yeah, but all the window functions are stored in pg_proc. So are aggregate functions, and they have their own separate way of being addressed in psql :) Aggregate functions are stored in pg_aggregate. And they are combinations of plain function which is stored in pg_proc. Maybe trigger functions should be displayed separately too than ? We're up to at least four different categories of functions that people think might require special treatment: window, trigger, I/O, everything else. The current psql has \da and \df, the latter of which now includes I/O functions. I contend that windowing functions are different enough that they require a separate category. You do bring up an interesting point for 8.5 or later, which would be a complete redo of psql from the ground up. Let's hash out a proposal for that in a separate thread once we get 8.4 out the door :) :-) I'm sure consensus will be reached quickly and painlessly. :-) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
All, Having an extra column in \df for Windowing was rejected out of hand. Why? \df (let alone \df+) already displays too many wide columns to fit in any standard terminal window. You're pretty much forced to use \x regardless. What's one more column? And has it occurred to anyone that a pg_functions view is *way* overdue? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Josh Berkus j...@agliodbs.com writes: Having an extra column in \df for Windowing was rejected out of hand. Why? I'd definitely support adding it to \df+. Basic \df might be a harder sell, because it still does mostly fit in 80 columns now, but would certainly no longer do so with another column. And has it occurred to anyone that a pg_functions view is *way* overdue? Too late for 8.4 I'm afraid, but we could talk about it for 8.5. What have you got in mind that would be different from \df? 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] Closing some 8.4 open items
Tom, It fits into 80 columns if you don't have any functions with 11 parameters. ;-) Actually, I'm thinking the new column ought to be called type. That way, it could be window or trigger or internal, and more types later if we develop any (like MED). Too late for 8.4 I'm afraid, but we could talk about it for 8.5. What have you got in mind that would be different from \df? Well, \df+. It would have the same columns. But you'd be able to query just the column you want, and just the types you want. SELECT name, parameters FROM pg_functions WHERE function_type = window. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Josh Berkus j...@agliodbs.com writes: Tom, It fits into 80 columns if you don't have any functions with 11 parameters. ;-) Well, yeah, but in typical cases I think it fits. A look at the current regression database shows all but 6 of 117 functions fitting. With another ten characters eaten by a new column, a lot more of them would wrap. Actually, I'm thinking the new column ought to be called type. Yes, that's what I had in mind too. 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] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 03:12:39PM -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Tom, It fits into 80 columns if you don't have any functions with 11 parameters. ;-) Well, yeah, but in typical cases I think it fits. A look at the current regression database shows all but 6 of 117 functions fitting. With another ten characters eaten by a new column, a lot more of them would wrap. Actually, I'm thinking the new column ought to be called type. Yes, that's what I had in mind too. Excellent idea. I just plain don't believe that there's anything process-critical and automated that depends on \da, although we could have it rewritten as an alias for convenience. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
David Fetter wrote: On Sat, Apr 11, 2009 at 03:12:39PM -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Tom, It fits into 80 columns if you don't have any functions with 11 parameters. ;-) Well, yeah, but in typical cases I think it fits. A look at the current regression database shows all but 6 of 117 functions fitting. With another ten characters eaten by a new column, a lot more of them would wrap. Actually, I'm thinking the new column ought to be called type. Yes, that's what I had in mind too. Excellent idea. I just plain don't believe that there's anything process-critical and automated that depends on \da, although we could have it rewritten as an alias for convenience. I assume the 'type' column will identify triggers, i/o functions (cstring), window functions, and maybe aggregates too; this solves several problems at once. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 2:47 PM, Josh Berkus j...@agliodbs.com wrote: All, Having an extra column in \df for Windowing was rejected out of hand. Why? I have no idea. I suggested it and the only one I remember speaking against it was Tom. \df (let alone \df+) already displays too many wide columns to fit in any standard terminal window. You're pretty much forced to use \x regardless. What's one more column? And has it occurred to anyone that a pg_functions view is *way* overdue? For what purpose? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 03:34:31PM -0400, Bruce Momjian wrote: David Fetter wrote: On Sat, Apr 11, 2009 at 03:12:39PM -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Tom, It fits into 80 columns if you don't have any functions with 11 parameters. ;-) Well, yeah, but in typical cases I think it fits. A look at the current regression database shows all but 6 of 117 functions fitting. With another ten characters eaten by a new column, a lot more of them would wrap. Actually, I'm thinking the new column ought to be called type. Yes, that's what I had in mind too. Excellent idea. I just plain don't believe that there's anything process-critical and automated that depends on \da, although we could have it rewritten as an alias for convenience. I assume the 'type' column will identify triggers, i/o functions (cstring), window functions, and maybe aggregates too; this solves several problems at once. Lemme whip up a patch :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Bruce Momjian br...@momjian.us writes: I assume the 'type' column will identify triggers, i/o functions (cstring), window functions, and maybe aggregates too; this solves several problems at once. +1 for all except i/o functions. The cstring check for that was always flat-out wrong, and getting it right is far more expensive than it's worth --- AFAICS you'd have to grovel through all entries in pg_type. But aggregates are only relevant if we decide to start showing aggregates in \df --- is there consensus for that? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 04:30:02PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I assume the 'type' column will identify triggers, i/o functions (cstring), window functions, and maybe aggregates too; this solves several problems at once. +1 for all except i/o functions. The cstring check for that was always flat-out wrong, and getting it right is far more expensive than it's worth --- AFAICS you'd have to grovel through all entries in pg_type. I'll leave it out :) But aggregates are only relevant if we decide to start showing aggregates in \df --- is there consensus for that? I'd throw 'em in. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 01:43:35PM -0700, David Fetter wrote: On Sat, Apr 11, 2009 at 04:30:02PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I assume the 'type' column will identify triggers, i/o functions (cstring), window functions, and maybe aggregates too; this solves several problems at once. +1 for all except i/o functions. The cstring check for that was always flat-out wrong, and getting it right is far more expensive than it's worth --- AFAICS you'd have to grovel through all entries in pg_type. I'll leave it out :) But aggregates are only relevant if we decide to start showing aggregates in \df --- is there consensus for that? I'd throw 'em in. It occurs to me that we ought to allow for a possibility that a function can be more than one special case. For example, sum() is both an aggregate and a windowing function, while rank() is only a windowing function. Working on a patch that allows a concise description of both. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Robert, For what purpose? See above, in thread. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
David Fetter da...@fetter.org writes: It occurs to me that we ought to allow for a possibility that a function can be more than one special case. For example, sum() is both an aggregate and a windowing function, while rank() is only a windowing function. If it makes the display even one character wider, -1 from me. That's a purely hypothetical problem for the classification we're discussing. (No, I don't feel a need for \df to remind me every time that aggregates can also be window functions.) 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] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 07:35:54PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: It occurs to me that we ought to allow for a possibility that a function can be more than one special case. For example, sum() is both an aggregate and a windowing function, while rank() is only a windowing function. If it makes the display even one character wider, -1 from me. That's a purely hypothetical problem for the classification we're discussing. (No, I don't feel a need for \df to remind me every time that aggregates can also be window functions.) OK :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Tue, Apr 07, 2009 at 07:28:25PM -0700, David Fetter wrote:
On Mon, Apr 06, 2009 at 10:51:22PM -0700, David Fetter wrote:
On Sun, Apr 05, 2009 at 05:57:46PM -0700, David Fetter wrote:
On Sun, Apr 05, 2009 at 08:55:07PM -0400, Tom Lane wrote:
David Fetter da...@fetter.org writes:
On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote:
The \df thing? That's something it'd be okay to revisit during
beta, IMHO.
OK, I'll work on this tomorrow :)
I think what we were lacking was consensus on what it should do, not
code ...
I was thinking I'd knock out a proposal or two.
Please find enclosed one way to handle it, this being prepending
WINDOW to the result types in \df.
Another way, patch coming tomorrow, would be to add a \dw and remove
the functions where pg_proc.iswindowing is true from \df.
Here's another way, adding \dw.
Revised patch attached. \dw does not need an 'S' decorator, and would
be confusing with one now as there are only a few windowing functions,
and all of those system.
Also included are SGML docs. Mea culpa.
There is one translatable string added. Sorry about that.
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 10d42ca..2e6484f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1257,6 +1257,18 @@ testdb=gt;
varlistentry
+termliteral\dw [ replaceable
class=parameterpattern/replaceable ]/literal/term
+listitem
+para
+Lists all windowing functions. If replaceable
+class=parameterpattern/replaceable is specified, only
+those windowing functions whose names match the pattern are listed.
+/para
+/listitem
+ /varlistentry
+
+
+ varlistentry
termliteral\edit/literal (or literal\e/literal)
literaloptional replaceable class=parameterfilename/replaceable
/optional/literal/term
listitem
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b39466d..b737daf 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -396,6 +396,9 @@ exec_command(const char *cmd,
case 'u':
success = describeRoles(pattern, show_verbose);
break;
+ case 'w':
+ success = describeWindowingFunctions(pattern);
+ break;
case 'F': /* text search
subsystem */
switch (cmd[2])
{
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 731baf8..5699e29 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -280,6 +280,9 @@ describeFunctions(const char *pattern, bool verbose, bool
showSystem)
appendPQExpBuffer(buf, AND n.nspname 'pg_catalog'\n
AND
n.nspname 'information_schema'\n);
+if (pset.sversion = 80400)
+ appendPQExpBuffer(buf, AND NOT p.proiswindow\n);
+
processSQLNamePattern(pset.db, buf, pattern, true, false,
n.nspname, p.proname,
NULL,
pg_catalog.pg_function_is_visible(p.oid));
@@ -3059,6 +3062,56 @@ listUserMappings(const char *pattern, bool verbose)
return true;
}
+bool
+describeWindowingFunctions(const char *pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (pset.sversion 80400)
+ {
+ fprintf(stderr, _(The server (version %d.%d) does not support
windowing functions.\n),
+ pset.sversion / 1, (pset.sversion / 100) %
100);
+ return true;
+ }
+
+ initPQExpBuffer(buf);
+
+ printfPQExpBuffer(buf,
+ SELECT n.nspname as \%s\,\n
+ p.proname as \%s\,\n
+
pg_catalog.pg_get_function_result(p.oid) as \%s\,\n
+
pg_catalog.pg_get_function_arguments(p.oid) as \%s\
+ \nFROM pg_catalog.pg_proc p
+ \n LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n
+ WHERE p.proiswindow\n,
+ gettext_noop(Schema),
+ gettext_noop(Name),
+
Re: [HACKERS] Closing some 8.4 open items
David Fetter da...@fetter.org writes: Revised patch attached. \dw does not need an 'S' decorator, Yes it does. We have only painfully gotten to the point of having consistent behavior across all the \d commands. We are not going to break that consistency before it's even shipped. Perhaps more to the point: the previous round of discussion about this already rejected the idea of treating window functions as a category fundamentally separate from plain functions --- that is, we are not following the aggregate model of having separate commands for aggregate functions. So it's not apparent to me that a separate \dw command is a good solution to start with. 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] Closing some 8.4 open items
Tom Lane wrote: David Fetter da...@fetter.org writes: Revised patch attached. \dw does not need an 'S' decorator, Yes it does. We have only painfully gotten to the point of having consistent behavior across all the \d commands. We are not going to break that consistency before it's even shipped. Perhaps more to the point: the previous round of discussion about this already rejected the idea of treating window functions as a category fundamentally separate from plain functions --- that is, we are not following the aggregate model of having separate commands for aggregate functions. So it's not apparent to me that a separate \dw command is a good solution to start with. Yea, I thought we were going to do this: Please find enclosed one way to handle it, this being prepending WINDOW to the result types in \df. but I don't see this behavior in CVS. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Bruce Momjian br...@momjian.us writes: Yea, I thought we were going to do this: Please find enclosed one way to handle it, this being prepending WINDOW to the result types in \df. but I don't see this behavior in CVS. IIRC, my original proposal involved adding something to the argument list --- it seems more natural to regard window-ness as having something to do with the arguments than the result. But that was shot down on the grounds of not fitting in well unless we wanted to add more decoration, like parens around the regular argument list. Another idea was to add a new column to the \df output to mark window-ness. Which, as I recall, *nobody* liked. But maybe if we only did it for \df+ it would be more tolerable? 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] Closing some 8.4 open items
On Fri, Apr 10, 2009 at 11:30:30AM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: Revised patch attached. \dw does not need an 'S' decorator, Yes it does. We have only painfully gotten to the point of having consistent behavior across all the \d commands. We are not going to break that consistency before it's even shipped. I'd be happy to revert that part. Perhaps more to the point: the previous round of discussion about this already rejected the idea of treating window functions as a category fundamentally separate from plain functions --- that is, we are not following the aggregate model of having separate commands for aggregate functions. I hadn't seen any such a consensus. If anything, the consensus seemed to be going toward the \da and not away from it, hence the revised patch. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
David Fetter da...@fetter.org writes: On Fri, Apr 10, 2009 at 11:30:30AM -0400, Tom Lane wrote: Perhaps more to the point: the previous round of discussion about this already rejected the idea of treating window functions as a category fundamentally separate from plain functions --- that is, we are not following the aggregate model of having separate commands for aggregate functions. I hadn't seen any such a consensus. We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION, ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be presenting a different world view than exists at the SQL level. 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] Closing some 8.4 open items
Tom Lane escreveu: Bruce Momjian br...@momjian.us writes: Yea, I thought we were going to do this: Please find enclosed one way to handle it, this being prepending WINDOW to the result types in \df. but I don't see this behavior in CVS. IIRC, my original proposal involved adding something to the argument list --- it seems more natural to regard window-ness as having something to do with the arguments than the result. But that was shot down on the grounds of not fitting in well unless we wanted to add more decoration, like parens around the regular argument list. Another idea was to add a new column to the \df output to mark window-ness. Which, as I recall, *nobody* liked. But maybe if we only did it for \df+ it would be more tolerable? Adding another column to \df+ is not a good idea; there are already too much columns. Window functions are special functions (they even have an different syntax and separate section in docs) and are not less special than aggregate functions. So +1 to add \dw for them. -- Euler Taveira de Oliveira http://www.timbira.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] Closing some 8.4 open items
Tom == Tom Lane t...@sss.pgh.pa.us writes: Perhaps more to the point: the previous round of discussion about this already rejected the idea of treating window functions as a category fundamentally separate from plain functions --- that is, we are not following the aggregate model of having separate commands for aggregate functions. I hadn't seen any such a consensus. Tom We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION, Tom ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be Tom presenting a different world view than exists at the SQL level. I'm not sure why that would matter. The fact that it is CREATE FUNCTION ... WINDOW rather than CREATE WINDOW FUNCTION doesn't mean that window functions aren't a distinctly different animal to normal functions. The usage and syntax is different enough that putting them all together under \df seems forced. -- Andrew (irc:RhodiumToad) -- 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] Closing some 8.4 open items
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Here is my thinking, and considering that that would basically involve a forward-looking design decision right now, I would support dropping the cardinality() function from 8.4 (if people agree that this is in fact the design decision to make). At this point I'd support that too. It doesn't seem that getting cardinality() into 8.4 is important enough to risk making a decision that we'd regret later. And I think it's not hard to make the case that we might regret either of the other choices later, depending on where we go with arrays. +1 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] Closing some 8.4 open items
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Here is my thinking, and considering that that would basically involve a forward-looking design decision right now, I would support dropping the cardinality() function from 8.4 (if people agree that this is in fact the design decision to make). At this point I'd support that too. +1 Since there were no objections, and there is no time left before beta1, I'm going to go ahead and remove cardinality(). 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] Closing some 8.4 open items
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Here is my thinking, and considering that that would basically involve a forward-looking design decision right now, I would support dropping the cardinality() function from 8.4 (if people agree that this is in fact the design decision to make). At this point I'd support that too. +1 Since there were no objections, and there is no time left before beta1, I'm going to go ahead and remove cardinality(). Do we want a TODO? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On 4/9/09 10:42 AM, Bruce Momjian wrote: Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: Tom Lane wrote: Peter Eisentrautpete...@gmx.net writes: Here is my thinking, and considering that that would basically involve a forward-looking design decision right now, I would support dropping the cardinality() function from 8.4 (if people agree that this is in fact the design decision to make). At this point I'd support that too. +1 Since there were no objections, and there is no time left before beta1, I'm going to go ahead and remove cardinality(). Do we want a TODO? No, I don't think so. It would just be a tag-on to whatever TODO we already have about implementing multisets and collections. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On 04/08/09 13:10, Josh Berkus wrote: On 4/8/09 9:44 AM, Tom Lane wrote: Josh Berkusj...@agliodbs.com writes: What about seq scans? If the kernel can't read-ahead a seqscan by itself, it's unlikely to be smart enough to be helped by posix_fadvise ... or at least so I would think. Do you have reason to think differently? Well, Solaris 10 + UFS should be helped by fadvise -- in theory at least, it would eliminate the need to modify your mount points for better readahead when setting up a PG-Solaris server. Solaris-UFS quite lazy about readahead. Zdenek, Jignesh? Definitely this helps.. specially since forcedirectio hurts CLOGs and helps WAL .. something that can be done without really impacting the whole file system always helps. Solaris by default only does readahead upto 56K and max tunable is 1MB. If you use forcedirectio there is no readahead by the filesystem itself ZFS is different it has no forcedirectio and hence fadvise flag for now is ignored. Regards, Jignesh You're probably correct about Linux and FreeBSD. I don't know if OSX + HFS supports fadvise. If so, it could only help; readahead on HFS right now is nonexistant. Presumably fadvise is useless on Windows. Anyone know? -- 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] Closing some 8.4 open items
On Wed, Apr 8, 2009 at 1:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkus j...@agliodbs.com wrote: So has fadvise been completely dropped from 8.4, or only partially? Bitmap scans will support it, but index scans will not. And please note that we think bitmap scans are the larger part of the win anyway. What's left undone there is some marginal mopup. Can you elaborate on this? I'm fuzzy on why index scans can't benefit from this as much as bitmap index scans. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Robert Haas robertmh...@gmail.com writes: On Wed, Apr 8, 2009 at 1:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: And please note that we think bitmap scans are the larger part of the win anyway. What's left undone there is some marginal mopup. Can you elaborate on this? I'm fuzzy on why index scans can't benefit from this as much as bitmap index scans. The main point is that the planner will prefer a bitmap scan for any query that's estimated to return more than quite a small number of rows. (In my experience the cutover point is in the single digits.) So there's just not that much room to win for plain indexscans. Their principal application is really for fetching single rows, a case where prefetch is entirely useless because you have nothing to overlap. 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] Closing some 8.4 open items
On Wed, Apr 8, 2009 at 10:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Apr 8, 2009 at 1:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: And please note that we think bitmap scans are the larger part of the win anyway. What's left undone there is some marginal mopup. Can you elaborate on this? I'm fuzzy on why index scans can't benefit from this as much as bitmap index scans. The main point is that the planner will prefer a bitmap scan for any query that's estimated to return more than quite a small number of rows. (In my experience the cutover point is in the single digits.) So there's just not that much room to win for plain indexscans. Their principal application is really for fetching single rows, a case where prefetch is entirely useless because you have nothing to overlap. That makes sense, but what about the nestloop-over-inner-indexscan case? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Robert Haas robertmh...@gmail.com writes: On Wed, Apr 8, 2009 at 10:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: The main point is that the planner will prefer a bitmap scan for any query that's estimated to return more than quite a small number of rows. That makes sense, but what about the nestloop-over-inner-indexscan case? What about it? The provided patch made no attempt to optimize that case. Doing so might well be interesting, but it's not getting done for 8.4. I think it would be quite an invasive patch --- it's hard to see how to do it without explicit support at the nestloop join level, so that you could pipeline the processing of multiple key values coming from the outer side of the join. 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] Closing some 8.4 open items
On Wed, Apr 8, 2009 at 11:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Apr 8, 2009 at 10:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: The main point is that the planner will prefer a bitmap scan for any query that's estimated to return more than quite a small number of rows. That makes sense, but what about the nestloop-over-inner-indexscan case? What about it? The provided patch made no attempt to optimize that case. Doing so might well be interesting, but it's not getting done for 8.4. I think it would be quite an invasive patch --- it's hard to see how to do it without explicit support at the nestloop join level, so that you could pipeline the processing of multiple key values coming from the outer side of the join. OK, I think I'm now understanding your line of thinking. Thanks for the explanation. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On 4/7/09 10:17 PM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkusj...@agliodbs.com wrote: So has fadvise been completely dropped from 8.4, or only partially? Bitmap scans will support it, but index scans will not. What about seq scans? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Josh Berkus j...@agliodbs.com writes: What about seq scans? If the kernel can't read-ahead a seqscan by itself, it's unlikely to be smart enough to be helped by posix_fadvise ... or at least so I would think. Do you have reason to think differently? 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] Closing some 8.4 open items
On 4/8/09 9:44 AM, Tom Lane wrote: Josh Berkusj...@agliodbs.com writes: What about seq scans? If the kernel can't read-ahead a seqscan by itself, it's unlikely to be smart enough to be helped by posix_fadvise ... or at least so I would think. Do you have reason to think differently? Well, Solaris 10 + UFS should be helped by fadvise -- in theory at least, it would eliminate the need to modify your mount points for better readahead when setting up a PG-Solaris server. Solaris-UFS quite lazy about readahead. Zdenek, Jignesh? You're probably correct about Linux and FreeBSD. I don't know if OSX + HFS supports fadvise. If so, it could only help; readahead on HFS right now is nonexistant. Presumably fadvise is useless on Windows. Anyone know? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Wednesday, April 8, 2009, Josh Berkus j...@agliodbs.com wrote: On 4/8/09 9:44 AM, Tom Lane wrote: Josh Berkusj...@agliodbs.com writes: What about seq scans? If the kernel can't read-ahead a seqscan by itself, it's unlikely to be smart enough to be helped by posix_fadvise ... or at least so I would think. Do you have reason to think differently? Well, Solaris 10 + UFS should be helped by fadvise -- in theory at least, it would eliminate the need to modify your mount points for better readahead when setting up a PG-Solaris server. Solaris-UFS quite lazy about readahead. Zdenek, Jignesh? You're probably correct about Linux and FreeBSD. I don't know if OSX + HFS supports fadvise. If so, it could only help; readahead on HFS right now is nonexistant. Presumably fadvise is useless on Windows. Anyone know? It is. -- Dave Page EnterpriseDB UK: 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] Closing some 8.4 open items
Josh Berkus wrote: On 4/8/09 9:44 AM, Tom Lane wrote: Josh Berkusj...@agliodbs.com writes: What about seq scans? If the kernel can't read-ahead a seqscan by itself, it's unlikely to be smart enough to be helped by posix_fadvise ... or at least so I would think. Do you have reason to think differently? Well, Solaris 10 + UFS should be helped by fadvise -- in theory at least, it would eliminate the need to modify your mount points for better readahead when setting up a PG-Solaris server. Solaris-UFS quite lazy about readahead. Zdenek, Jignesh? You're probably correct about Linux and FreeBSD. I don't know if OSX + HFS supports fadvise. If so, it could only help; readahead on HFS right now is nonexistant. Presumably fadvise is useless on Windows. Anyone know? It's important to distinguish what kind of fadvise we're talking about. The bitmap scan code issues hints about individual pages, using posix_fadvise(... POSIX_FADV_WILLNEED). For increasing the readahead of a sequential scan, you'd want to use POSIX_FADV_SEQUENTIAL. I believe the support for the latter is much more widespread than for the former. xlog.c now also uses POSIX_FADV_WONTNEED to drop WAL pages from the OS cache after writing them. -- 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] Closing some 8.4 open items
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: xlog.c now also uses POSIX_FADV_WONTNEED to drop WAL pages from the OS cache after writing them. Even when archiving is on? -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] Closing some 8.4 open items
Dave Page wrote: On Wednesday, April 8, 2009, Josh Berkus j...@agliodbs.com wrote: Presumably fadvise is useless on Windows. Anyone know? It is. cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert it), but not any of the other flags. It maps it to NtSetInformationFile() like this: if (advice == POSIX_FADV_SEQUENTIAL) fmi.Mode |= FILE_SEQUENTIAL_ONLY; status = NtSetInformationFile (get_handle (), io, fmi, sizeof fmi, FileModeInformation); -- 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] Closing some 8.4 open items
Kevin Grittner wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: xlog.c now also uses POSIX_FADV_WONTNEED to drop WAL pages from the OS cache after writing them. Even when archiving is on? No, not in that case. -- 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] Closing some 8.4 open items
Heikki Linnakangas wrote: Dave Page wrote: On Wednesday, April 8, 2009, Josh Berkus j...@agliodbs.com wrote: Presumably fadvise is useless on Windows. Anyone know? It is. cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert it), but not any of the other flags. It maps it to NtSetInformationFile() like this: We set this in our open() wrapper in the code today. That doesn't support changing it after the fact, of course. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Heikki, It's important to distinguish what kind of fadvise we're talking about. The bitmap scan code issues hints about individual pages, using posix_fadvise(... POSIX_FADV_WILLNEED). For increasing the readahead of a sequential scan, you'd want to use POSIX_FADV_SEQUENTIAL. I believe the support for the latter is much more widespread than for the former. OK, so this is potentially useful (pending testing) but it's a different feature. We'll discuss it for 8.5. The other thing I was going to ask you about is using posix_fadvise as an alternative to O_DIRECT for the xlog. O_DIRECT is, AFAIK, linux-only, whereas there are direct write fadvise flags which work on multiple OSes. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Josh Berkus wrote: The other thing I was going to ask you about is using posix_fadvise as an alternative to O_DIRECT for the xlog. O_DIRECT is, AFAIK, linux-only, whereas there are direct write fadvise flags which work on multiple OSes. What flags are those? I don't see any posix_fadvise flags that would do anything like O_DIRECT. -- 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] Closing some 8.4 open items
Magnus Hagander mag...@hagander.net writes: Heikki Linnakangas wrote: cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert it), but not any of the other flags. It maps it to NtSetInformationFile() like this: We set this in our open() wrapper in the code today. Really? Where? I didn't find any of the mentioned symbols in a quick grep. I'm not sure how Windows interprets FILE_SEQUENTIAL_ONLY, but if that really means that it assumes *only* sequential accesses will happen, I'm not sure that we'd want to turn it on. 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] Closing some 8.4 open items
Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: Heikki Linnakangas wrote: cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert it), but not any of the other flags. It maps it to NtSetInformationFile() like this: We set this in our open() wrapper in the code today. Really? Where? I didn't find any of the mentioned symbols in a quick grep. We pass FILE_FLAG_SEQUENTIAL_SCAN to the open call if O_SEQUENTIAL is specified. I'm not sure how Windows interprets FILE_SEQUENTIAL_ONLY, but if that really means that it assumes *only* sequential accesses will happen, I'm not sure that we'd want to turn it on. It's an access-optimization hint, that's all. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Wed, Apr 8, 2009 at 6:42 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Dave Page wrote: On Wednesday, April 8, 2009, Josh Berkus j...@agliodbs.com wrote: Presumably fadvise is useless on Windows. Anyone know? It is. cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert it), but not any of the other flags. It maps it to NtSetInformationFile() like this: if (advice == POSIX_FADV_SEQUENTIAL) fmi.Mode |= FILE_SEQUENTIAL_ONLY; status = NtSetInformationFile (get_handle (), io, fmi, sizeof fmi, FileModeInformation); Which is only useful with async IO as far as I'm aware. -- Dave Page EnterpriseDB UK: 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] Closing some 8.4 open items
Tom, change cardinality() for multi-dim arrays? Drop; there's no consensus that this should be changed Andrew pinged me on this. While there's no consensus that it should be changed, there's no consensus it shouldn't, either. And once we release it, we've set the way it operates in cement, so I'd like to get a consensus one way or the other. I think if we *can't* get a consensus, it's better to omit the syntax from 8.4 then risk deploying syntax we'll want to change later. For my part, I'd like to know what things other than arrays collection_expression in the standard applies to. I think the most sensible course is to make cardinality(array[]) behave consistently with cardinality(other_stuff) when we get around to implementing it. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Wed, 8 Apr 2009, Heikki Linnakangas wrote: Josh Berkus wrote: The other thing I was going to ask you about is using posix_fadvise as an alternative to O_DIRECT for the xlog. O_DIRECT is, AFAIK, linux-only, whereas there are direct write fadvise flags which work on multiple OSes. What flags are those? I don't see any posix_fadvise flags that would do anything like O_DIRECT. A good implementation of FADV_NOREUSE would work similarly to O_DIRECT, writing things out but not keeping them around the OS cache. (suggested long ago even: http://archives.postgresql.org//pgsql-hackers/2003-10/msg01492.php ) I know there's a problem with O_DIRECT not working on Solaris; see the following: http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and http://blogs.sun.com/roch/entry/zfs_and_directio http://docs.sun.com/app/docs/doc/816-5168/directio-3c I'm not sure whether using an fadvise call like FADV_NOREUSE will work any better though; it may be the case that only that directio call is sufficient on Solaris. A Solaris-specific code path that calls directio is what MySQL does here: http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_method I wanted to include such a patch in 8.4 but my one Solaris project got sidelined. Saying that O_DIRECT is linux-only doesn't seem right though. The same thread referenced above started by announcing O_DIRECT support on FreeBSD: http://archives.postgresql.org//pgsql-hackers/2003-10/msg01482.php and the above MySQL documentation supports that it works on FreeBSD, too. I've seen claims that it works fine on Mac OS X, too, although MySQL may not support that: http://labs.cybozu.co.jp/blog/kazuhoatwork/2009/02/using_o_direct_on_mac_os_x.php -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Closing some 8.4 open items
On Wed, 8 Apr 2009, Tom Lane wrote: If the kernel can't read-ahead a seqscan by itself, it's unlikely to be smart enough to be helped by posix_fadvise ... or at least so I would think. There's some interesting comments on this subject (and about what fadvise DONTNEED does) in the RRD research paper about managing their buffer cache: http://www.usenix.org/event/lisa07/tech/full_papers/plonka/plonka_html/index.html They suggest the Linux read-ahead is pretty aggressive by default, which might explain why I wasn't able to replicate any speed-up with the seqeuential scan patch on my system. (The original submission showed a significant speedup on Linux, but was from what sounded like a somewhat broken kernel--known buggy controller driver I seem to recall) I suspect we may need to find a platform where the default OS readahead is a slacker, *and* that pays attention to POSIX_FADV_SEQUENTIAL, in order to show any improvement from that patch. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Closing some 8.4 open items
Josh Berkus j...@agliodbs.com writes: Tom, change cardinality() for multi-dim arrays? Drop; there's no consensus that this should be changed Andrew pinged me on this. While there's no consensus that it should be changed, there's no consensus it shouldn't, either. And once we release it, we've set the way it operates in cement, so I'd like to get a consensus one way or the other. Yeah. I would like to change it; Peter evidently thinks it's good as-is. Where do we go from here? For my part, I'd like to know what things other than arrays collection_expression in the standard applies to. I think the most sensible course is to make cardinality(array[]) behave consistently with cardinality(other_stuff) when we get around to implementing it. There is no equivalent of multi-dimensional arrays in other kinds of collections, so I'm not seeing that there is any good guide 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] Closing some 8.4 open items
Tom, There is no equivalent of multi-dimensional arrays in other kinds of collections, so I'm not seeing that there is any good guide there. What else *does* SQL:2008 consider a collection? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Wednesday 08 April 2009 21:56:38 Tom Lane wrote: For my part, I'd like to know what things other than arrays collection_expression in the standard applies to. I think the most sensible course is to make cardinality(array[]) behave consistently with cardinality(other_stuff) when we get around to implementing it. There is no equivalent of multi-dimensional arrays in other kinds of collections, so I'm not seeing that there is any good guide there. Here is my thinking, and considering that that would basically involve a forward-looking design decision right now, I would support dropping the cardinality() function from 8.4 (if people agree that this is in fact the design decision to make). Collection types in SQL are arrays and multisets. Multisets are essentially arrays without ordering. Many people already use arrays like that, and I would find it interesting to support real multisets in the future. Currently, we don't support collections of collections, specifically arrays of arrays. We only have multidimensional arrays. Multidimensional arrays in PostgreSQL and arrays of arrays in SQL are actually pretty close in the interface they present, except that the subscript order is reversed. If you ignore that, the current cardinality() function gives you pretty much conforming behavior on nested arrays, at least for the first level. The question now is, if we want to move toward supporting multisets and arbitrary nested collections in the future, do we 1. Transform our view of a multidimensional array into nested arrays, and then extend that to allow multisets. (The implementation could stay quite the same; just mark some dimensions as this is a multiset.) And then perhaps address the subscript ordering issue by some hitherto unknown plan. - or - 2. Extend the system so you can have nested multidimensional arrays (e.g., a 4x4 array containing 3x3 arrays), and then extend that to also allow nesting with a separate multiset structure (possibly also multidimensional). I think this would probably make a mess out of the subscripting. - or - 3. SQL DIE DIE DIE!!! If you think (1) then the current implementation of cardinality() is correct, if you think (2) then Tom's proposed change is correct, if you think (3) the function should be removed. -- 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] Closing some 8.4 open items
Peter Eisentraut pete...@gmx.net writes: Here is my thinking, and considering that that would basically involve a forward-looking design decision right now, I would support dropping the cardinality() function from 8.4 (if people agree that this is in fact the design decision to make). At this point I'd support that too. It doesn't seem that getting cardinality() into 8.4 is important enough to risk making a decision that we'd regret later. And I think it's not hard to make the case that we might regret either of the other choices later, depending on where we go with arrays. 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] Closing some 8.4 open items
Tom Lane wrote: Greg Stark st...@enterprisedb.com writes: On Sun, Apr 5, 2009 at 6:54 PM, Robert Haas robertmh...@gmail.com wrote: I'm excited about some of them, but not to the point of not wanting to ship beta. ?So +1 for removing them as per your suggestions. I'm somewhat excited about posix_fadvise but my general feeling was that it was best to do nothing anyways. Yeah. One of the things in the back of my mind is that the planner is going to prefer bitmap scans anyway for anything that fetches more than a very few rows. So it's not clear that prefetching plain indexscans is going to buy enough to justify a whole lotta work or ugliness there. I'm content to throw this one on TODO. I am not inclined to add a TODO until we see actual value in doing it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Mon, Apr 06, 2009 at 10:51:22PM -0700, David Fetter wrote:
On Sun, Apr 05, 2009 at 05:57:46PM -0700, David Fetter wrote:
On Sun, Apr 05, 2009 at 08:55:07PM -0400, Tom Lane wrote:
David Fetter da...@fetter.org writes:
On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote:
The \df thing? That's something it'd be okay to revisit during
beta, IMHO.
OK, I'll work on this tomorrow :)
I think what we were lacking was consensus on what it should do, not
code ...
I was thinking I'd knock out a proposal or two.
Please find enclosed one way to handle it, this being prepending
WINDOW to the result types in \df.
Another way, patch coming tomorrow, would be to add a \dw and remove
the functions where pg_proc.iswindowing is true from \df.
Here's another way, adding \dw.
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b39466d..626eadc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -396,6 +396,9 @@ exec_command(const char *cmd,
case 'u':
success = describeRoles(pattern, show_verbose);
break;
+ case 'w':
+ success = describeWindowingFunctions(pattern,
show_system);
+ break;
case 'F': /* text search
subsystem */
switch (cmd[2])
{
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index cc88984..c718267 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -286,6 +286,9 @@ describeFunctions(const char *pattern, bool verbose, bool
showSystem)
appendPQExpBuffer(buf, AND n.nspname 'pg_catalog'\n
AND
n.nspname 'information_schema'\n);
+if (pset.sversion = 80400)
+ appendPQExpBuffer(buf, AND NOT p.proiswindow\n);
+
processSQLNamePattern(pset.db, buf, pattern, true, false,
n.nspname, p.proname,
NULL,
pg_catalog.pg_function_is_visible(p.oid));
@@ -3069,6 +3072,60 @@ listUserMappings(const char *pattern, bool verbose)
return true;
}
+bool
+describeWindowingFunctions(const char *pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (pset.sversion 80400)
+ {
+ fprintf(stderr, _(The server (version %d.%d) does not support
windowing functions.\n),
+ pset.sversion / 1, (pset.sversion / 100) %
100);
+ return true;
+ }
+
+ initPQExpBuffer(buf);
+
+ printfPQExpBuffer(buf,
+ SELECT n.nspname as \%s\,\n
+ p.proname as \%s\,\n
+
pg_catalog.pg_get_function_result(p.oid) as \%s\,\n
+
pg_catalog.pg_get_function_arguments(p.oid) as \%s\
+ \nFROM pg_catalog.pg_proc p
+ \n LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n
+ WHERE p.proiswindow\n,
+ gettext_noop(Schema),
+ gettext_noop(Name),
+ gettext_noop(Result data type),
+ gettext_noop(Argument data types));
+
+ if (!showSystem !pattern)
+ appendPQExpBuffer(buf, AND n.nspname 'pg_catalog'\n
+ AND
n.nspname 'information_schema'\n);
+
+ processSQLNamePattern(pset.db, buf, pattern, true, false,
+ n.nspname, p.proname,
NULL,
+
pg_catalog.pg_function_is_visible(p.oid));
+
+ appendPQExpBuffer(buf, ORDER BY 1, 2, 4;);
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _(List of windowing functions);
+ myopt.translate_header = true;
+
+ printQuery(res, myopt, pset.queryFout, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
/*
* printACLColumn
*
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index
Re: [HACKERS] Closing some 8.4 open items
Tom, finishing posix_fadvise patch Push to TODO So has fadvise been completely dropped from 8.4, or only partially? change psql's \df output for window functions? Drop; there's no consensus that this should be changed Also, Fetter is currently working on a \dw for 8.5. Polymorphic types vs. domains Push to TODO Agreed. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkus j...@agliodbs.com wrote: Tom, finishing posix_fadvise patch Push to TODO So has fadvise been completely dropped from 8.4, or only partially? Bitmap scans will support it, but index scans will not. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Robert Haas robertmh...@gmail.com writes: On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkus j...@agliodbs.com wrote: So has fadvise been completely dropped from 8.4, or only partially? Bitmap scans will support it, but index scans will not. And please note that we think bitmap scans are the larger part of the win anyway. What's left undone there is some marginal mopup. 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] Closing some 8.4 open items
On Tue, Apr 07, 2009 at 07:42:51PM -0700, Josh Berkus wrote: Tom, finishing posix_fadvise patch Push to TODO So has fadvise been completely dropped from 8.4, or only partially? change psql's \df output for window functions? Drop; there's no consensus that this should be changed Also, Fetter is currently working on a \dw for 8.5. I sent it, targeted for 8.4 :) Windowing functions are new in 8.4, so I'm thinking it should go into the front-end tools, too. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Sun, Apr 05, 2009 at 05:57:46PM -0700, David Fetter wrote: On Sun, Apr 05, 2009 at 08:55:07PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote: The \df thing? That's something it'd be okay to revisit during beta, IMHO. OK, I'll work on this tomorrow :) I think what we were lacking was consensus on what it should do, not code ... I was thinking I'd knock out a proposal or two. Please find enclosed one way to handle it, this being prepending WINDOW to the result types in \df. Another way, patch coming tomorrow, would be to add a \dw and remove the functions where pg_proc.iswindowing is true from \df. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate *** describe.c.orig 2009-04-06 19:47:44.0 -0700 --- describe.c 2009-04-06 20:02:25.0 -0700 *** *** 202,207 --- 202,208 if (pset.sversion = 80400) appendPQExpBuffer(buf, + CASE WHEN p.proiswindow THEN 'WINDOW ' END ||\n pg_catalog.pg_get_function_result(p.oid) as \%s\,\n pg_catalog.pg_get_function_arguments(p.oid) as \%s\, gettext_noop(Result data type), -- 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] Closing some 8.4 open items
Tom Lane wrote: If there are no objections, I'm going to remove the following items from the list at http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items change cardinality() for multi-dim arrays? Drop; there's no consensus that this should be changed I don't think we should let this go quite so easily, as this is a new function, so the bias should be to getting it right rather than don't change it. The supplied functionality is not only surprising, but easily obtained by an existing function. ISTM if we're supplying a new function it should have new functionality. 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] Closing some 8.4 open items
On Sun, Apr 5, 2009 at 7:45 AM, Andrew Dunstan and...@dunslane.net wrote: Tom Lane wrote: If there are no objections, I'm going to remove the following items from the list at http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items change cardinality() for multi-dim arrays? Drop; there's no consensus that this should be changed I don't think we should let this go quite so easily, as this is a new function, so the bias should be to getting it right rather than don't change it. I think it is right already, but the point is debatable. The supplied functionality is not only surprising, but easily obtained by an existing function. ISTM if we're supplying a new function it should have new functionality. Well, it's a compatibility function... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Robert Haas wrote: On Sun, Apr 5, 2009 at 7:45 AM, Andrew Dunstan and...@dunslane.net wrote: Tom Lane wrote: If there are no objections, I'm going to remove the following items from the list at http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items change cardinality() for multi-dim arrays? Drop; there's no consensus that this should be changed I don't think we should let this go quite so easily, as this is a new function, so the bias should be to getting it right rather than don't change it. I think it is right already, but the point is debatable. The supplied functionality is not only surprising, but easily obtained by an existing function. ISTM if we're supplying a new function it should have new functionality. Well, it's a compatibility function... compatible with what? The other thing that frankly bothers me is that we appear to have acquired this function by a curious process which involved no proposal or discussion that I have discovered. If there had been proper and adequate discussion before the item was committed I wouldn't be making a fuss now, whether or not I agreed with the 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] Closing some 8.4 open items
On Sun, Apr 05, 2009 at 07:55:44AM -0400, Robert Haas wrote: On Sun, Apr 5, 2009 at 7:45 AM, Andrew Dunstan and...@dunslane.net wrote: Tom Lane wrote: If there are no objections, I'm going to remove the following items from the list at http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items change cardinality() for multi-dim arrays? Drop; there's no consensus that this should be changed I don't think we should let this go quite so easily, as this is a new function, so the bias should be to getting it right rather than don't change it. I think it is right already, but the point is debatable. The supplied functionality is not only surprising, but easily obtained by an existing function. ISTM if we're supplying a new function it should have new functionality. Well, it's a compatibility function... It's actually in SQL:2008. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Andrew Dunstan and...@dunslane.net writes: Robert Haas wrote: Well, it's a compatibility function... compatible with what? It's required by the SQL standard. The other thing that frankly bothers me is that we appear to have acquired this function by a curious process which involved no proposal or discussion that I have discovered. If there had been proper and adequate discussion before the item was committed I wouldn't be making a fuss now, whether or not I agreed with the result. I think Peter put it in under the assumption that meeting spec-required syntax would always pass muster. It is however fair to question whether he made the right extrapolation of the spec's definition to cases that are not in the spec. Personally I am in favor of changing it to give the total number of array elements, on the grounds that (1) that's as defensible a reading of the spec as the other and (2) it would add actual new functionality rather than being only a relabeling of array_length. I will leave that item on the Open Items list. I take it no one's excited about the others? 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] Closing some 8.4 open items
On Sun, Apr 5, 2009 at 12:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: Robert Haas wrote: Well, it's a compatibility function... compatible with what? It's required by the SQL standard. The other thing that frankly bothers me is that we appear to have acquired this function by a curious process which involved no proposal or discussion that I have discovered. If there had been proper and adequate discussion before the item was committed I wouldn't be making a fuss now, whether or not I agreed with the result. I think Peter put it in under the assumption that meeting spec-required syntax would always pass muster. It is however fair to question whether he made the right extrapolation of the spec's definition to cases that are not in the spec. Personally I am in favor of changing it to give the total number of array elements, on the grounds that (1) that's as defensible a reading of the spec as the other and (2) it would add actual new functionality rather than being only a relabeling of array_length. I will leave that item on the Open Items list. I take it no one's excited about the others? I'm excited about some of them, but not to the point of not wanting to ship beta. So +1 for removing them as per your suggestions. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Sun, Apr 05, 2009 at 12:21:41PM -0400, Tom Lane wrote: I will leave that item on the Open Items list. I take it no one's excited about the others? When the windowing functions become a pain point, let's revisit :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
David Fetter da...@fetter.org writes: On Sun, Apr 05, 2009 at 12:21:41PM -0400, Tom Lane wrote: I will leave that item on the Open Items list. I take it no one's excited about the others? When the windowing functions become a pain point, let's revisit :) The \df thing? That's something it'd be okay to revisit during beta, IMHO. The things I'd really like to get right before beta are the ones that are going to require an initdb to change. Like, say, the cardinality() issue ... 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] Closing some 8.4 open items
On Sun, Apr 5, 2009 at 6:54 PM, Robert Haas robertmh...@gmail.com wrote: I'm excited about some of them, but not to the point of not wanting to ship beta. So +1 for removing them as per your suggestions. I'm somewhat excited about posix_fadvise but my general feeling was that it was best to do nothing anyways. I don't know how to test these questions though because they depend a lot on workload and pgbench or synthetic queries which stress prefetching aren't especially good at measuring how fast pages get evicted. As far as reimplementing regular index scans -- I don't currently see any way to do it in a way that would satisfy your demands that wouldn't be insanely complex. Hopefully I'm missing something obvious and if someone sees what I would be happy to go ahead and implement something. But everything I've tried has turned into a monster. -- 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] Closing some 8.4 open items
Greg Stark st...@enterprisedb.com writes: On Sun, Apr 5, 2009 at 6:54 PM, Robert Haas robertmh...@gmail.com wrote: I'm excited about some of them, but not to the point of not wanting to ship beta. So +1 for removing them as per your suggestions. I'm somewhat excited about posix_fadvise but my general feeling was that it was best to do nothing anyways. Yeah. One of the things in the back of my mind is that the planner is going to prefer bitmap scans anyway for anything that fetches more than a very few rows. So it's not clear that prefetching plain indexscans is going to buy enough to justify a whole lotta work or ugliness there. I'm content to throw this one on TODO. 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] Closing some 8.4 open items
On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Sun, Apr 05, 2009 at 12:21:41PM -0400, Tom Lane wrote: I will leave that item on the Open Items list. I take it no one's excited about the others? When the windowing functions become a pain point, let's revisit :) The \df thing? That's something it'd be okay to revisit during beta, IMHO. OK, I'll work on this tomorrow :) Cheers, David. The things I'd really like to get right before beta are the ones that are going to require an initdb to change. Like, say, the cardinality() issue ... 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 -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
David Fetter da...@fetter.org writes: On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote: The \df thing? That's something it'd be okay to revisit during beta, IMHO. OK, I'll work on this tomorrow :) I think what we were lacking was consensus on what it should do, not 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] Closing some 8.4 open items
On Sun, Apr 05, 2009 at 08:55:07PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote: The \df thing? That's something it'd be okay to revisit during beta, IMHO. OK, I'll work on this tomorrow :) I think what we were lacking was consensus on what it should do, not code ... I was thinking I'd knock out a proposal or two. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
