Postgres Pro
Downloads
Home   >   mailing lists

Thread: DDL & DML Logging doesn't work for calling functions

DDL & DML Logging doesn't work for calling functions

From
MURAT KOÇ
Date:
Hi list,
 
Version is PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit. 
 
We set logging parameters as below for DDL & DML Logging: 
logging_collector = on
log_statement = mod
log_line_prefix = '%t--%d--%u--%h--%a--%i--%e'
 
Server log file consists as below sample log information:
2011-11-28 16:35:23 EET--postgres--postgres--136.10.195.51--pgAdmin III - Query Tool--idle--00000LOG:  statement: update test set t=10 where t=3;
2011-11-28 16:35:34 EET--postgres--postgres--136.10.195.51--pgAdmin III - Query Tool--idle--00000LOG:  statement: update test set t=10 where t=5;
 
Logging works successfully while we run direct DML commands like "insert, update, delete".
 
But, when I call a function that does DML, logging doesn't work and server log file has no information about calling function.
 
I call function like this: SELECT p_dummy_insert();
 
###This is sample insert function###
CREATE OR REPLACE FUNCTION p_dummy_insert ()
  RETURNS void AS
$BODY$
BEGIN
 INSERT INTO employee values ('dummy', 'test');  
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 
"Not logging of function calls" is expected behavior or a bug? We have no information on server logs about who called function or when was it called or what did called function do?
 
Regards,
Murat KOC

Re: DDL & DML Logging doesn't work for calling functions

From
"Albe Laurenz"
Date:
MURAT KOÇ wrote:
> Version is PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red
> Hat 4.1.2-51), 64-bit.
> 
> We set logging parameters as below for DDL & DML Logging:
> logging_collector = on
> log_statement = mod
> log_line_prefix = '%t--%d--%u--%h--%a--%i--%e'

> Logging works successfully while we run direct DML commands like "insert, update, delete".
> 
> But, when I call a function that does DML, logging doesn't work and server log file has no information
> about calling function.
> 
> I call function like this: SELECT p_dummy_insert();
> 
> ###This is sample insert function###
> CREATE OR REPLACE FUNCTION p_dummy_insert ()
>   RETURNS void AS
> $BODY$
> BEGIN
>  INSERT INTO employee values ('dummy', 'test');
> END$BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100;
> 
> "Not logging of function calls" is expected behavior or a bug? We have no information on server logs
> about who called function or when was it called or what did called function do?

The function call itself is logged, but SQL statements inside
the function are not.

The function call does not show up in your log because it is
in a SELECT statement.

Set log_statement = 'all' to log the function call.

Yours,
Laurenz Albe

Re: DDL & DML Logging doesn't work for calling functions

From
MURAT KOÇ
Date:
Thanks for reply.
 
If we set log_statement='all', all of sql statements will be logged and log file will grow up immediately (also including unnecessary sql statements).
 
We don't want all sql statements to be logged, so we continue logging settings as my previous sending (log_statement = 'mod').
 
Regards,
Murat KOC

2011/11/30 Albe Laurenz <laurenz.albe@wien.gv.at>
MURAT KOÇ wrote:
> Version is PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red
> Hat 4.1.2-51), 64-bit.
>
> We set logging parameters as below for DDL & DML Logging:
> logging_collector = on
> log_statement = mod
> log_line_prefix = '%t--%d--%u--%h--%a--%i--%e'

> Logging works successfully while we run direct DML commands like "insert, update, delete".
>
> But, when I call a function that does DML, logging doesn't work and server log file has no information
> about calling function.
>
> I call function like this: SELECT p_dummy_insert();
>
> ###This is sample insert function###
> CREATE OR REPLACE FUNCTION p_dummy_insert ()
>   RETURNS void AS
> $BODY$
> BEGIN
>  INSERT INTO employee values ('dummy', 'test');
> END$BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100;
>
> "Not logging of function calls" is expected behavior or a bug? We have no information on server logs
> about who called function or when was it called or what did called function do?

The function call itself is logged, but SQL statements inside
the function are not.

The function call does not show up in your log because it is
in a SELECT statement.

Set log_statement = 'all' to log the function call.

Yours,
Laurenz Albe

Re: DDL & DML Logging doesn't work for calling functions

From
"Albe Laurenz"
Date:
MURAT KOÇ wrote:
> If we set log_statement='all', all of sql statements will be logged and log file will grow up
> immediately (also including unnecessary sql statements).
> 
> We don't want all sql statements to be logged, so we continue logging settings as my previous sending
> (log_statement = 'mod').

Unfortunately there is no way to log function executions withthis setting.

Yours,
Laurenz Albe