Measure XID use on failed queries in PostgreSQL

Question

I am investigating commonly executed queries on a Postgres database to help reduce XID use. I can get a list of queries executed and the number of calls using pg_stat_statements, however it does not include queries that failed for reasons such as a unique constraint violation. Is there a way I can record and get a count of these failing queries?

Example:

test_xid=# \d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

test_xid=# truncate test;
TRUNCATE TABLE
test_xid=# select pg_stat_statements_reset();
 pg_stat_statements_reset 
--------------------------

(1 row)

test_xid=# select txid_current();
 txid_current 
--------------
       224547
(1 row)

test_xid=# insert into test(id) values (1);
INSERT 0 1
test_xid=# insert into test(id) values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
test_xid=# insert into test(id) values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
test_xid=# insert into test(id) values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
test_xid=# select txid_current();
 txid_current 
--------------
       224552
(1 row)

test_xid=# select query, calls from pg_stat_statements;
               query                | calls 
------------------------------------+-------
 insert into test(id) values (?);   |     1
 select pg_stat_statements_reset(); |     1
 select txid_current();             |     2
(3 rows)

test_xid=# select pg_stat_statements_reset();
 pg_stat_statements_reset 
--------------------------

(1 row)

test_xid=# insert into test(id) values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
test_xid=# select query, calls from pg_stat_statements;
               query                | calls 
------------------------------------+-------
 select pg_stat_statements_reset(); |     1
(1 row)

As can be seen, the INSERT query will not appear in pg_stat_statments if it always failed and if the query is already present from a successful execution, the call count will not be incremented by a subsequent failing query, even though the failing query causes the current XID to increase.

score 0 · Answer 1 · answered Mar 22 '16 at 23:27

For general stats, you can look at pg_stat_database.xact_rollback. If you want to know about statements that rolled back, the only thing I can think of that doesn't involve C code is logging all statements and then looking in the logs.

If you want to dive into the C code (or pay someone to), I don't think it would be terribly difficult to add rollback support to pg_stat_statements, and I suspect the community would welcome that.

Measure XID use on failed queries in PostgreSQL

1 Answers1