Обсуждение: 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

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).
        Happy Xmas,                David

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

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