Thread: using indexes with the OR clause
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'
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'
Hello all, I'm a newbie to postgres (haven't got as far as setting up odbc on my system yet) so please forgive me if I'm talking ^&*%. At 23:00 20/10/98 -0400, you wrote: >Date: Tue, 20 Oct 1998 15:52:06 +0200 >From: Sferacarta Software <sferac@bo.nettuno.it> >Subject: using indexes with the OR clause > >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: <snip> It may be nothing to do with your error but I see from your code you allow NULL values as part of the primary key. The programming style is questionable, but Access is VERY particular about not having NULL values in JET tables so I suppose it could be the same for linked tables. I would try declaring each attribute of the primary key as NOT NULL. Colin
Colin McKinnon wrote: > Hello all, > I'm a newbie to postgres (haven't got as far as setting up odbc on my > system yet) so please forgive me if I'm talking ^&*%. > > At 23:00 20/10/98 -0400, you wrote: > >Date: Tue, 20 Oct 1998 15:52:06 +0200 > >From: Sferacarta Software <sferac@bo.nettuno.it> > >Subject: using indexes with the OR clause > > > >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: > <snip> > It may be nothing to do with your error but I see from your code you allow > NULL values as part of the primary key. The programming style is > questionable, but Access is VERY particular about not having NULL values in > JET tables so I suppose it could be the same for linked tables. > > I would try declaring each attribute of the primary key as NOT NULL. > In PostgreSQL the PRIMARY KEY clause forces an implied NOT NULL on each specified column.
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
);