Re: Table referenced by OID in a view?

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(dot)l(dot)johnson(at)cox(dot)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 |
+---------------------------------------------------------------+