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

Thanks again Andres,

Andres Freund <andres@anarazel.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@anarazel.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