Re: BUG #13970: Vacuum hangs on particular table; cannot be terminated - requires `kill -QUIT pid` [WORKAROUND]

> On Feb 19, 2016, at 13:41, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> Brian Ghidinelli wrote:
>>
>> Thanks Alvaro - both for the help and the reco. I’m pretty technical
>> but it definitely makes me nervous to start mucking around at that
>> level.
>>
>> Is there any chance that a tool like pg_repack or similar would fix
>> this? Or recreating the table and reattaching all of the foreign keys?
>> Wondering if there is a “safer” approach to achieve the same result?
>
> Yeah, that would work because if you drop the table, the Xmax values are
> all gone.

A follow-up here - I manually VACUUM’d the 106 other tables in my database to isolate that only my ClubMember table was having this Xid issue. With that confirmed, I scripted a replacement for the table like:

DROP TABLE IF EXISTS ClubMemberNew;
CREATE TABLE ClubMemberNew (LIKE ClubMember INCLUDING ALL) WITHOUT OIDS;
INSERT INTO ClubMemberNew SELECT * FROM ClubMember;

And then restored the 5 FKs on this table like:

ALTER TABLE ClubMemberNew
ADD CONSTRAINT clubmember_club_fkey FOREIGN KEY (uidclub)
REFERENCES club (uidclub) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;

And then migrated 16 FKs pointing to the ClubMember table like:

ALTER TABLE Credit DROP CONSTRAINT credit_clubmember_fkey,
ADD CONSTRAINT credit_clubmember_fkey FOREIGN KEY (uidClubMember) REFERENCES ClubMemberNew (uidClubMember) ON UPDATE CASCADE ON DELETE CASCADE;

And then switched the names and analyzed:

ALTER TABLE ClubMember RENAME TO ClubMemberOld;
ALTER TABLE ClubMemberNew RENAME TO ClubMember;
ANALYZE VERBOSE ClubMember;

I can now successfully VACUUM the new ClubMember table. At the moment, pg_controldata still reports oldestMultiXid = 1. I presume/hope once I drop ClubMemberOld, that will get updated?

Thanks for all the help!

Brian