Skip site navigation (1) Skip section navigation (2)

Re: Inheritance and trigger/FK propagation

From: "Davor J(dot)" <DavorJ(at)live(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Inheritance and trigger/FK propagation
Date: 2010-07-29 08:50:02
Message-ID: i2rfdj$4d8$ (view raw or whole thread)
Lists: pgsql-general
"Karsten Hilbert" <Karsten(dot)Hilbert(at)gmx(dot)net> wrote in message 
> 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 ;-)

For completeness, I think this link 
( 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 

In response to


pgsql-general by date

Next:From: Ivan Sergio BorgonovoDate: 2010-07-29 08:52:17
Subject: Re: Which CMS/Ecommerce/Shopping cart ?
Previous:From: Richard HuxtonDate: 2010-07-29 07:19:58
Subject: Re: Comparison of Oracle and PostgreSQL full text search

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group