Re: BUG #15840: Vacuum does not work after database stopped for wraparound protection. Database seems unrepearable.

Hi,

On 2019-06-07 14:59:11 -0500, Thierry Husson wrote:
> Thank you for your anwser. Precisions bellow:
> Andres Freund <andres(at)anarazel(dot)de> a écrit :
> > On 2019-06-07 18:22:20 +0000, PG Bug reporting form wrote:
> > > I was doing tables COPY between my old server with PG10.8 and the new one
> > > with 12Beta1. After each table is done, I make a vacuum of it.
> > > However PG12 has stopped working for wraparound protection. I was doing it
> > > on around 10 cpu, 1 table by cpu.
> >
> > That was a new postgres 12 cluster, not a pg_upgraded one? And you just
> > did a bunch of COPYs? How many?
> >
> > I'm not clear as to how the cluster got to wraparound if that's the
> > scenario. We use one xid per transaction, and copy doesn't use multiple
> > transactions internally. Any chance you have triggers on these tables
> > that use savepoints internally?
>
> Yes it was a new cluster. Around 30 copy were done.
> Yes there is a trigger to manage partitions. Around 1200 tables were
> created. 10 billions records transfered, I need to tranfert 180BR over 1700
> tables.
> I just realize I made vacuum on partitions for the first 8BR rows and forgot
> for the last 2BR That would explain the wraparound protection.

Do those triggers use savepoints / EXCEPTION handling?

Might be worthwhile to check - independent of this issue - if you still
need the partition handling via trigger, now that pg's builtin
partitioning can handle COPY (and likely *much* faster).

> > Could you also show
> >
> > SELECT oid, datname, datfrozenxid, age(datfrozenxid), datminmxid,
> > mxid_age(datminmxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;
> oid | datname | datfrozenxid | age | datminmxid | mxid_age
> -------+-----------+--------------+------------+------------+----------
> 16394 | emet_zhen | 36464 | 2146483652 | 1 | 0

Ok, so it's xids, and clearly not multixids. Could you connect to
emet_zhen and show the output of:

SELECT oid, oid::regclass, relkind, relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relfrozenxid <> 0 AND age(relfrozenxid) > 1800000000 ORDER BY age(relfrozenxid) DESC;

that will tell us which relations need to be vacuumed, and then we can
see why that doesn't work.

> Could it be that PG12 considers "vacuum" as a transaction and trigger
> wraparound protection against it?

I'm still somewhat confused - the output you showed didn't include
vacuum failing, as far as I can tell?

- Andres