bug(?) if int8 as primary key

On 23 Dec 1998, David Sauer wrote:

Date: 23 Dec 1998 14:26:06 +0100
From: David Sauer <davids@iol.cz>
To: pgsql-hackers@postgreSQL.org
Subject: [HACKERS] bug(?) if int8 as primary key

My database (pgsql 6.4.1, linux i386, compiled by egcs-1.1.1) does weird things:
david=> create table t( i int primary key );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index t_pkey for table t
CREATE
david=> drop table t;
DROP

*** table t doesn't exist

Just tried on my linux box with pgsql 6.4 and this works ok

david=> create table t( i int8 primary key );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index t_pkey for table t
ERROR: Can't find a default operator class for type 20.

*** exist table t or not ?

david=> create table t( i int primary key );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index t_pkey for table t
ERROR: t relation already exists

*** yes!

david=> drop table t;
ERROR: Relation t Does Not Exist!

*** no!

If I restart pgsql, I can create table t (with int as primary key, not
int8).

Yes, this problem exists also for me in 6.4

Oleg

Happy Xmas,
David

--
* David Sauer, student of Czech Technical University
* electronic mail: davids@iol.cz (mime compatible)

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

david=> create table t( i int8 primary key );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index t_pkey
for table t
ERROR: Can't find a default operator class for type 20.

int8 is a new data type, and I did not create the functions and catalog
entries required to implement a b-tree index yet. Since int8 relies on
compiler support for 8 byte integers, I wasn't sure how many platforms
would actually be able to compile the type. I think we know now that
most platforms do support the data type, so we should have more complete
support for it in the next release.

If I restart pgsql, I can create table t (with int as primary key, not
int8).

Yes, that's funny. It apparently does not quite clean up after itself.
The good news is that you only have to restart your session, not the
postmaster :)

- Tom

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