He is supposed to be a Starfleet captain and put his personal feelings on the backburner, especially in a HUGE crisis like this. So yeah, this really did bug me (though it was still well written).
If it were any threat other than the Borg, that's exactly what he would've done. It's hard to understand just how horrific his ordeal as Locutus was. The Borg broke him. They violated him, enslaved him, made him into a tool for killing thousands of his own people. There's no way he could ever entirely get over such a deep psychological wound. It would be two-dimensional writing to portray Picard as someone who would react exactly the same way to a huge invasion by the Borg as he would to any other danger. Yes, what you describe is what he's "supposed to be," but the Borg tore him apart, shattered his sense of identity, and though he put himself together again, there's still a big Locutus-shaped crack remaining. So it's not as easy to be who he's supposed to be when the Borg are hammering at that weak spot in his psyche. Especially when he's just committed to starting a family, something that's bound to throw his sense of identity into flux.
That was the whole point
of Picard's behavior in this trilogy. Yes, it was drastically out of character for him, but there was a very good reason
why it was. He was
failing to behave the way Jean-Luc Picard would ideally behave, and that was exactly the problem he had to resolve. And he needed his friends and fellow officers to help him do that.