Forcing WAL flush

[james]

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?

[François Beausoleil]

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?

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

[james]

Post by François Beausoleil

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?

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.

[Jeff Janes]

Post by james
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