Thread: History
I need to record history for a table so I have come up with a
trigger (in plpython) to automatically insert records when anything
happerns into a history table. (could also be used for replication etc...)
The problem is that the table is likly to grow very quickly once I put the
trigger on a few tables. Should I use inherrtance and have a different
table for each table I want history for or one table for the lot?
Currently the table looks like this.... (my key may not be an int)
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 |
Indexes: history_tab btree (tab),
history_tab_field btree (tab, field),
history_tab_key btree (tab, "key"),
history_who btree (who)
and for anyone who wants to see the trigger....
I found I could not do this at all in plpgsql.
Peter Childs
PS The Trigger..... for anyone intrested (will work on any table)
CREATE OR REPLACE FUNCTION history_update() RETURNS TRIGGER AS '
if TD["event"] == "INSERT":
lookup = "new"
elif TD["event"] == "DELETE":
lookup = "old"
else:
lookup = "new"
p = plpy.execute(" SELECT CASE i.indproc WHEN (''-''::pg_catalog.regproc) THEN a.attname ELSE
SUBSTR(pg_catalog.pg_get_indexdef(attrelid),POSITION(''('' in pg_catalog.pg_get_indexdef(attrelid))) END as pkey,
a.atttypid::int,c2.relname FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i,
pg_catalog.pg_attributea WHERE c.oid = " + TD["relid"] + " AND c.oid = i.indrelid AND i.indexrelid = c2.oid and
a.attrelid= i.indexrelid and NOT a.attisdropped and i.indisprimary ORDER BY i.indisprimary DESC, i.indisunique DESC,
c2.relname;")
if len(p) > 0:
pkey = TD[lookup][p[0]["pkey"]]
ppkey = p[0]["pkey"]
else:
pkey = ""
ppkey = ""
rel = plpy.execute("select relname from pg_class where oid=" + TD["relid"] + ";")
relname = rel[0]["relname"]
plan = plpy.prepare("INSERT INTO history (tab,field,action,before,after,occured,who,key) values
($1,$2,$3,$4,$5,now(),user,$6);",["text","text","text","text","text","text"])
if TD["event"] == "INSERT":
old = ""
new = pkey
plpy.execute(plan,[relname,ppkey,TD["event"],old,new,pkey])
else:
for key in TD[lookup].keys():
dont = 0
if TD["event"] == "INSERT":
old = ""
new = TD["new"][key]
if new == None:
dont = 1
elif TD["event"] == "UPDATE":
old = TD["old"][key]
new = TD["new"][key]
else:
old = TD["old"][key]
new = ""
if old == None:
old = "Null"
if new == None:
new = "Null"
if not(dont):
plpy.execute(plan,[relname,key,TD["event"],old,new,pkey])
' LANGUAGE 'plpython';
DROP TRIGGER test_history_update ON test;
CREATE TRIGGER test_history_update AFTER INSERT OR UPDATE OR DELETE ON test FOR EACH ROW
EXECUTE PROCEDURE history_update();
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 | > Indexes: history_tab btree (tab), > history_tab_field btree (tab, field), > history_tab_key btree (tab, "key"), > history_who btree (who) 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. Also, you don't need indices on both (tab) and (tab, field), the optimizer will happily use the latter, as it would the former. -- Alan Gutierrez - ajglist@izzy.net http://khtml-win32.sourceforge.net/ - KHTML on Windows
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/ !
On Sun, 2003-02-02 at 00:47, Alan Gutierrez wrote: > 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. Not so; inserts must go into a particular table in the hierarchy, and rows are stored in the particular table to which they belong. However, a SELECT further up the hierarchy will also report rows in the child tables (though not columns added in the child tables) unless the keyword ONLY is used. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Love not the world, neither the things that are in the world. If any man love the world, the love of the Father is not in him...And the world passeth away, and the lust thereof; but he that doeth the will of God abideth for ever." I John 2:15,17
Hello Oliver, I didn't realise that you are on this list too :-) Thanks for your help with the logging problem on the HANTS list, I used script(1) and then filtered out the reverse line feeds, thus: cat /tmp/log/psql.log | col -b > $HOME/psql.log seems ok. Many Thanks (I might see you at the next Hants meet. I took a look at your personal Website -- I'm impressed). Of course, my going to the meet depends on how much uni work I have left :-( -- Thomas Adam ===== Thomas Adam "The Linux Weekend Mechanic" -- www.linuxgazette.com __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com
On Sun, 2003-02-02 at 05:07, will trillich wrote: > but -- is there some way to tell which offspring table the delta > record came from? now THAT would be useful. junk=# select c.relname, d.* from delta as d, pg_class as c where d.tableoid = c.oid; relname | id | created ---------+----+------------ delta | 2 | 2002-05-18 loc | 1 | 2003-02-02 loc | 3 | 2003-02-02 (3 rows) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Love not the world, neither the things that are in the world. If any man love the world, the love of the Father is not in him...And the world passeth away, and the lust thereof; but he that doeth the will of God abideth for ever." I John 2:15,17
On Sunday 02 February 2003 00:47, 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 |
> > Indexes: history_tab btree (tab),
> > history_tab_field btree (tab, field),
> > history_tab_key btree (tab, "key"),
> > history_who btree (who)
>
> 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.
>
> Also, you don't need indices on both (tab) and (tab, field), the
> optimizer will happily use the latter, as it would the former.
From what people are saying Inheratance has little advantage unless you have
loads of simular tables with one or two additional columns. I take your point
on the indexes. Removing some should speed the database up and save disk
space. (Win, Win) must be more carful deciding on my indexes then...
Problem two. The trigger seams to crash the database back end when I try and
run several different quries accross multiple tables in quick sucsession. I
tries putting them in a transaction but this did not help. Run the query
indervidually did not seam to cause any problem just running them one after
the other quickly. I am trying to get it to occur from a script might have to
try it on a another small test box to try and get it too occur again.
Hmm Maybe this ought to go to Bugs. I'll post when I have found some easily
repeatable code (its only seams to be doing it from my Qt application
currently!)
Peter Childs
On Saturday 01 February 2003 23:07, will trillich wrote:
> 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!
Shift back. I am not advocating the use of PostgreSQL inheritance. When
I want to model inheritance I do so explicitly.
CREATE TABLE Person
(person_id INTEGER NOT NULL,
first_name VARCHAR(32),
last_name VARCHAR(32) NOT NULL,
PRIMARY KEY (person_id));
CREATE TABLE Worker
(worker_id int NOT NULL REFERENCES (Person),
date_hired DATE NOT NULL,
PRIMARY KEY (worker_id));
> but -- is there some way to tell which offspring table the delta
> record came from? now THAT would be useful.
There is no good way.
You moved the goal posts. I thought you wanted a history table to store
changes per row. How's that coming along?
--
Alan Gutierrez - ajglist@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows
On Sunday 02 February 2003 06:41, Oliver Elphick wrote: > On Sun, 2003-02-02 at 05:07, will trillich wrote: > > but -- is there some way to tell which offspring table the delta > > record came from? now THAT would be useful. There is no good way. > junk=# select c.relname, d.* from delta as d, pg_class as c where > d.tableoid = c.oid; See what I mean? > relname | id | created > ---------+----+------------ > delta | 2 | 2002-05-18 > loc | 1 | 2003-02-02 > loc | 3 | 2003-02-02 > (3 rows) -- Alan Gutierrez - ajglist@izzy.net http://khtml-win32.sourceforge.net/ - KHTML on Windows
On Sun, Feb 02, 2003 at 07:07:42PM -0600, Alan Gutierrez wrote:
> > 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!
>
> Shift back. I am not advocating the use of PostgreSQL inheritance. When
> I want to model inheritance I do so explicitly.
>
> CREATE TABLE Person
> (person_id INTEGER NOT NULL,
> first_name VARCHAR(32),
> last_name VARCHAR(32) NOT NULL,
> PRIMARY KEY (person_id));
>
> CREATE TABLE Worker
> (worker_id int NOT NULL REFERENCES (Person),
> date_hired DATE NOT NULL,
> PRIMARY KEY (worker_id));
a subset table, i think that's called. right. i've got those
coming and going. does the inheritence thing work similarly? (do
you advocate the avoidance of postgresql inheritance?)
curious aside -- do you not subscribe to the "all instances of
the same field much be names identically" camp? they'd have you
rename worker.worker_id to worker.person_id ... what's your
take?
> > but -- is there some way to tell which offspring table the delta
> > record came from? now THAT would be useful.
>
> There is no good way.
i like oliver's revelation. easy to hobble together a view to do
that and have it be part of the system toolkit...
> You moved the goal posts. I thought you wanted a history table
> to store changes per row. How's that coming along?
w.trillich (lurking and learning) != OP
i'm all for reducing redundancy -- and if i can have all of my
(
id serial,
created date,
modified timestamp(0),
by,
)
fields in one table with others referring to it, i'm all the
happier. is there a significant drawback to doing that kind of
thing? (this looks like exactly that kind of application that
that feature was born and bred for...)
--
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/ !