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.
--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
