Re: pg_dump and truncate

Hello Tom,

Thanks a lot for your reply. It was an error I made - I was doing
"pg_dump dbname | gzip -9 > dbname-archive.gz >& /dev/null". As soon as
I removed ">& /dev/null" the file was not empty any more.

However, having the pg_dump and truncate inside a transaction, and
setting the isolation level to SERIALIZABLE seems not working as I
intended - I have data inserted to the database during the process and
they were removed by the truncate operation:

Data-0 -> inserted |
Data-1 -> inserted | Begin;
Data-2 -> inserted | SET transaction ISOLATION level SERIALIZABLE;
Data-3 -> inserted | pg_dump dbname | gzip -9 > dbname-archive.gz
Data-4 -> inserted | truncate table1;
Data-5 -> inserted | truncate table2;
Data-6 -> inserted | truncate table3;
Data-7 -> inserted | end;

The resulted dbname-archive.gz contains data up to Data-0. However, the
truncated database does not have Data-1, Data-2, and Data-3 in it!

Why is that? I thought truncate supports transaction now - we use
PostgreSQL 8.1.2.

Thank you!

Feng

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, February 22, 2007 2:28 AM
To: Feng Chen
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] pg_dump and truncate

"Feng Chen" <fchen(at)covergence(dot)com> writes:
> I was trying to do a live db archive using the following steps:

> 1. Open a connection to the database;
> 2. With the connection, do the following:
> BEGIN;
> SET transaction ISOLATION level SERIALIZABLE;

> 3. Execute the following from the Linux shell:
> pg_dump dbname | gzip -9 > dbname-archive.gz

> 4. With the same connection, do the following:
> truncate table1, table2,...;
> end;

> Well, the tables are truncated all right, but the db dump file is
> empty!!!

You weren't very clear about the timing here, but I suppose you did the
TRUNCATE before pg_dump managed to acquire AccessShareLock on the tables
(because you certainly couldn't have done it after). There is some
startup delay for it to acquire lock on every table...

regards, tom lane