pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
What happens to transactions durring a pg_dump?
7+ messages / 5 participants
[nested] [flat]

* What happens to transactions durring a pg_dump?
@ 2006-01-09 17:22  Chris Hoover <revoohc@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Chris Hoover @ 2006-01-09 17:22 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: What happens to transactions durring a pg_dump?
@ 2006-01-09 17:29  Jaime Casanova <systemguards@gmail.com>
  parent: Chris Hoover <revoohc@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Jaime Casanova @ 2006-01-09 17:29 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

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 ;)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: What happens to transactions durring a pg_dump?
@ 2006-01-09 17:45  Michael Fuhr <mike@fuhr.org>
  parent: Chris Hoover <revoohc@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Michael Fuhr @ 2006-01-09 17:45 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: What happens to transactions durring a pg_dump?
@ 2006-01-09 18:00  Chris Hoover <revoohc@gmail.com>
  parent: Michael Fuhr <mike@fuhr.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Chris Hoover @ 2006-01-09 18:00 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: What happens to transactions durring a pg_dump?
@ 2006-01-09 20:22  Jaime Casanova <systemguards@gmail.com>
  parent: Chris Hoover <revoohc@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Jaime Casanova @ 2006-01-09 20:22 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-admin

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 ;)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: What happens to transactions durring a pg_dump?
@ 2006-01-22 00:25  Bruno Wolff III <bruno@wolff.to>
  parent: Jaime Casanova <systemguards@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Bruno Wolff III @ 2006-01-22 00:25 UTC (permalink / raw)
  To: Jaime Casanova <systemguards@gmail.com>; +Cc: Chris Hoover <revoohc@gmail.com>; Michael Fuhr <mike@fuhr.org>; pgsql-admin

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/concur...



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: What happens to transactions durring a pg_dump?
@ 2006-01-22 00:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruno Wolff III <bruno@wolff.to>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2006-01-22 00:50 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: Jaime Casanova <systemguards@gmail.com>; Chris Hoover <revoohc@gmail.com>; Michael Fuhr <mike@fuhr.org>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2006-01-22 00:50 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-09 17:22 What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
2006-01-09 17:29 ` Jaime Casanova <systemguards@gmail.com>
2006-01-09 17:45 ` Michael Fuhr <mike@fuhr.org>
2006-01-09 18:00   ` Chris Hoover <revoohc@gmail.com>
2006-01-09 20:22     ` Jaime Casanova <systemguards@gmail.com>
2006-01-22 00:25       ` Bruno Wolff III <bruno@wolff.to>
2006-01-22 00:50         ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox