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

The following bug has been logged on the website:

Bug reference:      10989
Logged by:          Sergey Burladyan
Email address:      eshkinkot@gmail.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 $$;

On Fri, Jul 18, 2014 at 5:59 PM, <eshkinkot@gmail.com> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      10989
> Logged by:          Sergey Burladyan
> Email address:      eshkinkot@gmail.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