Port Bug Report: psql create/drop table bug - Mailing list pgsql-ports

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        : James Thompson
Your email address    : jamest@math.ksu.edu

Category        : runtime: front-end
Severity        : non-critical

Summary: psql create/drop table bug

System Configuration
--------------------
  Operating System   : Linux 2.0.35 RH5.1

  PostgreSQL version : CVS (few days ago)

  Compiler used      : gcc 2.7.2.3

Hardware:
---------
P2-333 128MB RAM

Versions of other tools:
------------------------


--------------------------------------------------------------------------

Problem Description:
--------------------
Attempting to create the table with the int8 listed in
the example fails.  psql is left in an odd state until
exit.  Name of failed create table cannot be used.
Upon reentry create table (int4 version) works.

--------------------------------------------------------------------------

Test Case:
----------
Username: postgresql
Password:

Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: obe

obe=> drop table packet;
ERROR:  Relation 'packet' does not exist
obe=> create table
obe->   packet (
obe->         uid                     int8         primary key,
obe->         id                      varchar(30)  unique,
obe->         bill_to_id              varchar(30)  not null,
obe->         ship_to_id              varchar(30)  not null,
obe->         date_entered            date         not null,
obe->         date_modified           date         not null,
obe->         status                  varchar(15)  not null,
obe->         entry_employee          varchar(30)  not null,
obe->         modification_employee   varchar(30)  not null
obe->   );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index packet_pkey for table packet
NOTICE:  CREATE TABLE/UNIQUE will create implicit index packet_id_key for table packet
ERROR:  Can't find a default operator class for type 20.
obe=> create table
obe->   packet (
obe->         uid                     int4         primary key,
obe->         id                      varchar(30)  unique,
obe->         bill_to_id              varchar(30)  not null,
obe->         ship_to_id              varchar(30)  not null,
obe->         date_entered            date         not null,
obe->         date_modified           date         not null,
obe->         status                  varchar(15)  not null,
obe->         entry_employee          varchar(30)  not null,
obe->         modification_employee   varchar(30)  not null
obe->   );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index packet_pkey for table packet
NOTICE:  CREATE TABLE/UNIQUE will create implicit index packet_id_key for table packet
ERROR:  Relation 'packet' already exists
obe=> obe=> drop table packet;
ERROR:  Relation 'packet' does not exist
obe=> \d packet
Couldn't find table packet!
obe=> vacuum analyze;
VACUUM
obe=> create table
obe->   packet (
obe->         uid                     int4         primary key,
obe->         id                      varchar(30)  unique,
obe->         bill_to_id              varchar(30)  not null,
obe->         ship_to_id              varchar(30)  not null,
obe->         date_entered            date         not null,
obe->         date_modified           date         not null,
obe->         status                  varchar(15)  not null,
obe->         entry_employee          varchar(30)  not null,
obe->         modification_employee   varchar(30)  not null
obe->   );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index packet_pkey for table packet
NOTICE:  CREATE TABLE/UNIQUE will create implicit index packet_id_key for table packet
ERROR:  Relation 'packet' already exists
obe=>
obe=> \q
[jamest@calvin Storage]$ sql
Username: postgresql
Password:

Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: obe

obe=> create table
obe->   packet (
obe->         uid                     int4         primary key,
obe->         id                      varchar(30)  unique,
obe->         bill_to_id              varchar(30)  not null,
obe->         ship_to_id              varchar(30)  not null,
obe->         date_entered            date         not null,
obe->         date_modified           date         not null,
obe->         status                  varchar(15)  not null,
obe->         entry_employee          varchar(30)  not null,
obe->         modification_employee   varchar(30)  not null
obe->   );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index packet_pkey for table packet
NOTICE:  CREATE TABLE/UNIQUE will create implicit index packet_id_key for table packet
CREATE
obe=>
obe=> \d packet

Table    = packet
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| uid                              | int4 not null                    |     4 |
| id                               | varchar()                        |    30 |
| bill_to_id                       | varchar() not null               |    30 |
| ship_to_id                       | varchar() not null               |    30 |
| date_entered                     | date not null                    |     4 |
| date_modified                    | date not null                    |     4 |
| status                           | varchar() not null               |    15 |
| entry_employee                   | varchar() not null               |    30 |
| modification_employee            | varchar() not null               |    30 |
+----------------------------------+----------------------------------+-------+
Indices:  packet_id_key
          packet_pkey
obe=>




--------------------------------------------------------------------------

Solution:
---------


--------------------------------------------------------------------------