Thread: Inheritance: delete parent deletes children

Hello,

I'm using inheritance in a way that may be wrong and would appreciate
comments.

In several tables, I want to have record creation and modification
times and user names.

So instead of repeating theses fields in all table definitions, I
created a logging table, which the others inherit:

CREATE TABLE "logging" (
        "log_ct" timestamp(0) without time zone DEFAULT now(),
        "log_mt" timestamp(0) without time zone DEFAULT now(),
        "log_cu" character varying(20) DEFAULT "current_user"(),
        "log_mu" character varying(20) DEFAULT "current_user"()
);

And then other tables like
CREATE TABLE some_table ( ... ) INHERITS (logging);

Fine, I have the logging fields in all other tables.

But I just realized that things actually go into that "logging" table,
not into the child tables. I wonder how the database knows which row
in logging is related to which row in a child table.

Also, I noticed that if I delete a record in the parent table, the
child is also deleted!! This sounds bad!

Is it just a bad idea to use inheritance for this (should I simply
repeat the fields in the definitions of all tables that need them?),
or is there just a detail I should correct? Or is that all fine as
long as I don't accidentally delete records in the parent table?

Thank you for your comments.

Mi

On 2 Oct 2002, M. I. wrote:

> But I just realized that things actually go into that "logging" table,
> not into the child tables. I wonder how the database knows which row
> in logging is related to which row in a child table.

Well, selects, inserts, updates, deletes by default go through the
inheritance tree if you do them on a parent, so select * from
logging will show rows from the child tables.  You can use ONLY
to turn off that behavior (select * from ONLY logging) should
show rows only in that particular table and not the children.

On Fri, 4 Oct 2002, M. I. wrote:

> At 17.37 04.10.02, Stephan Szabo wrote:
>
> >On 2 Oct 2002, M. I. wrote:
> >
> > > But I just realized that things actually go into that "logging" table,
> > > not into the child tables. I wonder how the database knows which row
> > > in logging is related to which row in a child table.
> >
> >Well, selects, inserts, updates, deletes by default go through the
> >inheritance tree if you do them on a parent, so select * from
> >logging will show rows from the child tables.  You can use ONLY
> >to turn off that behavior (select * from ONLY logging) should
> >show rows only in that particular table and not the children.
>
> Oh, I see. Yes, it seems to make sense.
>
> But the Delete is scary. It looks like I could inadvertently empty the
> whole database with a DELETE FROM logging; if I forget that it
> happens to have (many) children.
>
> The day I die, I certainly hope my children will survive me :-) ...
>
> Is there something I can do with rules, to protect me from this
> "exterminator" attitude of the database?

Actually there is the SQL_INHERITANCE configuration option which
changes the default behavior (see docs).

At 17.37 04.10.02, Stephan Szabo wrote:

>On 2 Oct 2002, M. I. wrote:
>
> > But I just realized that things actually go into that "logging" table,
> > not into the child tables. I wonder how the database knows which row
> > in logging is related to which row in a child table.
>
>Well, selects, inserts, updates, deletes by default go through the
>inheritance tree if you do them on a parent, so select * from
>logging will show rows from the child tables.  You can use ONLY
>to turn off that behavior (select * from ONLY logging) should
>show rows only in that particular table and not the children.

Oh, I see. Yes, it seems to make sense.

But the Delete is scary. It looks like I could inadvertently empty the
whole database with a DELETE FROM logging; if I forget that it
happens to have (many) children.

The day I die, I certainly hope my children will survive me :-) ...

Is there something I can do with rules, to protect me from this
"exterminator" attitude of the database?

Thank you for your help,

Mi