pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat 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>
2006-01-09 17:29 ` Re: What happens to transactions durring a pg_dump? Jaime Casanova <systemguards@gmail.com>
2006-01-09 17:45 ` Re: What happens to transactions durring a pg_dump? Michael Fuhr <mike@fuhr.org>
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:22 What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
@ 2006-01-09 17:29 ` Jaime Casanova <systemguards@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:22 What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
@ 2006-01-09 17:45 ` Michael Fuhr <mike@fuhr.org>
2006-01-09 18:00 ` Re: What happens to transactions durring a pg_dump? 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 17:22 What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
2006-01-09 17:45 ` Re: What happens to transactions durring a pg_dump? Michael Fuhr <mike@fuhr.org>
@ 2006-01-09 18:00 ` Chris Hoover <revoohc@gmail.com>
2006-01-09 20:22 ` Re: What happens to transactions durring a pg_dump? Jaime Casanova <systemguards@gmail.com>
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 17:22 What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
2006-01-09 17:45 ` Re: What happens to transactions durring a pg_dump? Michael Fuhr <mike@fuhr.org>
2006-01-09 18:00 ` Re: What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
@ 2006-01-09 20:22 ` Jaime Casanova <systemguards@gmail.com>
2006-01-22 00:25 ` Re: What happens to transactions durring a pg_dump? Bruno Wolff III <bruno@wolff.to>
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-09 17:22 What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
2006-01-09 17:45 ` Re: What happens to transactions durring a pg_dump? Michael Fuhr <mike@fuhr.org>
2006-01-09 18:00 ` Re: What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
2006-01-09 20:22 ` Re: What happens to transactions durring a pg_dump? Jaime Casanova <systemguards@gmail.com>
@ 2006-01-22 00:25 ` Bruno Wolff III <bruno@wolff.to>
2006-01-22 00:50 ` Re: What happens to transactions durring a pg_dump? Tom Lane <tgl@sss.pgh.pa.us>
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-09 17:22 What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
2006-01-09 17:45 ` Re: What happens to transactions durring a pg_dump? Michael Fuhr <mike@fuhr.org>
2006-01-09 18:00 ` Re: What happens to transactions durring a pg_dump? Chris Hoover <revoohc@gmail.com>
2006-01-09 20:22 ` Re: What happens to transactions durring a pg_dump? Jaime Casanova <systemguards@gmail.com>
2006-01-22 00:25 ` Re: What happens to transactions durring a pg_dump? Bruno Wolff III <bruno@wolff.to>
@ 2006-01-22 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
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