Обсуждение: Inheritance and trigger/FK propagation
It seems no secret that a child table will not fire a trigger defined on it's parent table. Various posts comment on this. But nowhere could I find a reason for this. Now, I just wonder whether the people who request this are wrong in their assumption that a trigger should fire on the child table, since those requests date from 2004 and are still not implemented? As far as I see propagation has numerous advantages and not-propagation leads to maintenance problems resulting in data inconsistencies in case of designs where triggers should propagate. On the other hand, do any design(s) exist where there should be no propagation? I think the same could be argued for FK propagation. I read in the change logs of 8.4: "Force child tables to inherit CHECK constraints from parents (Alex Hunsaker, Nikhil Sontakke, Tom)." So why not with triggers and FK's? Regards, Davor
On 15/07/10 16:05, Davor J. wrote: > It seems no secret that a child table will not fire a trigger defined on > it's parent table. Various posts comment on this. But nowhere could I find a > reason for this. [snip] > I read in the change logs of 8.4: "Force child tables to inherit CHECK > constraints from parents (Alex Hunsaker, Nikhil Sontakke, Tom)." So why not > with triggers and FK's? My understanding is that it's mostly an implementation limitation. In other words, rather than any fundamental reason why it should not be done, the issue is that nobody has gone and implemented it, tested it, and ironed out the quirks and corner cases yet. -- Craig Ringer
On Thu, Jul 15, 2010 at 4:05 AM, Davor J. <DavorJ@live.com> wrote: > It seems no secret that a child table will not fire a trigger defined on > it's parent table. Various posts comment on this. But nowhere could I find a > reason for this. Do you want your trigger that redirects insert on parent table to the proper child table should run on child tables too?
> On Thu, Jul 15, 2010 at 4:05 AM, Davor J. <DavorJ@live.com> wrote: > > It seems no secret that a child table will not fire a trigger defined on > > it's parent table. Various posts comment on this. But nowhere could I > find a > > reason for this. > > Do you want your trigger that redirects insert on parent table to the > proper child table should run on child tables too? Well, inheritance is not used for partitioning ONLY. So, yes, for *my* use cases I would appreciate being able to tell triggers defined on parent tables to run on child tables when an insert/update/delete happens on a child table. (We use inheritance for auditing and for data aggregation.) But since I am not in a position to code the necessary infrastructure I won't complain about the status quo. Karsten wiki.gnumed.de -- GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl. Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl
""Karsten Hilbert"" <Karsten.Hilbert@gmx.net> wrote in message news:20100719182027.123920@gmx.net... >> On Thu, Jul 15, 2010 at 4:05 AM, Davor J. <DavorJ@live.com> wrote: >> > It seems no secret that a child table will not fire a trigger defined >> > on >> > it's parent table. Various posts comment on this. But nowhere could I >> find a >> > reason for this. >> >> Do you want your trigger that redirects insert on parent table to the >> proper child table should run on child tables too? > > Well, inheritance is not used for partitioning ONLY. So, yes, for *my* > use cases I would appreciate being able to tell triggers defined on > parent tables to run on child tables when an insert/update/delete > happens on a child table. (We use inheritance for auditing and for > data aggregation.) > > But since I am not in a position to code the necessary infrastructure > I won't complain about the status quo. > > Karsten > For me Vick's question just proves that inheritance in relational databases is a complex issue. It shows that trigger propagation is not always desired, contrary to what I believed. But I also have to concur with Karsten. Regards, Davor
"Craig Ringer" <craig@postnewspapers.com.au> wrote in message news:4C3ED37C.1070007@postnewspapers.com.au... > My understanding is that it's mostly an implementation limitation. In > other words, rather than any fundamental reason why it should not be > done, the issue is that nobody has gone and implemented it, tested it, > and ironed out the quirks and corner cases yet. Well... I found it out the hard way :). There are some extra caveats I have come along. There is the very clumsy ALTER TABLE table_name INHERIT(parent_table) which simply presupposes the parent's columns, but doesn't enforce it thereafter? So you can remove an inherited column from the child table when inheritance is made after the child table creation. Anyhow, I thought it could be quite usable for development a row level security system. For example, one could have a table rls_security (rls_owner name, rls_select name, rls_delete name, rls_update name) and a simple trigger: CREATE OR REPLACE FUNCTION rls_inherit_enforce() RETURNS trigger AS $BODY$ DECLARE BEGIN CASE TG_OP WHEN 'UPDATE' THEN IF NOT has_rowaccess(OLD.rls_update || OLD.rls_owner) THEN RAISE EXCEPTION 'No permission for update of row'; END IF; WHEN 'DELETE' THEN IF NOT has_rowaccess(OLD.rls_delete || OLD.rls_owner) THEN RAISE EXCEPTION 'No permission for deletion of row'; END IF; ELSE -- case when access type is not handled RAISE EXCEPTION 'Access type % not handled', TG_OP; END CASE; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE Function has_rowaccess(name[]) would check whether the current/session_user is an admin or if he inherits any of the privileged passed-by users. Now, with a "proper" (?) implementation of inheritance and trigger propagation, RLS could be enforced on any table which would inherit from rls_security. In the end I dumped this approach and implemented something similar to Veil through plpgsql. Personally, I am not really a fan of statement-like (or constraint-based if you like) RLS, like "GRANT user_name privilege_type TO query" or something similar. For table/column privileges it is OK, but once you have to manage many users and many rows, such RLS systems tend to become unmanageable. But then again, this is MHO, and not really a place to discuss RLS. I just wanted to point out that inheritance might also be usable for some RLS implementation. Regards, Davor
On Tue, Jul 27, 2010 at 10:36:16AM +0200, Davor J. wrote: > For me Vick's question just proves that inheritance in relational databases > is a complex issue. It shows that trigger propagation is not always desired, Now that's for sure :-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Tue, Jul 27, 2010 at 10:33:19AM +0200, Davor J. wrote: > Well... I found it out the hard way :). There are some extra caveats I have > come along. There is the very clumsy ALTER TABLE table_name > INHERIT(parent_table) which simply presupposes the parent's columns, but > doesn't enforce it thereafter? So you can remove an inherited column from > the child table when inheritance is made after the child table creation. > > Anyhow, I thought it could be quite usable for development a row level > security system. For example, one could have a table rls_security > (rls_owner name, rls_select name, rls_delete name, rls_update name) and a > simple trigger: While, as you found out, the trigger won't auto-propagate this approach is still useful ! - let all tables inherit from a base table providing the rls fields - write a generic trigger that accesses the rls fields *only* (the table oid of the child table is available in the parent table row, fortunately, which will help making error messages better) - use an external script (or even plpgsql function) to attach said generic trigger to each table - the script does not need to know the list of relevant tables because that can be derived from the schema metadata inside PostgreSQL (they are children of the parent table ;-) While a bit more cumbersome than (on-demand) trigger propagation it is still a fairly clean and close-to-the-ideal solution. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
"Karsten Hilbert" <Karsten.Hilbert@gmx.net> wrote in message news:20100728182051.GJ2726@hermes.hilbert.loc... > On Tue, Jul 27, 2010 at 10:33:19AM +0200, Davor J. wrote: > >> Well... I found it out the hard way :). There are some extra caveats I >> have >> come along. There is the very clumsy ALTER TABLE table_name >> INHERIT(parent_table) which simply presupposes the parent's columns, but >> doesn't enforce it thereafter? So you can remove an inherited column from >> the child table when inheritance is made after the child table creation. >> >> Anyhow, I thought it could be quite usable for development a row level >> security system. For example, one could have a table rls_security >> (rls_owner name, rls_select name, rls_delete name, rls_update name) and a >> simple trigger: > > While, as you found out, the trigger won't auto-propagate > this approach is still useful ! > > - let all tables inherit from a base table providing the rls fields > > - write a generic trigger that accesses the rls fields *only* > (the table oid of the child table is available in the parent table > row, fortunately, which will help making error messages better) Interesting. > > - use an external script (or even plpgsql function) to > attach said generic trigger to each table - the script > does not need to know the list of relevant tables because > that can be derived from the schema metadata inside PostgreSQL > (they are children of the parent table ;-) For completeness, I think this link (http://projects.nocternity.net/index.py/en/psql-inheritance) provides some scripts you mention. I haven't tested them, but I think they are great to start with. > > While a bit more cumbersome than (on-demand) trigger > propagation it is still a fairly clean and > close-to-the-ideal solution. Now if Postgres supported firing triggers on CREATE TABLE (so these scripts could fire "auto-magically"), then it would have been even closer-to-the-ideal :) > > Karsten
On Thu, Jul 29, 2010 at 10:50:02AM +0200, Davor J. wrote: > For completeness, I think this link > (http://projects.nocternity.net/index.py/en/psql-inheritance) provides some > scripts you mention. Very interesting. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346