On 06/27/2017 11:47 PM, Daniel Westermann wrote:
AFAIK the wal writer process.
>Um, no. "Synchronous" means that the caller has to wait for the
result to appear before it can move on. "Asynchronous" means that >he
caller can issue the instruction and immediately move on. I guessing
here but while usually the caller would have to provide >callback hook
to get the answer in the future in this case the caller is assuming a
positive result and doesn't listen for a response. It is >for the
asynchronous mode that wal_writer exists. In synchronous mode it would
be somewhat inefficient to hand-off/leave the work to >a separate
process to perform while the main process remains idle - better to just
have the main process do it. Its not a total win since >the WAL file
takes on the inherent contention.
>
>The linked readme (and I suspect much of the docs) was written under
the assumption that the calling session performs all work not >otherwise
explicitly designated as being handled by a separate process. That is
why you cannot find an affirmative answer to the >posed question - it is
taken as something having been previously learned (or deduced in my case
- the others links being illustrative >too).
>Now, I'm still just going off of human documentation and not the
actual code - but my confidence level is quite high.
Seems I am not the only one who is confused here. To summarize: When
synchronous_commit is set to on it is the user session that does the
write to the wal. When synchronous_commit is set to off (which means
asynchronous commit) it is the job of the wal_writer to (batch) commit
what needs to be commited since the last flush (can be configured with
wal_writer_delay).
The wal_writer is always running regardless of the synchronous_commit
setting. What turning it off does is allow Postgres to return a
transaction completed signal before the WAL info is actually written to
the disk. This means there is a chance of data loss should the
machine/server crash between the time Postgres said the transaction was
completed and the time the WAL records for that transaction hit the disk:
https://www.postgresql.org/docs/9.6/static/wal-async-commit.html
"If the database crashes during the risk window between an asynchronous
commit and the writing of the transaction's WAL records, then changes
made during that transaction will be lost. The duration of the risk
window is limited because a background process (the "WAL writer")
flushes unwritten WAL records to disk every wal_writer_delay
milliseconds. The actual maximum duration of the risk window is three
times wal_writer_delay because the WAL writer is designed to favor
writing whole pages at a time during busy periods."
This might help:
https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-WAL-WRITER-DELAY
Now there are caveats. This does not apply to UNLOGGED tables:
https://www.postgresql.org/docs/9.6/static/sql-createtable.html:
"
UNLOGGED
If specified, the table is created as an unlogged table. Data
written to unlogged tables is not written to the write-ahead log (see
Chapter 30), which makes them considerably faster than ordinary tables.
However, they are not crash-safe: an unlogged table is automatically
truncated after a crash or unclean shutdown. The contents of an unlogged
table are also not replicated to standby servers. Any indexes created on
an unlogged table are automatically unlogged as well.
"
Also synchronous_commit has different behavior if
synchronous_standby_names is non-empty:
https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT
The important thing to know from all of the above is Postgres writes to
WAL before it writes to the data files(UNLOGGED tables excepted).
Maybe it is worth to enhance the documentation for this, at least for
synchronous_commit=true? The asynchronous behavior is well documented
here: https://www.postgresql.org/docs/current/static/wal-async-commit.html.
Again, thanks David and Adrian for your help
Kind Regards
Daniel
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
