Thread

1. View refuses to work after system reboot

   Jake Newren <jake_newren@yahoo.com> — 2001-02-08T01:53:27Z

   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.
   
   
   

2. Re: View refuses to work after system reboot

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-12T17:05:56Z

   "Jake Newren" <jake_newren@yahoo.com> writes:
   > 	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
   
   IIRC, 7.0.* has some rather nasty bugs with nested JOIN clauses, such as
   you have here.  It'd be safer to express the query with a simple "FROM
   wire_tickets, accounts, funds" and put the join conditions in WHERE.
   
   			regards, tom lane