Re: [GENERAL] Division by zero
Sam Mason writes: > I'm not sure what you're responding to here, but I'm pretty sure the OP > wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT. Yeah --- STRICT will prevent inlining. The function's expression actually is strict, but the planner isn't smart enough about NULLIF to realize that, so it doesn't inline. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
2009/8/2 Sam Mason : > On Sun, Aug 02, 2009 at 06:03:11PM +0200, Pavel Stehule wrote: >> 2009/8/2 Sam Mason : >> > On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote: >> >> 2009/8/2 Sam Mason : >> >> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote: >> >> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe >> >> >> bug >> >> > >> >> > Not in any tests I've done. >> >> >> >> I did it - and in this case immutable is wrong and strict not. >> > >> > I'm not sure what you're responding to here, but I'm pretty sure the OP >> > wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT. >> >> I checked if function was inlined or not. When I mark function as >> strict then it was inlined. When I mark function as IMMUTABLE then it >> wasn't inlined. That's all - you can check it too. > > I will be checking different things, please say what you're testing. > look on thread "IMMUTABLE break inlining simple SQL functions." Pavel > Different things are inlined in different places, its the different > places things get inlined that cause the optimizer to do different > things. > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Sun, Aug 02, 2009 at 06:03:11PM +0200, Pavel Stehule wrote: > 2009/8/2 Sam Mason : > > On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote: > >> 2009/8/2 Sam Mason : > >> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote: > >> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe > >> >> bug > >> > > >> > Not in any tests I've done. > >> > >> I did it - and in this case immutable is wrong and strict not. > > > > I'm not sure what you're responding to here, but I'm pretty sure the OP > > wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT. > > I checked if function was inlined or not. When I mark function as > strict then it was inlined. When I mark function as IMMUTABLE then it > wasn't inlined. That's all - you can check it too. I will be checking different things, please say what you're testing. Different things are inlined in different places, its the different places things get inlined that cause the optimizer to do different things. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
2009/8/2 Sam Mason : > On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote: >> 2009/8/2 Sam Mason : >> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote: >> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug >> > >> > Not in any tests I've done. >> >> I did it - and in this case immutable is wrong and strict not. > > I'm not sure what you're responding to here, but I'm pretty sure the OP > wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT. > I checked if function was inlined or not. When I mark function as strict then it was inlined. When I mark function as IMMUTABLE then it wasn't inlined. That's all - you can check it too. >> It's an >> new for me, because I used rules that are well only for plpgsql or C >> language. What I see now, the rules for sql are totally different. > > SQL language functions are going to be different from anything else > because the can be. The planner has intimate knowledge of SQL and hence > will try hard to expand these out and optimize them (in a similar way to > how it handles views). > > The semantics of these keywords shouldn't change between SQL, plpgsql > and C functions though, it's just that the optimizer can look inside an > SQL function and not other functions. > > Maybe if you can say what you did and what result you got back? > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote: > 2009/8/2 Sam Mason : > > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote: > >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug > > > > Not in any tests I've done. > > I did it - and in this case immutable is wrong and strict not. I'm not sure what you're responding to here, but I'm pretty sure the OP wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT. > It's an > new for me, because I used rules that are well only for plpgsql or C > language. What I see now, the rules for sql are totally different. SQL language functions are going to be different from anything else because the can be. The planner has intimate knowledge of SQL and hence will try hard to expand these out and optimize them (in a similar way to how it handles views). The semantics of these keywords shouldn't change between SQL, plpgsql and C functions though, it's just that the optimizer can look inside an SQL function and not other functions. Maybe if you can say what you did and what result you got back? -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
2009/8/2 Sam Mason : > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote: >> 2009/8/2 Sam Mason : >> > On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists >> > wrote: >> >> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer >> >> AS 'SELECT $1 / NULLIF($2,0);' >> >> LANGUAGE SQL >> >> IMMUTABLE >> >> RETURNS NULL ON NULL INPUT; >> > >> > If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used >> > to think of it as just a "hint" to the planner as to its behavior, >> > but it turns out that it's interpreted much more strongly by PG. The >> > interpretation means that the function doesn't end up getting be inlined >> > where I'd expect it to be and hence the optimizer doesn't get as much >> > freedom to rewrite your queries as you may want. >> >> I thing, it's not true - RETURNS NULL ON NULL INPUT is equal to STRICT >> flag, and it means, don't run function, when any param is null. > > Yes, this is how PG interprets it. > >> For >> optimalisator it means only one - when any parameter is constant NULL, >> then function evaluation should be replaced by NULL. But not too much >> often optimalizer should detect this case, so this is shortcut for >> evaluator. This flag doesn't change inlining. > > No, not unless things have changed since this discussion: > > http://archives.postgresql.org/message-id/20090604090045.gr5...@samason.me.uk > >> > Admittedly it's going to be less of an issue with division that other >> > operators, but it's worth bearing in mind. The "IMMUTABLE" options is a >> > good one to specify though, keep that! >> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug > > Not in any tests I've done. I did it - and in this case immutable is wrong and strict not. It's an new for me, because I used rules that are well only for plpgsql or C language. What I see now, the rules for sql are totally different. Pavel > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote: > 2009/8/2 Sam Mason : > > On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists > > wrote: > >> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer > >> AS 'SELECT $1 / NULLIF($2,0);' > >> LANGUAGE SQL > >> IMMUTABLE > >> RETURNS NULL ON NULL INPUT; > > > > If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used > > to think of it as just a "hint" to the planner as to its behavior, > > but it turns out that it's interpreted much more strongly by PG. The > > interpretation means that the function doesn't end up getting be inlined > > where I'd expect it to be and hence the optimizer doesn't get as much > > freedom to rewrite your queries as you may want. > > I thing, it's not true - RETURNS NULL ON NULL INPUT is equal to STRICT > flag, and it means, don't run function, when any param is null. Yes, this is how PG interprets it. > For > optimalisator it means only one - when any parameter is constant NULL, > then function evaluation should be replaced by NULL. But not too much > often optimalizer should detect this case, so this is shortcut for > evaluator. This flag doesn't change inlining. No, not unless things have changed since this discussion: http://archives.postgresql.org/message-id/20090604090045.gr5...@samason.me.uk > > Admittedly it's going to be less of an issue with division that other > > operators, but it's worth bearing in mind. The "IMMUTABLE" options is a > > good one to specify though, keep that! > > There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug Not in any tests I've done. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
2009/8/2 Sam Mason : > On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote: >> -- This routine creates an alterantive division operator >> -- that doesn't throw an error on a divide by zero >> -- but rather returns null >> >> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer >> AS 'SELECT $1 / NULLIF($2,0);' >> LANGUAGE SQL >> IMMUTABLE >> RETURNS NULL ON NULL INPUT; > > If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used > to think of it as just a "hint" to the planner as to its behavior, > but it turns out that it's interpreted much more strongly by PG. The > interpretation means that the function doesn't end up getting be inlined > where I'd expect it to be and hence the optimizer doesn't get as much > freedom to rewrite your queries as you may want. > I thing, it's not true - RETURNS NULL ON NULL INPUT is equal to STRICT flag, and it means, don't run function, when any param is null. For optimalisator it means only one - when any parameter is constant NULL, then function evaluation should be replaced by NULL. But not too much often optimalizer should detect this case, so this is shortcut for evaluator. This flag doesn't change inlining. > Admittedly it's going to be less of an issue with division that other > operators, but it's worth bearing in mind. The "IMMUTABLE" options is a > good one to specify though, keep that! > There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote: > -- This routine creates an alterantive division operator > -- that doesn't throw an error on a divide by zero > -- but rather returns null > > CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer > AS 'SELECT $1 / NULLIF($2,0);' > LANGUAGE SQL > IMMUTABLE > RETURNS NULL ON NULL INPUT; If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used to think of it as just a "hint" to the planner as to its behavior, but it turns out that it's interpreted much more strongly by PG. The interpretation means that the function doesn't end up getting be inlined where I'd expect it to be and hence the optimizer doesn't get as much freedom to rewrite your queries as you may want. Admittedly it's going to be less of an issue with division that other operators, but it's worth bearing in mind. The "IMMUTABLE" options is a good one to specify though, keep that! -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On 31 Jul 2009, at 19:49, Jeff Davis wrote: Yes -- you can do what you want anyway. If you want it to be more readable, you can redefine the division operator yourself -- it's just a function and operator that happens to be named "/" (although that might not be wise). Maybe you can call it "//" to avoid confusion with people used to the SQL standard behavior. Great Idea, that's a very powerful feature, being able to redefine an operator. I did that as you suggest and it seems to work fine. My users access postgres through a web app layer so I modified the application to replace any cases of / with // in calculations as they're created. In case there are any improvements to suggest and for the benefit of anyone else who wants to swallow division by zero, the function and operator are below. I only use integer and double precision numbers. I assume that using the more general 'numeric' rather than all combinations of these would have a performance penalty? Regards Oliver Kohll oli...@gtwm.co.uk / 0845 456 1810 / 07814 828608 www.gtwm.co.uk - company www.gtportalbase.com - product --- -- This routine creates an alterantive division operator -- that doesn't throw an error on a divide by zero -- but rather returns null CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer AS 'SELECT $1 / NULLIF($2,0);' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION gtpb_divide(double precision, double precision) RETURNS double precision AS 'SELECT $1 / NULLIF($2,0);' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION gtpb_divide(double precision, integer) RETURNS double precision AS 'SELECT $1 / NULLIF($2,0);' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION gtpb_divide(integer, double precision) RETURNS double precision AS 'SELECT $1 / NULLIF($2,0);' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; CREATE OPERATOR // ( PROCEDURE = gtpb_divide, LEFTARG = integer, RIGHTARG = integer ); CREATE OPERATOR // ( PROCEDURE = gtpb_divide, LEFTARG = double precision, RIGHTARG = double precision ); CREATE OPERATOR // ( PROCEDURE = gtpb_divide, LEFTARG = double precision, RIGHTARG = integer ); CREATE OPERATOR // ( PROCEDURE = gtpb_divide, LEFTARG = integer, RIGHTARG = double precision ); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Fri, 31 Jul 2009 11:49:47 -0700 Jeff Davis wrote:
> "Find all store locations which have not achieved an average sale price
> of $100."
>
> SELECT store_name FROM sales WHERE totalsales/nsales < 100;
>
> The person running that query might be missing stores with no sales at
> all, and they might prefer an ERROR to the silent omission of results.
Not that I'm suggesting that Postgres mimic MySQL's behavior in this
case, but just for fun I tried the following SQL in both PG 8.3 and MySQL 5.0:
-
CREATE TEMPORARY TABLE sales(
store_name varchar( 128 ) ,
totalsales float NOT NULL DEFAULT 0.0,
nsales int NOT NULL DEFAULT 0) ;
INSERT INTO sales(store_name, totalsales, nsales)
VALUES ('store A', 1000.0, 100), ('store B', 0, 0), ('store C', 51.1, 2);
SELECT store_name FROM sales WHERE totalsales / nsales < 100 ;
-
MySQL gave results:
++
| store_name |
++
| store A|
| store C|
++
since divide-by-zero errors in MySQL produce NULL values, while Postgres gave:
"ERROR: division by zero".
I am also no expert on the SQL Standard; there was a Stack Overflow discussion
relating to this topic which didn't produce any useful answers:
http://stackoverflow.com/questions/1140860/treatment-of-error-values-in-the-sql-standard
To the original poster, I'd suggest just using NULLIF(column, 0) on
your denominators to
achieve an effect similar to MySQL's behavior.
Cheers,
Josh
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Fri, 2009-07-31 at 18:27 +0100, Oliver Kohll - Mailing Lists wrote: > Divide by zero errors have come up a couple of times on this list > (once raised by me). I wonder if I could propose a feature for > discussion. Could the result of a division by zero be treated as > infinity or null, rather than raising an error? Floating point types > already have the concept of infinity. The SQL spec seems to require that an exception be raised in 6.26 General Rules 4. (Disclaimer: I'm not an expert on the SQL standard. This was based on a quick text search of the document). > I'd have thought that there's no reason why a /0 in one row > necessarily has to be fatal for the whole view. In many cases, you can > imagine that returning infinity makes more sense. Strictly, I suppose, > 1/0 should return infinity, 0/0 null and -1/0 negative infinity. > Alternatively, all could return NaN. At least there could be a > configuration option to turn on this behaviour. For every situation in which coming up with some kind of result "makes sense", there is another situation in which the result would be wrong or hides some subtle logical flaw in the query. For instance: "Find all store locations which have not achieved an average sale price of $100." SELECT store_name FROM sales WHERE totalsales/nsales < 100; The person running that query might be missing stores with no sales at all, and they might prefer an ERROR to the silent omission of results. > The concern stems from the fact that when a divide by zero occurs in a > view, no rows at all are returned, just the error message. This makes > it very difficult to work out where the problem value is, compared to > other tools like spreadsheets, which return a cell error. A view can > be very fragile. Further, the Postgres error doesn't give any details > of the field and of course can't point to the row, it just says > ERROR: division by zero I agree that it may be useful to show you the values that are causing the expression to be throwing an error. However, we shouldn't say that it's a problem with the field itself -- it may be a problem with the query. > I know you can use CASE and NULLIF but if you have complex > calculations, that makes them a lot less readable. Yes -- you can do what you want anyway. If you want it to be more readable, you can redefine the division operator yourself -- it's just a function and operator that happens to be named "/" (although that might not be wise). Maybe you can call it "//" to avoid confusion with people used to the SQL standard behavior. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Division by zero
Hello, Divide by zero errors have come up a couple of times on this list (once raised by me). I wonder if I could propose a feature for discussion. Could the result of a division by zero be treated as infinity or null, rather than raising an error? Floating point types already have the concept of infinity. I'd have thought that there's no reason why a /0 in one row necessarily has to be fatal for the whole view. In many cases, you can imagine that returning infinity makes more sense. Strictly, I suppose, 1/0 should return infinity, 0/0 null and -1/0 negative infinity. Alternatively, all could return NaN. At least there could be a configuration option to turn on this behaviour. The concern stems from the fact that when a divide by zero occurs in a view, no rows at all are returned, just the error message. This makes it very difficult to work out where the problem value is, compared to other tools like spreadsheets, which return a cell error. A view can be very fragile. Further, the Postgres error doesn't give any details of the field and of course can't point to the row, it just says ERROR: division by zero There may well be good reasons for not treating this. I've come across comments such as 'I think everybody would agree that this would be a bad thing to do!' but remain to be convinced. I know you can use CASE and NULLIF but if you have complex calculations, that makes them a lot less readable. Regards Oliver Kohll oli...@gtwm.co.uk / 0845 456 1810 / 07814 828608 www.gtwm.co.uk - company www.gtportalbase.com - product
Re: [GENERAL] Division by zero
On 4 Jun 2009, at 13:11, Sam Mason wrote: You need to take care of only one case here: denominator == 0; rest of the cases will be handled sanely by the database. CASE WHEN packet_size = 0 THEN null ELSE cost/packet_size END as unit_cost Yes; or even shorter: cost/nullif(packet_size,0) AS unit_cost Thanks Sam and others. nullif is a good one to remember. However my problem is I want to be able to deal with an arbitrary calculation a user may create. In the light of a new day I realise this is obviously not trivial and would entail reasonably complex parsing. You'd have to find elements that could cause an error (division, some aggregates) and insert in the correct place nullif or CASE, taking care of bracket matching for starters - a messy workaround to the problem. I might look into functions if that's the only reasonable way of catching exceptions. Oliver
Re: [GENERAL] Division by zero
On Thu, Jun 04, 2009 at 11:17:39AM -0400, Tom Lane wrote: > Sam Mason writes: > > I think that with 8.3 at least I'm going to carry on putting > > IMMUTABLE on where I think it should be. Consider: > > there are corner cases where it's useful to have the function > marked correctly rather than sloppily. I think I originally noticed it when I'd written an "implies" operator (i.e. (NOT $1) OR $2, as I don't trust myself to remember the logic) and queries were running slowly until I noticed that a view was using it deep inside itself somewhere. Seems like views would trigger this behavior pretty reliably. Doesn't seem like it needs fixing too urgently though. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
Sam Mason writes: > Hum, I think that with 8.3 at least I'm going to carry on putting > IMMUTABLE on where I think it should be. Consider: > CREATE FUNCTION fn_i(INTEGER) RETURNS BOOLEAN LANGUAGE SQL IMMUTABLE AS $$ > SELECT $1 < 1000 $$; > CREATE FUNCTION fn_v(INTEGER) RETURNS BOOLEAN LANGUAGE SQL AS $$ > SELECT $1 < 1000 $$; > and then doing: > SELECT * FROM ( > SELECT fn_i(num), fn_v(num) > FROM bigtable) x > WHERE fn_i; > I get very different plans out if I replace "WHERE fn_i" with "WHERE > fn_v". I read this as it not inlining where I'd expect it to be, or am > I missing something else? Huh, interesting example. I don't have time to trace through it right now, but I think what is happening is that the decision about whether or not the sub-select can be flattened is being made before the inlining of the SQL functions in the sub-select happens. So at that point the sub-select qualifier expression still looks volatile and the planner chickens out of flattening it. The functions do both get inline'd eventually, as you can see in EXPLAIN VERBOSE output ... but it's too late to make any real difference in the plan shape. So yeah, there are corner cases where it's useful to have the function marked correctly rather than sloppily. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Thu, Jun 04, 2009 at 10:34:31AM -0400, Tom Lane wrote: > create function foo(int) returns int as > $$ select coalesce($1, 42); $$ language sql strict; > > Because this function is declared STRICT, it must return null on null > input. However, the contained expression would not act that way --- > it would in fact return 42 for null input. Therefore inlining would > change the behavior, and therefore we don't inline. Bah, not sure what I was thinking--that's kind of obvious isn't it! I think I was thinking about every language apart from SQL, but they can't be inlined and hence it would never apply to them. > The same sorts of considerations arise for marking the function as > less volatile than the contained expression really is. In this case > the "behavioral change" has to do with what later phases of the planner > will think they can do with the function or expression. The bottom line > is the same though: easier to leave off the marking. Hum, I think that with 8.3 at least I'm going to carry on putting IMMUTABLE on where I think it should be. Consider: CREATE FUNCTION fn_i(INTEGER) RETURNS BOOLEAN LANGUAGE SQL IMMUTABLE AS $$ SELECT $1 < 1000 $$; CREATE FUNCTION fn_v(INTEGER) RETURNS BOOLEAN LANGUAGE SQL AS $$ SELECT $1 < 1000 $$; and then doing: SELECT * FROM ( SELECT fn_i(num), fn_v(num) FROM bigtable) x WHERE fn_i; I get very different plans out if I replace "WHERE fn_i" with "WHERE fn_v". I read this as it not inlining where I'd expect it to be, or am I missing something else? -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
Sam Mason writes: > On Thu, Jun 04, 2009 at 09:48:17AM -0400, Tom Lane wrote: >> Actually, if you're intending that a SQL function be inline-able then >> it's best *not* to mark it as IMMUTABLE (nor STRICT either). If the >> marking matches the behavior of the contained expression then it >> doesn't matter, but if the marking is stricter than the expression >> it will prevent inlining. > Why would strictness alter planning? Because it alters behavior. For example, consider create function foo(int) returns int as $$ select coalesce($1, 42); $$ language sql strict; Because this function is declared STRICT, it must return null on null input. However, the contained expression would not act that way --- it would in fact return 42 for null input. Therefore inlining would change the behavior, and therefore we don't inline. The converse case (function not marked strict, but expression really is) doesn't pose a hazard. So it's easier to just not mark the function strict than to think carefully about whether (a) the contained expression is strict and (b) the planner will be able to prove that. The same sorts of considerations arise for marking the function as less volatile than the contained expression really is. In this case the "behavioral change" has to do with what later phases of the planner will think they can do with the function or expression. The bottom line is the same though: easier to leave off the marking. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Thu, Jun 04, 2009 at 09:48:17AM -0400, Tom Lane wrote: > Sam Mason writes: > > If it's an SQL function and marked as IMMUTABLE it should (I believe > > anyway) get inlined somewhere along the line and take no more overhead > > than writing it out in full. > > Actually, if you're intending that a SQL function be inline-able then > it's best *not* to mark it as IMMUTABLE (nor STRICT either). If the > marking matches the behavior of the contained expression then it > doesn't matter, but if the marking is stricter than the expression > it will prevent inlining. Why would strictness alter planning? I was under the impression that it only affected evaluation, i.e. it doesn't need to call the code if any parameter is NULL. That said, I was just saying what I've normally done. I've just checked the docs[1] and they may need rewording: For best optimization results, you should label your functions with the strictest volatility category that is valid for them. > Rather than think hard, I usually just don't > annotate the SQL function at all. Hum, I only tend to annotate small functions where I can easily verify what they're doing. I think I picked up this practice when PG wasn't inlining things I thought it should be and saying it was immutable made it work. Not sure when I got this habit, if I could drop it now that would be nice. -- Sam http://samason.me.uk/ [1] http://www.postgresql.org/docs/current/static/xfunc-volatility.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
Sam Mason writes: > If it's an SQL function and marked as IMMUTABLE it should (I believe > anyway) get inlined somewhere along the line and take no more overhead > than writing it out in full. Actually, if you're intending that a SQL function be inline-able then it's best *not* to mark it as IMMUTABLE (nor STRICT either). If the marking matches the behavior of the contained expression then it doesn't matter, but if the marking is stricter than the expression it will prevent inlining. Rather than think hard, I usually just don't annotate the SQL function at all. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Thu, Jun 04, 2009 at 12:12:09AM +0530, Gurjeet Singh wrote: > On Wed, Jun 3, 2009 at 9:18 PM, Oliver Kohll wrote: > > SELECT cost / pack_size AS unit_cost from products; > > > > Either variable could be null or zero. > > > > I don't want to write new functions, I'd rather keep it in plain SQL. > > Putting that in a function is definitely going to be expensive.. If it's an SQL function and marked as IMMUTABLE it should (I believe anyway) get inlined somewhere along the line and take no more overhead than writing it out in full. > You need to take care of only one case here: denominator == 0; rest of the > cases will be handled sanely by the database. > > CASE WHEN packet_size = 0 THEN null ELSE cost/packet_size END as unit_cost Yes; or even shorter: cost/nullif(packet_size,0) AS unit_cost -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Division by zero
On Wed, Jun 3, 2009 at 9:18 PM, Oliver Kohll - Mailing Lists <
oliver.li...@gtwm.co.uk> wrote:
> Hello,
>
> We have a system that allows users to create views containing calculations
> but divisions by zero are commonly a problem.
>
> An simple example calculation in SQL would be
>
> SELECT cost / pack_size AS unit_cost from products;
>
> Either variable could be null or zero.
>
> I don't think there's a way of returning null or infinity for divisions by
> zero, rather than causing an error but I'd just like to check - and put in a
> vote for that functionality!
>
> If not, I will have to get the system to check for any numeric fields in
> user input calculations and rewrite them similar to
>
> CASE WHEN cost IS NULL THEN null
> WHEN pack_size IS NULL THEN null
> WHEN cost = 0 THEN null
> WHEN pack_size = 0 THEN null
> ELSE cost / pack_size
> AS unit_cost
>
> I don't want to write new functions, I'd rather keep it in plain SQL.
>
>
Putting that in a function is definitely going to be expensive..
You need to take care of only one case here: denominator == 0; rest of the
cases will be handled sanely by the database.
CASE WHEN packet_size = 0 THEN null ELSE cost/packet_size END as unit_cost
Best regards,
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device
[GENERAL] Division by zero
Hello, We have a system that allows users to create views containing calculations but divisions by zero are commonly a problem. An simple example calculation in SQL would be SELECT cost / pack_size AS unit_cost from products; Either variable could be null or zero. I don't think there's a way of returning null or infinity for divisions by zero, rather than causing an error but I'd just like to check - and put in a vote for that functionality! If not, I will have to get the system to check for any numeric fields in user input calculations and rewrite them similar to CASE WHEN cost IS NULL THEN null WHEN pack_size IS NULL THEN null WHEN cost = 0 THEN null WHEN pack_size = 0 THEN null ELSE cost / pack_size AS unit_cost I don't want to write new functions, I'd rather keep it in plain SQL. Best regards Oliver Kohll oli...@gtwm.co.uk / 0845 456 1810 / 07814 828608 www.gtwm.co.uk - company www.gtportalbase.com - product -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [professionel] Re: [GENERAL] division by zero error in a request
Bernard Grosperrin wrote: Oisin SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) / (sold_parts_amount_dly + sold_labor_amount_dly) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)>0 Thanks for your answer. The real request would be something like this: SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) - cost_amount_dly / (sold_parts_amount_dly + sold_labor_amount_dly) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)>0 My problem is that in fact I want to SUM those amounts, with a GROUP BY per location. But by doing so, any location where one row has where = 0 is eliminated. So, is there a way to SUM inside a subset returned by SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) - cost_amount_dly / (sold_parts_amount_dly + sold_labor_amount_dly) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)>0 Should I select into a temporary table, then SUM that table? Thanks, Bernard Please always copy the list on your responses as others may be interested. Something like this should work. I am not sure how inefficient it is. select location_id, SUM((sold_parts_amount_dly + sold_labor_amount_dly) - cost_amount_dly) / SUM(sold_parts_amount_dly + sold_labor_amount_dly) from sales s1 where (select SUM(sold_parts_amount_dly + sold_labor_amount_dly) from sales s2 where s2.location_id = s1.location_id) > 0 group by location_id; smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] division by zero error in a request
Bernard Grosperrin wrote: I wants to make a view giving me some statistics. I am not sure to understand why something like this SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) / (sold_parts_amount_dly + sold_labor_amount_dly) from sales give me a division by zero error? If that is not the way to go, should I write a function that I would call instead? Thanks, Bernard ---(end of broadcast)--- TIP 6: explain analyze is your friend If (sold_parts_amount_dly + sold_labor_amount_dly) equals zero you will get an error as you cannot divide by zero. Also in every case where (sold_parts_amount_dly + sold_labor_amount_dly) is not zero wont the answer be 1 as (sold_parts_amount_dly + sold_labor_amount_dly)/(sold_parts_amount_dly + sold_labor_amount_dly) is always 1?? To try and find the zero you could do the following: select count(*) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)=0; and if that is not a staggering amount of rows select * from sales where (sold_parts_amount_dly + sold_labor_amount_dly)=0; and try and identify why this is happening if it is not expected? or if you want to ignore rows where (sold_parts_amount_dly + sold_labor_amount_dly)=0; then SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) / (sold_parts_amount_dly + sold_labor_amount_dly) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)>0 Oisin smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] division by zero error in a request
I wants to make a view giving me some statistics. I am not sure to understand why something like this SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) / (sold_parts_amount_dly + sold_labor_amount_dly) from sales give me a division by zero error? If that is not the way to go, should I write a function that I would call instead? Thanks, Bernard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] division by zero error in a request
Bernard Grosperrin wrote: > I wants to make a view giving me some statistics. > > I am not sure to understand why something like this > > SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) / > (sold_parts_amount_dly + sold_labor_amount_dly) from sales > > give me a division by zero error? > > If that is not the way to go, should I write a function that I would call > instead? > > Thanks, > Bernard Hi Bernard, In mathematics, you cannot divide by zero. So 4/0 is not possible for example. In your SELECT query, sold_parts_amount_dly and sold_labor_amunt_dly are zero in some cases, giving you the division by zero error. You could solve this by using CASE. http://www.postgresql.org/docs/8.1/static/functions-conditional.html Or you could create a pl/pgsql function that 1.) either uses exceptions to handle the division by zero error or 2.) check that sold_parts_amount_dly and sold_labor_amount_dly are not zero before dividing by them within a function. The simplest would be to use CASE in your query. regards, karen ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Division by zero
Hi Costin I am very experienced with MS-SQL and have had very basic training on Oracle and both of these raise an exception as well. I would suggest that a RDBMS that automatically converts to null is way off the standards. Craig - Original Message - From: "Costin Manda" <[EMAIL PROTECTED]> To: Sent: Saturday, April 16, 2005 9:15 AM Subject: [GENERAL] Division by zero > > In other SQL programs a division by zero is solved by transforming the > result to NULL. How can I make postgres have the same behaviour without > using CASE ? > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Division by zero
On Sat, Apr 16, 2005 at 10:15:55AM +0300, Costin Manda wrote: > > In other SQL programs a division by zero is solved by transforming the > result to NULL. The SQL standards state that "If the value of a divisor is zero, then an exception condition is raised: data exception -- division by zero." Databases that silently convert this exception to NULL (e.g., MySQL) are violating standards. > How can I make postgres have the same behaviour without using CASE ? Why don't you want to use CASE? Because it's unwieldy? You could wrap CASE in a function and call that function instead of using the / operator. I'd avoid any temptation to change the behavior of the operator itself because that could cause problems in other code that isn't expecting it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Division by zero
In other SQL programs a division by zero is solved by transforming the result to NULL. How can I make postgres have the same behaviour without using CASE ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] division by zero issue
Greg Donald <[EMAIL PROTECTED]> writes: > On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance > <[EMAIL PROTECTED]> wrote: >> Add : >> AND count(user_tasks.task_id) > 0 in the where clause. > I get the error: > aggregates not allowed in WHERE clause You need to put it in HAVING, instead. Note also this 7.4.4 bug fix: * Check HAVING restriction before evaluating result list of an aggregate plan which means that this isn't really gonna work unless you are on 7.4.5. (It's fairly astonishing that no one noticed we were doing this in the wrong order until recently, but no one did ...) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] division by zero issue
On Wed, Sep 15, 2004 at 12:23:55PM -0500, Greg Donald wrote: > On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance > <[EMAIL PROTECTED]> wrote: > > Add : > > > > AND count(user_tasks.task_id) > 0 in the where clause. > > I get the error: > aggregates not allowed in WHERE clause HAVING count(user_tasks.task_id) > 0 I know it's a little weird to have WHERE for non-aggregate and HAVING for aggregates, but that's the SQL standard... Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] division by zero issue
Greg Donald wrote: > I get the error: > aggregates not allowed in WHERE clause Try HAVING then. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] division by zero issue
On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance <[EMAIL PROTECTED]> wrote: > Add : > > AND count(user_tasks.task_id) > 0 in the where clause. I get the error: aggregates not allowed in WHERE clause -- Greg Donald http://gdconsultants.com/ http://destiney.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] division by zero issue
Maybe try something like this : SELECT task_id, CASE WHEN task_count = '0' THEN '0'::int4 ELSE (task_duration * task_duration_type / task_count) as hours_allocated END FROM (SELECT task_id, task_duration, task_duration_type, count(user_tasks.task_id) as task_count FROM tasks LEFT JOIN user_tasks ON tasks.task_id = user_tasks.task_id WHERE tasks.task_milestone = '0' GROUP BY tasks.task_id, task_duration, task_duration_type ) as intermediate ; This was done off the cuff so it may not work as is. Greg Donald wrote: Converting some MySQL code to work with Postgres here. I have this query: SELECT tasks.task_id, (tasks.task_duration * tasks.task_duration_type / count(user_tasks.task_id)) as hours_allocated FROM tasks LEFT JOIN user_tasks ON tasks.task_id = user_tasks.task_id WHERE tasks.task_milestone = '0' GROUP BY tasks.task_id, task_duration, task_duration_type ; The problem is that sometimes count(user_tasks.task_id) equals zero, so I get the division by zero error. Is there a simple way to make that part of the query fail silently and just equal zero instead of dividing and producing the error? TIA.. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] division by zero issue
Add : AND count(user_tasks.task_id) > 0 in the where clause. Greg Donald wrote: Converting some MySQL code to work with Postgres here. I have this query: SELECT tasks.task_id, (tasks.task_duration * tasks.task_duration_type / count(user_tasks.task_id)) as hours_allocated FROM tasks LEFT JOIN user_tasks ON tasks.task_id = user_tasks.task_id WHERE tasks.task_milestone = '0' GROUP BY tasks.task_id, task_duration, task_duration_type ; The problem is that sometimes count(user_tasks.task_id) equals zero, so I get the division by zero error. Is there a simple way to make that part of the query fail silently and just equal zero instead of dividing and producing the error? TIA.. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] division by zero issue
Converting some MySQL code to work with Postgres here. I have this query: SELECT tasks.task_id, (tasks.task_duration * tasks.task_duration_type / count(user_tasks.task_id)) as hours_allocated FROM tasks LEFT JOIN user_tasks ON tasks.task_id = user_tasks.task_id WHERE tasks.task_milestone = '0' GROUP BY tasks.task_id, task_duration, task_duration_type ; The problem is that sometimes count(user_tasks.task_id) equals zero, so I get the division by zero error. Is there a simple way to make that part of the query fail silently and just equal zero instead of dividing and producing the error? TIA.. -- Greg Donald http://gdconsultants.com/ http://destiney.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
