Re: History

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(at)serensoft(dot)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/ !