Re: Transaction ID not logged if no explicit transaction used

On 02/10/2016 02:58 PM, Martín Marqués wrote:
> Hi,
>
> I've been fooling around on a test environment where I wanted to run
> some PITR tests using recovery_target_xid.
>
> So I started setting up postgresql.conf with log_statement='ddl' (and
> 'mod' also) and the %x placeholder in log_line_prefix:
>
> Odd result was that I always got a zero as the xid.
>
>
> So after some chats (and a power outage at home :( ) I tried setting
> log_statement to 'none' and log_min_duration_statement = 0 with the same
> log_line_prefix:
>
> I ran these commands to test:
>
> data=# create table test_xid (id int);
> CREATE TABLE
> data=# begin;
> BEGIN
> data=# drop table test_xid;
> DROP TABLE
> data=# end;
> COMMIT
>
> Which give these logs:
>
> <2016-02-10 17:41:19 EST [5729]: [1] xid=0
> db=data,user=postgres,app=psql,client=[local]>LOG: duration: 17.242 ms
> statement: create table test_xid (id int);
> <2016-02-10 17:41:21 EST [5729]: [2] xid=0
> db=data,user=postgres,app=psql,client=[local]>LOG: duration: 0.055 ms
> statement: begin;
> <2016-02-10 17:41:32 EST [5729]: [3] xid=31063
> db=data,user=postgres,app=psql,client=[local]>LOG: duration: 6.858 ms
> statement: drop table test_xid;
> <2016-02-10 17:41:34 EST [5729]: [4] xid=0
> db=data,user=postgres,app=psql,client=[local]>LOG: duration: 2.540 ms
> statement: end;
>
> It's clear that if the command isn't executed in an explicit
> transaction, you don't get the xid in the logs. Very annoying!
>
> Worst, I guess most people would want the xid of a DROP TABLE to be
> logged, as well as with other DDLs and/or DMLs with log_statement
> configured accordingly. I suppose this is not so simple to achieve.
>
> So, my question is: Is this a bug, or a feature? I recall being able to
> log xids on DDLs but can't find the correct settings now.

Maybe?:

%v Virtual transaction ID (backendID/localXID)

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com