DDL & DML Logging doesn't work for calling functions
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
Import Notes
Reply to msg id not found: CAA4y46z6U-x8J-bDq6W37UPr7OdCXVQc-PpvE3=8tOOQ9Nj4eA@mail.gmail.comReference msg id not found: CAA4y46z6U-x8J-bDq6W37UPr7OdCXVQc-PpvE3=8tOOQ9Nj4eA@mail.gmail.com
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
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>
Show quoted text
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
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