PostgreSQL7.0手冊-程序員手冊 -40. 擴展的 SQL: 操作符
2019-09-08 23:33:46
供稿：網友
第四十章. 擴展的 SQL: 操作符
Postgres 支持左目，右目和雙目操作符．操作符可以重載，或以不同數目或類型的參數重新使用．如果有語義含混而系統無法決定使用哪個正確的操作符，它將返回一個錯誤。你可能必須轉換左和/或右操作數的類型來幫助系統明白你想用的是哪個操作符．
每個操作符都是對真正干活的對應函數的"語義修飾"；所以你在創建操作符之前必須先創建對應的函數。不過，一個操作符也并不僅僅是語義修飾，因為它還帶著可以幫助查詢規劃器優化使用該操作符的查詢的附加信息。本章的大部分將用于解釋那些附加信息。
下面是一個創建用于兩個復數相加的操作符的例子。我們假設已經創建了復數類型的定義。首先我們需要做（相加）工作的函數；然后我們就可以定義操作符：
CREATE FUNCTION complex_add(complex, complex)
RETURNS complex
AS '$PWD/obj/complex.so'
LANGUAGE 'c';
CREATE OPERATOR + (
leftarg = complex,
rightarg = complex,
procedure = complex_add,
commutator = +
);
現在我們可以：
SELECT (a + b) AS c FROM test_complex;
+----------------+
|c |
+----------------+
|(5.2,6.05) |
+----------------+
|(133.42,144.95) |
+----------------+
我們在這里已經演示了如何創建兩目操作符。要創建單目操作符，只需要省略左操作數（對左目操作符）或者右操作數（對右操作符）即可。只有過程（procedure）子句和參數（argument）子句是 CREATE OPERATOR 里需要的條目。例子里演示的 COMMUTATOR 子句是一個給查詢優化器的可選的暗示。關于 COMMUTATOR 和其它優化器暗示的詳細信息在下面給出。
操作符優化信息
作者：由 Tom Lane 書寫。
Postgres 的操作符定義可以包括幾個可選的子句，這些子句告訴系統一些關于該操作符的特性的有用信息。在可能的情況下，我們都應該提供這些子句，因為它們可能為使用這個操作符的查詢帶來可觀的速度提升。不過要注意如果你聲明了這些子句，你必須確保它們是正確的！對優化子句的錯誤使用將導致后端的崩潰，精細的輸出錯誤或者其他糟糕事情。如果你對這些事情不確定的話，你可以總是忽略優化子句；唯一的后果是查詢可能比需要的運行的慢一些。
附加的優化子句可能在今后的 Postgres 版本里出現。這里描述的都是版本 6.5 可以理解的。
COMMUTATOR（換向器）
如果提供了 COMMUTATOR 子句，則命名一個操作符是被定義的操作符的換向器。如果有兩個操作符 A，B，對于任何可能的輸入 x，y 都有 (x A y) 等于 (y B x)，那么我們就說 A 是 B 的換向器，同樣 B 也是 A 的換向器。例如，操作符 '<' 和 '>' 對于所使用的一定的數據類型通常都是對方的換向器，而操作符 '+' 通常是它自身的換向器。但是操作符 '-' 通常沒有換向器。
一個被換向的操作符的左操作符與它的換向器的右操作符相同，反之亦然。所以Postgres 所需要的只是一個換向器操作符的名稱用以查找該換向器，那也是 COMMUTATOR 子句里所需要的唯一的東西。
當你定義一個自換向的操作符時，你定義它就是了。當你定義一對換向器操作符時，事情就有一點棘手：怎樣定義一個操作符的換向器指向另一個你還沒有定義的操作符呢？我們對這個問題有兩個解決方法：
個方法是省略你定義的第一個操作符的 COMMUTATOR 子句，然后在第二個操作符的定義里提供一個（ COMMUTATOR 子句）。因為 Postgres 知道換向操作符是成對出現的，所以當它看到第二個定義時它會自動返回并填充第一個定義里空缺的 COMMUTATOR 子句。
另一個更直接的方法是在兩個定義里面都包含 COMMUTATOR 子句。當 Postgres 處理第一個定義并意識到 COMMUTAOTR 指向一個不存在的操作符，系統會在系統的 pg_operator 表里面為該操作符記錄一個虛擬的記錄。這個虛擬的記錄只有操作符名，左和右參數類型以及結果類型是有效的，因為這些是到目前為止Postgres 可以推導出來的東西。第一個操作符表記錄將和這個虛擬記錄聯接。稍后，當你定義第二個操作符時，系統將用來自第二個操作符的信息更新該虛擬記錄。如果你試圖在虛擬操作符被填充之前使用它，你將只能收到一條錯誤信息。（注意：這個過程在Postgres 6.5以前的版本不能可靠的工作，但是現在這種方法是我們推薦的方法。）
NEGATOR（否定符）
如果提供了 NEGATOR 子句，則命名一個操作符是被定義的操作符的否定符。如果有兩個都返回布爾變量的操作符 A 和 B，對任何可能的輸入 x 和 y，都有 (x A y) 等于 NOT (x B y)，那么我們說 A 是 B 的否定符。當然 B 也是 A 的否定符。例如，'<' 和 '>=' 對大多數數據類型是一對否定符。一個操作符不可能是它自身的有效操作符。
不象 COMMUTATOR，一對單目操作符可以互為有效的否定符；那就意味著對于所有的 x，(A x) 等于 NOT (B x)，或者類似的右目操作符的這種情況。
一個操作符的否定符必須有于該操作符本身一樣的左和/或右參數，所以就象 COMMUTATOR 一樣，只有操作符名需要在 NEGATOR 子句里面給出。
提供 NEGATOR 對查詢優化器是非常有幫助的，因為這樣就允許象 NOT (x = y) 這樣的表達式簡化成 x <> y。這樣的情況比你想象的要頻繁的多，因為 NOT 可能因為其他的重排列而被引入。
否定符對可以用上面換向符對中解釋的相同的方法來定義。
RESTRICT（限制）
如果提供了 RESTRICT 子句，則為操作符命名一個選擇性限制計算函數（注意這里是一個函數名，而不是一個操作符名）。RESTRICT 子句只是對返回布爾變量的雙目操作符有意義。選擇性限制計算符的概念是猜測一個表中所有行的哪一部分對于目前的操作符和特定的常量將滿足一個象下面這樣形式的 WHERE 條件子句
field OP constant
它可以給出這種類型的 WHERE 子句可以刪除多少行的一個概念，這將幫助優化器進行優化。（你可能會說，如果該常量（constant）在左邊怎么辦？哦，那是 COMMUTATOR 干的事...）
書寫新的選擇性限制計算函數遠遠超出了本章的范圍，不過很幸運的是，通常你對自己的操作符只需要使用系統標準的計算器之一就行了。下面是一些標準限制計算器：
eqsel for =
neqsel for <>
scalarltsel for < or <=
scalargtsel for > or >=
這些都是分類，看起來有點奇怪，不過如果你仔細想想，就會覺得有道理。'=' 大多將只接受表中的一小部分行；'<>' 大多將拒絕一小部分行。'<' 將接受的行取決于給出的常量落在表的該列數據值的哪一個范圍里（該值碰巧是 VACUUM ANALYZE 收集并且提供給選擇性計算器的信息）。'<=' 在同樣的常量時會接受比 '<' 略微大一些的行，不過它們也非常接近，幾乎不值得區別開來，尤其是無論如何我們也比做盲猜好得多。類似的情況也適用于 '>' 和 '>='。
你可能常習慣于把 eqsel 或者 neqsel 用于那些非常高或者非常低選擇性的操作符，即使它們并非真正相等或者不相等。例如，規則表達式匹配操作符（~，~*，等）常使用 eqsel，是基于這樣的假設：它們只會匹配整個表中的一小部分記錄。
你可以把 scalarltsel 和 scalargtsel 用于比較那些為進行范圍比較被轉化為數字尺度后有明顯意義的數據類型。如果可能，把該數據類型增加到可以被文件 src/backend/utils/adt/selfuncs.c 里的過程 convert_to_scalar() 理解的部分。（最終，這個過程將被放到由 pg_type 表里的一個列標識的每種類型一個的函數代替，不過目前還沒有這么做。）如果你沒有做這些，系統仍然能工作，不過優化器的估計不會象想象的那么好。
在 src/backend/utils/adt/geo_selfuncs.c 里還有為幾何操作符設計的額外的選擇性函數：areasel，positionsel，和 contsel。在我寫這些的時候，它們都只是存根，但是你還是可以使用（或者更好的是，改良它們）它們。
JOIN（聯合）
如果提供了 JOIN 子句，則為操作符命名一個聯合選擇性函數（注意這里是函數名，不是操作符名）。JOIN 子句只是對返回布爾量的雙目操作符有意義。一個聯合選擇性計算器后面的概念是猜測一對表上的那一部分行對目前的操作符將滿足下面形式的 WHERE 子句的條件
table1.field1 OP table2.field2
和 RESTRICT 子句一樣，這些很有可能幫助優化器用最少的處理勾畫出要采取可能的聯合順序中的哪一個。
和前面一樣，本節不會試圖解釋如何書寫一個聯合選擇性計算器函數，但是會建議你在有一個可用的情況下，使用一個標準的計算器：
eqjoinsel for =
neqjoinsel for <>
scalarltjoinsel for < or <=
scalargtjoinsel for > or >=
areajoinsel for 2D area-based comparisons
positionjoinsel for 2D position-based comparisons
contjoinsel for 2D containment-based comparisons
HASHES
如果出現了 HASHES 子句，則告訴系統對于一個基于此操作符的聯合可以使用哈希（散列）聯合。HASHES 只對返回布爾量的雙目操作符有意義，并且實際上該操作符最好是對某種數據類型的相等操作符。
哈希（散列）聯合的假設是：對于一對哈希（散列）到同樣的哈希（散列）代碼的左和右操作數值，該聯合操作符只能返回 TRUE。如果兩個值被放到不同的哈希桶里，聯合將根本不比較它們，隱含地意味著聯合操作符的結果一定是 FALSE。所以對于不代表相等的操作符，聲明 HASHES 是沒有意義的。
實際上，邏輯相等還不夠好；該操作符最好是代表完全的按位相等，因為哈希函數將對該值的內存表現形式進行計算而不管這些位的含義是什么。例如，時間間隔的相等不是按位相等；時間間隔相等操作符認為如果兩個時間間隔具有相同持續時間時就是相等的，而不管它們的兩個端點是否相等。這就意味著對于一個用 "=" 在時間間隔域之間的聯合，如果用哈希聯合實現將會和用別的聯合實現生成不同的結果，因為可以匹配的大部分數據對將被哈希成不同的值因而不會被哈希聯合進行比較。但是如果優化器選擇使用不同的聯合方法，所有等號操作符說相等的數據對都會被找出來。我們不想出現那種不一致性，所以我們沒有標記時間間隔等號為可哈希的。
同時還有一些硬件相關的因素會導致一個哈希聯合的計算錯誤。例如，如果你的數據類型是一個結構，結構里可能有不引人注意的填充位，這時把這個等號操作符標記為 HASHES 也是不安全的。（除非你書寫你的其他操作符以確保這些未用的位總是零。）另一個例子是 FLOAT 數據類型對哈希聯合也是不安全的。在符合 IEEE 浮點標準的機器上，負零和正零是不同的值（不同的位模式），但是它們被定義為比較相等。所以，如果浮點等號被標記為 HASHES，一個負零和一個正零可能不被哈希聯合匹配，但是用其他聯合處理，它們應該是匹配的。
底線是：你可能只能把 HASHES 用于用（或可能用） memcmp（）實現的等號操作符。
SORT1 和 SORT2
如果出現了 SORT 子句，則告訴系統對基于目前的操作符可以使用融合聯合方法。如果兩者（左右數據類型）都是則都要聲明。目前的操作符必須是對某一數據類型對的相等，并且 SORT1 和 SORT2 子句分別為左邊和右邊的數據類型命名了排序操作符（'<' 操作符）。
融合聯合是以這樣的概念為基礎的：對左邊和右邊的表進行排序，然后并行地掃描它們。所以，兩種數據類型都必須是能夠完全排序的，并且聯合操作符必須只對那些落在排序順序中的"某個位置"的數值對成功。實際上這意味著聯合操作符必須表現得象等于。但是和哈希聯合不同，（哈希聯合里左邊和右邊的數據類型最好是相同的（至少是按位相等）），可以對兩種不同數據類型進行融合聯合 -- 只要他們邏輯相等即可。例如， int2 對 int4 的相等操作符是可以用融合聯合的。我們只需要可以把兩種數據類型排列成邏輯可比的序列的排序操作符即可。
當聲明融合排序操作符時，目前的操作符和兩個引用的操作符必須返回布爾變量；SORT1 操作符的兩個輸入數據類型必須和目前操作符的左參數的類型相同，而 SORT2 操作符的兩個輸入數據類型必須和目前操作符的右參數的類型相同。（和 COMMUTATOR 已經 NEGATOR 一樣，這意味著對于聲明該操作符而言，操作符名稱就足夠了，并且如果你碰巧在另一個等于操作符之前定義一個等于操作符，系統能夠自動填充虛擬操作符記錄。）
實際上你只能為一個 '=' 操作符書寫 SORT 子句，并且兩個參考的操作符應該總是命名為 '<'。試圖對命名為其他東西的操作符使用融合聯合將導致讓人絕望的沖突，我們一會兒就會看到原因。
還有一些對你標記為可融合連接的操作符的附加限制。這些限制目前沒有被 CREATE OPERATOR 檢查，但是如果下面之一是真的話，融合聯合會在運行時失敗：
可融合聯合的相等操作符必須有一個換向器（如果兩種數據類型相同則是它自身，如果不同則是一個相關的操作符）。
必須有和可融合聯合操作符本身有相同左右輸入數據類型的 '<' 和 '>' 排序操作符。這些操作符必須命名為 '<' 和 '>'；在這方面你沒有任何選擇，因為沒有顯式聲明它們的規定。要注意如果左和右數據類型不同，這些操作符沒有一個和 SORT 操作符中的任何一個相同。但是它們最好能對與 SORT 操作符兼容的數據值進行排序，否則融合聯合將在工作中失效。
--------------------------------------------------------------------------------
