search

2ndQuadrant / audit-trigger

Simple, easily customised trigger-based auditing for PostgreSQL (Postgres). See also pgaudit.
Other
657 stars 241 forks source link

Audit failing with ERROR out of memory #25

Closed HUSSTECH closed 7 years ago

HUSSTECH commented 7 years ago

Hello all,

Just want to log this issue that I faced when trying to implement this advanced audit trigger for my application.

TL;DR summary: The trigger results in out of memory errors in pg, due to request sizes in the 100s of mb.

As is common these days, I work on my dev machine before I move forward to other environments. As such compared to prod my machine is under resourced, however it's certainly enough to function well for a single client while developing.

work_mem
19MB

shared_buffers
512MB

temp_buffers
8MB

effective_cache_size
4GB

But the pg log shows a request of over 200mb when the trigger function attempts to run. Causing the memory issue.

2016-11-24 15:02:27 GMT ERROR   out of memory   
2016-11-24 15:02:27 GMT DETAIL  Failed on request of size 203437246.    
2016-11-24 15:02:27 GMT CONTEXT PL/pgSQL function if_modified_func() line 51 at assignment  
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
            SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"  
            PL/pgSQL function if_modified_func() line 58 at SQL statement   
2016-11-24 15:02:27 GMT STATEMENT   UPDATE company SET name=('test'),full_name=('TBU4'),active_flag=(true) WHERE id=(111)

Even with my under resourced machine, I cannot believe that a request of 200mb+ is normal for an update of a single row of a table. Also, the multiple log lines SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)" are also suspicious to me, but when I enabled statement logging I only saw 1 actual insert attempted.

I could not find a resolution to my issue after all day of looking on dba stackexchange and going through the pg mailing list archives. As many people seem to have implemented this correctly, I think I must have something configured incorrectly somewhere. But unfortunately, until I find a solution, I will go back to a more basic audit.

ringerc commented 7 years ago

I'd say you created the audit trigger on the audit table, so it's trying to audit its self in infinite recursion.​

HUSSTECH commented 7 years ago

You're spot on there! Saw your reply and with a fresh look the next day I saw the trigger being added to the audit table deep in my migration script. Glad that's solved, and I learned some things about the pg system and memory settings along the way. Not a total loss. Thanks for taking the time to reply @ringerc.