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'



Tue, 20 Oct 1998 15:52:06 +0200
Sferacarta Software <sferac@bo.nettuno.it>
using indexes with the OR clause
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'




Tue, 20 Oct 1998 11:34:38 -0400
David Hartwig <daveh@insightdist.com>
Re: [INTERFACES] using indexes with the OR clause


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
        );





Wed, 21 Oct 1998 08:39:53 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Re: [INTERFACES] using indexes with the OR clause