Re: History - Mailing list pgsql-general

On Sat, Feb 01, 2003 at 06:47:05PM -0600, Alan Gutierrez wrote:
> On Friday 31 January 2003 05:27, you wrote:
> > Should I use inherrtance and have a different table for each
> > table I want history for or one table for the lot?
> >               Table "public.history"
> >  Column  |            Type             | Modifiers
> > ---------+-----------------------------+-----------
> >  tab     | text                        |
> >  field   | text                        |
> >  action  | text                        |
> >  before  | text                        |
> >  after   | text                        |
> >  occured | timestamp without time zone |
> >  key     | text                        |
> >  who     | text                        |
>
> One table for the lot. If you use inheritance, it is my
> understanding that it will be the same thing, since all the
> data will be stored in the base table. The only thing you'd
> remove is the table name.

aha. that clears up a question i had as well. lemme see if i
understand--

    create table delta (
        id serial primary key,
        created date default current_date
    );

    create table loc (
        addr varchar(80),
        st   varchar(4),
        city varchar(30),
        zip  varchar(12),
        nation varchar(3) default 'USA'
    ) inherits ( delta );

    insert into loc(addr,city,zip) values
        ('329 Main','Middlegulch','24680');

then when i

    select * from delta;

i'll see the id (from loc) and the created date as well?  hmm!
and this way it's ONE sequence for all related tables. i bet
that's a nice un-cluttering side-effect. plus, the child tables
would all take up that much LESS space, right? whoa, serious
paradigm shift in the works... cool!

but -- is there some way to tell which offspring table the delta
record came from? now THAT would be useful.

i see that we can tell WHICH tables inherit from others:

    select
        p.relname as inherited,
        c.relname as inheritor
    from
        pg_class p,
        pg_class c
    where
        pg_inherits.inhrelid=c.oid
        and
        pg_inherits.inhparent=p.oid
    ;

but that doesn't say which fields are involved -- is there a
way, aside from finding common fields between inheritor and
inheretee?

nor does it show how to determine which child table planted
records in the parent--

    select * from delta;

 id |  created
----+------------
  2 | 2002-10-21 <= which child record
  3 | 2003-01-15 <= did this particular
  7 | 2003-01-27 <= inherited record
  9 | 2003-02-01 <= come from?

(in this case i can use id, i suppose, and try all tables in
turn... but is there a pg_* table that would enable this if
there wasn't an id field?)

> > Indexes: history_tab btree (tab),
> >          history_tab_field btree (tab, field),
> >          history_tab_key btree (tab, "key"),
> >          history_who btree (who)
> Also, you don't need indices on both (tab) and (tab, field), the
> optimizer will happily use the latter, as it would the former.

quite. history_tab_field is different from history_tab_key, tho,
so they're okay; but either one of them makes history_tab
irrelevant. (all three start with "tab", so the one that's "tab"
alone is redundant.)

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !