Обсуждение: Trigger based logging alternative to table_log

> I have some code which uses table_log (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to selected
tables.  
> I don't use the restore part, just the logging part.  
> It creates a new table for each table being logged, with several additional columns, and adds triggers to insert rows
inthe new table for changes in the original. 
> The problem is that table_log hasn't been maintained in nearly 10 years, and pgfoundry itself seems to have one foot
inthe grave and one on a banana peel. 
> There are several other systems out there which store the data in hstore or json, which I would probably use if doing
thisfrom scratch.  But I'd rather preserve the existing log tables than either throw away that data, or port it over to
anew format. 
> Is there any better-maintained code out there which would be compatible with the existing schema used by table_log?
 
>Cheers,
>Jeff

Afaik, there is no compatible solution. If tablelog works for you then keep it. Do you miss a feature or why do you
worryabout the unmaintained code base? I think, if there would be a problem with any new version that the developer
wouldfix it. There is also an existing github repo (https://github.com/glynastill/table_log_pl).  

Recently, I have done a comparison of different audit tools to check how good my creation (pgMemento) works compared to
theothers. So I know how most of them work. tablelog for example logs both OLD and NEW. So you got all your data twice.
Othersolutions log either OLD of NEW. tablelog uses only one timestamp field whereas others using two (or a range). 

As tablelog is using history tables with relational layout I would suggest to consider other extensions that do a
similarthing. If you are interested in only logging the data you might check out the temporal_tables extension
(http://pgxn.org/dist/temporal_tables/).In my test it had the least impact to write operations and disk consumption. 

Using hstore or json for logging might sound cool in the first place, but it only has its benefits if you don't want to
adaptthe auditing behaviour to schema changes (like new columns etc.). With pgMemento I decided to go for jsonb but
aftermany hours of programming complex restoring functions I can say that my only real argument of using it now, is
thatI only log values of changed fields. I like that but it makes the trigger overhead bigger. 

Greetings from Berlin
Felix

Jeff Janes schrieb am 27.03.2017 um 19:07:
> I have some code which uses table_log
> (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to
> selected tables.  I don't use the restore part, just the logging
> part.
>
> It creates a new table for each table being logged, with several
> additional columns, and adds triggers to insert rows in the new table
> for changes in the original.
>
> The problem is that table_log hasn't been maintained in nearly 10
> years, and pgfoundry itself seems to have one foot in the grave and
> one on a banana peel.
>
> There are several other systems out there which store the data in
> hstore or json, which I would probably use if doing this from
> scratch.  But I'd rather preserve the existing log tables than either
> throw away that data, or port it over to a new format.
>
> Is there any better-maintained code out there which would be
> compatible with the existing schema used by table_log?

Logical replication maybe?

I don't know which programming language you are using, but the JDBC driver has a nice example:

https://jdbc.postgresql.org/documentation/head/replication.html

The upside to a trigger based solution is, that it's faster.
The downside is, that the replication "consumer" has to be running to ensure the logging

> From: Jeff Janes <jeff.janes@gmail.com>
> To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Sent: Monday, 27 March 2017, 18:08
> Subject: [GENERAL] Trigger based logging alternative to table_log
>
> I have some code which uses table_log (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to selected
tables. I don't use the restore part, just the logging part.  
>
> It creates a new table for each table being logged, with several additional columns, and adds triggers to insert rows
inthe new table for changes in the original. 
>
> The problem is that table_log hasn't been maintained in nearly 10 years, and pgfoundry itself seems to have one foot
inthe grave and one on a banana peel. 
>
>There are several other systems out there which store the data in hstore or json, which I would probably use if doing
thisfrom scratch.  But I'd rather preserve the existing log tables than either throw away that data, or port it over to
anew format. 
>
>Is there any better-maintained code out there which would be compatible with the existing schema used by table_log?

I was in exactly the same situation a few years ago.  As you say ideally we'd move away from table_log - but when the
usersare used to doing things the table_log way and they like it... 

I have a slightly more up to date fork (here: https://github.com/glynastill/pg_table_audit), which as I recall works
finewith 9.6.  In general the whole thing would benefit an overhaul, but I think the effort of moving to a better
formatwould be less. 


I also wrote a pl/pgsql version as mentioned by Felix, but I wasn't ever particularly happy it so stuck with the above
forkwith the intention of switching away to a json format eventually. 

Glyn