Re: Inheritance Algebra - Mailing list pgsql-general

On 12/23/05, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
> On Thu, Dec 22, 2005 at 05:05:49PM -0700, Trent Shipley wrote:
>
> > On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote:
>
> > > I would assume quite a few people would use table
> > > inheritance in a simple way were it available in a more
> > > convenient fashion: to transport fields, primary and foreign
> > > keys to child tables.
> >
> > I am not clear on why this sort of scenario benefits more from CREATE TABLE's
> > "INHERITS" clause than the "LIKE" clause
> Because the inherited fields are aggregated in the parent
> table.
>
> Imagine a database:
>
> create table narrative_base (
>         narrative text
> );
>
> create table memo (
>         author text default CURRENT_USER
> ) inherits (narrative_base);
>
> create table ads (
>         fk_campaign integer references campaigns(pk)
> ) inherits (narrative_base);
>
> ... more child tables
>
> ... even more child tables

We use something very similar to this to track user transactions
(circulation of material, billings, etc.) in our (developing) ILS
(Integrated Library System), OpenILS.  But we take it even further
with multiple levels of inheritance (simplified):

CREATE TABLE payment (
   pid serial,
   xact bigint,
   ptime timestamptz,
   pamount numeric(10,2)
);

CREATE TABLE bnm_payment ( -- "brick-n-mortar"
   accepting_user int
) INHERITS (payment);

CREATE TABLE bnm_desk_payment (
   cash_drawer_id text
) INHERITS (bnm_payment);

CREATE TABLE check_payment (
   check_number text
) INHERITS (bnm_desk_payment);

... and so on ...

>
> Then we go on merrily inserting all sorts of stuff into the
> narrative_base child tables for two years.
>
> Now the boss asks me: "Has anyone ever written anything with
> 'PostgreSQL' in it in our company ?"
>
> So I go
>
>  select tableoid, * from narrative_base where narrative ilike '%postgresql';
>
> et voila. I don't have to remember all the tables
> potentially containing narrative and join them.


Precisely.  We can report on daily payments at each of the "levels"
all the way down to payment type, or just get a total for the cash
drawers, or a grand total.  Billing line items are structured
similarly, so it's also very easy to grab a summary bill for a user
and "explode" it for a detailed view using tableoid.

>
> Now, if this properly transporter primary and foreign keys
> to child tables I could add
>
>         pk serial primary key
>
> to narrative_base and be done with primary keys for all
> children.
>
> Get the drift ?

While I originally wanted this as well, by using a serial for the
"pid" field in the root table you've essentially go that.  While
cross-table unique indexes aren't available now, I know that some
smart people are thinking about them.  Most of the time it comes up in
relation to O*'s "global indexes" on partitioned tables, and in that
sense is not of much use due to performance implications, but I think
/our/ use makes a strong case for such a beast.

That said, I believe I have a workaround that may suffice if you
absolutely require constraint enforced globally unique PKEYs.  This
example uses the pid field from the root table (that is inherited
everywhere) to track uniqueness.

CREATE TABLE payment_entities (
  id bigint primary key,
  toid oid  -- tableoid
);

CREATE FUNCTION global_unique_payment_entity RETURNS TRIGGER AS $$
  BEGIN
    BEGIN
      insert into entities (id, toid) values (NEW.pid, TG_RELID);
    EXCEPTION
      WHEN UNIQUE_VIOLATION THEN
        RAISE EXCEPTION 'Ack!  Key % already exists as a payment ID', NEW.pid;
      END;
    RETURN NEW;
  END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER global_unique_entity_payment_trig
  BEFORE INSERT ON cash_payment
    FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity();

CREATE TRIGGER global_unique_entity_payment_trig
  BEFORE INSERT ON check_payment
    FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity();

CREATE TRIGGER global_unique_entity_payment_trig
  BEFORE INSERT ON credit_card_payment
    FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity();

etc...

That doesn't cover UPDATEs of course, but that should be easy enough
to do. It does, however, give you a simple "type" lookup table if you
happen to have a pid in hand and want to know what it is.

Thoughts?

>
> Karsten
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org