I'm using version 7.0.3 on Red Hat 7.0. I built the package using the
defaults. I have also experienced the problem on Red Hat 6.2 Here are
the deatils of the systems:
System 1:
Red Hat 6.2, Dell Intel PIII/I810 700/133MHz, 128MB, 10GB IDE
Postgresql 7.0.3 built from .tgz using ./configure && make
System 2:
Red Hat 7.0 Homebuilt Dual PIII800/133 512MB, 1x20GB IDE, 1x30gb IDE
Postgresql 7.0.3 built from .tgz using ./configure && make
To reproduce the problem, write a simple sql file with 2 tables liek
this:
drop table1;
create table1 (
v1 integer,
v2 integer
);
create unique index table1_index on table1(v1,v2);
drop table2;
create table2 (
vi intteger,
v2 varhcar(128),
primary key (v2)
);
Run the script by redirecting from stdin to psql - you should get a
delayed error like
ERROR: relation table1 does not exist.
Start psql and create the first table by hand, rerun the script.
Eventually you should get 'file not found' errors on the first table.
I've attached a sample .sql file and transcript.
If I create the tables before I drop them, everything seems to work
fine.
Bob Weiler
Perfect Sense Software
[rotweiler@pss5 schema]$ psql --version
psql (PostgreSQL) 7.0.3
contains readline, history support
Portions Copyright (c) 1996-2000, PostgreSQL, Inc
Portions Copyright (c) 1996 Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.
[rotweiler@pss5 schema]$ type psql
psql is hashed (/usr/local/pgsql/bin/psql)
[rotweiler@pss5 schema]$ type pg_ctl
pg_ctl is /usr/local/pgsql/bin/pg_ctl
[rotweiler@pss5 schema]$ psql
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
rotweiler=> drop table unique_id;
DROP
rotweiler=> drop table domain;
DROP
rotweiler=> \q
[rotweiler@pss5 schema]$ psql < postgresql.sql
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'domain_pkey' for table
'domain'
ERROR: Relation 'unique_id' does not exist
[rotweiler@pss5 schema]$ psql < postgresql.sql
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'domain_pkey' for table
'domain'
ERROR: Relation 'unique_id' does not exist
[rotweiler@pss5 schema]$ psql < postgresql.sql
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'domain_pkey' for table
'domain'
ERROR: Relation 'unique_id' does not exist
[rotweiler@pss5 schema]$ psql < postgresql.sql
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'domain_pkey' for table
'domain'
ERROR: Relation 'unique_id' does not exist
...
[rotweiler@pss5 schema]$ psql < postgresql.sql
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'domain_pkey' for t
able 'domain'
ERROR: Relation 'unique_id' does not exist
[rotweiler@pss5 schema]$ psql < postgresql.sql
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'domain_pkey' for t
able 'domain'
ERROR: Relation 'unique_id' does not exist
[rotweiler@pss5 schema]$ psql
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
rotweiler=> select * from unique_id;
ERROR: Relation 'unique_id' does not exist
rotweiler=> select * from domain;
ERROR: Relation 'domain' does not exist
rotweiler=> create table unique_id(
rotweiler(> session integer,
rotweiler(> id integer);
CREATE
rotweiler=> \q
[rotweiler@pss5 schema]$ psql < postgresql.sql
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'domain_pkey' for t
able 'domain'
ERROR: Relation 'domain' does not exist
[rotweiler@pss5 schema]$ psql < postgresql.sql
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'domain_pkey' for t
able 'domain'
NOTICE: mdopen: couldn't open unique_id: No such file or directory
NOTICE: RelationIdBuildRelation: smgropen(unique_id): No such file or directory
NOTICE: mdopen: couldn't open unique_id: No such file or directory
ERROR: Relation 'domain' does not exist
[rotweiler@pss5 sc
/*
* Cmail schema
*/
/*
* The ux table provides the basis for application driven unique ID
* generation. Each instance of the application obtains a 'session'
* and assigns id's consecutively in that session. When it terminates,
* the session id and next available id in that session are written
* back to the DB.
*/
drop table unique_id;
create table unique_id (
session integer,
id integer
);
create unique index unique_id_index on unique_id(session, id);
drop table domain;
create table domain(
id integer,
name varchar(128),
primary key(name)
);
