Re: [HACKERS] bug(?) if int8 as primary key

Hello David, hello Thomas,

a couple of weeks ago I had a similar problem, I solved it by a little
shell-script, (sorry, no pg-sql) which registers the missing operators.
As I thought it4s going to be fixed in the next version, I didn4t post
it. But untill then, perhaps it helps ....
First you need the int8cmp-Operator (I something like a
signature-function of the difference). Than you should register this
operator.


Kind regards

Michael Contzen

-------------- int8cmp.c ---------------


/*-------------------------------------------------------------------------
** int8cmp.c--*        Internal 64-bit integer
operations**-------------------------------------------------------------------------
*/
#include <stdio.h>                              /* for sprintf proto,
etc. */
#include <stdlib.h>                             /* for strtod, etc. */
#include <string.h>
#include <ctype.h>
#include <time.h>
#include <math.h>
#include <float.h>
#include <limits.h>

#include "postgres.h"
#include "utils/palloc.h"

#define MAXINT8LEN              25

#if defined(__alpha) || defined(__GNUC__)
#define HAVE_64BIT_INTS 1
#endif

#ifndef HAVE_64BIT_INTS
typedef char[8] int64;

#elif defined(__alpha)
typedef long int int64;

#define INT64_FORMAT "%ld"

#elif defined(__GNUC__)
typedef long long int int64;

#define INT64_FORMAT "%Ld"

#else
typedef long int int64;

#define INT64_FORMAT "%ld"
#endif

int32      int8cmp(int64 * val1, int64 * val2);

int32
int8cmp(int64 * val1, int64 * val2)
{       if (*val1 > *val2) return 1;       if (*val1 < *val2) return -1;       return 0;
}       /* int8cmp() */


------------------ End of int8cmp.c ------------------





------------------ initlongbtree.sh -----------------


initlongbtree.sh:

SQL="psql -t MYDATABASE  "

OPS=`$SQL -c  "SELECT am.amname, opc.opcname, opr.oprname   FROM pg_am am, pg_amop amop,        pg_opclass opc,
pg_operatoropr   WHERE amop.amopid = am.oid AND         amop.amopclaid = opc.oid AND         amop.amopopr = opr.oid AND
       opc.opcname='int8_ops'   ORDER BY am.amname, opc.opcname"`
 

if [ "$OPS" != "" ]; thenecho "int8-btree ops already registerd."exit 0
fi

TYPE=`$SQL -c  "select oid from pg_type where typname='int8'"`
echo "TYP=$TYPE"
if [ "$TYPE" = "" ]; thenecho "No int8-type found!"exit -1
fi

$SQL </usr/local/pgsql/lib/int8cmp.sql

$SQL -c  "delete from pg_opclass where opcname='int8_ops'"
OPCLOID=`$SQL -c  "insert into pg_opclass values('int8_ops', $TYPE)" |
cut -f2 -d" "`

OP1=`$SQL -c  "select oid from pg_operator where oprname='<' and
oprleft=$TYPE and oprright=$TYPE"`
OP2=`$SQL -c  "select oid from pg_operator where oprname='<=' and
oprleft=$TYPE and oprright=$TYPE"`
OP3=`$SQL -c  "select oid from pg_operator where oprname='=' and
oprleft=$TYPE and oprright=$TYPE"`
OP4=`$SQL -c  "select oid from pg_operator where oprname='>=' and
oprleft=$TYPE and oprright=$TYPE"`
OP5=`$SQL -c  "select oid from pg_operator where oprname='>' and
oprleft=$TYPE and oprright=$TYPE"`
OP6=`$SQL -c  "select oid from pg_proc where proname='int8cmp' "`


AMOID=`$SQL -c  "select oid from pg_am where amname='btree'"`

echo "OPCLOID=$OPCLOID AMOID=$AMOID OP1..5: $OP1 $OP2 $OP3 $OP4 $OP5
cmp=$OP6"

if [ "$OP6" = "" ]; thenecho "No int8cmp found!"exit
fi

$SQL -c  "insert into pg_amop values ($AMOID, $OPCLOID, $OP1, 1,
'btreesel', 'btreenpage')"
$SQL -c  "insert into pg_amop values ($AMOID, $OPCLOID, $OP2, 2,
'btreesel', 'btreenpage')"
$SQL -c  "insert into pg_amop values ($AMOID, $OPCLOID, $OP3, 3,
'btreesel', 'btreenpage')"
$SQL -c  "insert into pg_amop values ($AMOID, $OPCLOID, $OP4, 4,
'btreesel', 'btreenpage')"
$SQL -c  "insert into pg_amop values ($AMOID, $OPCLOID, $OP5, 5,
'btreesel', 'btreenpage')"
$SQL -c  "insert into pg_amproc(amid, amopclaid, amproc, amprocnum)         values ($AMOID, $OPCLOID, $OP6, 1)"

------------------- end initlongbtree.sh ---------------