hierarchy

Saving a Tree in Postgres Using LTREE

In my last post, I showed you how to install and enable a Postgres extension called LTREE. LTREE allows me to save, query on and manipulate trees or hierarchical data structures using a relational database table. As we’ll see, using LTREE I can count leaves, cut off branches, and climb up and down trees easily – all using SQL right inside my application’s existing Postgres database!

But trees are natural, haphazard, branching structures with countless leaves, while database tables are man-made rectangles full of numbers and text. How can I possibly save a beautiful tree structure into an ugly, boring database table?

Path Enumeration

Let’s return to the example tree from the first post in this series:

The LTREE extension uses the path enumeration algorithm, which calls for each node in the tree to record the path from the root you would have to follow to reach that node.

What are the options for storing hierarchical data in a relational database?

Generally speaking, you’re making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the options below that best fit your needs. The following provides some in-depth reading:

One more Nested Intervals vs. Adjacency List comparison: the best comparison of Adjacency List, Materialized Path, Nested Set and Nested Interval I’ve found.

Models for hierarchical data: slides with good explanations of tradeoffs and example usage

Representing hierarchies in MySQL: very good overview of Nested Set in particular

Hierarchical data in RDBMSs: most comprehensive and well-organized set of links I’ve seen, but not much in the way of explanation

Why Trump Supporters Believe He Is Not Corrupt

What the president’s supporters fear most isn’t the corruption of American law, but the corruption of America’s traditional identity.

On Wednesday morning, the lead story on FoxNews.com was not Michael Cohen’s admission that Donald Trump had instructed him to violate campaign-finance laws by paying hush money to two of Trump’s mistresses. It was the alleged murder of a white Iowa woman, Mollie Tibbetts, by an undocumented Latino immigrant, Cristhian Rivera.

On their face, the two stories have little in common. Fox is simply covering the Iowa murder because it distracts attention from a revelation that makes Trump look bad. But dig deeper and the two stories are connected: They represent competing notions of what corruption is.

.. Cohen’s admission highlights one of the enduring riddles of the Trump era. Trump’s supporters say they care about corruption. During the campaign, they cheered his vow to “drain the swamp” in Washington, D.C. When Morning Consult asked Americans in May 2016 to explain why they disliked Hillary Clinton, the second-most-common answer was that she was “corrupt.” And yet, Trump supporters appear largely unfazed by the mounting evidence that Trump is the least ethical president in modern American history. When asked last month whether they considered Trump corrupt, only 14 percent of Republicans said yes. Even Cohen’s allegation is unlikely to change that.

The answer may lie in how Trump and his supporters define corruption. In a forthcoming book titled How Fascism Works, the Yale philosophy professor Jason Stanley makes an intriguing claim. “Corruption, to the fascist politician,” he suggests, “is really about the corruption of purity rather than of the law. Officially, the fascist politician’s denunciations of corruption sound like a denunciation of political corruption. But such talk is intended to evoke corruption in the sense of the usurpation of the traditional order.”

.. In the eyes of many Fox viewers, I suspect, the network isn’t ignoring corruption so much as highlighting the kind that really matters. When Trump instructed Cohen to pay off women with whom he’d had affairs, he may have been violating the law. But he was upholding traditional gender and class hierarchies. Since time immemorial, powerful men have been cheating on their wives and using their power to evade the consequences.

.. The Iowa murder, by contrast, signifies the inversion—the corruption—of that “traditional order.” Throughout American history, few notions have been as sacrosanct as the belief that white women must be protected from nonwhite men. By allegedly murdering Tibbetts, Rivera did not merely violate the law. He did something more subversive: He violated America’s traditional racial and sexual norms.

Once you grasp that for Trump and many of his supporters, corruption means less the violation of law than the violation of established hierarchies, their behavior makes more sense. Since 2014, Trump has employed the phrase rule of law nine times in tweets. Seven of them refer to illegal immigration.

.. Why were Trump’s supporters so convinced that Clinton was the more corrupt candidate even as reporters uncovered far more damning evidence about Trump’s foundation than they did about Clinton’s? Likely because Clinton’s candidacy threatened traditional gender roles. For many Americans, female ambition—especially in service of a feminist agenda—in and of itself represents a form of corruption.

“When female politicians were described as power-seeking,” noted the Yale researchers Victoria Brescoll and Tyler Okimoto in a 2010 study, “participants experienced feelings of moral outrage (i.e., contempt, anger, and/or disgust).”

Cohen’s admission makes it harder for Republicans to claim that Trump didn’t violate the law. But it doesn’t really matter. For many Republicans, Trump remains uncorrupt—indeed, anticorrupt—because what they fear most isn’t the corruption of American law; it’s the corruption of America’s traditional identity.

Go Ahead, Millennials, Destroy Us

they forgot what adults always forget: that our children grow up, and remember everything, and forgive nothing.

.. Those kids have suddenly understood how little their lives were ever worth to the people in power. And they’ll soon begin to realize how efficient and endless are the mechanisms of governance intended to deflect their appeals, exhaust their energy, deplete their passion and defeat them. But anyone who has ever tried to argue with adolescents knows that in the end they will have a thousand times more energy for that fight than you and a bottomless reservoir of moral rage that you burned out long ago.

.. whenever you disapprove of young people, you’re in the wrong, because you’re going to die and they’ll get to write history, but I just can’t help noticing that the liberal side isn’t much fun to be on anymore.

.. Young people have only just learned that the world is an unfair hierarchy of cruelty and greed, and it still shocks and outrages them. They don’t understand how vast and intractable the forces that have shaped this world really are and still think they can change it. Revolutions have always been driven by the young.

.. the N.R.A.’s unassailable coalition of greed and fear

.. I’d come to the conclusion that America has always been a violent nation, from our founding genocide to the slave labor that built the country to the arsenal, unprecedented in human history, that maintains our empire.

.. We spend $60 billion a year on pets

.. cynicism is also a kind of faith: the faith that nothing can change, that those institutions are corrupt beyond all accountability, immune to intimidation or appeal

.. Harvey Weinstein ultimately wasn’t the one enforcing the code of silence around his predations: It was all the agents and managers and friends and colleagues who warned actresses that he was too powerful to accuse.

.. Once people stopped believing in his invulnerability, his destruction was as instantaneous

.. It has been inspiring and thrilling to watch furious, cleareyed teenagers shame and vilify gutless politicians and soul-dead lobbyists for their complicity in the murders

.. Wayne LaPierre was reduced to gibbering like Gen. Jack D. Ripper in “Dr. Strangelove” about a “socialist” takeover and “hardening” our schools. You could see the whites all around his irises. That look is fear.

.. why adults should listen to anything young people had to say about the world. My answer:

because they’re afraid of you.

They don’t understand you. And

they know you’re going to replace them.

.. Go get us. Take us down — all those cringing provincials who still think climate change is a hoax, that being transgender is a fad or that “socialism” means purges and re-education camps. Rid the world of all our outmoded opinions, vestigial prejudices and rotten institutions. Gender roles as disfiguring as foot-binding, the moribund and vampiric two-party system, the savage theology of capitalism — rip it all to the ground. I for one can’t wait till we’re gone.