<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by raylu on Medium]]></title>
        <description><![CDATA[Stories by raylu on Medium]]></description>
        <link>https://medium.com/@rayllu?source=rss-57b64c5c534------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*hgM1ydatBOgqc0WrTWg8og.png</url>
            <title>Stories by raylu on Medium</title>
            <link>https://medium.com/@rayllu?source=rss-57b64c5c534------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 23:16:26 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rayllu/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Signals, shells, and docker: an onion of footguns]]></title>
            <link>https://benchling.engineering/signals-shells-and-docker-an-onion-of-footguns-ee592e2b587b?source=rss-57b64c5c534------2</link>
            <guid isPermaLink="false">https://medium.com/p/ee592e2b587b</guid>
            <category><![CDATA[linux]]></category>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[bash]]></category>
            <dc:creator><![CDATA[raylu]]></dc:creator>
            <pubDate>Wed, 22 May 2024 16:01:31 GMT</pubDate>
            <atom:updated>2024-05-22T16:01:31.522Z</atom:updated>
            <content:encoded><![CDATA[<p>On a few occasions, we’ve needed to debug POSIX signals (SIGINT, SIGTERM, etc.). Inevitably, there’s a shell involved too. One day, we were debugging some weird interaction between signals, shells, and containers and found ourselves bamboozled by some behaviors. People who consider themselves knowledgeable about Linux have found some of the details of our investigation surprising, so read on if this sort of thing doesn’t make you want to defenestrate your laptop and become an alpaca-farming hermit.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*kHXBgOZPjmxyt91C.png" /></figure><h3>The scene of the crime</h3><p>At Benchling, we have a pretty standard testing/continuous integration (CI) setup: when you push code to a pull request branch, we run tests for you. A few years back, we added a little optimization: if you push again and tests are still running on the previous commit, we cancel the previous test run. You probably don’t care about that run anyway and we save some money… or do we?</p><p>The code that runs our tests is basically</p><pre>def test_pipeline() -&gt; int:<br>    test_result = subprocess.run([&quot;pytest&quot;, …])<br>    report_test_metrics()<br>    upload_artifacts()<br>    return test_result.returncode</pre><p>So our process tree is</p><pre>test_pipeline<br>└──pytest</pre><p>subprocess.run blocks until the child process exits, so it should take almost all the time. We see in our CI logs that the tests get interrupted halfway through and then we see no more logs, so it sure looks like it’s working. But we’re able to get metrics and artifacts for our canceled runs, which makes no sense. We’ll later discover that while we reported that the run was canceled and stopped forwarding logs, pytest just kept running.</p><h3>Back to basics</h3><p>Thinking that perhaps the problem was not forwarding a signal from test_pipeline to pytest, we thought about basic signal handling first. In a terminal running zsh, we can get the pid of zsh with</p><pre>$ echo $$<br>20147</pre><p>Then, we can run bash inside zsh and sleep infinity (like our tests, a very slow command) inside bash.</p><pre>$ bash<br>$ sleep infinity</pre><p>From another shell, we can see the process tree.</p><pre>$ pstree -p 20147<br>zsh(20147)───bash(65453)───sleep(65904)</pre><p>(pstree is in the psmisc package on Debian/Ubuntu and the pstree formula in brew.) This shows zsh running bash running sleep, as expected. If we now send a SIGINT with ctrl+c, sleep stops.</p><p>Why does that happen? The terminal interprets ctrl+c as <a href="https://en.wikipedia.org/wiki/Line_discipline">“send SIGINT”</a>. zsh receives SIGINT and forwards it to the foreground process which happens to be bash. bash receives the signal and forwards it to sleep. sleep didn’t set up its own signal handler for SIGINT and the default signal handler exits (<a href="https://manpages.org/signal/7">SIGINT has the “term” disposition</a>).</p><p>At the start of the investigation, this was our mental model for shell signal handling.</p><h3>Non-interactive shells</h3><p>The actual problem manifested when a shell script was run with bash (we run the python code above in a bash script).</p><pre>bash<br>  └─test_pipeline<br>      └─pytest</pre><p>Thinking that perhaps interactive shells (which read stdin, among other differences) behaved differently than non-interactive ones or “scripts”, we wrote 2 lines to a file</p><pre>sleep infinity<br>echo done</pre><p>and ran</p><pre>$ ./test.sh</pre><p>In another shell, we could see the same process tree</p><pre>$ pstree -p 20147<br>zsh(20147)───bash(65910)───sleep(65911)</pre><p>Then, we tried directly signaling bash</p><pre>$ kill -s INT 65910</pre><p>but nothing happened. Buried in the bash docs (man bash) is a <a href="https://www.gnu.org/software/bash/manual/html_node/Signals.html">“signals” section</a> that says</p><blockquote>When job control is not enabled, […] the shell and the command are in the same process group as the terminal, and ‘^C’ sends SIGINT to all processes in that process group. […]</blockquote><blockquote>When Bash is running without job control enabled and receives SIGINT […], it waits until that foreground command terminates and then [exits itself]</blockquote><p>Job control is on by default for interactive shells and off for scripts (see the docs about “monitor mode”). So that explains why nothing happened: bash was waiting for sleep (the foreground command) to terminate.</p><p>But there’s also a hint in there about <a href="https://biriukov.dev/docs/fd-pipe-session-terminal/3-process-groups-jobs-and-sessions/">process groups</a>. pstree can show us those too (unless you’re on macOS):</p><pre>$ pstree -pg 20147<br>zsh(20147,20147)───bash(65910,65910)───sleep(65911,65910)</pre><p>So here, we see that bash, which we ran in an interactive zsh, got its own process group. But sleep, which we ran in a non-interactive bash, shares a pgid with bash. We can signal both processes in the group by negating the pid:</p><pre>$ kill -s INT -65910</pre><p>This causes sleep to receive a SIGINT and exit. bash also received a SIGINT and, like the docs say, exits itself. Back in our interactive zsh, we can run</p><pre>$ sleep infinity</pre><p>and see that sleep gets its own pgid, as expected.</p><pre>$ pstree -p 20147<br>zsh(20147,20147)───sleep(65916,65916)</pre><h3>Last command in a non-interactive shell</h3><p>So now we know that sometimes, the shell won’t forward signals to its child process. At one point, someone tried to reproduce this by running bash -c &#39;sleep infinity&#39;. They were able to ctrl+c and stop sleep. But that’s a non-interactive shell, so bash shouldn’t be forwarding SIGINT! What gives?</p><pre>$ bash -c ‘sleep infinity’</pre><p>As usual, in another shell:</p><pre>$ pstree -p 20147<br>zsh(20147)───sleep(65920)</pre><p>Wait, where did bash go? We ran bash! Why does pstree say that zsh is running sleep?</p><p>When we “run” a program, what we generally mean is we <a href="https://lisper.in/fork-exec-python">fork and then exec</a> it. fork sets the new process’ parent pid so that tools like pstree can come along after the fact and draw a pretty tree. exec sets the new process’ command so that tools like pstree can show you something meaningful about what that pid is running.</p><p>But what happened here is that bash simply didn’t fork before exec-ing sleep. We couldn’t find any documentation about this behavior, so instead we offer you some <a href="https://github.com/mirror/busybox/blob/1_36_0/shell/ash.c#L10566-L10568">ash source code</a>:</p><pre>/* Can we avoid forking? For example, very last command<br>* in a script or a subshell does not need forking,<br>* we can just exec it.<br>*/</pre><p>So bash replaced itself with sleep and pstree shows that the parent of the thing that is now running sleep is zsh. We can get the previous behavior by instead running bash -c &#39;sleep infinity &amp;&amp; done&#39;.</p><p>This was particularly exciting because we actually run our bash script with sh -c, so our mental model was</p><pre>sh<br>└─bash<br>    └─test_pipeline<br>        └─pytest</pre><p>for a bit until we realized the sh wasn’t its own pid in the tree.</p><h3>A brief interlude about sh, bash, dash, and ash</h3><p>Wait, what is ash? Did you just link me to some unrelated code? (Yes, sort of; the behavior is the same as bash but the source code is less… abstracted.)</p><p>sh is the Bourne shell (but usually referred to as “POSIX sh”). Bash is the Bourne Again shell. Historically, many systems linked sh to bash, which would check argv[0] and run in sh compatibility mode. On modern Linux systems, <a href="https://en.wikipedia.org/wiki/Almquist_shell#Adoption_in_Debian_and_Ubuntu">sh is now usually dash</a>, but on macOS, it is still bash in sh mode.</p><p>The original ash was the Almquist shell from 1989 written for NetBSD. It was ported to Linux and renamed to dash (Debian Almquist shell). Nowadays, “ash” generally refers to busybox ash, <a href="https://github.com/mirror/busybox/blob/1_36_0/shell/ash.c#L29-L31">which is a derivative of dash</a>. Yes, you read that correctly: the lineage is ash → dash → ash. Shell programmers are not the best at naming things.</p><p>By the way, bash in sh compatibility mode and ash both implement the exec-without-fork behavior described in the previous section, but dash does not. Also, if you try to run sh in the official bash image on Docker Hub (docker run -it --rm bash sh), instead of bash in sh compatibility mode like you’d expect, you get ash (not to be confused with ash).</p><h3>Flowchart</h3><p>Here’s the flowchart that we wish had existed before we started peeling the onion of shell signal handling.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6xW6hToI3k5NiWsM" /></figure><h3>Back to the crime scene</h3><p>Armed with our handy flowchart, we went to read our ci-agent’s code and found that when a build is canceled, it sends SIGTERM to the running job.</p><pre>ci-agent<br>    └─bash<br>        └─test_pipeline<br>            └─pytest</pre><p>bash was run non-interactively, test_pipeline was not the last command, so no signals are forwarded anyway. Does that explain what happened?</p><p>We tried to cut the bash out of the tree by making it exec test_pipeline.py, but that didn’t fix the problem. That must mean our process tree is still wrong.</p><h3>Containers</h3><p>The ci-agent actually just tells docker to run our script.</p><pre>ci-agent<br>    └─docker<br>        └─bash<br>            └─test_pipeline<br>                └─pytest</pre><p>Are signals being forwarded by docker to bash? Docker creates a new <a href="https://www.redhat.com/sysadmin/pid-namespace">pid namespace</a> for each container, so the command it runs becomes pid 1. 1 is a very special pid (it’s normally the init process) and <a href="https://petermalmgren.com/signal-handling-docker/">doesn’t get default signal handlers</a>. A common trick is to use tini or dumb-init to be pid 1 to solve this problem.</p><p>After investigating our image, it turned out we were already using dumb-init, leaving us with this tree</p><pre>ci-agent<br>    └─docker<br>        └─dumb-init<br>            └─bash<br>                └─test_pipeline<br>                    └─pytest</pre><p>and no explanation for the problem.</p><h3>This is the last tree, I swear</h3><p>Actually, we don’t run the docker container directly; we use docker compose run.</p><pre>ci-agent<br>    └─docker compose<br>        └─docker<br>            └─dumb-init<br>                └─bash<br>                    └─test_pipeline<br>                        └─pytest</pre><p>After finally building this tree, we were able to reproduce the problem. It only occurs on docker compose versions between v2.0.0 and v2.19.0, where docker compose run fails to forward signals. This was fixed <a href="https://github.com/docker/compose/commit/fed8ef6b791813f8b1479cf095a37cb4352fa02d">here</a> after we reported <a href="https://github.com/docker/compose/issues/10586">the issue</a>.</p><p>The bug manifested when we upgraded from docker-compose (v1; note the hyphen) to docker compose (v2). Noticing the missing hyphen was necessary to understanding this problem, but it was tough to notice because both versions take nearly identical arguments and have nearly identical behavior. One takeaway from reading this story should be that naming things, despite being hard, is important. If you ever find yourself writing docs like “<a href="https://docs.docker.com/compose/migrate/#docker-compose-vs-docker-compose">update scripts to use Compose V2 by replacing the hyphen (-) with a space</a>”, you’ve probably made a critical naming error.</p><p>Another thing that made debugging this hairy was needing to understand the full chain of custody. Signals need to be forwarded by each process to their children. Understanding why pytest didn’t receive a signal required constructing the tree up to the point that the forwarding chain was broken, which in this case was quite far.</p><p>We considered downgrading back to docker compose v1, but we instead chose to track containers run by our CI step and docker kill them at the end. Later, after upstream fixed the issue, our mitigation simply never kicked in. With the problem fixed, our CI runs now actually stop when we tell them to again. When someone pushes multiple times in quick succession to a PR branch, we don’t waste cycles running on old commits, resulting in faster runs overall! (We also no longer report metrics about these canceled runs, which helps us greatly in identifying flaky or failing tests.)</p><h3>Bonus about foreground processes</h3><p>Back in the “non-interactive shells” section, we had a process tree of</p><pre>zsh(20147)───bash(65910)───sleep(65911)</pre><p>and directly signaled bash with</p><pre>$ kill -s INT -65910</pre><p>Why didn’t we just signal zsh instead? zsh is running interactively, so shouldn’t it forward SIGINT to bash? We can try</p><pre>$ kill -s INT -20147</pre><p>but nothing happens.</p><p>It turns out when you hit ctrl+c in this situation, the terminal sends the SIGINT to bash, not zsh. This is because zsh is no longer in the foreground process group. We can see this by running</p><pre>$ ps -xO stat<br>   PID STAT S TTY          TIME COMMAND<br> 20147 Ss   S pts/0    00:00:00 zsh<br> 65910 S+   S pts/0    00:00:00 bash<br> 65911 S+   S pts/0    00:00:00 sleep</pre><p>The <a href="https://www.man7.org/linux/man-pages/man1/ps.1.html#PROCESS_STATE_CODES">“process state codes”</a> section of man ps says</p><blockquote>+ is in the foreground process group</blockquote><p>And we can see that bash and sleep are, but zsh isn’t. They can’t both be at the same time anyway, since there can only be one foreground process group and zsh gave bash its own process group (because zsh is running interactively). So when we said “zsh receives SIGINT and forwards it to the foreground process which happens to be bash”, it turns out that was a lie.</p><p>But wherefore is bash’s process group the foreground one? <a href="https://www.man7.org/linux/man-pages/man3/tcsetpgrp.3.html">tcsetpgrp</a>. We can see it being called with ltrace:</p><pre>$ ltrace -e tcsetpgrp bash<br>bash-&gt;tcsetpgrp(255, 0xa9850, 0, 0x7f290bdb2fe4) = 0</pre><p>and when bash exits, the parent shell (zsh, in my case) reclaims foreground status with the same call.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ee592e2b587b" width="1" height="1" alt=""><hr><p><a href="https://benchling.engineering/signals-shells-and-docker-an-onion-of-footguns-ee592e2b587b">Signals, shells, and docker: an onion of footguns</a> was originally published in <a href="https://benchling.engineering/">Benchling Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[10x faster python test iteration via fork(2)]]></title>
            <link>https://benchling.engineering/10x-faster-python-test-iteration-via-fork-2-3aae52d2f6?source=rss-57b64c5c534------2</link>
            <guid isPermaLink="false">https://medium.com/p/3aae52d2f6</guid>
            <category><![CDATA[developer-productivity]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[fork]]></category>
            <category><![CDATA[benchling]]></category>
            <dc:creator><![CDATA[raylu]]></dc:creator>
            <pubDate>Thu, 20 Jul 2023 16:01:44 GMT</pubDate>
            <atom:updated>2023-07-20T23:02:38.923Z</atom:updated>
            <content:encoded><![CDATA[<p>It’s ideal to get feedback on your code faster — to make a code change and see the result instantly. But, as projects get larger, reload times get longer. Each incremental dependency or bootstrap code block that adds 200ms feels worth it, but 50 of them later and it takes 10 seconds to see the result of a code change.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kXnodzozvuBFupVH2kYfmA.png" /></figure><p>On the Build team at Benchling, that’s where we found ourselves one day. We used 146 packages which pull in 128 transitive dependencies for a total of 274 packages. We also spent a lot of time waiting for SQLAlchemy models to initialize. The result is our test harness took 10 seconds to set up. After making a code change, you’d start the test runner, wait a few seconds, alt+tab to your browser, get distracted for a few minutes, and then find out you had a typo in your code.</p><p>This is a common challenge for a growing codebase, but it’s something we knew we needed to fix. Here’s the process we arrived at which allowed the second run of tests to start 10x faster — 90% less waiting. While it’ll work a little differently for your codebase depending on the language, dependencies, etc. you’re using, hopefully this can inspire you on your journey to faster feedback and testing.</p><h3>importlib.reload()</h3><p>Since the problem is that we spend so long setting up a bunch of modules just right and then want to see the change in a single file we’re editing, the most obvious solution is to use <a href="https://docs.python.org/3/library/importlib.html#importlib.reload">importlib.reload</a> from the standard library.</p><pre>import importlib<br>import sys<br>import test_harness_stuff  # takes 10 seconds<br>import tests<br>def rerun_tests(changed_path):<br>    for mod in sys.modules.values():<br>        if mod.__file__ == changed_path:<br>            importlib.reload(mod)<br>            tests.run_tests()<br>            break<br>if __name__ == &#39;__main__&#39;:<br>  setup_file_watcher(rerun_tests)<br>  tests.run_tests()</pre><p>This (with some special handling for built-in modules, relative path resolution, and batching to handle editors that perform multiple filesystem operations per save) works alright when the file being changed is a test file (or any other leaf node in the dependency tree).</p><p>However, as you’ve probably guessed from the very long documentation for reload(), this doesn’t work in many other cases. A very common one is if you have animal.py:</p><pre>cow = &quot;woof&quot;</pre><p>and then cow_say.py:</p><pre>from animal import cow</pre><p>If you change cow = &quot;moo&quot;, reloading animal.pyis not enough because cow_say.py has its own global bound to the old str. After reloading animal.py, you must then reload all reverse dependencies in topological order. You must also ensure that if a class definition is changed, all instantiations of that are reinitialized. For projects of almost any complexity, this is not feasible.</p><h3>Not importing</h3><p>Despite reload() not solving our problems, thinking about its issues is helpful in building a more useful solution. The giant list of caveats with reload() means you need to do surgery on the already-loaded modules.</p><p>What if we just didn’t load the code you were going to change until after you changed it? Then we wouldn’t need to do surgery! It’s not too hard to guess what code might be changed. Roughly speaking, our codebase has 3 kinds of modules: 3rd-party dependencies, SQLAlchemy models, and actual app code/tests. More than 90% of the time, we’re working in that last category, so we can just import the 3rd-party dependencies and SQLAlchemy models and not load the app/tests until we’re ready to run a test.</p><h3>zeus, fork()</h3><p>That leaves one problem: after we run a test, the test is loaded. How do we reset back to the state where dependencies and models were loaded but not app/tests? <a href="https://github.com/burke/zeus">zeus</a> actually solved this for Rails: load Rails, fork(), then load app code.</p><blockquote>fork() creates a new process by duplicating the calling process. […] The child process and the parent process run in separate memory spaces. At the time of <em>fork()</em> both memory spaces have the same content. Memory writes […] performed by one of the processes do not affect the other.</blockquote><p>So we can use fork()to snapshot the parent, import some code that is going to change (app/tests), and then rewind back to the snapshot later. Rather than doing surgery on in-memory modules, we can just let the child process exit, re-fork, and re-import any changed code.</p><pre>import os<br>import sys<br>import test_harness_stuff  # takes 10 seconds<br>def run_tests():<br>    pid = os.fork()<br>    if pid == 0:  # child<br>        import tests<br>        tests.run_tests()<br>        sys.exit()<br>    else:  # parent<br>        os.waitpid(pid, 0)<br>if __name__ == &#39;__main__&#39;:<br>    setup_file_watcher(run_tests)<br>    run_tests()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/493/1*gw3y9mouurXRIBgsxyGoig.png" /></figure><p>Something like this sped up our test iteration time from 10 seconds to 1 second, which is a workflow-altering speed improvement (someone told me “I wouldn’t have bothered writing this tricky test if it weren’t for the fast reloader”).</p><p>zeus actually has a multi-level process tree and, when a file changes, it identifies which level imported it and terminates that process and all its ancestors. We do this too at Benchling: we divide up our modules into tiers based on how often developers work on them and where they fall in our dependency tree and then import each tier after forking. This allows us to discard as little import work as possible when a file closer to the root of our dependency tree changes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/444/1*hN4X4y6xyNsPZ_fTRTXxJA.png" /><figcaption>Our process tree</figcaption></figure><p>We actually ended up with some other components for ergonomics (a terminal forwarder that uses libreadline) and performance (file watcher that can’t fork because it’s threaded).</p><h3>Bonus: memory savings by not garbage collecting</h3><p>Once you start running python code after os.fork(), you start running into the same <a href="https://instagram-engineering.com/dismissing-python-garbage-collection-at-instagram-4dca40b29172">memory usage problems Instagram faced</a>. They run a Django web server and load up all their dependencies before forking the web workers. At first, they tried to solve their runaway memory usage by disabling garbage collection entirely. Later, they came up with a <a href="https://instagram-engineering.com/copy-on-write-friendly-python-garbage-collection-ad6ed5233ddf">more elegant solution</a> and upstreamed it into CPython 3.7.</p><p>But what caused the memory usage? In short, copy-on-write pages and reference counting.</p><h4>Copy-on-write</h4><p>The fork() docs say “At the time of fork() both memory spaces have the same content. Memory writes […] performed by one of the processes do not affect the other”. The simplest way to implement this is to copy all the memory from the parent into the child.</p><p>The Linux kernel doesn’t do that. Instead, it makes new <a href="https://wiki.osdev.org/Memory_management#Paging">page tables</a> for the child process that point back at the parent’s memory and marks them both as read-only. When the child tries to write to any memory, it triggers a page fault. The kernel’s page fault handler looks at the page, sees that it was a copy-on-write page, makes an actual copy of the page, and lets the child retry the write operation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/397/1*pTr64TonnUJkIA6tzjM6nQ.png" /><figcaption>Parent and child processes sharing the same physical memory</figcaption></figure><p>As you can imagine, this saves a lot of memory (and makes fork quite a bit faster). So what’s the problem? The child rarely writes to any modules imported by the parent (the app/tests code rarely makes any changes to SQLAlchemy models or 3rd-party dependencies); it only reads them and calls functions defined in them.</p><h4>gc_refs</h4><p>Python’s garbage collector needs to know which objects are safe to free. To do this, every object has a <a href="https://github.com/python/cpython/blob/8d999cbf4adea053be6dbb612b9844635c4dfb8e/Include/objimpl.h#L256">gc_refs field</a> stored in its header that is incremented whenever it is referred to (for example, added to a list).</p><p>This means that if a module imported by our parent process defines a str and we later <em>read</em> that str in the child (which we do all the time), we will modify its object header to increment the ref count and trigger the kernel’s copy-on-write behavior.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/563/1*3fm2cmM-3WogymwrZDLAVQ.png" /><figcaption>Child process with its own memory after incrementing gc_refs</figcaption></figure><h4>gc.freeze()</h4><p>Instagram’s solution to this problem is to (rewrite all the CPython code that looks at gc_refs, introduce, and then) call <a href="https://docs.python.org/3/library/gc.html#gc.freeze">gc.freeze()</a>. This tells the interpreter that all existing objects should be considered ineligible for garbage collection and future accesses shouldn’t increment the ref counter. (The new object header layout, after Instagram’s changes in 3.7 and after another change in 3.12, is documented <a href="https://github.com/python/cpython/blob/main/Objects/object_layout.md">here</a>.)</p><p>Implementing this is very easy: just call gc.freeze() right before you fork()! Running a typical test, we saw a 160 MiB reduction in <a href="https://en.wikipedia.org/wiki/Unique_set_size">unique set size</a>.</p><h4>Don’t gc.collect()!</h4><p>Now that you’re thinking about the garbage collector, you might be tempted to call gc.collect() right before freezing and forking. It sounds like it would save memory — otherwise, objects with no refs in the parent will stick around forever in both the parent and the child. Unfortunately, that’s a bad idea.</p><p>When the garbage collector actually “collects” something, the <a href="https://github.com/python/cpython/blob/main/Objects/obmalloc.c">object allocator</a> “frees” that object’s memory. This doesn’t return any memory back to the system; it simply marks that memory as unused. It also creates a “hole” in the memory. A later allocation can fill that hole by using that freed memory.</p><p>If we think about what happens in the child after GC has created “holes” in the memory, we realize that the child will fill those holes in copy-on-write pages. In your development environment, your pages are likely <a href="https://dengking.github.io/Linux-OS/Kernel/%E4%B8%BB%E8%A6%81%E5%8A%9F%E8%83%BD/Memory-management/Virtual-memory/Paging/Unix-system-page-size/">4 KiB</a>. If you free a 1 KiB object, in the absolute best case it resides entirely within a page boundary and you replace it with another 1 KiB worth of objects. When the child tries to allocate 1 KiB, the kernel copies the entire 4 KiB page: you spent 3 KiB to save 1 KiB.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*40CzyrfGXQa1A8AAcSjNZg.png" /><figcaption>3 KiB used to save 1 KiB</figcaption></figure><p>This is why Instagram actually <a href="https://bugs.python.org/issue31558#msg302780">disables GC entirely in the parent</a>. In their words, “we’re wasting a bit of memory in the shared pages to save a lot of memory later (that would otherwise be wasted on copying entire pages after forking).”</p><h3>General applicability</h3><p>The approach we’ve described here solves a problem that we think a lot of others face — if you rack up enough dependencies, you probably have slow startup/reload times. It works on any system with fork (everything but Windows sans WSL). There are a few caveats, though:</p><ul><li>You need to be able to fork and then continue executing your code. Some languages’ standard libraries, such as nodejs, don’t offer this out of the box, so you may need platform-specific C extensions.</li><li>Your language needs to be able to dynamically load modules at runtime. This is pretty tricky for most compiled languages.</li><li>If you want this to work for your webserver (like zeus), it’s a bit more work. You need to integrate with your <a href="https://peps.python.org/pep-3333/">WSGI</a>/<a href="https://github.com/rack/rack/blob/main/SPEC.rdoc">rack</a>/etc. server to handle requests in a properly setup child process. Each server is different, so we don’t have any general advice for how to do this.</li></ul><p>Also, the benefits are only realized after you separate out modules based on their position in the dependency tree and frequency of edit. Because this is going to be different for everyone, we don’t have much code to share. We undertook this project because we noticed that SQLAlchemy models were close to the root of our dependency tree and took up the majority of startup time, but your mileage may vary.</p><h3>We’re hiring!</h3><p>If you’re interested in working with us to solve complex engineering problems, check out our <a href="https://www.benchling.com/careers/">careers page</a> or <a href="/service/mailto:jobs@benchling.com">contact us</a>!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UneW9cQzCOETv6sgn-JqHw.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3aae52d2f6" width="1" height="1" alt=""><hr><p><a href="https://benchling.engineering/10x-faster-python-test-iteration-via-fork-2-3aae52d2f6">10x faster python test iteration via fork(2)</a> was originally published in <a href="https://benchling.engineering/">Benchling Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analyzing Performance Analysis Performance]]></title>
            <link>https://benchling.engineering/analyzing-performance-analysis-performance-56cb2e212629?source=rss-57b64c5c534------2</link>
            <guid isPermaLink="false">https://medium.com/p/56cb2e212629</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[containers]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[docker]]></category>
            <dc:creator><![CDATA[raylu]]></dc:creator>
            <pubDate>Wed, 22 May 2019 17:09:00 GMT</pubDate>
            <atom:updated>2019-05-30T18:05:45.709Z</atom:updated>
            <content:encoded><![CDATA[<h4>Why is EXPLAIN ANALYZE slow?</h4><p>“Why is EXPLAIN ANALYZE so slow?”, I asked into the void one day. Specifically, why is PostgreSQL’s EXPLAIN ANALYZE an order of magnitude slower than the query it’s analyzing when run in a container in Docker for Mac?</p><p><em>(See </em><a href="#7e79"><em>Summary</em></a><em> for the summary)</em></p><h3>Background</h3><p><a href="https://www.benchling.com/">Benchling</a> is a data platform for life science research. Our users upload DNA sequences, proteins, notebook entries, and other research data, and search over them to find the data they need to run experiments. I was investigating a PostgreSQL query used in search that was too slow. It was a tricky query with many JOINs, multiple UNIONs - the kind that doesn’t fit in small console windows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*udtchMCF6pVNaPMQ9latNw.png" /><figcaption>The UI for the search I was debugging.</figcaption></figure><p>We use MacBooks and run Docker for Mac to get pretty close to our production systems. I tried running it locally to investigate. Running it with just EXPLAIN gave me a pretty big query plan so, as usual, I ran it with EXPLAIN ANALYZE to get some timing numbers to help me focus on the slow part. But it turned out <strong>EXPLAIN ANALYZE was taking almost a minute when the original query took only a few seconds</strong>.<br> <br>That’s the start of how I ended up going down a rabbit hole for an hour and reasoning about a time warp and at this point you think this is an <em>Alice in Wonderland</em> joke but it’s not. I’m writing the post that I wished existed when I started this because, although all the information here exists elsewhere, I didn’t find it put together for our particular stack.<br> <br>Spoiler: the answer is restart Docker for Mac.</p><h3>Setup</h3><p>We can easily reproduce the issue without needing the full Benchling development setup.<br> <br>docker run --rm --name db postgres:9.6.9 gives us a PostgreSQL server. Once it’s up, we can get a shell with docker exec -it db psql -U postgres.<br>And now, for the fun part:</p><pre>postgres=# \timing<br>Timing is on.</pre><pre>postgres=# SELECT count(*) FROM generate_series(1, 100000) AS numbers1 JOIN generate_series(1, 100000) AS numbers2 on numbers1 = numbers2;<br> count<br>--------<br> 100000<br>(1 row)</pre><pre>Time: <strong>154.999 ms</strong></pre><pre>postgres=# <strong><em>EXPLAIN ANALYZE</em></strong> SELECT count(*) FROM generate_series(1, 100000) AS numbers1 JOIN generate_series(1, 100000) AS numbers2 on numbers1 = numbers2;<br>                                                                     QUERY PLAN<br>-----------------------------------------------------------------------------------------------------------------------------------------------------<br> Aggregate  (cost=212.16..212.17 rows=1 width=8) (actual time=7640.240..7640.246 rows=1 loops=1)<br>   -&gt;  Merge Join  (cost=119.66..199.66 rows=5000 width=0) (actual time=2987.783..6887.173 rows=100000 loops=1)<br>         Merge Cond: (numbers1.numbers1 = numbers2.numbers2)<br>         -&gt;  Sort  (cost=59.83..62.33 rows=1000 width=4) (actual time=1477.798..2266.221 rows=100000 loops=1)<br>               Sort Key: numbers1.numbers1<br>               Sort Method: external sort  Disk: 1368kB<br>               -&gt;  Function Scan on generate_series numbers1  (cost=0.00..10.00 rows=1000 width=4) (actual time=18.623..729.027 rows=100000 loops=1)<br>         -&gt;  Sort  (cost=59.83..62.33 rows=1000 width=4) (actual time=1509.904..2303.086 rows=100000 loops=1)<br>               Sort Key: numbers2.numbers2<br>               Sort Method: external sort  Disk: 1760kB<br>               -&gt;  Function Scan on generate_series numbers2  (cost=0.00..10.00 rows=1000 width=4) (actual time=8.836..735.784 rows=100000 loops=1)<br> Planning time: 0.247 ms<br> Execution time: 7644.305 ms<br>(13 rows)</pre><pre>Time: <strong>7646.616 ms</strong></pre><p>So the query itself ran in well under a second, but EXPLAIN ANALYZE on that same query took over 7 seconds. What gives?</p><h3>“Timing”</h3><p>The PostgreSQL documentation has a <a href="https://www.postgresql.org/docs/9.6/using-explain.html#USING-EXPLAIN-CAVEATS">caveats section</a> for EXPLAIN.</p><blockquote><em>Second, the measurement overhead added by </em><em>EXPLAIN ANALYZE can be significant, especially on machines with slow </em><em>gettimeofday() operating-system calls. You can use the </em><a href="https://www.postgresql.org/docs/9.6/pgtesttiming.html"><em>pg_test_timing</em></a><em> tool to measure the overhead of timing on your system.</em></blockquote><p>pg_test_timing, you say?</p><pre>$ docker exec -it db pg_test_timing<br>Testing timing overhead for 3 seconds.<br>Per loop time including overhead: <strong>7353.94 nsec</strong><br>Histogram of timing durations:<br>&lt; usec   % of total      count<br>     1      0.00000          0<br>     2      0.00000          0<br>     4      0.00000          0<br>     8     82.43007     336271<br>    16     14.80805      60409<br>    32      2.27505       9281<br>    64      0.25101       1024<br>   128      0.18336        748<br>   256      0.04020        164<br>   512      0.00907         37<br>  1024      0.00221          9<br>  2048      0.00074          3<br>  4096      0.00000          0<br>  8192      0.00025          1</pre><p>Hm. Is that good?</p><blockquote>Interpreting results<br><em>Good results will show most (&gt;90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7–860 system using a TSC clock source shows excellent performance:<br></em>Testing timing overhead for 3 seconds.<br><em>Per loop time including overhead: </em><strong><em>35.96 nsec</em></strong><em><br>Histogram of timing durations:<br>&lt; usec % of total count<br> 1 96.40465 80435604<br> 2 3.59518 2999652<br> 4 0.00015 126<br> 8 0.00002 13<br> 16 0.00000 2</em></blockquote><p>So… no. Their results show 96% of calls taking under a microsecond, averaging 36 nanoseconds. My results show most calls taking 8–16 microseconds, averaging 7.4 microseconds. But what’s a 200× difference between friends? Maybe it was just slow that one time because something else was using my CPU or something. Let’s try again.</p><pre>$ docker exec -it db pg_test_timing<br>Testing timing overhead for 3 seconds.<br>Detected clock <em>going backwards in time</em>.<br>Time warp: -34612 microseconds</pre><p>Well that’s downright unfortunate. “Going backwards in time” is not something you want to see until you’ve very carefully prepared your <a href="https://aliceinwonderland.fandom.com/wiki/Chronosphere">chronosphere</a>. pg_test_timing repeatedly calls gettimeofday and the second call returned a time 34 milliseconds <strong>before</strong> the first call.<br> <br>So here’s where we’re at: something is causing PostgresSQL’s performance analysis to have bad performance. Time warps caused us to not be able to analyze that further since we don’t have a trustworthy timing mechanism. Let’s figure out why.</p><h3>Time Warp</h3><p><a href="https://github.com/docker/for-mac/issues/2076">There</a>’s a GitHub issue with 50+ comments about time drift between macOS and the Linux VM that runs all the Docker stuff. It is <a href="https://serverfault.com/q/403732/47776">really bad</a> when your clock goes backwards, like it did above. Docker’s <a href="https://docs.docker.com/docker-for-mac/release-notes/#docker-community-edition-18060-ce-mac70-2018-07-25">fix</a> for this is</p><blockquote><em>Use Simple NTP to minimise clock drift between the VM and the host.</em></blockquote><p>which works when we’re concerned about second-level resolution, but not when we’re talking about microseconds.<br> <br>So we know there’s a clock sync problem. Do we care to fix it? We’re not doing the sort of thing that is sensitive to microsecond-level precision (time-based authentication, etc.). It would be nice if our EXPLAIN ANALYZEs were super accurate, but we just want it to run in a reasonable amount of time.</p><h3>TSC vs. HPET</h3><p>There’s another detail in the PostgreSQL docs that is interesting. Requoting with emphasis:</p><blockquote><em>This example from an Intel i7–860 system using a </em><strong><em>TSC clock source</em></strong><em> shows excellent performance</em></blockquote><p>What’s that?<br> <br><a href="https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_MRG/2/html/Realtime_Reference_Guide/chap-Timestamping.html">This</a> is the simplest explanation I could find:</p><blockquote><em>The preferred clock source is the Time Stamp Counter (TSC), but if it is not available the High Precision Event Timer (HPET) is the second best option.</em></blockquote><p>OK. What are we using?</p><pre>$ docker run -it postgres:9.6.9 cat /sys/devices/system/clocksource/clocksource0/available_clocksource<br>hpet acpi_pm<br>$ docker run -it postgres:9.6.9 cat /sys/devices/system/clocksource/clocksource0/current_clocksource<br><strong>hpet</strong></pre><p>That doesn’t look good. HPET is our current clock source and TSC isn’t even an available clock source.<br> <br>Why aren’t we using TSC? The answer lies in the kernel, so things get a bit tricky. We need to search our Docker for Mac’s Linux VM’s kernel logs to see what happened to our clock source. If your Docker for Mac VM hasn’t been running very long (but long enough to experience enough drift), you can find the answer with<br>docker run --privileged -it postgres:9.6.9 dmesg | egrep (tsc|hpet)<br> Otherwise, you may have to go digging through other logs:<br>docker run --privileged -v /var/log:/logs -it postgres:9.6.9 egrep (tsc|hpet) /logs/kmsg.log.0<br>(This volume mount mounts /var/log from the Linux VM, not your macOS volume, because /var/log is not shared with the VM.) In any case, you should see something along the lines of</p><pre>clocksource: timekeeping watchdog on CPU2: Marking clocksource &#39;tsc&#39; as unstable because the skew is too large:<br>clocksource:                       &#39;hpet&#39; wd_now: eb454b8a wd_last: ea528dea mask: ffffffff<br>clocksource:                       &#39;tsc&#39; cs_now: 2c195dabad88 cs_last: 2c0e63f53a4a mask: ffffffffffffffff<br>clocksource: <em>Switched to clocksource hpet</em></pre><p>So besides making pg_test_timing angry, the time warps were also making Linux switch from TSC to HPET. What if we just run our EXPLAIN ANALYZE before it’s marked unstable? Restarting Docker for Mac, we see</p><pre>$ docker run -it postgres:9.6.9 cat /sys/devices/system/clocksource/clocksource0/{available,current}_clocksource<br>tsc hpet acpi_pm<br><strong>tsc</strong></pre><p>and rerunning the first commands gives</p><pre>postgres=# EXPLAIN ANALYZE SELECT count(*) FROM generate_series(1, 100000) AS numbers1 JOIN generate_series(1, 100000) AS numbers2 on numbers1 = numbers2;<br>                                                                     QUERY PLAN<br>----------------------------------------------------------------------------------------------------------------------------------------------------<br> Aggregate  (cost=212.16..212.17 rows=1 width=8) (actual time=184.858..184.858 rows=1 loops=1)<br>   -&gt;  Merge Join  (cost=119.66..199.66 rows=5000 width=0) (actual time=128.732..179.602 rows=100000 loops=1)<br>         Merge Cond: (numbers1.numbers1 = numbers2.numbers2)<br>         -&gt;  Sort  (cost=59.83..62.33 rows=1000 width=4) (actual time=69.437..81.579 rows=100000 loops=1)<br>               Sort Key: numbers1.numbers1<br>               Sort Method: external sort  Disk: 1368kB<br>               -&gt;  Function Scan on generate_series numbers1  (cost=0.00..10.00 rows=1000 width=4) (actual time=12.373..24.829 rows=100000 loops=1)<br>         -&gt;  Sort  (cost=59.83..62.33 rows=1000 width=4) (actual time=59.291..73.667 rows=100000 loops=1)<br>               Sort Key: numbers2.numbers2<br>               Sort Method: external sort  Disk: 1760kB<br>               -&gt;  Function Scan on generate_series numbers2  (cost=0.00..10.00 rows=1000 width=4) (actual time=9.795..19.561 rows=100000 loops=1)<br> Planning time: 0.229 ms<br> Execution time: <strong>188.242 ms</strong></pre><p>a much better result!</p><h3>Summary</h3><p>After running for a while, Docker for Mac experiences time drift which causes the clock source to switch from TSC to HPET which makes PostgreSQL sad. My performance analysis query, EXPLAIN ANALYZE, was running slow enough that I had to analyze <em>its</em> performance and the solution ended up being turning Docker for Mac off and on again.<br> <br>At this point, you’re probably wondering if there’s a more permanent fix than “restart Docker for Mac every time the VM switches to HPET”. I am, too. I assume if this were easily fixable, Docker, Inc. would have shipped something less kludgy than an NTP sync.<br> <br>Was this worth the investigation time? The point of this wasn’t to save a minute waiting for EXPLAIN ANALYZE to finish. When the overhead of your performance analysis dominates the time spent, <em>it’s hard to trust the resulting analysis</em>. If it says that some section of the query supposedly took 1.23 seconds, how much of that 1.23 seconds is just gettimeofday() being slow? So I spent an hour not to speed up my performance analysis, but to enable my use of the tool.<br> <br>As always, if you’re interested in working on interesting technical challenges or solving problems in the life science industry, <a href="https://www.benchling.com/careers/">Benchling is hiring</a>.</p><p><a href="https://news.ycombinator.com/item?id=20054157">Discuss on Hacker News</a></p><p><em>Thanks to </em><a href="https://benchling.engineering/@munchybunch"><em>Josh</em></a><em>, </em><a href="https://benchling.engineering/@somakrdas"><em>Somak</em></a><em>, and </em><a href="https://benchling.engineering/@vineetgopal"><em>Vineet</em></a><em> for reading drafts of this.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=56cb2e212629" width="1" height="1" alt=""><hr><p><a href="https://benchling.engineering/analyzing-performance-analysis-performance-56cb2e212629">Analyzing Performance Analysis Performance</a> was originally published in <a href="https://benchling.engineering/">Benchling Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Enforcing Transitive Constraints in SQL]]></title>
            <link>https://benchling.engineering/enforcing-transitive-constraints-in-sql-bc9caaf06884?source=rss-57b64c5c534------2</link>
            <guid isPermaLink="false">https://medium.com/p/bc9caaf06884</guid>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[constraints]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[raylu]]></dc:creator>
            <pubDate>Fri, 30 Nov 2018 04:43:46 GMT</pubDate>
            <atom:updated>2019-05-20T21:02:31.625Z</atom:updated>
            <content:encoded><![CDATA[<p>In this post, we’ll use compound foreign keys to enforce triangular relationships with an example using organizations, teams, users, and a nursery rhyme.</p><p>Many webapps like Benchling start with the concept of users and then add a concept of organizations later. In SQL, it’s pretty easy to model a many-to-many relationship between organizations and users. It’s also pretty straightforward to enforce that the association/membership table refers only to organizations and users that exist (via foreign keys).</p><p>Just like GitHub, we needed a 3rd level of access control, so we added teams. Users still belong directly to organizations, but they can also belong to any number of teams within that organization (and teams belong to exactly one organization). It’s a little less obvious how to enforce that a user U can’t belong to a team T if T is in organization O but U is not in O — a “transitive” constraint.</p><p>These sorts of transitive constraints appear in any app that has this sort of orgs/teams/users setup and in some other cases (nested subfolders, multi-tenancy) discussed at the end. But first, let’s walk through our orgs/teams/users example.</p><p>If you happen to have docker installed, you can follow along by running a Postgres container named otu:<br>$ docker run --name otu -d postgres<br> and then pasting the SQL statements into a psql shell:<br>$ docker exec -it otu psql -U postgres</p><h3>Getting the farm set up</h3><p><a href="https://en.wikipedia.org/wiki/Old_MacDonald_Had_a_Farm">Ancient lore</a> tells us of man and his cow:</p><pre>CREATE TABLE users (<br>    user_id serial PRIMARY KEY,<br>    name VARCHAR UNIQUE NOT NULL<br>);</pre><pre>INSERT INTO users (name) VALUES (&#39;Old MacDonald&#39;), (&#39;a cow&#39;);</pre><p>and this happens to be how many webapps start. But we learn from that same lore (as many webapp developers also learn from their users) that the man and his cow belong to the same farm.</p><pre>CREATE TABLE organizations (<br>    org_id serial PRIMARY KEY,<br>    name VARCHAR UNIQUE NOT NULL<br>);<br>CREATE TABLE org_memberships (<br>    org_id INTEGER NOT NULL REFERENCES organizations (org_id),<br>    user_id INTEGER NOT NULL REFERENCES users (user_id),<br>    UNIQUE (org_id, user_id)<br>);</pre><pre>INSERT INTO organizations (name) VALUES(&#39;MacDonald&#39;&#39;s Farm&#39;);<br>INSERT INTO org_memberships (org_id, user_id) VALUES (1, 1), (1, 2);</pre><p>This puts Old MacDonald and a cow in the same organization.</p><pre>SELECT users.*, organizations.* FROM users<br>    JOIN org_memberships ON users.user_id = org_memberships.user_id<br>    JOIN organizations ON org_memberships.org_id = organizations.org_id;<br> user_id |     name      | org_id |       name<br>---------+---------------+--------+------------------<br>       1 | Old MacDonald |      1 | MacDonald&#39;s Farm<br>       2 | a cow         |      1 | MacDonald&#39;s Farm</pre><h3>Federating access via teams</h3><p>For some farms and some webapps, this is enough. But for many, it is not. Certainly not for MacDonald, who built a secret barn and wanted to ensure access to the barn was limited to a trusted few. At first, he tried securing the secret barn with a password (E-I-E-I-O), but he ran into all the standard problems of a shared password. Eventually, he realized he needed to federate secret barn access through the concept of a “team”.</p><pre>CREATE TABLE teams (<br>    team_id serial PRIMARY KEY,<br>    org_id INTEGER NOT NULL REFERENCES organizations (org_id),<br>    name VARCHAR UNIQUE NOT NULL<br>);<br>CREATE TABLE team_memberships (<br>    team_id INTEGER NOT NULL REFERENCES teams (team_id),<br>    user_id INTEGER NOT NULL REFERENCES users (user_id)<br>);</pre><pre>INSERT INTO teams (org_id, name) VALUES (1, &#39;Secret Barn Access&#39;);<br>INSERT INTO team_memberships (team_id, user_id) VALUES (1, 2);</pre><p>And thus, the cow had access to the secret barn.</p><pre>SELECT users.*, teams.* FROM users<br>    JOIN team_memberships ON users.user_id = team_memberships.user_id<br>    JOIN teams ON team_memberships.team_id = teams.team_id;<br> user_id | name  | team_id | org_id |        name<br>---------+-------+---------+--------+--------------------<br>       2 | a cow |       1 |      1 | Secret Barn Access</pre><p>And all was well… for a while.</p><h3>Deauthorizing</h3><p>Eventually, as happens occasionally, the cow went rogue and started abusing her powers. There was nothing to do but remove her from the farm entirely.</p><pre>BEGIN;<br>DELETE FROM org_memberships WHERE org_id = 1 AND user_id = 2;</pre><p>Does this remove all of the cow’s access from the farm? No, it does not! The cow is still in the Secret Barn Access team! We rollback to leave the cow in the farm for the time being.</p><pre>SELECT * FROM team_memberships;<br> team_id | user_id<br>---------+---------<br>       1 |       2</pre><pre>ROLLBACK;</pre><p>If the secret barn door only checked team_memberships for access, it would have let the cow in despite not being in the organization at all! But the larger issue is that even if if the secret barn door checks org_memberships, it shouldn’t have to. The database shouldn’t be in an inconsistent state.</p><p>It’s clear that there should be some kind of constraint on the team_memberships table that stops this from happening. The constraint has something to do with org_memberships, which means it&#39;s a foreign key. Right now, team_memberships has nothing to do with organizations, but that&#39;s solved by adding an org_id column.</p><h3>Transitive constraints</h3><p>Let’s redo our concept of teams so that MacDonald can manage his farm with proper transitive constraints.</p><pre>DROP TABLE team_memberships;<br>DROP TABLE teams;<br>CREATE TABLE teams (<br>    team_id serial PRIMARY KEY,<br>    org_id INTEGER NOT NULL REFERENCES organizations (org_id),<br>    name VARCHAR UNIQUE NOT NULL,<br>    UNIQUE (team_id, org_id)<br>);<br>CREATE TABLE team_memberships (<br>    org_id INTEGER NOT NULL,<br>    team_id INTEGER NOT NULL,<br>    user_id INTEGER NOT NULL REFERENCES users (user_id),<br>    FOREIGN KEY (org_id, team_id) REFERENCES teams (org_id, team_id),<br>    FOREIGN KEY (org_id, user_id) REFERENCES org_memberships (org_id, user_id)<br>);</pre><p>We added an org_id column to team_memberships so that we could create <a href="https://www.postgresql.org/docs/9.6/ddl-constraints.html#DDL-CONSTRAINTS-FK">composite foreign keys</a>.</p><p>The first one is to the teams table on (org_id, team_id). This simply checks that when you declare a team membership, you also declare the correct organization. Normally, foreign keys are to the primary key of some other table, but teams&#39; primary key is just team_id. We could have made the teams table have a composite primary key, but we instead add a (team_id, org_id) unique constraint on teams to <a href="https://dba.stackexchange.com/q/153392">support</a> this foreign key.</p><p>The second one is to the org_memberships table on (org_id, user_id) and enforces that team memberships can only exist where organization memberships also exist. This foreign key is what would have prevented us from only removing the cow from the organization without also removing her from the team.</p><p>This second key is a bit unusual because not only is it not referencing a primary key, it references an association table. We aren’t enforcing that some referenced model exists (orgs or users), but that some relationship exists (user belongs to org). It&#39;s also a little unusual that we are using the org_id column in two foreign keys, but this is how we declare the transitive constraint in SQL.</p><p>Now when we re-instate secret barn access</p><pre>INSERT INTO teams (org_id, name) VALUES (1, &#39;Secret Barn Access&#39;);<br>INSERT INTO team_memberships (org_id, team_id, user_id) VALUES (1, 1, 2);</pre><p>we can do so with the confidence that our database won’t let us make a mistake revoking it.</p><pre>BEGIN;<br>DELETE FROM org_memberships WHERE org_id = 1 AND user_id = 2;<br>ERROR:  update or delete on table &quot;org_memberships&quot; violates foreign key constraint &quot;team_memberships_org_id_fkey&quot; on table &quot;team_memberships&quot;<br>DETAIL:  Key (org_id, user_id)=(1, 2) is still referenced from table &quot;team_memberships&quot;.</pre><h3>When would I use this?</h3><p>Besides the orgs/teams/users case, this technique has also been useful in a couple of other places in Benchling. We have a concept of projects with root folders which can have nested subfolders. We store the parent folder of each folder to track the folder tree. One way to find out what project a folder belongs to is to traverse all the way up to the root folder, but this is not great (you need to do as many joins as your depth in the tree or rewrite your queries to use <a href="https://www.postgresql.org/docs/9.6/queries-with.html#QUERIES-WITH-SELECT">recursive CTEs</a>). The obvious solution is to store the project on each folder, but you might break a transitive constraint if a folder’s project doesn’t match the root folder’s project. This is subtly different from the orgs/teams/users problem because users can belong to many teams but folders can only have one parent. Luckily, since the entire folder hierarchy must belong to the same project, we can enforce this by just looking at the our parent’s project: FOREIGN KEY (project_id, parent_id) REFERENCES folder (project_id, folder_id).</p><p>We also approach tenant isolation very similarly to what Sentry describes in <a href="https://blog.sentry.io/2018/11/14/how-to-build-saas-application">this blog post</a>. They have an automatic tenant check that runs as a SQLAlchemy event listener:</p><pre>@db.event.listens_for(TenantQuery, &#39;before_compile&#39;, retval=True)<br>def ensure_tenant_constrained(query):<br>    return query.filter_by(tenant=get_current_tenant())</pre><p>To make something like this possible, we need to have a tenant_id on the models we want to check. Since most of them are not top-level models that directly belong to a tenant (item belongs to a user which belongs to a tenant), this results in another transitive constraint.</p><p>Is it always worth enforcing transitive constraints this way? Not necessarily. Besides generally making your data model more complex, it makes moving a team from one org to another harder. You have to move the team and the users at the same time, which means you have to <a href="https://www.postgresql.org/docs/9.6/sql-set-constraints.html">defer constraints</a>. But for things critical enough that you prefer to not be able to make a change without understanding the complexity, this technique is very useful. Benchling’s orgs/teams/users are modeled this way because we believe it’s better for permission changes to fail than be subtly wrong.</p><p>Finally, as always, <a href="https://grnh.se/wt7plq">we’re hiring engineers</a> who are excited to make life sciences research faster by solving these sorts of problems.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bc9caaf06884" width="1" height="1" alt=""><hr><p><a href="https://benchling.engineering/enforcing-transitive-constraints-in-sql-bc9caaf06884">Enforcing Transitive Constraints in SQL</a> was originally published in <a href="https://benchling.engineering/">Benchling Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>