Обсуждение: rolled back transactions logging

Поиск
Список
Период
Сортировка

rolled back transactions logging

От
Andrey Lizenko
Дата:
Hi all,
I can see that value pg_stat_database.xact_rollback for my db is instantly growing, but I can not find a way to log these rolled back transactions (or, may be, last statement within).
Even with 
log_min_duration_statement = 0
log_statement = 'all'
there is no error messages in log.

I mean that I was expecting something like this (for example)

ERROR:  value too long for type character varying(4096)
ERROR:  current transaction is aborted, commands ignored until end of transaction block

or to see ROLLBACK statement directly.

Can VACUUM be a reason of xact_rollback increasing?

Server version is 9.2.4

P.S,
Root problem is the number of wal files,  it raised up significantly (more than 4 times) with the same workload and I do not know why.


--
Regards, Andrey Lizenko

Re: rolled back transactions logging

От
Jim Nasby
Дата:
On 4/23/15 5:49 PM, Andrey Lizenko wrote:
> Can VACUUM be a reason of xact_rollback increasing?

I don't think so, not without generating some kind of error.

What could be happening is plpgsql catching exceptions. A BEGIN block
with an EXCEPTION handle creates an implicit subtransaction (savepoint),
and if you catch the exception it's going to do the equivalent of
ROLLBACK TO savepoint. That might be counted as a rollback. More
importantly...

> Server version is 9.2.4
>
> P.S,
> Root problem is the number of wal files,  it raised up significantly
> (more than 4 times) with the same workload and I do not know why.

... plpgsql catching a lot of exceptions in blocks that have modified
data will definitely create WAL. To be clear, the WAL was generated by
whatever DML you did inside the block, but if you trap an exception and
then loop back again and again and again... lots of extra WAL.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com