Обсуждение: What happens to transactions durring a pg_dump?

I'm trying to understand better what happens to a transaction that is commited in the middle of a pg_dump.  Is the transaction included in the database dump, or is it excluded.

Example

pg_dump starts
    - transaction is begun
      - row from table a is deleted
      - row from table b is updated
    - transaction is commited
pg_dump finishes

What happens to the deleted row and the updated row with respect to the backup file.

1.  If they are done before table a and table b are backed up?

2.  If table a is dumped and table b is not?

Thanks,

Chris

On 1/9/06, Chris Hoover <revoohc@gmail.com> wrote:
> I'm trying to understand better what happens to a transaction that is
> commited in the middle of a pg_dump.  Is the transaction included in the
> database dump, or is it excluded.
>

AFAIK, it's excluded...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Mon, Jan 09, 2006 at 12:22:27PM -0500, Chris Hoover wrote:
> I'm trying to understand better what happens to a transaction that is
> commited in the middle of a pg_dump.  Is the transaction included in the
> database dump, or is it excluded.

If you look at the pg_dump source code or turn on query logging,
you should see that pg_dump starts with

BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

See "Transaction Isolation" in the "Concurrency Control" chapter of the
documentation for an explanation of what that means.

http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html

--
Michael Fuhr

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

On 1/9/06, Chris Hoover <revoohc@gmail.com> wrote:
>
>
> On 1/9/06, Michael Fuhr <mike@fuhr.org> wrote:
> > On Mon, Jan 09, 2006 at 12:22:27PM -0500, Chris Hoover wrote:
> > > I'm trying to understand better what happens to a transaction that is
> > > commited in the middle of a pg_dump.  Is the transaction included in the
> > > database dump, or is it excluded.
> >
> > If you look at the pg_dump source code or turn on query logging,
> > you should see that pg_dump starts with
> >
> > BEGIN
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> >
> > See "Transaction Isolation" in the "Concurrency Control" chapter of the
> > documentation for an explanation of what that means.
> >
> >
> http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html
> >
> > --
> > Michael Fuhr
> >
>
> 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

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Mon, Jan 09, 2006 at 15:22:09 -0500,
  Jaime Casanova <systemguards@gmail.com> wrote:
> On 1/9/06, Chris Hoover <revoohc@gmail.com> 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

Bruno Wolff III <bruno@wolff.to> writes:
> 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.

Right.  Specifically, any statement *other* than those enumerated in
PortalRunUtility() will set the snapshot.  As of CVS tip (and for
several past releases IIRC), that code looks like:

    /*
     * Set snapshot if utility stmt needs one.    Most reliable way to do this
     * seems to be to enumerate those that do not need one; this is a short
     * list.  Transaction control, LOCK, and SET must *not* set a snapshot
     * since they need to be executable at the start of a serializable
     * transaction without freezing a snapshot.  By extension we allow SHOW
     * not to set a snapshot.  The other stmts listed are just efficiency
     * hacks.  Beware of listing anything that can modify the database --- if,
     * say, it has to update an index with expressions that invoke
     * user-defined functions, then it had better have a snapshot.
     */
    if (!(IsA(utilityStmt, TransactionStmt) ||
          IsA(utilityStmt, LockStmt) ||
          IsA(utilityStmt, VariableSetStmt) ||
          IsA(utilityStmt, VariableShowStmt) ||
          IsA(utilityStmt, VariableResetStmt) ||
          IsA(utilityStmt, ConstraintsSetStmt) ||
    /* efficiency hacks from here down */
          IsA(utilityStmt, FetchStmt) ||
          IsA(utilityStmt, ListenStmt) ||
          IsA(utilityStmt, NotifyStmt) ||
          IsA(utilityStmt, UnlistenStmt) ||
          IsA(utilityStmt, CheckPointStmt)))
        ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
    else
        ActiveSnapshot = NULL;


            regards, tom lane