MIME-Version: 1.0
References: <CAKygsHQHhd4rzYcNCd8OJMH46saT1E+9+5MT2CgOCGgf_yaQAw@mail.gmail.com>
In-Reply-To: <CAKygsHQHhd4rzYcNCd8OJMH46saT1E+9+5MT2CgOCGgf_yaQAw@mail.gmail.com>
From: Guillaume Lelarge <guillaume@lelarge.info>
Date: Mon, 20 May 2024 11:33:45 +0200
Message-ID: <CAECtzeUQ3hJYPM6k6_XqZLq=DygHbQ3+5YQLrv-ngcht2rQjQg@mail.gmail.com>
Subject: Re: pg_dump and not MVCC-safe commands
To: PetSerAl <petseral@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000081b5c50618df66fc"
Archived-At: <https://www.postgresql.org/message-id/CAECtzeUQ3hJYPM6k6_XqZLq%3DDygHbQ3%2B5YQLrv-ngcht2rQjQg%40mail.gmail.com>
Precedence: bulk

--00000000000081b5c50618df66fc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

Le lun. 20 mai 2024 =C3=A0 11:27, PetSerAl <petseral@gmail.com> a =C3=A9cri=
t :

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


--=20
Guillaume.

--00000000000081b5c50618df66fc
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi,<br></div><br><div class=3D"gmail_quot=
e"><div dir=3D"ltr" class=3D"gmail_attr">Le=C2=A0lun. 20 mai 2024 =C3=A0=C2=
=A011:27, PetSerAl &lt;<a href=3D"mailto:petseral@gmail.com">petseral@gmail=
.com</a>&gt; a =C3=A9crit=C2=A0:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex">How pg_dump interact with not MVCC-safe commands?<br>
<br>
As I understand, pg_dump first take snapshot and then lock all tables<br>
it intended to dump. What happens if not MVCC-safe command committed<br>
after snapshot but before lock? From comment to pg_dump.c I understand<br>
that it may fail with &#39;cache lookup failed&#39; error. But, can it happ=
en,<br>
that pg_dump not fail, but instead capture inconsistent dump? For<br>
example TRUNCATE committed after snapshot and pg_dump will see result<br>
of TRUNCATE but not result of other commands in TRUNCATE transaction?<br>
<br>
<br>
</blockquote></div><div><br></div><div>You can&#39;t truncate an already ex=
isting table while pg_dump is running. TRUNCATE needs an exclusive lock, an=
d pg_dump already has a lock on all tables of the database it&#39;s dumping=
. So TRUNCATE will be blocked until pg_dump finishes all its work.</div><di=
v><br></div><div>(The same will happen for VACUUM FULL, CLUSTER and some (a=
ll?) ALTER TABLE commands.)<br></div><div><br></div><br><span class=3D"gmai=
l_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature=
"><div dir=3D"ltr"><div><div dir=3D"ltr"><div>Guillaume.<br></div></div></d=
iv></div></div></div>

--00000000000081b5c50618df66fc--