Thread: operator precedence issues
Hackers, The operator precedence rules seem hard wired to not be able to be worked around, not matter what. The pain point for me has always been the division operator -- once in a while I end up in a situation where I want to override it so that it wraps the divisor with NULLIF. There is no way I can see to do that: custom operator (for example '//') names evaluate in different precedence order which is a non-starter essentially. That I'm ok with given the reasoning in the docs, but I'm really scratching my head over this rule (via http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html#SQL-PRECEDENCE): "When a schema-qualified operator name is used in the OPERATOR syntax, as for example in: SELECT 3 OPERATOR(pg_catalog.+) 4; the OPERATOR construct is taken to have the default precedence shown in Table 4-2 for "any other" operator. This is true no matter which specific operator appears inside OPERATOR()." That rule seems intentionally designed to make it impossible to to override mathematical behaviors. Mainly curious -- was that intentional? merlin
Merlin Moncure <mmoncure@gmail.com> writes: > The operator precedence rules seem hard wired to not be able to be > worked around, not matter what. That's right. In the first place, bison is incapable of doing anything other than hard-wired operator precedence. In the second, if we did try to allow catalog-driven precedence, it would require catalog lookups during the "raw parser" phase, which isn't going to work for a number of implementation reasons; but worse than the implementation troubles is that the grammar would then become fundamentally ambiguous, eg there could be multiple correct parsings of A+B*C depending on what data types A,B,C have. So precedence is hard-wired based on the operator name. > I'm really scratching my head over this rule (via > http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html#SQL-PRECEDENCE): > "When a schema-qualified operator name is used in the OPERATOR syntax, > as for example in: > SELECT 3 OPERATOR(pg_catalog.+) 4; > the OPERATOR construct is taken to have the default precedence shown > in Table 4-2 for "any other" operator. This is true no matter which > specific operator appears inside OPERATOR()." Yeah. I'd rather have said that it's the same precedence as for the undecorated operator name, but again bison doesn't really have a way to do that. regards, tom lane
Hi, On 2013-08-30 17:35:04 -0500, Merlin Moncure wrote: > "When a schema-qualified operator name is used in the OPERATOR syntax, > as for example in: > SELECT 3 OPERATOR(pg_catalog.+) 4; > the OPERATOR construct is taken to have the default precedence shown > in Table 4-2 for "any other" operator. This is true no matter which > specific operator appears inside OPERATOR()." > > That rule seems intentionally designed to make it impossible to to > override mathematical behaviors. Mainly curious -- was that > intentional? You can change your search_path to include your schema before an explicitly listed pg_catalog afair. Not nice, but should work... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Aug 30, 2013 at 5:48 PM, Andres Freund <andres@2ndquadrant.com> wrote: > Hi, > > On 2013-08-30 17:35:04 -0500, Merlin Moncure wrote: >> "When a schema-qualified operator name is used in the OPERATOR syntax, >> as for example in: >> SELECT 3 OPERATOR(pg_catalog.+) 4; >> the OPERATOR construct is taken to have the default precedence shown >> in Table 4-2 for "any other" operator. This is true no matter which >> specific operator appears inside OPERATOR()." >> >> That rule seems intentionally designed to make it impossible to to >> override mathematical behaviors. Mainly curious -- was that >> intentional? > > You can change your search_path to include your schema before an > explicitly listed pg_catalog afair. Not nice, but should work... hurk -- wish I had known that last week, but that's a nifty trick! It satisfies my particular problem (safe division) since in this case the problem is handled 'in function' and I can temporarily hack the search_path. Interestingly, if you do this the database doesn't match Ideally though you could specify operator precedence in the operator name itself though in such a way that bison pick it up. I don't know if that's possible since so many operator names have been given out without any thought to reserving characters for precedence, or if it would be worth the extra parsing time even if you could do it. Overriding stock operator behaviors is a really dodgy practice with the limited but important exception of handling certain classes of mathematical errors. While playing around with Andres's trick, I noticed that it works but will not match against operators taking "any" although those will match with explicit schema declaration (FWICT it goes through the search_path trying to explicitly match int/int operator then goes again matches "any"). That's pretty weird: postgres=# CREATE OR REPLACE FUNCTION SafeDiv( postgres(# anyelement, postgres(# anyelement) RETURNS anyelement AS postgres-# $$ postgres$# SELECT $1 OPERATOR(pg_catalog./) NULLIF($2, 0); postgres$# $$ LANGUAGE SQL; CREATE FUNCTION postgres=# set search_path to safediv, pg_catalog, public; SET postgres=# CREATE OPERATOR safediv./ postgres-# ( postgres(# PROCEDURE = SafeDiv, postgres(# LEFTARG = anyelement, postgres(# RIGHTARG = anyelement, postgres(# COMMUTATOR = / postgres(# ); CREATE OPERATOR postgres=# select 1/0; ERROR: division by zero postgres=# select 1 operator(safediv./) 0;?column? ---------- (1 row) postgres=# CREATE OR REPLACE FUNCTION SafeDiv( postgres(# int4, postgres(# int4) RETURNS int4 AS postgres-# $$ postgres$# SELECT $1 OPERATOR(pg_catalog./) NULLIF($2, 0); postgres$# $$ LANGUAGE SQL; CREATE FUNCTION postgres=# postgres=# CREATE OPERATOR safediv./ postgres-# ( postgres(# PROCEDURE = SafeDiv, postgres(# LEFTARG = int4, postgres(# RIGHTARG = int4, postgres(# COMMUTATOR = / postgres(# ); CREATE OPERATOR postgres=# select 1/0;?column? ---------- (1 row) merlin
On 2013-09-03 08:59:53 -0500, Merlin Moncure wrote: > While playing around with Andres's trick, I noticed that it works but > will not match against operators taking "any" although those will > match with explicit schema declaration (FWICT it goes through the > search_path trying to explicitly match int/int operator then goes > again matches "any"). That's pretty weird: Not surprising. We look for the best match for an operator and explicitly matching types will be that. If there were no operator(int, int) your anyelement variant should get called. > Ideally though you could specify operator precedence in the operator > name itself though in such a way that bison pick it up. I don't know > if that's possible since so many operator names have been given out > without any thought to reserving characters for precedence, or if it > would be worth the extra parsing time even if you could do it. > Overriding stock operator behaviors is a really dodgy practice with > the limited but important exception of handling certain classes of > mathematical errors. I have to say, even those it seems like it's primary advantage is making it harder to read the code, but YMMV. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On 2013-09-03 08:59:53 -0500, Merlin Moncure wrote:
>> While playing around with Andres's trick, I noticed that it works but
>> will not match against operators taking "any" although those will
>> match with explicit schema declaration (FWICT it goes through the
>> search_path trying to explicitly match int/int operator then goes
>> again matches "any"). That's pretty weird:
> Not surprising. We look for the best match for an operator and
> explicitly matching types will be that. If there were no operator(int,
> int) your anyelement variant should get called.
Yeah, this has exactly nothing to do with operator precedence.
Precedence is about which operator binds tighter in cases like "A+B*C".
regards, tom lane
On Tue, Sep 3, 2013 at 9:13 AM, Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br />>Andres Freund <<a href="mailto:andres@2ndquadrant.com">andres@2ndquadrant.com</a>> writes:<br />>> On 2013-09-0308:59:53 -0500, Merlin Moncure wrote:<br /> >>> While playing around with Andres's trick, I noticed thatit works but<br />>>> will not match against operators taking "any" although those will<br />>>> matchwith explicit schema declaration (FWICT it goes through the<br /> >>> search_path trying to explicitly matchint/int operator then goes<br />>>> again matches "any"). That's pretty weird:<br />><br />>> Notsurprising. We look for the best match for an operator and<br /> >> explicitly matching types will be that. If therewere no operator(int,<br />>> int) your anyelement variant should get called.<br />><br />> Yeah, this hasexactly nothing to do with operator precedence.<br /> > Precedence is about which operator binds tighter in cases like"A+B*C".<br /><br />That all makes perfect sense -- thanks guys. For posterity, Andres's trick worked and did end upsaving me some coding after all -- in my case I have to eval() some externally generated fairly complex expressions inSQL (via pl/pgsql EXECUTE) in the context of a much larger query. My de-parsing code ended up having bugs and it was mucheasier to tip-toe around the search_path (via SET LOCAL) and force the modified operator.<br /><br />merlin<br />