Re: Table referenced by OID in a view?

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".