PGCon2014 - 20140704

PGCon 2014
The PostgreSQL Conference

Noah Misch
Day Talks - Day 1 - Thu May 22 - 2014-05-22
Room Morisset 221
Start time 15:15
Duration 00:45
ID 670
Event type Lecture
Track Hacking
Language used for presentation English

I know greater-than-or-equal-to when I see it!

A tour of the operator class facility

A PostgreSQL operator class tells the database server how to sort, match, and index values of a particular data type, and an operator family ties related data types together for the purpose of these operations. They constitute agreements between data type authors and data type-independent code, with most data types defining at least one operator class.

This talk will explore the theory behind operator classes and families, including the assumptions data type-independent code is and is not entitled to make based on available operator classes. We will walk through the creation of new operator classes, some practical and others deliberately perverse, and examine some exceptional operator classes already present in PostgreSQL.

Writing a query expression such as "val >= $1" without knowing the data type of "val" is almost always a mistake. It will work for the vast majority of data types, but it will malfunction in rare cases. We will examine the use of operator classes to adapt such an expression to each data type.

This talk caters to writers of code intended to deal with unforeseen data types. Application code such as replication systems and client interfaces are affected, as are backend features such as integrity constraints and join algorithms. As an author of such software, you will leave the talk equipped to audit for mistakes and substitute code that harnesses operator classes and families to process values of arbitrary type. The talk will also help prospective authors of new data types.