PostgreSQL Administration - What happens to transactions durring a pg

Author

What happens to transactions durring a pg_dump?

Chris Hoover

2006-01-09, 11:23 am

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

Jaime Casanova

2006-01-09, 1:23 pm

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

---------------------------(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

Michael Fuhr

2006-01-09, 1:23 pm

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/...action-iso.html

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Chris Hoover

2006-01-09, 1:23 pm

On 1/9/06, Michael Fuhr <mike@fuhr.org> wrote:
>
> On Mon, Jan 09, 2006 at 12:22:27PM -0500, Chris Hoover wrote:
>
> 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/...action-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

Jaime Casanova

2006-01-09, 8:26 pm

On 1/9/06, Chris Hoover <revoohc@gmail.com> wrote:
>
>
> On 1/9/06, Michael Fuhr <mike@fuhr.org> wrote:
> http://www.postgresql.org/docs/8.1/...action-iso.html
>
> 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

Bruno Wolff III

2006-01-21, 8:23 pm

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:
>
> 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=cac...ializable&hl=en

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2006-01-21, 8:23 pm

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(GetTran
sactionSnapshot());
else
ActiveSnapshot = NULL;

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links