Обсуждение: Forcing WAL flush
Is there a way to force a WAL flush so that async commits (from other connections) are flushed, short of actually updating a sacrificial row? Would be nice to do it without generating anything extra, even if it is something that causes IO in the checkpoint. Am I right to think that an empty transaction won't do it, and nor will a transaction that is just a NOTIFY?
Le 2013-01-07 à 16:49, james a écrit : > Is there a way to force a WAL flush so that async commits (from other connections) are flushed, short of actually updatinga sacrificial row? > > Would be nice to do it without generating anything extra, even if it is something that causes IO in the checkpoint. > > Am I right to think that an empty transaction won't do it, and nor will a transaction that is just a NOTIFY? Does pg_start_backup() trigger a full WAL flush? http://www.postgresql.org/docs/9.2/static/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE Bye, François
> Le 2013-01-07 à 16:49, james a écrit : > >> Is there a way to force a WAL flush so that async commits (from other connections) are flushed, short of actually updatinga sacrificial row? >> >> Would be nice to do it without generating anything extra, even if it is something that causes IO in the checkpoint. >> >> Am I right to think that an empty transaction won't do it, and nor will a transaction that is just a NOTIFY? > > Does pg_start_backup() trigger a full WAL flush? > > http://www.postgresql.org/docs/9.2/static/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP > http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE > > Bye, > François That sounds rather heavyweight! I'm looking for something lightweight - I might call this rather often, as a sort of application-level group commit where I commit async but defer the ack to the requester (or other externally visible side effects) slightly until some other thread forces a flush.
On Mon, Jan 7, 2013 at 1:49 PM, james <james@mansionfamily.plus.com> wrote: > Is there a way to force a WAL flush so that async commits (from other > connections) are flushed, short of actually updating a sacrificial row? > > Would be nice to do it without generating anything extra, even if it is > something that causes IO in the checkpoint. > > Am I right to think that an empty transaction won't do it, and nor will a > transaction that is just a NOTIFY? This was discussed in "[HACKERS] Pg_upgrade speed for many tables". It seemed like turning synchronous_commit back on and then creating an temp table was the preferred method to force a flush. Although I wonder if that behavior might be optimized away at some point. Cheers, Jeff