Re: [GENERAL] table versioning approach (not auditing)

2014-10-10 Thread Jim Nasby
On 10/7/14, 10:40 PM, Gavin Flower wrote: Yeah, I'm pretty convinced at this point that history/versioning should be built on top of a schema that always contains the current information, if for no other reason than so you always have a PK that points to what's current in addition to your hist

Re: [GENERAL] table versioning approach (not auditing)

2014-10-07 Thread Gavin Flower
On 08/10/14 13:29, Jim Nasby wrote: On 10/6/14, 6:10 PM, Gavin Flower wrote: Even if timestamps are used extensively, you'd have to be careful joining on them. You may have information valid at T1 and changing at T3, but the transaction has T2, where T1 < T2 < T3 - the appropriate set of data

Re: [GENERAL] table versioning approach (not auditing)

2014-10-07 Thread Jim Nasby
On 10/6/14, 6:10 PM, Gavin Flower wrote: Even if timestamps are used extensively, you'd have to be careful joining on them. You may have information valid at T1 and changing at T3, but the transaction has T2, where T1 < T2 < T3 - the appropriate set of data would be associated with T1, would w

Re: [GENERAL] table versioning approach (not auditing)

2014-10-06 Thread Gavin Flower
On 07/10/14 10:47, Jim Nasby wrote: On 10/2/14, 9:27 AM, Adam Brusselback wrote: i've also tried to implement a database versioning using JSON to log changes in tables. Here it is: https://github.com/fxku/audit[https://github.com/fxku/audit][https://github.com/fxku/audit[https://github.com/fxku

Re: [GENERAL] table versioning approach (not auditing)

2014-10-06 Thread Jim Nasby
On 10/2/14, 9:27 AM, Adam Brusselback wrote: i've also tried to implement a database versioning using JSON to log changes in tables. Here it is: https://github.com/fxku/audit[https://github.com/fxku/audit][https://github.com/fxku/audit[https://github.com/fxku/audit]]

Re: [GENERAL] table versioning approach (not auditing)

2014-10-02 Thread Adam Brusselback
transfer the code soon to a more public repo on GitHub. As far as >> I see I have to create an organization for that. >> >> Cheers >> Felix >> >> *Gesendet:* Mittwoch, 01. Oktober 2014 um 17:09 Uhr >> >> *Von:* "Adam Brusselback" >> *An:*

Re: [GENERAL] table versioning approach (not auditing)

2014-10-02 Thread Adam Brusselback
"Adam Brusselback" > *An:* "Felix Kunde" > *Cc:* "pgsql-general@postgresql.org" > *Betreff:* Re: [GENERAL] table versioning approach (not auditing) > I know we're kinda hijacking this thread, so sorry for that. If you'd > like to do that, i'

Re: [GENERAL] table versioning approach (not auditing)

2014-10-01 Thread Adam Brusselback
here to go next. I think switching to JSONB > for example will be easy, as it offers the same functions than JSON afaik. > > > Gesendet: Dienstag, 30. September 2014 um 21:16 Uhr > Von: "Adam Brusselback" > An: "Felix Kunde" > Cc: "pgsql-general@post

Re: [GENERAL] table versioning approach (not auditing)

2014-10-01 Thread Felix Kunde
014 um 21:16 Uhr Von: "Adam Brusselback" An: "Felix Kunde" Cc: "pgsql-general@postgresql.org" Betreff: Re: [GENERAL] table versioning approach (not auditing) Felix, I'd love to see a single, well maintained project. For example, I just found yours, and gave it a sho

Re: [GENERAL] table versioning approach (not auditing)

2014-09-30 Thread Adam Brusselback
det: Montag, 29. September 2014 um 23:25 Uhr > Von: "Abelard Hoffman" > An: "Felix Kunde" > Cc: "pgsql-general@postgresql.org" > Betreff: Re: [GENERAL] table versioning approach (not auditing) > > Thank you Felix, Gavin, and Jonathan for your respons

Re: [GENERAL] table versioning approach (not auditing)

2014-09-30 Thread Felix Kunde
25 Uhr Von: "Abelard Hoffman" An: "Felix Kunde" Cc: "pgsql-general@postgresql.org" Betreff: Re: [GENERAL] table versioning approach (not auditing) Thank you Felix, Gavin, and Jonathan for your responses.   Felix & Jonathan: both of you mention just storing deltas. B

Re: [GENERAL] table versioning approach (not auditing)

2014-09-29 Thread Jonathan Vanasco
On Sep 29, 2014, at 4:06 PM, Nick Guenther wrote: > A newbie tangent question: how do you access the transaction serial? Is it > txid_current() as listed in > http://www.postgresql.org/docs/9.3/static/functions-info.html? My implementations were ridiculously simple/naive in design, and existed

Re: [GENERAL] table versioning approach (not auditing)

2014-09-29 Thread Abelard Hoffman
the database and commit their changes to the production state. > As I've got a unique history ID for each table and each row, I should be > able to map the affected records. > > Have a look and tell me what you think of it. > > Cheers > Felix > > > Gesendet:

Re: [GENERAL] table versioning approach (not auditing)

2014-09-29 Thread Nick Guenther
On September 29, 2014 11:08:55 AM EDT, Jonathan Vanasco wrote: > >- use a "transaction" log. every write session gets logged into the >transaction table (serial, timestamp, user_id). all updates to the >recorded tables include the transaction's serial. then there is a >"transactions" table,

Re: [GENERAL] table versioning approach (not auditing)

2014-09-29 Thread Jonathan Vanasco
In the past, to accomplish the same thing I've done this: - store the data in hstore/json. instead of storing snapshots, I store deltas. i've been using a second table though, because it's improved performance on reads and writes. - use a "transaction" log. every write session gets logged in

Re: [GENERAL] table versioning approach (not auditing)

2014-09-29 Thread Felix Kunde
tag, 29. September 2014 um 04:00 Uhr Von: "Abelard Hoffman" An: "pgsql-general@postgresql.org" Betreff: [GENERAL] table versioning approach (not auditing) Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when

Re: [GENERAL] table versioning approach (not auditing)

2014-09-28 Thread Gavin Flower
On 29/09/14 15:00, Abelard Hoffman wrote: Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when necessary (customer service makes an incorrect edit, etc.). I have studied these two audit trigger examples: https://wiki.postgresq

[GENERAL] table versioning approach (not auditing)

2014-09-28 Thread Abelard Hoffman
Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when necessary (customer service makes an incorrect edit, etc.). I have studied these two audit trigger examples: https://wiki.postgresql.org/wiki/Audit_trigger https://wiki.postgres