Hello,
I'm writing this as kind of an after thought so I don't have the exact
error message available to me, but I'll try to give you the best information
I can.
The baseline info: Linux 2.4.0 (P200, 32MB Ram, 1.2GB HD) this is the df
output:
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda1 851388 561164 246280 69% /
/dev/hda5 538920 85848 425256 17% /usr/local
/dev/hda6 398124 51672 325891 14% /static
/dev/pda1 19910816 7412112 12498704 37% /mnt
PostgreSQL 7.0.3 installed in the /usr/local/pgsql directory.
I created a view on my database with the following statement:
CREATE VIEW wireticket_out AS SELECT wire_tickets.fund_cusip,
funds.fund_desc, accounts.acct_name, wire_tickets.acct_num,
wire_tickets.cash_actual, wire_tickets.cash_invest,
wire_tickets.date_entered FROM wire_tickets INNER JOIN accounts ON
wire_tickets.acct_num = accounts.acct_num INNER JOIN funds ON
wire_tickets.fund_cusip = funds.fund_cusip
The tables in question:
CREATE SEQUENCE wire_tickets_id_seq
CREATE TABLE wire_tickets (wt_id integer DEFAULT
nextval('wire_tickets_id_seq') NOT NULL PRIMARY KEY, acct_num char(9),
date_entered date, cash_actual numeric(30,2), cash_invest numeric(30,2),
fund_cusip integer)
CREATE TABLE accounts (acct_num char(9) PRIMARY KEY NOT NULL, acct_rep
char(5), acct_name varchar(25) NOT NULL, acct_cli_id integer NOT NULL,
acct_balance numeric(30,2), acct_liquid_cash numeric(30,2))
CREATE TABLE funds (fund_rep char(9) NOT NULL, fund_cusip integer PRIMARY
KEY NOT NULL, fund_manager varchar (20) NOT NULL, fund_desc varchar(40) NOT
NULL, fund_class char(1) NOT NULL, fund_ticker char(5), fund_address integer
NOT NULL)
I ran a couple of SELECT tests on the view, concluded that the output was
what I was looking for and moved on. The next day I attempted to run the
view over an ODBC link and the driver reported that the view was damaged or
was no longer on the system. I telnetted over to the machine and ran tried
to SELECT * FROM wiretickets_out and psql reported that there was an error
with relation (number) and that the psql couldn't continue. I tried to DROP
VIEW and it said that relation wiretickets_out did not exist. I looked in
data/base/ims and the wireticket_out file was still there. After creating a
zero length wireticket_out file psql let me drop the view. I've resorted to
using the SELECT statement straight in the code of my application which
works fine every time. I haven't been able to take the server down again to
try the view again after a reboot so I can't verify if it is a recurring
problem or a one-time deal. I hope this helps out in some way.
