Hi, This is a restart of the earlier thread on flushing statistics mid-transaction [1]. Based on Michael's feedback [2] that the best approach would be to provide an API that allows users/extensions to trigger the flush on demand, this patch does exactly that.
The patch provides a SQL API for performing the flush for the requested PID. If the PID is for the calling backend, the flush occurs immediately. Otherwise, the target process is signaled and the flush occurs at the next CHECK_FOR_INTERRUPTS (for regular backends) or at the next main-loop iteration (for auxiliary processes like bgwriter, walwriter, checkpointer, etc.). This is unlike the existing pg_stat_force_next_flush() which forces a flush but only at the next transaction boundary. A C API is also provided that can be used by extensions. This will be needed for the pg_stat_statements improvements being proposed here [3]. This only flushes the calling backend. For relations modified by INSERT, UPDATE, or DELETE in the current transaction, only the transactional write counters (tuples inserted, updated, deleted, plus live/dead tuple estimates) are deferred until the transaction ends, since their final values depend on the transaction outcome (commit/rollback). All other relation counters (scans, tuples fetched, blocks hit, hot updates, etc.) are flushed immediately. All other pending stats (function stats, IO stats, WAL stats, etc.) are flushed unconditionally. [1] https://www.postgresql.org/message-id/flat/aWTVEycKj7Qh/SXH%40ip-10-97-1-34.eu-west-3.compute.internal [2] https://www.postgresql.org/message-id/acNTfL1xO_UUXkZQ%40paquier.xyz [3] https://www.postgresql.org/message-id/CAA5RZ0vZwR_dSK6fo0P2-EnskUVN0NjLHnGnJMFDPC8-kEW3sQ%40mail.gmail.com -- Sami Imseih Amazon Web Services (AWS)
v1-0001-pgstat-Introduce-pg_stat_report_anytime-for-mid-t.patch
Description: Binary data
