Skip site navigation (1) Skip section navigation (2)

View refuses to work after system reboot

From: "Jake Newren" <jake_newren(at)yahoo(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: View refuses to work after system reboot
Date: 2001-02-08 01:53:27
Message-ID: HDEIJFCPILGMDEEPGGNNOEONDEAA.jake_newren@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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.


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-02-08 02:12:51
Subject: Re: Problem displaying functions and views in 7.1b3
Previous:From: Hiroshi InoueDate: 2001-02-08 01:39:47
Subject: Re: Problem displaying functions and views in 7.1b3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group