Hello-

I'm receiving the following error message:
ERROR:  Relation "log" with OID 3694127 no longer exists

When running the following script (a stripped-down version of what I'm 
really doing, but it demostrates the behavior):

CREATE TABLE log (logid int4);
CREATE TABLE data (dataid int4);
CREATE RULE r_delete_data
   AS ON DELETE TO data
   DO DELETE FROM log WHERE logid=OLD.dataid;
CREATE RULE r_insert_data
   AS ON INSERT TO data
   DO INSERT INTO log (logid) VALUES (NEW.dataid);
INSERT INTO data (dataid) VALUES (1);
DROP TABLE log;
CREATE TABLE log (logid int4);
DELETE FROM data WHERE dataid=1;

My setup: linux v2.4.9, pg v7.1.2

Is this a bug?  If this is *not* a bug in postgres, then any suggestions 
on the right way to go about rebuilding the "log" table above?  In my 
real application, I've dropped and added some columns to "log" (changes 
such that ALTER TABLE isn't able to help).

TIA, Jon

-- 

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham
  Extracta Molï¿½culas Naturais, Rio de Janeiro, Brasil
  email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------



Thu, 30 Aug 2001 00:11:11 -0300
Jon Lapham <lapham@extracta.com.br>
Odd rule behavior?
On Thu, 30 Aug 2001, Jon Lapham wrote:

> I'm receiving the following error message:
> ERROR:  Relation "log" with OID 3694127 no longer exists
> 
> When running the following script (a stripped-down version of what I'm 
> really doing, but it demostrates the behavior):
> 
> CREATE TABLE log (logid int4);
> CREATE TABLE data (dataid int4);
> CREATE RULE r_delete_data
>    AS ON DELETE TO data
>    DO DELETE FROM log WHERE logid=OLD.dataid;
> CREATE RULE r_insert_data
>    AS ON INSERT TO data
>    DO INSERT INTO log (logid) VALUES (NEW.dataid);
> INSERT INTO data (dataid) VALUES (1);
> DROP TABLE log;
> CREATE TABLE log (logid int4);
> DELETE FROM data WHERE dataid=1;
> 
> My setup: linux v2.4.9, pg v7.1.2
> 
> Is this a bug?  If this is *not* a bug in postgres, then any suggestions 
> on the right way to go about rebuilding the "log" table above?  In my 
> real application, I've dropped and added some columns to "log" (changes 
> such that ALTER TABLE isn't able to help).

When you drop and recreate the table, you'll need to drop and recreate the
rules that reference it as well. There's been little to no concensus as to
what the correct behavior should be in such cases: delete the rules when
a referenced table is removed, refuse to remove the table due to the
references, try to reconnect by name (and somehow handle the possibility
that the reference is no longer valid, like say the lack of a logid column
in your case)...


Wed, 29 Aug 2001 21:05:55 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Odd rule behavior?
Stephan Szabo wrote:
> 
> When you drop and recreate the table, you'll need to drop and recreate the
> rules that reference it as well. There's been little to no concensus as to
> what the correct behavior should be in such cases: delete the rules when
> a referenced table is removed, refuse to remove the table due to the
> references, try to reconnect by name (and somehow handle the possibility
> that the reference is no longer valid, like say the lack of a logid column
> in your case)...
> 
> 

Okay, thanks, dropping and recreating the rule worked.

After thinking a bit about this, it would seem that the 'problem' is 
that I was *able* to drop a table that had rules referencing it.  Would 
it be possible to either not allow this, or to issue some type of 
warning message?  Otherwise, you go down the path of this (for me 
anyway) subtle problem.

Also, who should I send documentation patches to about this?  I couldn't 
find any mention of this issue in the "create rule" documentation (or am 
I looking in the wrong place?) or in "Chapter 17: The Postgres Rule 
System".  Hmmm, further perusal shows that Jan Weick is the author of 
the Chapter 17 documentation, I guess I can send text to Jan.

-- 

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham
  Extracta Molï¿½culas Naturais, Rio de Janeiro, Brasil
  email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------


Thu, 30 Aug 2001 09:39:56 -0300
Jon Lapham <lapham@extracta.com.br>
Re: Odd rule behavior?
Jon Lapham writes:

> I'm receiving the following error message:
> ERROR:  Relation "log" with OID 3694127 no longer exists

As a general rule, this won't work in PostgreSQL:

CREATE TABLE foo (...);
CREATE RULE bar ... ON foo ...; # view, trigger, etc.
DROP TABLE foo (...);
CREATE TABLE foo (...);

The rule (view, trigger) references the table by oid, not by name.  (This
is a good thing.  Consider what happens when the newly created table has a
totally different structure.)  The correct fix would be to prevent the
DROP TABLE or drop the rule with it, but it hasn't been done yet.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 30 Aug 2001 15:26:30 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Odd rule behavior?
On Thu, 30 Aug 2001, Jon Lapham wrote:

> Okay, thanks, dropping and recreating the rule worked.
> 
> After thinking a bit about this, it would seem that the 'problem' is 
> that I was *able* to drop a table that had rules referencing it.  Would 
> it be possible to either not allow this, or to issue some type of 
> warning message?  Otherwise, you go down the path of this (for me 
> anyway) subtle problem.

The problem is right now we don't keep track of that sort of information
in any really usable way (apart from scanning all objects that might refer
to an oid).  There've been discussions on -hackers in the past about this
and it should be on the todo list.

> Also, who should I send documentation patches to about this?  I couldn't 
> find any mention of this issue in the "create rule" documentation (or am 
> I looking in the wrong place?) or in "Chapter 17: The Postgres Rule 
> System".  Hmmm, further perusal shows that Jan Weick is the author of 
> the Chapter 17 documentation, I guess I can send text to Jan.

You might as well send patches to pgsql-patches and let everyone see them.


Thu, 30 Aug 2001 08:57:00 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Odd rule behavior?