Re: [HACKERS] Re: [INTERFACES] using indexes with the OR clause - Mailing list pgsql-interfaces
From | David Hartwig |
---|---|
Subject | Re: [HACKERS] Re: [INTERFACES] using indexes with the OR clause |
Date | |
Msg-id | 362DD619.A3AE849A@insightdist.com Whole thread Raw |
In response to | using indexes with the OR clause (Sferacarta Software <sferac@bo.nettuno.it>) |
List | pgsql-interfaces |
Jose' Soares wrote: > David Hartwig wrote: > > > > In the "Connect Settings" (either global or per data source) add the > > following line: > > SET ksqo TO 'on' > > > > Stands for "Key Set Query Oprimizer". It actually breaked those nasty > > OR's into UNIONS. There will eventually be a radio button for this. > > We have be in a crunch lately at work. > > > Yes, this works well now. > > > Are you using row versioning? If so, you may have to overload an > > operator for > on xid. > > Do you mean < I think, because my log says ERROR: Unable to find an > ordering operator '<' for type xid > Any way. I tried to create the operators =, < and > but I have some > troubles to do this. > > I modified xidint4.c and xidint4.sql, the sources that you sent me some > time ago, as follow: > > ---------------xidint4.c------------- > /* Insight Distribution Systems - System V - Apr 1998i > static char accntnum_c[] = "@(#)accntnum.c 1.1 > /sccs/sql/extend/s.accntnum. > */ > #include <stdio.h> /* for sprintf() */ > #include <string.h> > #include "postgres.h" > #include "utils/palloc.h" > > bool xidint4_eq(int32 arg1, int32 arg2); > bool xidint4_gt(int32 arg1, int32 arg2); > bool xidint4_lt(int32 arg1, int32 arg2); > > bool xidint4_eq(int32 arg1, int32 arg2) > { > return (arg1 == arg2); > } > > bool xidint4_gt(int32 arg1, int32 arg2) > { > return (arg1 > arg2); > } > > bool xidint4_lt(int32 arg1, int32 arg2) > { > return (arg1 < arg2); > } > > --------------------xidint4.sql--------------------------- > create function xidint4_eq(xid,int4) > returns bool > as '/usr/local/pgsql/lib/contrib/xidint4.so' > language 'c'; > > create function xidint4_gt(xid,int4) > returns bool > as '/usr/local/pgsql/lib/contrib/xidint4.so' > language 'c'; > > create function xidint4_lt(xid,int4) > returns bool > as '/usr/local/pgsql/lib/contrib/xidint4.so' > language 'c'; > > create operator = ( > leftarg=xid, > rightarg=int4, > procedure=xidint4_eq, > commutator='=', > negator='<>', > restrict=eqsel, > join=eqjoinsel > ); > > create operator < ( > leftarg=xid, > rightarg=int4, > procedure=xidint4_lt, > commutator='<', > negator='>', > restrict=intltsel, > join=intltjoinsel > ); > > create operator > ( > leftarg=xid, > rightarg=int4, > procedure=xidint4_gt, > commutator='>', > negator='<', > restrict=intgtsel, > join=intgtjoinsel > ); > > This script gives me this message for every operator it creates, > NOTICE: buffer leak [392] detected in BufferPoolCheckLeak() > CREATE > > but at end seems that it works, I can query a table as: > > select xmin from attivita where xmin = 92017; > select xmin from attivita where xmin > 92016; > select xmin from attivita where xmin < 92018; > > But psqlodbc.log has still the message: > > ERROR: Unable to find an ordering operator '<' for type xid > > Thanks David for your help. > > Jose' Hmmm... Sound a bit strange. Lets keep an eye on it. Anyway, I have a minimalist patch which is confined to SQL which accomplishes the same thing. I just overload the int4 functions. Works well. If you use this patch, be sure to DROP the other XID operators and functions that you just created to remove any ambiguity. ++++++++++++++++++++++++++++++++++++++ create function int4eq(xid,int4) returns bool as '' language 'internal'; create operator = ( leftarg=xid, rightarg=int4, procedure=int4eq, commutator='=', negator='<>', restrict=eqsel, join=eqjoinsel ); create function int4lt(xid,xid) returns bool as '' language 'internal'; create function int4lt(xid,xid) returns bool as '' language 'internal'; create operator < ( leftarg=xid, rightarg=xid, procedure=int4lt, commutator='=', negator='<>', restrict=eqsel, join=eqjoinsel );
pgsql-interfaces by date: