2011/9/28, Merlin Moncure <mmoncure@xxxxxxxxx>: > 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@xxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
