Re: Logging transaction IDs for DDL.

On Thu, Jun 14, 2018 at 12:34 AM, Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com>
wrote:

> I just noticed a problem with log_statement = 'ddl' and log_line_prefix
> containing '%x'. If the statement is the first in the transaction, it
> will be logged before it is executed, and more importantly, before a
> transaction ID is assigned. That means that %x will be 0.
>
> If the administrator has configured postgres like this in order to ease
> PITR on bad DDL, it's actually of no use whatsoever and they'll have to
> use pg_waldump to try to figure things out.
>
> PFA a simple patch that I hope addresses the issue in a clean way. It
> also handles the same problem for 'mod'.
>
> I'm undecided whether this is a bugfix or an improvement. I'm leaning
> towards bugfix.
>

FWIW, I'm with Andres on the general points -- especially about having to
look at overheads etc...

For your specific usecase, and until the actual logging can be rethought on
it, I suggest you take a look at event triggers for it. E.g.:

https://blog.hagander.net/logging-transactions-that-dropped-tables-236/

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>