Thread
-
using indexes with the OR clause
jose' soares <sferac@bo.nettuno.it> — 1998-10-20T13:52:06Z
Hi all, I'm trying v6.4beta2 via ODBC-Access. I have the following table: CREATE TABLE attivita ( azienda CHAR(11) NOT NULL, attivita CHAR(03) NOT NULL, specifica CHAR(02), cod_alternativo CHAR(10), conto_economico CHAR(02), inizio_attivita DATE, fine_attivita DATE, PRIMARY KEY (azienda,attivita,specifica,inizio_attivita) ); I linked this table on M$-Access specifying the Access-key equal to PostgreSQL PRIMARY KEY to test indexes with the OR clause but I have the following error message: 'FATAL 1: palloc failure memory exhausted' Any ideas ? There's the PSLODBC.LOG file attached. Jose' -
Re: [INTERFACES] using indexes with the OR clause
David Hartwig <daveh@insightdist.com> — 1998-10-20T15:34:38Z
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. Are you using row versioning? If so, you may have to overload an operator for > on xid. Let me know how it goes. Sferacarta Software wrote: > Hi all, > > I'm trying v6.4beta2 via ODBC-Access. > > I have the following table: > > CREATE TABLE attivita ( > azienda CHAR(11) NOT NULL, > attivita CHAR(03) NOT NULL, > specifica CHAR(02), > cod_alternativo CHAR(10), > conto_economico CHAR(02), > inizio_attivita DATE, > fine_attivita DATE, > PRIMARY KEY (azienda,attivita,specifica,inizio_attivita) > ); > > I linked this table on M$-Access specifying the Access-key equal to > PostgreSQL PRIMARY KEY to test indexes with the OR clause but > I have the following error message: > > 'FATAL 1: palloc failure memory exhausted' > > Any ideas ? > > There's the PSLODBC.LOG file attached. > > Jose' -
Re: [HACKERS] Re: [INTERFACES] using indexes with the OR clause
David Hartwig <daveh@insightdist.com> — 1998-10-21T12:39:53Z
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 );