X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id B611B9DC880 for ; Sat, 21 Jan 2006 20:17:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 80401-09 for ; Sat, 21 Jan 2006 20:17:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 533189DC80A for ; Sat, 21 Jan 2006 20:17:49 -0400 (AST) Received: (qmail 25122 invoked by uid 500); 22 Jan 2006 00:25:19 -0000 Date: Sat, 21 Jan 2006 18:25:19 -0600 From: Bruno Wolff III To: Jaime Casanova Cc: Chris Hoover , Michael Fuhr , "pgsql-admin@postgresql.org" Subject: Re: What happens to transactions durring a pg_dump? Message-ID: <20060122002519.GA20969@wolff.to> Mail-Followup-To: Bruno Wolff III , Jaime Casanova , Chris Hoover , Michael Fuhr , "pgsql-admin@postgresql.org" References: <1d219a6f0601090922s5224e78emd68cf850b836c2af@mail.gmail.com> <20060109174511.GA43775@winnie.fuhr.org> <1d219a6f0601091000u45439e1dq1eb8296e8ffa3dba@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.067 required=5 tests=[AWL=0.067] X-Spam-Score: 0.067 X-Spam-Level: X-Archive-Number: 200601/255 X-Sequence-Number: 20343 On Mon, Jan 09, 2006 at 15:22:09 -0500, Jaime Casanova wrote: > On 1/9/06, Chris Hoover wrote: > > > > Just to make sure I correctly understood what I read, once pg_dump issues > > the : > > BEGIN > > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE > > > > The backup will only see transactions that were commited before the > > isolation level was set Is this correct? Any transactions that are commited > > while the backup is running are not seen by pg_dump. > > > > Correct? > > > > Thanks, > > > > Chris > > > > correct I don't think that is precisely correct. I believe that the snapshot isn't set until the first query is made within the transaction. In practice this won't be too much of a difference for pg_dump. But if you were entering the commands interactively and there was a long delay between the SET command and the first query, the difference might be significant. I found a comment on this at: http://64.233.167.104/search?q=cache:1QgAKZwFxK8J:www.sai.msu.su/~megera/postgres/gist/papers/concurrency/concurrency.pdf+postgres+snapshot+set+serializable&hl=en