Re: [Solved] Generic logging system for pre-hstore using plperl triggers

On Wed, Sep 28, 2011 at 11:45 AM, Diego Augusto Molina
<diegoaugustomolina@gmail.com> wrote:
> 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).

I disagree.  unnest() and array_agg() (or, even better, array()
constructor syntax) are an absolute joy to work with and thinking in a
more functional way, which is usually the key to making things run
quickly.  Also both functions are trivial to emulate in userland for
compatibility.  Arrays of composites IIRC only go back to 8.3 so  that
would be a true stopper for any solution in that vein.

As for the rest of it, I'd be looking to try and come up with an all
sql implementation.  Also you should give an honest comparison between
what you've come up with vs. this:
http://pgfoundry.org/projects/tablelog/.

merlin