MarioFlach
Hello, I have a single SQL table looking as follow:
create table("git_commits", primary_key: false) do
add :oid, :binary, primary_key: true
add :parents, {:array, :binary}, null: false
end
Each commit has one or multiple parents. I would like to create a recursive Common Table Expressions in order to traverse the entire commit history.
Let’s say I want to start from commit 161b3955043064f33e3684183837fb7160cccfea and traverse all the parent commits.
I came with the following SQL query:
WITH RECURSIVE dg AS (
SELECT oid FROM git_commits c WHERE c.oid = '\x161b3955043064f33e3684183837fb7160cccfea'
UNION
SELECT p.oid FROM git_commits AS c JOIN git_commits AS p ON p.oid = ANY(c.parents)
)
SELECT COUNT(oid) FROM dg;
The query is not invalid but does not return the number of parents (it returns more that it should).
So my question is how is the first (non-recursive) SELECT statement related to the second?
Is this kind of recursive CTE traversal even possible when working on a single table?
Thank you in advance.
Where Next?
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Master modern tools, write cleaner code, and tackle real-world challenges with Manning books, videos, and courses. Start building today.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #library
- #deployment
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #elixirconf
- #channels
- #exunit
- #discussion
- #code-sync
- #podcasts
- #javascript
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixirconf-us
- #ai
- #blog-post
- #elixir-ls
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #hex
- #security
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Elixir, Erlang & BEAM conferences from Code Sync - join us in 2026!
Bringing learning, work and compliance together since 1987.
**Building the future of software with AI, trust, and human connection.**
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
First Post!- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
crazymevt
So when writing a recursive cte, you need to join back to the cte, which I don’t see you doing here. I believe the second one should be select c.oid from git_commits as c join dg on dg.oid = c.parents
Most Liked
MarioFlach
I’m working on a Git hosting platform and trying to get better performances when browsing repositories. I’m using libgit2 and traversing/walking the commit history is quite slow by design.
When working on big repositories (linux kernel source tree, >800,000 commits) it can take up to 10 seconds when computing something like commit count. I’ve been able to work around quiet a few performance bottlenecks using different tricks (clever pagination in combination with
Stream, etc.) but it still needs to get faster than that…I’m currently experimenting with storing/caching some meta informations in the database and hope to achieve better results.
Thank you for the query, it works like a charm
I’m surprised that it counts merges correctly. I would have assumed that it would return twice as much as commits each time the recursion queries more than one parent…
MarioFlach
That’s what I’m doing right now. I use exclusively libgit2 to interact with the underlying git objects.
I currently have two interchangeable storage backends available :
:filesystem(default) –.gitdirectory:postgres(experimental) – usinggit_referencesandgit_objectstables.The problem is that traversing the commit history is quiet slow by design. There is no fast way of querying the history of a given pathspec (tree, blob) or even counting commit ancestors.
I’m not quite sure how you would implement caching effectively. I don’t want to cache on demand, the first hit will always be slow…
Currently, I’m experimenting with storing meta data for commit and tree objects and the performance boost I got so far is impressive.
In future I would like to extend my
:postgresbackend to write Git objects in separated tables (references, commits, tags, blobs, trees). Being able to perform more complex queries (authors, committers, query avatars, GPG signatures, ACLs, etc.) directly on the DB would be ahttps://github.com/almightycouch/gitgud
hauleth
You can cache on upload once.
You could use
git notesattached to commits for storing metadata and cached values, like the commit “depth”. Alternatively instead of storing all commit data you could just store cached metadata in Postgres, however I would say that this still would be a little overdoing. But this is how I would solve that, if your way works for you then ok.The problem is that this will not scale well in time as PSQL wasn’t meant to store BLOBs.