Subject: Re: pg_dump and not MVCC-safe commands

From: PetSerAl <[email-masked]>
Date: Mon, 20 May 2024 13:36:08 +0300

My question:
What happens if not MVCC-safe command committed after snapshot but before lock?

On Mon, May 20, 2024 at 12:33 PM Guillaume Lelarge
<[email-masked]> wrote:
>
> Hi,
>
> Le lun. 20 mai 2024 à 11:27, PetSerAl <[email-masked]> a écrit :
>>
>> How pg_dump interact with not MVCC-safe commands?
>>
>> As I understand, pg_dump first take snapshot and then lock all tables
>> it intended to dump. What happens if not MVCC-safe command committed
>> after snapshot but before lock? From comment to pg_dump.c I understand
>> that it may fail with 'cache lookup failed' error. But, can it happen,
>> that pg_dump not fail, but instead capture inconsistent dump? For
>> example TRUNCATE committed after snapshot and pg_dump will see result
>> of TRUNCATE but not result of other commands in TRUNCATE transaction?
>>
>>
>
> You can't truncate an already existing table while pg_dump is running. TRUNCATE needs an exclusive lock, and pg_dump already has a lock on all tables of the database it's dumping. So TRUNCATE will be blocked until pg_dump finishes all its work.
>
> (The same will happen for VACUUM FULL, CLUSTER and some (all?) ALTER TABLE commands.)
>
>
> --
> Guillaume.

---

From: Tom Lane <[email-masked]>
Date: Mon, 20 May 2024 10:24:02 -0400

> What happens if not MVCC-safe command committed after snapshot but before lock?

Then you'd get a dump that's not exactly consistent with the state
at the time of the snapshot.  However, it would be the very same
database state that any other query would see at that time.  So
if it's functionally inconsistent for your purposes, then you brought
that on your own head by using TRUNCATE concurrently with other
operations.

The other thing I can think of that's a hazard in this area is
that pg_dump will record current values of sequence objects that
may be advanced beyond where the sequence was at the instant of
the snapshot, since nextval() isn't MVCC-safe.  There are a lot
of other ways (rollbacks and crashes) in which a sequence can get
advanced beyond the last derived value you can find in the database,
so an application that finds this to be unacceptable should probably
not be using sequences.

			regards, tom lane

---

From: PetSerAl <[email-masked]>
Date: Mon, 20 May 2024 18:49:46 +0300

> However, it would be the very same
> database state that any other query would see at that time.
Other queries can use different techniques to prevent this. For
example, lock tables before snapshot, as recommended in documentation.
I understand, that pg_dump can not use that, as it need snapshot to
enumerate tables to lock.

> then you brought
People, who admins database servers, are not always the same people,
who write application/queries. That means, if you know nothing about
application, you can not guarantee, if live database backup would be
consistent. Basically, you need application cooperation to make
consistent live database backup.