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_operator opr
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" != "" ]; then
echo "int8-btree ops already registerd."
exit 0
fi

TYPE=`$SQL -c "select oid from pg_type where typname='int8'"`
echo "TYP=$TYPE"
if [ "$TYPE" = "" ]; then
echo "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" = "" ]; then
echo "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 ---------------