There are also positive rewards, such as XP and treasure, and of course narrative progression. But you seem to be arguing against negative consequences. Frankly, I know no RPG where such do not exist in some form.
Nope, not at all. But if you tell people that death actually kinda sucks as a consequence and other, more tailored ones are almost always vastly superior, people will immediately jump to that conclusion. It's happened enough times to be tedious rather than shocking now.
Because once we remove attrition, the only negative mechanical consequence left is death;
No, it isn't. The fact you think it is is exactly the problem.
There are other consequences besides death. Most of them are, in fact,
better than death for exactly the reasons you mentioned (that I clipped out)--they don't necessitate TPKs, they don't end games, and as I said, they don't result in a brief moment of "WHAAAAT" followed by "guess I just grind all that way up again...for the fifth time..." (or, IMO worse, "Bob IV" syndrome).
The thing is, all those other consequences are--
must be--tailored to each game, each group, each
person. Because, surprise surprise, motivation is a personal thing! If you want to motivate people, if you want them to be truly invested, you have to build internal, intrinsic motivations with and for them. This is yet another reason why I advocate so strongly for DMs to aggressively pursue genuine player enthusiasm. Because if the player is genuinely enthusiastic--no ill will or ulterior motive, just doing stuff because they enjoy it--then you have little to no need to do any work at all to create meaningful consequences. Consequences that can resonate across an entire campaign, that can
define a character's life.
The one and only advantage death has is that you don't have to do any work to make it a consequence. Everyone agrees that it is one (with the
raise dead/TPK thing noted.) Otherwise, death is a pretty poor consequence; it dead-ends stories, it forces generally un-fun results, it erases current player investment, it trades all potential futures and all the anxieties and triumphs those might contain for a brief one-shot gut punch, it abandons existing gameplay for (generally) much more barebones gameplay...etc. Regardless of what axis of play you prefer, other than maybe some really hyper-pure simulationism, death tends to....well, deaden, not enrich.
Death is the instant-gratification consequence: all flash, no substance. If you want consequences with substance, they
have to come from building things up for, with, and between characters first.