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

Thanks again Andres,

Andres Freund <andres(at)anarazel(dot)de> a écrit :

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

Yes, those triggers use exception handling (if partition doesn't
exist, create it) but no savepoint.
Thanks for the suggestion, I take that in note!

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

oid | oid | relkind |
relfrozenxid | age
--------+--------------------------------------+---------+--------------+------------
460564 | pg_temp_3.cur_semt700_progsync_4996 | r |
36464 | 2146483652
460764 | pg_temp_8.cur_semt700_progsync_5568 | r |
19836544 | 2126683572
460718 | pg_temp_4.cur_semt700_progsync_5564 | r |
19836544 | 2126683572
460721 | pg_temp_5.cur_semt700_progsync_5565 | r |
19836544 | 2126683572
461068 | pg_temp_22.cur_semt700_progsync_5581 | r |
19836544 | 2126683572

These are temporary tables to manage concurrency & server load. It
seems the sudden disconnection due to wraparound protection didn't get
them removed. I removed them manually under single mode and there is
no more warning now, vacuum command included. Your command is very
interesting to know.

It annoying PG create a xId for empty temporary tables. You can't
clear it with a vacuum as there is no record. I have to terminate
connexions of my deamon processes daily to avoid wraparound
protection. Is there a way to tell PG to forget these tables on its
age estimation?

Thank you so much Andres! You saved me!

Thierry