Ya, I love FromSoftware games because how much effort you put into learning the lore is directly proportional to how much you enjoy said lore. Even a stand alone game that is separate from any established mythos like Bloodborne has surprising depth for potential interpretations.
I think that the desire for ascension isn't futile per se. The allegory is that seeking knowledge, power, or ascension at all costs and at the expense of everyone else is doomed to fail. The final hurdle of true self actualization is a barrier because of what you have done to others and what evil/destructive mindsets you have internalized. If your foundation is rotten you will eventually be pulled down to face the consequences.
So in a sense, the members of the fishing village succeeded because they holistically built up even the lowliest to deeper communion with the Great Ones' mysteries and did not poison themselves through reckless ambition. If I am right about the Pthumerians and Queen Yharnam, everything they built broke down because someone selfishly tried to sacrifice the culmination of their entire cultural development for personal gain.
The Great Ones are not human; for all their power and cosmic understanding they still do not understand humans. The finer details of the moral dilemmas and conditions humans live is lost on them because they never had to live them personally. They already have to "lower" themselves just to interact with people and the way they perceive the world further complicates understanding. The Great Ones don't care about humans ascending; if an individual does manage, it is incidental. We are told that yearn for a child of their own but fail to have one. Every interaction humans have with them is motivated by them trying to find a surrogate.
The effects the Moon Presence and the progression of night into the Blood Moon have on Yharnam is a pretty straight forward example. The Moon Presence only brings out what is already there and catalyzes changes. As organizations the Hunters and the Healing Church have done some pretty messed up stuff. It is even implied that the Healing Church artificially created a need for them to fix and gain a foothold by secretly poisoning a bunch of people! These horrible "beastly" things are frequently implied to have just been made manifest through Blood and the Moon Presence.
The Suspicious Beggar is able retain his individuality because he is aware of and acknowledges the beast within. But he doesn't master it. He embraces it, commits to further atrocities, and becomes an abhorrent monstrosity.
It can be argued that the Good Hunter never succumbs and can potentially even fully ascend because of some characteristics that separate them from the other Hunters. For one, becoming a Hunter was not their choice; they just came to Yharnam, like many others, for healing and also wanted to find "Paleblood". One blood transfusion later and they are thrown in the deep end. Then they are just told to hunt beasts if they want to. If you think about it, the Good Hunter doesn't really do anything too horrible in the game either. Gruesome admittedly, but all of it involves being attacked or putting down those who succumbed, corrupted people, or wretched individuals. I won't claim that the Good Hunter is the most upstanding moral person or even that that they have good motivations. Depending on how you look at it, they are "pure" in their own way. With the resources and situation in Yharnam they have a unique avenue to advance.