Thread: Re: BUG #13970: Vacuum hangs on particular table; cannot be terminated - requires `kill -QUIT pid` [WORKAROUND]
Re: BUG #13970: Vacuum hangs on particular table; cannot be terminated - requires `kill -QUIT pid` [WORKAROUND]
From
Brian Ghidinelli
Date:
> On Feb 19, 2016, at 13:41, Alvaro Herrera <alvherre@alvh.no-ip.org> = wrote: >=20 > Brian Ghidinelli wrote: >>=20 >> Thanks Alvaro - both for the help and the reco. I=E2=80=99m pretty = technical >> but it definitely makes me nervous to start mucking around at that >> level.=20 >>=20 >> 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 =E2=80=9Csafer=E2=80=9D approach to achieve = the same result? >=20 > Yeah, that would work because if you drop the table, the Xmax values = are > all gone. A follow-up here - I manually VACUUM=E2=80=99d 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 =3D 1. I presume/hope once I = drop ClubMemberOld, that will get updated?=20 Thanks for all the help! Brian