Thread: Port Bug Report: psql create/drop table bug

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


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

> System Configuration
> --------------------
>   PostgreSQL version : CVS (few days ago)
> 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.

Index support for int8 was contributed, but it wasn't quite right and
applied to the source tree without testing. I was planning on fixing
the original patches, but will have to now start again with the
current source tree.

Bruce, can you add this to the list of "must fix" items for the v6.5
release? Put my name on it...

                     - Tom

Is this done?


> > System Configuration
> > --------------------
> >   PostgreSQL version : CVS (few days ago)
> > 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.
>
> Index support for int8 was contributed, but it wasn't quite right and
> applied to the source tree without testing. I was planning on fixing
> the original patches, but will have to now start again with the
> current source tree.
>
> Bruce, can you add this to the list of "must fix" items for the v6.5
> release? Put my name on it...
>
>                      - Tom
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

> Is this done?

afaict it is OK. I went back to fix the indices and was not able to
provoke a failure. And I can't recall seeing any reports from others
that there are still problems.

                     - Tom

> > > 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.
> > Index support for int8 was contributed, but it wasn't quite right and
> > applied to the source tree without testing. I was planning on fixing
> > the original patches, but will have to now start again with the
> > current source tree.

--
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California