Re: BUG #10989: log_statement = ddl does not log ddl statements from stored functions

On Fri, Jul 18, 2014 at 5:59 PM, <eshkinkot(at)gmail(dot)com> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 10989
> Logged by: Sergey Burladyan
> Email address: eshkinkot(at)gmail(dot)com
> PostgreSQL version: 9.3.4
> Operating system: Debian testing
> Description:
>
> If I run DROP directly, it logged:
> drop view test_view ;
> 2014-07-18 16:20:51 MSK LOG: statement: drop view test_view ;
>
> but this query is not:
> do $ begin execute 'drop view test_view'; end $;

The reason for not logging this statement is that as per current design
postgres only logs top level statements that get executed with
log_statement settings.

Example:
1. Create table t1(c1 int, c2 char(30));

2.
CREATE OR REPLACE FUNCTION test_func() RETURNS int AS
$BODY$
DECLARE
r int;
BEGIN
FOR r IN 1 .. 10
LOOP
insert into t1 values(1,'aaa');
update t1 set c1=10;
delete from t1;
END LOOP;
RETURN 1;
END
$BODY$
LANGUAGE plpgsql;

3. Change log_statement = 'all'

4. Execute the function:
select test_func();

Now the result will be that in logs you will just see 'select test_func();'.

I think this is not a bug, rather if you want such functionality, this will
be a new feature in itself "log statements executed as part of function
execution" for which if there is a good use case then we need to discuss
it on -hackers (pgsql-hackers(at)postgresql(dot)org) mailing list.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com