History

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/ !