Re: [Solved] Generic logging system for pre-hstore using plperl triggers - Mailing list pgsql-general

2011/9/28, Merlin Moncure <mmoncure@gmail.com>:
> Yup -- I get it now.  Well, one point about this is that it seems
> directed towards your personal requirements. This is a very 'heavy'
> audit system that will not be suitable for high performance
> transactional systems.  That said, it looks well thought out.  Storing
> only the changed fields is pretty clever, but I can't help but wonder
> if you're better off using arrays for that purpose:
>
> create type audit_field_t as (field text, old_value text, new_value text);
>
> and inside the audit table itself having
>   fields audit_field_t,
>
> and, if at all possible, constructing the array of audit fields in a
> single expression.  This will be much more compact than one record per
> field -- normally, arrays in table definitions tend to be bad mojo but
> this is one case they could be useful.  Audit records are WORM, 'Write
> Once Read Maybe', so compactness is important.   Obviously, for 9.0+,
> I would be rigging a solution around hstore for an 'all sql' solution
> which is usually better if you can get away with it.
>
> merlin
>

Well that sounds pretty fair to me. But that flow would not allow me
to make partial indexes on primary key fields. As you can see in the
"audet" table, there's a column named "is_pk" which tells if that
column was considered a primary key at the moment of the logging.
Normally there's no indexes, but when I have to make some audits I do
the following:
1) Dump the audits.
2) Restore somewhere else.
3) Generate some indexes on: timestamp, schema|table, field|is_pk and
id (I think, I've got the procedure annotated too, but not here hehe).
This indexing is a pain sometimes but even adding it to the time it
takes to run one query it is really cheap. Making the indexes gets far
more necessary if you run more than one query (which is probably the
case).
I had considered the solution you're posting, but it would get a
_real_ pain to run a query with 'unnest's and 'array_agg's. Also, note
that some of these may not be available in versions of PostgreSQL
prior to 8.4 (I think), so if you're planning to track the tupple you
won't be able to do it in clear (maybe using temp tables).

But! all those arguments above get beat by only one you asserted: that
"WORM" thing. You are defintly right about that. Logging in the
majority of the cases should be meaningful, light to run,
compact/compressed, and rotated so that it doesn't take up all your
space with time.

Having said that, I'm going to take your advice for the next version,
which I hope that also checks some TODO's in the list. When I get home
I'll send the current code attached and when I get some fresh air at
work I'll make the changes and post the new version.

Any other ideas for the new version? (get some previews in the TODO
list at the top of the perl trigger function in the attachment of the
next mail).

--
Diego Augusto Molina
diegoaugustomolina@gmail.com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html