On Wed, 2005-01-12 at 18:12 -0500, Tom Lane wrote:
> Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx> writes:
> > I'm wondering if the function under = is an SQL function being inlined.
>
> Bingo --- that's surely it. After inlining, the expression would no
> longer look like it matched the index.
>
> You don't want to use SQL functions to define indexable operators
> anyway. They leak memory, and are slow, and neither of those are
> good properties for an index support function.
>
> regards, tom lane
Thanks very much guys. I got confused and tried to simplify it to a
simple test case and confused myself more when it still didn't work.
Just to be sure I redid it in plpgsql with no problems.
I understand that index operations should be written in C for production
use, or at least tested in C to see if it helps matters. Comparison
operators are of course very simple to write in any language anyway.
I attached a proposed documentation patch. I wasn't able to readily see
the implications of writing a function in SQL regarding an index, so
perhaps this will help someone in the future (not that many people will
attempt writing index access methods in SQL, but someone could get
confused like I did).
Regards,
Jeff Davis
--- postgresql-8.0.0rc4/doc/src/sgml/ref/create_opclass.sgml 2003-11-29 11:51:38.000000000 -0800
+++ postgresql-8.0.0rc4.new/doc/src/sgml/ref/create_opclass.sgml 2005-01-12 18:06:44.000000000 -0800
@@ -128,6 +128,10 @@
<para>
The name (optionally schema-qualified) of an operator associated
with the operator class.
+
+ Note: this operator should NOT be defined by a SQL function.
+ If the operator is defined with a SQL function, the SQL function
+ could be inlined, preventing the use of an index.
</para>
</listitem>
</varlistentry>
@@ -172,6 +176,10 @@
<para>
The name (optionally schema-qualified) of a function that is an
index method support procedure for the operator class.
+
+ Note: this function should NOT be written in SQL.
+ If the function is written in SQL, it could be inlined,
+ preventing the use of an index.
</para>
</listitem>
</varlistentry>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
