> 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