Re: Sequence of processing: WAL / shared buffers

OK, so I guess the sequence with synchronous_commit=ON is:

1. client requests any UPDATE - and waits until step 4 is finished
   
2. bp_c (backend process of this client) modifies shared buffers
3. bp_c writes changes to WAL buffers
   
4. bp_c confirms the UPDATE request
5. client requests COMMIT - and waits until step 10 is finished
6. bp_c writes COMMIT to WAL buffers
7. bp_c requests 'write and flash WAL buffers to file' from WAL writer process - and waits until step 9 is finished
   
8. WAL writer writes and flushes WAL buffers
9. WAL writer confirms to bp_c
10. bp_c confirms the COMMIT to client
11. Background Writer writes shared buffers to disc - asynchronous, at any time.
    

Maybe, #2 and #3 are in reverse order? Or even in parallel?

Regards, Jürgen Purtz

Hi Jurgen,

If synchronous_commit is enabled, transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client. Shared buffers is not involved when commit is issued.  For flushing of shared buffers to data files, we can use checkpoint instead. BGWR in charge of writing shared_buffers to the datafiles. 

Thanks!

On 28 January 2016 at 19:49, Jürgen Purtz <juergen@purtz.de> wrote:

Hi,

can anyone explain, what happens first during an UPDATE of an indexed column: writing and flushing to WAL or modification of shared buffers? And: both actions are finished before a confirmation is send to the requesting client - or?

Regards, Jürgen Purtz




--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

--

Regards,
Ang Wei Shan