What happens to transactions durring a pg_dump?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

What happens to transactions durring a pg_dump?

Chris Hoover-2
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
Reply | Threaded
Open this post in threaded view
|

Re: What happens to transactions durring a pg_dump?

Jaime Casanova
On 1/9/06, Chris Hoover <[hidden email]> 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 ;)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: What happens to transactions durring a pg_dump?

Michael Fuhr
In reply to this post by Chris Hoover-2
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: What happens to transactions durring a pg_dump?

Chris Hoover-2


On 1/9/06, Michael Fuhr <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: What happens to transactions durring a pg_dump?

Jaime Casanova
On 1/9/06, Chris Hoover <[hidden email]> wrote:

>
>
> On 1/9/06, Michael Fuhr <[hidden email]> 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 ;)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: What happens to transactions durring a pg_dump?

Bruno Wolff III
On Mon, Jan 09, 2006 at 15:22:09 -0500,
  Jaime Casanova <[hidden email]> wrote:

> On 1/9/06, Chris Hoover <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: What happens to transactions durring a pg_dump?

Tom Lane-2
Bruno Wolff III <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly