Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aP5lx-0004M7-MG for pgsql-novice@arkaria.postgresql.org; Fri, 29 Jan 2016 09:55:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aP5lx-0003aI-92 for pgsql-novice@arkaria.postgresql.org; Fri, 29 Jan 2016 09:55:21 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aP5l6-0002CI-Ou for pgsql-novice@postgresql.org; Fri, 29 Jan 2016 09:54:28 +0000 Received: from mout.kundenserver.de ([217.72.192.74]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.84) (envelope-from ) id 1aP5l3-0006LI-K8 for pgsql-novice@postgresql.org; Fri, 29 Jan 2016 09:54:27 +0000 Received: from [192.168.178.26] ([84.165.208.200]) by mrelayeu.kundenserver.de (mreue103) with ESMTPSA (Nemesis) id 0LfY1R-1Zj7B905JB-00p4fQ for ; Fri, 29 Jan 2016 10:54:22 +0100 Subject: Re: Sequence of processing: WAL / shared buffers References: <20160105193334.GA296052@alvherre.pgsql> <5690218B.9060103@purtz.de> <56AA0064.1010807@purtz.de> To: pgsql-novice@postgresql.org From: =?UTF-8?Q?J=c3=bcrgen_Purtz?= Message-ID: <56AB36CC.9050800@purtz.de> Date: Fri, 29 Jan 2016 10:54:20 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.5.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------090304000000040501050109" X-Provags-ID: V03:K0:/gZTWTtCmpI5xe4u4UQAr9aQLIi98UFaiETLgz7m9+qWSRZ9vmM t0uRrcuxe8m4m11Wtw0ENaqd9RxEeZoqJNHXesLNG2TTGphDSv4SVCYD+QTRlOzQzTHuzPm k4cY/x3uCG5g1AuZ+oz3C/QiVdyVqoSJyUCqq3yoDKUOPBdWIJDaf3WDfEWJvaQZtPDx3zr fjY3DyWmkth9tvN6+tt3Q== X-UI-Out-Filterresults: notjunk:1;V01:K0:4/JSLTMd1bI=:FKhWKAiWUsJNtsHQbTLp1U 7CVZyZZJ8rueAEliPNP2bLG4DsrkfrY2XtRqUW0WJpyI0cu2xSZaeYcVS3tL7lMOY48rP2Sm8 K+AaUdVuQKfjMCGY7ZuyOHfr5WC80Ule7LcciyuGhQ3urki/iN+Zz0AS1jjtBnqrYZk7ZJ2f4 J2shFddDkUNAp57VXfzA+tyLQZJC9Ag+sqWf3rjVyWeSh8VVSNHAWjg4QbcXJ9QiRuY2WfUnB Aj7kNYVvziEc+ZsY+Dgo7HnONBjrWwuAguW+dr+hr47l6aA/9Oz0xvAwJxA40JD+9T33I8sPA m66LXjDIKsCu8G46QqHMW4V6+CIJH9dR33S8jT/bzcDga9MoBPwPwPkwAi5o2f+Dv7gG5tyoe 73zODTtMD9HN10DC7z1xpBZlI7xPy7RuGA3U7c0Dtqhq1jyAmh6vmEZkhQCbfCgsmTnC9ENWz YirSI8nv4BPAM2eUV88JOf5glb0OsWAs7BxttzhGNpF5jpDyikAuEaM3WITUcQeWEct++/dQk 5ynN0HJx77s8EvM4kzneFKA9Wz4Js1iM116BwfczK6rD120JAd39ypVMrCx8Ay1wiJpslf3N/ OeVqJcbN1/4I6W4q6AgR/eRv+xDBc1dOBNGbY8JyjR3m1+lTFv4YTnEER+6IGg+i8Yf98pdUW 7DYci8NF8qL5tw8bMJgmAqhBeoWsBUU2Hc2FfUHEG19c/ogvahGQRgiBVbH5WYpAiuKSlrq6D 5J1iifotc/ZFFOmd X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-novice Precedence: bulk Sender: pgsql-novice-owner@postgresql.org This is a multi-part message in MIME format. --------------090304000000040501050109 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 On 28.01.2016 13:57, Wei Shan wrote: > 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 > 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 --------------090304000000040501050109 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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



On 28.01.2016 13:57, Wei Shan wrote:
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

--------------090304000000040501050109--