Thread
-
History
Peter Childs <blue.dragon@blueyonder.co.uk> — 2003-01-31T11:27:43Z
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_attribute a 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(); -
Re: History
Alan Gutierrez <ajglist@izzy.net> — 2003-02-02T00:47:05Z
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
-
Re: History
Will Trillich <will@serensoft.com> — 2003-02-02T05:07:06Z
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/ ! -
Re: History
Oliver Elphick <olly@lfix.co.uk> — 2003-02-02T12:00:38Z
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 -
Oliver Elphick :-)
Thomas Adam <thomas_adam16@yahoo.com> — 2003-02-02T12:09:28Z
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
-
Re: History
Oliver Elphick <olly@lfix.co.uk> — 2003-02-02T12:41:36Z
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 -
Re: History
Peter Childs <blue.dragon@blueyonder.co.uk> — 2003-02-02T17:30:22Z
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
-
Re: History
Alan Gutierrez <ajglist@izzy.net> — 2003-02-03T01:07:42Z
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 -
Re: History
Alan Gutierrez <ajglist@izzy.net> — 2003-02-03T01:09:18Z
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
-
Re: History
Will Trillich <will@serensoft.com> — 2003-02-03T10:06:59Z
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/ !