Re: 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)...