Postgres Pro
Downloads
Home   >   mailing lists

Thread: What causes a table's oid to change?

What causes a table's oid to change?

From
Richard Ray
Date:
I have a table with many views. I did something to cause the table to change
oid. I don't know what I did. When I try to access the data using one of
these views I get "ERROR:  Relation "documents" with OID 24389201 no longer
exists". The table is fine. If I drop the view and recreate it it works fine.
Can you tell me some of the common thing that would cause this? Can I "reset"
the oid?

Thanks
Richard

Re: What causes a table's oid to change?

From
Josh Berkus
Date:
Richard,

> I have a table with many views. I did something to cause the table to
> change oid. I don't know what I did. When I try to access the data using
> one of these views I get "ERROR:  Relation "documents" with OID 24389201 no
> longer exists". The table is fine. If I drop the view and recreate it it
> works fine. Can you tell me some of the common thing that would cause this?
> Can I "reset" the oid?

This is caused by dropping and re-creating the table.   Whenever you drop and
re-created a table, all views based on the table must be dropped and
re-created as well.   Someday, we'll fix this, but right now Postgres'
performance optimizations depend on it.

--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco

Re: What causes a table's oid to change?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> This is caused by dropping and re-creating the table.   Whenever you drop and
> re-created a table, all views based on the table must be dropped and
> re-created as well.   Someday, we'll fix this,

7.3 fixes it, if you consider a fix to be disallowing drop of a table
that has dependent views...

regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create view v1 as select * from t1;
CREATE VIEW
regression=# drop table t1;
NOTICE:  rule _RETURN on view v1 depends on table t1
NOTICE:  view v1 depends on rule _RETURN on view v1
ERROR:  Cannot drop table t1 because other objects depend on it
        Use DROP ... CASCADE to drop the dependent objects too
regression=# drop table t1 cascade;
NOTICE:  Drop cascades to rule _RETURN on view v1
NOTICE:  Drop cascades to view v1
DROP TABLE
regression=# select * from v1;
ERROR:  Relation "v1" does not exist

            regards, tom lane

Re: What causes a table's oid to change?

From
"Josh Berkus"
Date:
Tom,

> 7.3 fixes it, if you consider a fix to be disallowing drop of a table
> that has dependent views...
>
> regression=# create table t1 (f1 int);
> CREATE TABLE
> regression=# create view v1 as select * from t1;
> CREATE VIEW
> regression=# drop table t1;
> NOTICE:  rule _RETURN on view v1 depends on table t1
> NOTICE:  view v1 depends on rule _RETURN on view v1

Cool!  This wouldn't be much of a fix if you hadn't implemented DROP
COLUMN as well, but with that, it helps a lot.

One of the most gratifying things about being part of the Postgresql
commmunity is being able to see the (rapid) progress happen.

-Josh