PostgreSQL – How to Use Operator Negator

datatypesexecution-planpostgresql

I'm wondering if the negator clause of CREATE OPERATOR specifies how the negator can be interpreted, or how it will be interpreted. For example, if I have an operator >= and I set the negator to the operator <, will NOT(x >= y) always be interpreted as x < y, or does it just mean that it will be interpreted that way when it's advantageous.

I'm asking because I'd like to be able to be able to use NOT(x op y) in my queries as something other than the complement of x op y. Is this possible and/or would it be playing with fire?

Concretely, I have a composite type with a subcolumn match_id and a subcolumn second. I want to be able to use, for example, NOT(x >= y) to mean x.match_id = y.match_id AND x.second < y.second, which would be encoded in the operator <.

Best Answer

Starting with PostgreSQL documentation about Negators:

36.13.2. NEGATOR

The NEGATOR clause, if provided, names an operator that is the negator of the operator being defined. We say that operator A is the negator of operator B if both return Boolean results and (x A y) equals NOT (x B y) for all possible inputs x, y. Notice that B is also the negator of A. For example, < and >= are a negator pair for most data types. An operator can never validly be its own negator.

[...]

The way the NEGATOR is supposed to behave does not match your intended usage. I guess this could result in some execution plans not giving the intended results.

Again, according to the docs:

Providing a negator is very helpful to the query optimizer since it allows expressions like NOT (x = y) to be simplified into x <> y. This comes up more often than you might think, because NOT operations can be inserted as a consequence of other rearrangements.

The way the text has been written, I understand that this implies that the planner might simpify a clause like NOT(a op b) into a negator_op b (or even the other way around, as commented out by @craig-ringer); but it also might not do it. The planner is allowed to make the simplification, but not coerced to or has an obligation to perform the conversion.

I suggest that, if you need different kind of comparisons, that you create other opeartors (let's say <% >% <=% >=% or something like that), with corresponding negators if they make sense.

Related Solutions

PostgreSQL synchronized commit performance

Don't do this with only one slave. If you do you are making your application more subject to failure, not less. Basically if you do this, and you lose your slave, your master will hang. You say your backup box is crappy, and I assume that means reliability-wise too, so this is more likely than losing your master. This is probably not what you want. Do not use synchronized commit option with replication and only one slave.

Synchronized commits in replication means that the master waits for the slave to acknowledge that the WAL was applied before noting that the commit happened and passing that back to the client. If you don't get the configured quorum, you wait for it, possibly for.... a..... long..... time......

Do it async. Use fsync set to on. You should be golden.

PostgreSQL – Operator Uses Varchar Pattern Ops Index While ORDER BY Doesn’t

This revolves around COLLATION rules (which are part of your locale). Per documentation:

The C and POSIX collations both specify "traditional C" behavior, in which only the ASCII letters "A" through "Z" are treated as letters, and sorting is done strictly by character code byte values.

Bold emphasis mine. These internal operators:

~<~, ~<=~, ~>=~, ~>~

are members of the operator classes text_pattern_ops, varchar_pattern_ops and bpchar_pattern_ops which ignore current collation rules and default to "traditional C" behavior. Per documentation:

The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules.

That's why they can use your first index, while their locale-aware equivalents

<, <=, >=, >

would need the second (using the default operator class).

ORDER BY defaults to the current collation rules. Unless you happen to work with the "C" collation the second set of operators is used for sorting, which can only use indexes with a matching collation (default btree indexes).

Notably, pattern-matching generally relies on "traditional C" behavior, that's why a left anchored LIKE match can use a _patern_ops index with a non-C locale, but not a default index. Compare (last chapter):

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

In Postgres 9.1+ you can force a selected sort order with COLLATE "locale_name" (the locale has to be available in your system!). So if you run your query with COLLATE "C" (which is a much better option than the evil hack using ~<~) , it can use the _pattern_ops index:

SELECT * FROM individual
ORDER  BY upper(surname) COLLATE "C"
        , upper("name") COLLATE "C"
        , upper(patronymic) COLLATE "C"
        , birth_dt
LIMIT  100;

Results are different. Try with non-ASCII letters like ö, é, etc. Those typically sort depending on your current collation rules, while letters a-z typically sort the same in all locales.

Best Answer

Related Solutions

PostgreSQL synchronized commit performance

PostgreSQL – Operator Uses Varchar Pattern Ops Index While ORDER BY Doesn’t

Related Question