The only way that scenario makes sense is if the whole story is a fabrication, or the timeline of events reported in various places is way off.
The affair allegedly dates back to when he was in charge of the military campaign in Afghanistan, and he wasn't even in charge of the CIA at the time ... so the idea that we was not a CIA insider or not well-liked in the CIA would have had no bearing on anything.
As you say, it seems illogical (I would have thought Petraeus at the very least might have suspected that she was KGB), but it did happen.
IIRC, Petraeus was on active duty until Aug 2011.
Obama might have been grooming him for some post...and what better way to groom someone for a post than to have leverage over him (like an affair). And remember, Leon Panetta was CIA Director before Petraeus. Do you doubt Panetta's loyalty to Obama in light of his recent behavior.
Set Petraeus up for any possibility. If we (Obama) use him in our adminstration...we have leverage over him.
If Romney wants to use him...we have the power to destroy him.