Thread: Table referenced by OID in a view?
I've got a view that is currently reporting that a table it uses, with a specific OID, no longer exists.
The table does exist, however it _may_ have been droped and recreated, or coloumns added/removed.
I take it views reference tables not by the table name but by OID?
MIKE WEAVER
Software Developer
5, 42 Ladner Street
O'Connor, WA, 6163
All correspondence:
PO Box Y3502
East St Georges Terrace
Perth WA 6832
P: (+618) 9331 2700
F: (+618) 9331 3733
M: 0419 299 170
W: http://www.corpusglobe.com/
E: mweaver@corpusglobe.com
This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You.
Micheal, > I've got a view that is currently reporting that a table it uses, > with a > specific OID, no longer exists. > The table does exist, however it _may_ have been droped and > recreated, or > coloumns added/removed. > > I take it views reference tables not by the table name but by OID? Yes. For optimization purposes, Views refer to all tables and functions they reference by OID. Therefore, if you drop and re-create a table, you need to regenerate all views dependent on it. The same is true of views that reference other views. In 7.3, this is helped by a "dependency tracker" which will prevent you from dropping some database objects on which others depend. By 7.4, the postgres hackers hope to have tools that will help automate the process of updating dependant objects. Until then, it is up to you to be careful. For my part, when I am still "playing with" the schema of a database, I usually do my modifications by dumping it to a text file, making my changes there, and reloading. By the time you get to beta-testing, when the database has real data, you *should* no longer be modifying the tables other than adding the occasional column (although I realize that in the real world we don't necessarily get to decide our own development schedules). Of course, more in-place modifications (DROP COLUMN, ADD CONSTRAINT ... FOREIGN KEY, etc.) are supported in 7.3 than were in 7.2, but you should still be halted if you try to drop a column used in a view. -Josh Berkus P.S. You'll get more responses on the list if you send your mail as "plain text" and *not* "html" or "rich text e-mail".
I don't know about other commercial RDBMSs, but some have this: DROP TABLE <foo> CASCADE Thus, any views, constraints, triggers, etc, that refer to <foo> get dropped automatically when the table is dropped. This seems to be a step beyond what you mention for v7.4. (Of course, all the deleteted objects are listed as they are deleted, so you can easily recreate them... On Fri, 2002-12-20 at 10:46, Josh Berkus wrote: > Micheal, > > > I've got a view that is currently reporting that a table it uses, > > with a > > specific OID, no longer exists. > > The table does exist, however it _may_ have been droped and > > recreated, or > > coloumns added/removed. > > > > I take it views reference tables not by the table name but by OID? > > Yes. For optimization purposes, Views refer to all tables and > functions they reference by OID. Therefore, if you drop and re-create > a table, you need to regenerate all views dependent on it. The same is > true of views that reference other views. > > In 7.3, this is helped by a "dependency tracker" which will prevent you > from dropping some database objects on which others depend. By 7.4, > the postgres hackers hope to have tools that will help automate the > process of updating dependant objects. > > Until then, it is up to you to be careful. For my part, when I am > still "playing with" the schema of a database, I usually do my > modifications by dumping it to a text file, making my changes there, > and reloading. By the time you get to beta-testing, when the database > has real data, you *should* no longer be modifying the tables other > than adding the occasional column (although I realize that in the real > world we don't necessarily get to decide our own development > schedules). > > Of course, more in-place modifications (DROP COLUMN, ADD CONSTRAINT > ... FOREIGN KEY, etc.) are supported in 7.3 than were in 7.2, but you > should still be halted if you try to drop a column used in a view. -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "My advice to you is to get married: If you find a good wife, | | you will be happy; if not, you will become a philosopher." | | Socrates | +---------------------------------------------------------------+
Ron, > I don't know about other commercial RDBMSs, but some have this: > DROP TABLE <foo> CASCADE > > Thus, any views, constraints, triggers, etc, that refer to <foo> get > dropped automatically when the table is dropped. This seems to be a > step beyond what you mention for v7.4. (Of course, all the deleteted > objects are listed as they are deleted, so you can easily recreate > them... Hmmm. Not sure this is planned; I personally would not want to *ever* use a "DROP TABLE CASCADE". Not up to me, though. -- -Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> I don't know about other commercial RDBMSs, but some have this:
>> DROP TABLE <foo> CASCADE
> Hmmm. Not sure this is planned; I personally would not want to *ever* use a
> "DROP TABLE CASCADE". Not up to me, though.
It's not "planned", it's already done in 7.3.
regards, tom lane
Ron, Tom > It's not "planned", it's already done in 7.3. Geez! Isn't open source amazing? What service! <grin> -- -Josh Berkus Aglio Database Solutions San Francisco
On Fri, 2002-12-20 at 19:31, Josh Berkus wrote: > Ron, > > > I don't know about other commercial RDBMSs, but some have this: > > DROP TABLE <foo> CASCADE > > > > Thus, any views, constraints, triggers, etc, that refer to <foo> get > > dropped automatically when the table is dropped. This seems to be a > > step beyond what you mention for v7.4. (Of course, all the deleteted > > objects are listed as they are deleted, so you can easily recreate > > them... > > Hmmm. Not sure this is planned; I personally would not want to *ever* use a > "DROP TABLE CASCADE". Not up to me, though. Why not? Without it, I'd have to scan thru the metadata myself. This just does what I want, without me telling The Engine how to do it. Sort of like SQL itself... -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "My advice to you is to get married: If you find a good wife, | | you will be happy; if not, you will become a philosopher." | | Socrates | +---------------------------------------------------------------+
On Fri, 2002-12-20 at 19:33, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: > >> I don't know about other commercial RDBMSs, but some have this: > >> DROP TABLE <foo> CASCADE > > > Hmmm. Not sure this is planned; I personally would not want to *ever* use a > > "DROP TABLE CASCADE". Not up to me, though. > > It's not "planned", it's already done in 7.3. Ask, and it shall be given... It this part of the SQLxx standard? -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "My advice to you is to get married: If you find a good wife, | | you will be happy; if not, you will become a philosopher." | | Socrates | +---------------------------------------------------------------+
>
> P.S. You'll get more responses on the list if you send your mail as
> "plain text" and *not* "html" or "rich text e-mail".
>
My humble apology for my heinous crime. - oh the shame! ;(
hehe,
No. I do apologies for my error, I hate getting RTF or HTML mail at home, I should have been more careful. Thanks for the heads-up. :P
Also a big thanks you to _all_ of you that seem to post 80% of the replies to the PostgreSQL mailing lists.
I don't know how you get any work done in-between answering these emails.
Mike.
Ron Johnson <ron.l.johnson@cox.net> writes:
> It this part of the SQLxx standard?
Yup.
regards, tom lane
Micheal, > > P.S. You'll get more responses on the list if you send your mail as > > "plain text" and *not* "html" or "rich text e-mail". > > > > My humble apology for my heinous crime. - oh the shame! ;( For the benefit of novices on this list: Some of the readers on this list use the old-but-reliable Unix mail programs Pine or Mutt to read their e-mail. These readers may simply delete HTML mail unread. The problem with this for you, the novice, is that some of these participants are major PostgreSQL contributors and their help might be invaluable to you. As such, you will get more and better feedback on your problem if you send "plain text" e-mail. FYI. -Josh Berkus