tag:blogger.com,1999:blog-86964057907885561582024-03-13T18:55:19.501+00:00puntoblogspotTechnical stuff, mostlyRaimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.comBlogger634125tag:blogger.com,1999:blog-8696405790788556158.post-12244795640312854682023-11-30T18:29:00.001+00:002023-11-30T18:29:18.302+00:00Nesting group by in postgres<p>Since my times using Crystal Reports (around 2005) I've been used to "a group by flattens all your columns into one value". It was something inescapable (unless big hacks)</p><p>But lately, with complex types in postgres, and smarter aggregation functions, I've grown to create nested structures in group bys when I want to retain information about individual records inside the group.</p><p>It probably violates a few normalization rules, but who cares?</p><p>So here's it:</p><p><br /></p>
<pre>select country,
count(*),
jsonb_pretty(jsonb_agg((id, status, deleted_at) order by deleted_at desc)) as statuses
from customer
group by country having count(*)>1
order by count desc;
</pre>I think it's super cool to be able to generate a result with all the nested information of the integrants of each group, and even more, being able to order by, inside the groups!<div><br /></div><div>I imagine there's a lot of cool stuff you can do later with UNNEST-style functions, that would allow us to recreate the registers as tables, to do further queries.</div><div><br /></div><div>Or maybe this is just another hack, like the ones I was writing in 2005. But anyhow, they are very useful for data explorations.</div><div><br /></div><div>Happy hacking</div>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-50932801043453168552023-10-23T14:32:00.008+01:002024-02-21T09:41:19.058+00:00Just use Postgres for everything!<p> Just discovered this post <a href="https://www.amazingcto.com/postgres-for-everything/">https://www.amazingcto.com/postgres-for-everything/</a> which is short, and to the point.</p><p>Copying it verbatim, because it deserves to be read even if you don't click my links</p><p>"</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">One way to simplify your stack and reduce the moving parts, speed up development, lower the risk and deliver more features in your startup is <strong>“Use Postgres for everything”</strong>. Postgres can replace - up to millions of users - many backend technologies, Kafka, RabbitMQ, Mongo and Redis among them.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">Use Postgres for caching instead of Redis with <a href="https://www.compose.com/articles/faster-performance-with-unlogged-tables-in-postgresql/" rel="noreferrer noopener" style="display: inline-block; font-weight: 700; text-decoration-line: none;" target="_blank">UNLOGGED tables</a> and TEXT as a JSON data type. <a href="https://chat.openai.com/chat" rel="noreferrer noopener" style="display: inline-block; font-weight: 700; text-decoration-line: none;" target="_blank">Use stored procedures</a> to add and enforce an expiry date for the data just like in Redis.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">Use Postgres as a message queue with <a href="https://www.enterprisedb.com/blog/what-skip-locked-postgresql-95" rel="noreferrer noopener" style="display: inline-block; font-weight: 700; text-decoration-line: none;" target="_blank">SKIP LOCKED</a> instead of Kafka (if you only need a message queue).</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">Use Postgres with <a href="https://www.timescale.com/" rel="noreferrer noopener" style="display: inline-block; font-weight: 700; text-decoration-line: none;" target="_blank">Timescale</a> as a data warehouse.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">Use Postgres with <a href="https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql/" rel="noreferrer noopener" style="display: inline-block; font-weight: 700; text-decoration-line: none;" target="_blank">JSONB</a> to store Json documents in a database, search and index them - instead of Mongo.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">Use Postgres as a cron demon to take actions at certain times, like sending mails, with <a href="https://github.com/citusdata/pg_cron" rel="noreferrer noopener" style="display: inline-block; font-weight: 700; text-decoration-line: none;" target="_blank">pg_cron</a> adding events to a message queue.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">Use Postgres for <a href="https://postgis.net/" rel="noreferrer noopener" style="display: inline-block; font-weight: 700; text-decoration-line: none;" target="_blank">Geospacial queries</a>.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">Use Postgres for <a href="https://supabase.com/blog/postgres-full-text-search-vs-the-rest" rel="noreferrer noopener" style="display: inline-block; font-weight: 700; text-decoration-line: none;" target="_blank">Fulltext Search</a> instead of Elastic.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">Use Postgres to <a href="https://www.amazingcto.com/graphql-for-server-development/" style="display: inline-block; font-weight: 700; text-decoration-line: none;">generate JSON in the database</a>, write no server side code and directly give it to the API.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">Use Postgres with a <a href="https://graphjin.com/" rel="noreferrer noopener" style="display: inline-block; font-weight: 700; text-decoration-line: none;" target="_blank">GraphQL adapter</a> to deliver GraphQL if needed.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;"><br /></p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">There I’ve said it, <strong>just use Postgres for everything</strong>.</p><p style="background-color: white; font-family: -apple-system, "system-ui", "Segoe UI", Roboto, Oxygen-Sans, Ubuntu, Cantarell, "Helvetica Neue", sans-serif; font-size: 20.8px; margin: 0.75rem 0px; padding: 0px; width: 780px;">"</p><p><br /></p><p>Also, there's a link to <a href="https://www.radicalsimpli.city/">radical simplicity,</a> which seems like another iteration of use-boring-technology.</p><p>This one is also great gist about using pg for basically everything https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb</p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-21332049931412577582023-10-02T15:31:00.002+01:002024-02-21T09:42:04.685+00:00Idempotency is so great<p>Once I'm presented with a problem, there are a few angles I instantly look at it, to get a shape of which kinds of solutions might work, and whether the problem allows for unknowns as I go, or it has to be right.</p><p>- One of them is idempotency. </p><p>- Another is "does it allow for layered development/deployment?". Can I keep figuring out the rest of the owl as I'm drawing it?</p><p>- Steel thread vs gaining resolution as we go through <br /></p><p>I remember Christopher Alexander's</p><p>The difference between the novice and the master is simply that the
novice has not learnt, yet, how to do things in such a way that he can
afford to make small mistakes. The master knows that the sequence of his
actions will always allow him to cover his mistakes a little further
down the line. It is this simple but essential knowledge which gives the
work of a master carpenter its wonderful, smooth, relaxed, and almost
unconcerned simplicity.</p><p>https://ericlathrop.com/2021/04/idempotence-now-prevents-pain-later/</p><p>https://news.ycombinator.com/item?id=31027403</p><p>https://www.berkansasmaz.com/every-programmer-should-know-idempotency/<br /></p><p>https://newsletter.casewhen.xyz/p/data-explained-idempotence</p><p>https://www.youtube.com/watch?v=GRr4xeMn1uU<br /></p><p>https://danluu.com/simple-architectures/ (links to some brandur posts about idempotency and jobs)</p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-32796534226717405302023-09-29T10:21:00.001+01:002023-10-02T15:30:07.825+01:00A Few C++ Talks?<p> Here are a few talks from the C++ community that when I watched I thought I'd rewatch again because they either are very cool even though I don't grok C++, or they talk about general programming concepts. Here's the list:</p><p><br /></p><p>- https://www.youtube.com/watch?v=V5SCJIWIPjk Optimizing for Change in C++ - Ben Deane</p><p>- https://www.youtube.com/watch?v=2ouxETt75R4 Easy to use, hard to missuse - Ben Deane</p><p>- https://www.youtube.com/watch?v=sWgDk-o-6ZE CppCon 2015: Sean Parent "Better Code: Data Structures"</p><p>- https://www.youtube.com/watch?v=W2tWOdzgXHA&list=PLM5v5JsFsgP21eB4z2mIL8upkvT00Tw9B Sean Parent "Seasoning" talking about algorithms</p><p>- https://www.youtube.com/watch?v=JELcdZLre3s Function Composition in Programming Languages - Conor Hoekstra - CppNorth 2023</p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-58516681633968975492023-09-26T11:47:00.006+01:002023-09-26T11:47:46.865+01:00Naive option picker (fzf) in pure shell<p> I've been a big fan of option pickers since.... forever. I've used ratmenu, ratmen, dmenu, dzen, percol, fzf, helm, vertico, consul...</p><p><br /></p><p>For me, it's so fundamental that I need this functionality in every piece of code/script I write.</p><p>Finally I got to a working version of a very small version of it.</p><p>As always, pure shell, but with some advanced features can give you a succint function that works predictably. The /dev/tty part was the trickiest to get working, as I didn't know how to get input from a pipe, and at the same time be able to read data from the keyboard.</p><p><br /></p><p><br /></p>
<script src="https://gist.github.com/kidd/12112ffc44f79ea8b44cadf48243c6a4.js"></script>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-62028262767206370412023-09-04T09:27:00.004+01:002023-09-04T09:27:50.182+01:00Yet another example of "code the stupidest thing first"<p> I've read a post called "<a href="https://evanlh.com/posts/shortest_path_first_sdlc/">Code the shortest path first</a>", and I couldn't agree more, really. The core idea is a well known one to the reader of this blog (me, mostly). It's not even about YAGNI, or "The Wrong Abstraction". It's about having that "tracer bullet" or "<a href="https://www.rubick.com/steel-threads/">steel thread</a>" that guides you through the implementation phases. Also, if the feature looks too big from the beginning, you can try "<a href="https://www.mountaingoatsoftware.com/uploads/blog/spidr-poster.pdf">SPIDR</a>".</p><p>Also, what I find is that my first code, it's usually <a href="https://news.ycombinator.com/item?id=17090319">highly compressed</a>, and that also gives a first soul to the implementation that lives on during the implementation. </p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-80296529854466684112023-08-31T10:18:00.005+01:002023-08-31T10:18:54.797+01:00postgres jsonb_diff and array_unique<p> I recently copypasted a couple of functions from stackoverflow for my prostgres projects that are worth mentioning, cause they show a pattern on how to write plpsql functions:</p><p><br /></p><p><script src="https://gist.github.com/kidd/f931d067f495100b4f8f2b963dc62930.js"></script><br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-26334797861016918402023-08-31T10:11:00.004+01:002023-10-05T07:07:12.355+01:00Clojure and aplisms<p>The more I do APL and K, the more I see forks and hooks everywhere else.</p><p>That's just a couple of little helpers I've added in some clojure projects. One is "apply-if", which more than from APL, I'm taking it from any procedural language, where you can do `x= if p(x) then f(x) else x`. We used to do this a lot in lua (using ands and ors). But in a combinator style.</p><p><br /></p><p>Second one is an attempt at the fork pattern.</p><p>Look how the avg looks like. it's verbose, but it captures the idea very close to how APL does it.</p><p><br /></p><p><script src="https://gist.github.com/kidd/a771ee9488fac8cd4ffb0d87dfbc0097.js"></script></p><p><br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-41515692850956283772023-08-22T10:31:00.003+01:002023-08-22T10:31:42.871+01:002 classes of system designers<p>Here's a devastating quote from Andy Kinslow (NATO SOFTWARE ENGINEERING CONFERENCE ,1968) </p><p><br /></p><p></p><blockquote><p>There are two classes of system designers.
The first, if given five problems will solve
them one at a time.</p><p>The second will come back and announce
that these aren’t the real problems, and
will eventually propose a solution to the
single problem which underlies the
original five.</p><p>This is the ‘system type’ who is great
during the initial stages of a design project.
However, you had better get rid of him
after the first six months if you want to get
a working system.</p></blockquote><p><br /></p><p>I found it in the <a href="http://homepages.cs.ncl.ac.uk/brian.randell/NATO/nato1968.PDF">Software Engineering conference pdf</a> , but I didn't know anything about that "Andy Kinslow". And looking for that person, led me to some interesting findings, like an <a href="https://dl.acm.org/doi/pdf/10.1145/586148.586155">APL paper</a>, or finding out that A<a href="https://www.servethehome.com/virtualization-long-history/">ndy Kinslow pioneered work on Time-Sharing-System, following leads of Corbato and Strachey</a></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-64385136993609285462023-08-16T10:43:00.006+01:002023-08-16T10:45:11.490+01:00Copy table from another db in postgres<p> This one is somehow a followup on "<a href="https://puntoblogspot.blogspot.com/2023/06/duplicate-row-in-plain-sql.html">Duplicate row in plain sql</a>". What I needed in this case was to copy </p><p>Both dbs live in the same physical postgres, and the main user has rights over everything, so the process was painless, and took 5 minutes to set it up (accounting for reading the docs).</p><p><br /></p><p>The trick is to use the <a href="https://www.postgresql.org/docs/current/postgres-fdw.html">postgres_fdw</a> and create a schema "dev" inside db_test, so I can access the tables in db_dev.</p><p><br /></p><script src="https://gist.github.com/kidd/42c772ddb95c5dec7300a354b44b1087.js"></script>
Now you can easily do your `insert into table_to_copy select * from dev.table_to_copy;`Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-3732689111478471132023-06-29T16:04:00.001+01:002023-06-29T16:04:29.902+01:00Duplicate row in plain sql<p> Another no-magic-here post, but quite a useful one when working with data in dev, and needing to massage it.</p><p>Have you found the need to duplicate a row, only with slight modification? SQL doesn't have a standard way to do it, and while some rdbms accept some sort of "SELECT * EXCEPT(id) FROM xyz", you might not have it available in your db (postgres)</p><p>The clearest solution I found is <a href="https://stackoverflow.com/questions/15429756/postgresql-how-to-duplicate-a-row">here</a>:</p><p>It's all about creating a temporary table, and use "like" and "insert into", to avoid having to write all the fields:</p><pre class="lang-sql s-code-block" style="--_cb-line-numbers-bg: var(--black-050); border-radius: var(--br-md); border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--fs-body1); font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: var(--lh-md); margin-bottom: 0px; margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: var(--su12); vertical-align: baseline; width: auto;"><code class="hljs language-sql" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--_pr-code-fs); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;"><span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">CREATE</span> TEMP <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">TABLE</span> tmp (<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">like</span> web_book);
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">INSERT</span> <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">INTO</span> tmp <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">SELECT</span> <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">*</span> <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">FROM</span> web_book <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">WHERE</span> id <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> <span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">3</span>;
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">UPDATE</span> tmp <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">SET</span> id <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> nextval(<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'web_book_id_seq'</span>);
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">INSERT</span> <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">INTO</span> web_book <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">SELECT</span> <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">*</span> <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">from</span> tmp;</code></pre><pre class="lang-sql s-code-block" style="--_cb-line-numbers-bg: var(--black-050); border-radius: var(--br-md); border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--fs-body1); font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: var(--lh-md); margin-bottom: 0px; margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: var(--su12); vertical-align: baseline; width: auto;"><code class="hljs language-sql" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--_pr-code-fs); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;"><br /></code></pre><pre class="lang-sql s-code-block" style="--_cb-line-numbers-bg: var(--black-050); border-radius: var(--br-md); border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--fs-body1); font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: var(--lh-md); margin-bottom: 0px; margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: var(--su12); vertical-align: baseline; width: auto;"><code class="hljs language-sql" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--_pr-code-fs); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;"><br /></code></pre><pre class="lang-sql s-code-block" style="--_cb-line-numbers-bg: var(--black-050); border-radius: var(--br-md); border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--fs-body1); font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: var(--lh-md); margin-bottom: 0px; margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: var(--su12); vertical-align: baseline; width: auto;">In fact, we could simplify it further (at least in postgres) with:</pre><pre class="lang-sql s-code-block" style="--_cb-line-numbers-bg: var(--black-050); border-radius: var(--br-md); border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--fs-body1); font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: var(--lh-md); margin-bottom: 0px; margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: var(--su12); vertical-align: baseline; width: auto;"><br /></pre><pre class="lang-sql s-code-block" style="--_cb-line-numbers-bg: var(--black-050); border-radius: var(--br-md); border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--fs-body1); font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: var(--lh-md); margin-bottom: 0px; margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: var(--su12); vertical-align: baseline; width: auto;"><pre class="lang-sql s-code-block" style="--_cb-line-numbers-bg: var(--black-050); border-radius: var(--br-md); border: 0px; box-sizing: inherit; color: var(--highlight-color); font-family: var(--ff-mono); font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--fs-body1); font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: var(--lh-md); margin-bottom: 0px; margin-top: 0px; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: var(--su12); vertical-align: baseline; width: auto;"><code class="hljs language-sql" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: var(--_pr-code-fs); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;"><span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">CREATE</span> TEMP <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">TABLE</span> tmp as <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">SELECT</span> <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">*</span> <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">FROM</span> web_book <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">WHERE</span> id <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> <span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">3</span>;
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">UPDATE</span> tmp <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">SET</span> id <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span> nextval(<span class="hljs-string" style="border: 0px; box-sizing: inherit; color: var(--highlight-variable); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'web_book_id_seq'</span>);
<span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">INSERT</span> <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">INTO</span> web_book <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">SELECT</span> <span class="hljs-operator" style="border: 0px; box-sizing: inherit; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">*</span> <span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">from</span> tmp;</code></pre></pre>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-64870021747460347462023-06-23T12:20:00.007+01:002023-06-29T11:57:02.006+01:00"delete limit X" is not possible in postgres...<p>And how to fix it.</p><p> </p><p>So I was pretty sure that I had done some sort of large-ish destructive (as in side-effect-y) operations in batches of small numbers. And today I had to delete a bunch of rows from a bunch of tables.</p><p>It turns out that postgres doesn't support "LIMIT" in UPDATE nor DELETEs.</p><p> First of all, the misunderstanding when I remembered doing destructive operations in batches comes from calling postgres functions via select.</p><p>Let's say you have to kill the db connections to a db:</p><pre><span class="pl-k">SELECT</span> pg_terminate_backend(pid) <span class="pl-k">FROM</span> pg_stat_activity <span class="pl-k">WHERE</span> datname<span class="pl-k">=</span><span class="pl-s"><span class="pl-pds">'</span>db_1234567890abcdef<span class="pl-pds">'</span></span>;</pre><p></p><p>This, being a select, you can do things like:</p><pre><span class="pl-k">SELECT</span> pg_terminate_backend(pid) <span class="pl-k">FROM</span> pg_stat_activity <span class="pl-k">WHERE</span> datname like <span class="pl-s"><span class="pl-pds">'db_%</span><span class="pl-pds">' limit 100</span></span>;</pre><p>So yes, you are batching operations, but it's because "selects" are not always side-effect-free.</p><p>So now, back to our real problem of doing updates or deletes in batches.</p><p> It's not that it hasn't been asked or thought about. Here's one of the <a href="https://www.postgresql.org/message-id/20211217094718.0d4d1c9eea684d09d8111c5d%40sraoss.co.jp">mail threads</a> about it. Tom Lane's answer highlights some of the issues from the correctness perspective.</p><p><br /></p><p>Ok, but if I really want to do it... what's the best way? Crunchydata has a <a href="https://www.crunchydata.com/blog/simulating-update-or-delete-with-limit-in-postgres-ctes-to-the-rescue">blog post</a> explaining multiple ways of doing it, but the main takeaway is to use CTEs to select the amount you want (using limit), and then delete by id "where exists" or "where id in".</p><pre class="language-sql" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; background: rgb(22, 37, 54); border-radius: 0.5rem; border: 1px solid rgb(48, 48, 48); box-sizing: border-box; color: white; font-family: var(--font-mono),ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,"Liberation Mono","Courier New",monospace; font-size: 1rem; hyphens: none; line-height: 1.25; margin-bottom: 2em; margin-top: 2em; max-width: 100%; overflow-wrap: normal; overflow: auto; padding: 1.11111em 1.33333em; tab-size: 4; word-break: normal;" tabindex="0"><code class="language-sql" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; background-color: transparent; border-color: rgb(228, 228, 231); border-radius: 0px; border-style: solid; border-width: 0px; box-sizing: border-box; color: #d6deeb; font-family: var(--font-mono),ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,"Liberation Mono","Courier New",monospace; font-size: 1rem; font-weight: inherit; hyphens: none; line-height: 1.25; max-width: 100%; overflow-wrap: normal; padding: 0px; tab-size: 4; word-break: normal; word-spacing: normal;"><span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">WITH</span> <span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">rows</span> <span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">AS</span> <span class="token punctuation" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #a7b1b6;">(</span>
<span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">SELECT</span>
something
<span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">FROM</span>
big_table
<span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">LIMIT</span> <span class="token number" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #f78c6c;">10</span>
<span class="token punctuation" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #a7b1b6;">)</span>
<span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">DELETE</span> <span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">FROM</span> big_table
<span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">WHERE</span> something <span class="token operator" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">IN</span> <span class="token punctuation" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #a7b1b6;">(</span><span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">SELECT</span> something <span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">FROM</span> <span class="token keyword" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #48b0e8;">rows</span><span class="token punctuation" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #a7b1b6;">)</span><span class="token punctuation" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #a7b1b6;">;</span>
</code></pre><div><code class="language-sql" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; background-color: transparent; border-color: rgb(228, 228, 231); border-radius: 0px; border-style: solid; border-width: 0px; box-sizing: border-box; color: #d6deeb; font-family: var(--font-mono),ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,"Liberation Mono","Courier New",monospace; font-size: 1rem; font-weight: inherit; hyphens: none; line-height: 1.25; max-width: 100%; overflow-wrap: normal; padding: 0px; tab-size: 4; word-break: normal; word-spacing: normal;"><span class="token punctuation" style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgb(59 130 246 / .5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 #0000; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 #0000; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 #0000; --tw-shadow: 0 0 #0000; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border-color: rgb(228, 228, 231); border-style: solid; border-width: 0px; box-sizing: border-box; color: #a7b1b6;"><br /></span></code></div><p>Cool stuff. Nothing magical, but it's a good pattern to be familiar with.</p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-375156993845077472023-05-17T18:34:00.009+01:002023-06-15T09:04:39.176+01:00Naming, Notation, and Idioms conventions<p>Variable names is a big point of discussion in communities, projects, PRs...</p><p>I'm on the "terse" side of the spectrum, and I think many times variable names are hindering more than helping. I lay on the "more inline anonymous things" rather than "everything-has-to-have-a-very-specific-name-in-the-toplevel-of-a-file". Of course, names should convey their meaning, and if they fail to do so, it's a bad naming, but I want to talk about the more subtle process of choosing active/passive names, short/long,etc... that conveys how one thinks about the code while navigating it.<br /></p><p>Here are some of the concepts that inspire me on naming variables and organizing code "on the small". <br /></p><p>Misscomunicating via overconstraining and overcommunicating: Sometimes, we create a piece of code thought for a particular use case, and the more descriptive we name the variables, the less obvious it is that that functionality can be used in other situations. In strongly typed languages, this interchangeability of use cases and discoverability is already killed by the language "features", but in the languages I like to use, it's perfectly ok to call the same functions to objects of different semantic meanings, even of different type or structure.<br /></p><p><a href="https://developers.slashdot.org/story/16/07/14/1349207/the-slashdot-interview-with-larry-wall">Huffmanization</a>:"huffman coding" principle, meaning that things that are mentioned more often should be shorter.<br /></p><p><a href="http://www.wall.org/~larry/natural.html">Perl</a>: has so many good tips about naming, and how to understand the dimensions of concepts in programming. To name something, "topicalization" is introducing a topic you wanna talk about, and then be able to refer it with another (shorter) name (usually a pronoun) for some time, until that topic is out of scope.<br /></p><p><a href="https://www.youtube.com/watch?v=v7Mt0GYHU9A">APL</a>, <a href="https://news.ycombinator.com/item?id=13565743">SmallerCodeBetterCode</a>, <a href="https://www.sacrideo.us/suggestivity-and-idioms-in-apl/">Suggestivity</a>: Showing the guts of the algorithms inline has its benefits (see <a href="https://www.jsoftware.com/papers/tot.htm">Notation as a Tool of Thought paper</a>), and the apl family is a great showcase of that. Variable names and the symbols used for the operators have a great importance there too. Also, not binding names keeps the algorithms generic and reusable (at the human level, not binding an algorithm to a usecase but to the concrete thing it does to a data structure has way more suggestivity than a triple nested structure with its own accessors). <br /></p><p>K: The great k's <a href="http://www.nsl.com/papers/style.pdf">style.pdf</a> has so much unconventional knowledge I can only suggest to go and read it with fresh and curious eyes. So bold it's what got me into k. Also, "<a href="https://news.ycombinator.com/item?id=22504106">Stages of denial encountering K</a>"<br /></p><p>fast.ai naming conventions. Jeremy Howard's insights on programming are again, a bit controversial, but his tips have been always enlightening. If he says X, doesn't mean X is the only truth, but it means you should look at X with fresh and curious eyes, and maybe you'll discover a treasure. He comes from an APL and Perl background, and writes so nice terse code I can only agree with what he says over and over. Look at <a href="https://docs.fast.ai/dev/style.html">fast.ai style conventions</a>, and the <a href="https://docs.fast.ai/dev/abbr.html">fast.ai abbreviations list</a>. "<span class="U8d2H"><b>One line of code should implement one complete idea, where possible</b>"</span></p><p>Picolisp. I don't know how Alexander Burger chose all the names for <a href="https://software-lab.de/doc/ref.html">picolisp's core functions</a>, but there are lots of cool ideas both in the namings, and how the language api makes the code flow in a very natural way. (For example, @, or the `do` flexibility). Picolisp code tends to be very dense. Not apl/j/k-dense, but very dense still.</p><p><a href="https://code.kx.com/q/basics/peach/">k/q</a>: Also, lots of names to pick from, and the information density is pretty high. From k I picked the massive overloading of functions to do "the right thing" depending on the types. I've never seen such a compact core, and it's funny to think that the functions and their overloads seem quite intuitive after having learned them for some time. I never thought I'd say that, but here we are.</p><p><a href="https://www.complang.tuwien.ac.at/forth/gforth/Docs-html/">Forth</a> and <a href="https://docs.factorcode.org/content/article-vocab-index.html">Factor</a>: A bunch of words to pick from. If you're looking for a word, chances are that there's a Forth or Factor dictionary with a related concept. dup, dip, swap, bi2,.... If you get familiar with those, there's a bunch of names and name compositions you can start using in your code, and things will just make sense.</p><p><a href="https://clojure.github.io/clojure/">Clojure</a>: Clojure is of course very nice namewise, and it has very carefully choosen words. Even the threading macros, which are not names, but allow writing code in a way that leans to a very flow-y way. I never understood why `assert` doesn't return the first value in case of not throwing, but apart from that, no complains.</p><p><a href="https://www.red-by-example.org/">Red</a>: Red language is the one I know the least of this list (maybe along with forth/factor). The language has such impressive properties also, not found anywhere else that I know of. In that link I pasted, there's a function reference. Also, lots of interesting names to pick from.</p><p><a href="https://michaelfeathers.typepad.com/michael_feathers_blog/2013/11/unconditional-programming.html">Unconditional Code</a>: Michael Feathers did a very down-to-earth talk in <a href="https://www.youtube.com/watch?v=AnZ0uTOerUI">2018</a> about <a href="https://bminard.github.io/2019/12/unconditional-code">Unconditional Code</a>. The concept is not the same as the "anti-if" movement from the smalltalk community. In this case, Michael proposes to solve corner cases of our code by either changing the specs a little bit (many times making the code more generic), assume sane defaults (see Sussman's NYCLisp talk when he talks about flight autopilot, or John Ousterhout's Philosophy of Software Design), and some times, look for generality in the algorithm by exploiting data properties (examples of this is to make functions always process lists of things, and allowing empty lists so the function does nothing)<br /></p><p><a href="https://github.com/k-qy/notation">Other notations</a><br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-11940311289750436962023-05-05T11:04:00.002+01:002023-05-05T11:04:22.483+01:00Where to stop in the extensibility ladder<p>Here's a snippet where I explored different places where to "stop" on the ladder of extensibility/abstraction/suggestivity. It's at a very small scale, but those things add up.<br /></p><p>Code is in clojure, which makes it even easier to mix and match approaches, because symbols and sets are functions (Like K, where list access and function application looks the same) <br /></p><p><br /></p><p><br /></p>
<script src="https://gist.github.com/kidd/f857ea45575922e76aaf03e8402119c2.js"></script>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-45659287914810559422023-04-14T08:57:00.002+01:002023-04-14T08:57:16.962+01:00SQL UNNEST to iterate over an array<p>I'm a very happy user of <a href="https://supabase.com/blog/audit">supa_audit</a> library. This very small sql library allowed leverages some old postgres common knowledge about triggers and audits in a very nicely packed form, so you can track down changes to tables or rows in tables.</p><p>You can easily track changes in table "foo" just by running </p><p>SELECT audit.enable_tracking('foo'::regclass)</p><p>I started with very naive way of tracking down objects, by identifying them via `table_name`+`record->>'id'`. That sort of works, but it doesn't scale at all, requiring full scans for everything, and detoasting `record` when the record is big enough.</p><p>supa_audit provides this `to_record_id` function that generates the indexed record_id value for a given row. What happens if we want to run it for 2 ad-hoc values?</p><p>Well, there's `unnest`, which is very cool. It takes an array, and converts it to a table-like relation, so you can use it as a `from`. That way, we could process N values in one sql go with the following: <br /></p><p>select audit.to_record_id('tablename'::oid,'{id}', jsonb_build_object('id',id))::uuid as record_id from unnest(?)</p><p>Nice trick, right?<br /><br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-45222390066697743972023-03-29T08:31:00.005+01:002023-05-04T10:43:28.626+01:00State machines<p> I love state machines, and I use them in real world scenarios at least once a year.</p><p>The more I'm using them, in different languages or different situations, the more I get to "it's just an if" conclusion.</p><p><br /></p><p>In this <a href="https://news.ycombinator.com/item?id=35328995">HN post about xstate</a>, there's a <a href="https://gist.github.com/andymatuschak/d5f0a8730ad601bcccae97e8398e25b2">minimalistic implementation</a> in swift which I find a very nice distillation of how I use state machines usually. </p><p>switch [current-state, event ]</p><p> case ['foo', 'bar' ] -> ...</p><p><br /></p><p>And that's 90% of it.</p><p>I've used ruby's statemachine, clojure's tilakone and clj-statecharts (there's also this new <a href="https://github.com/yogthos/maestro">maestro </a>lib which I haven't used yet), and home made lua ones. </p><p>But the essence is, having a clear distinction of where am I, what I get, and what do I have to do. For the "what do I have to do", here's what I usually do:</p><p>- Updating the state of an object (usually the one bound to the state machine) is fine. <br /></p><p>- I tend to run actions only for things that need to be immediate. Sending an alert in a UI has to be synchronous I guess. <br /></p><p>- But if I can, I like to run some idempotent process that will run the actions. That process should notice that the state machine has stuff to do. That process is the one that has the knowledge of actions that should happen in XYZ situation. This process runs them, and logs them somewhere, so that next polling iteration doesn't redo them. These actions are things like "cancel a subscription after being unpaid for x days". The state machine will set the status to "unpaid", and set the cancel-at, but the polling process will check for "unpaid" subscriptions with cancel-at<now() that are still running, and kill them for good.<br /></p><p><br /></p><p>Further reading on xstate and fsm and one/two/three level fsms:</p><p>- <a href="http://www.findinglisp.com/blog/archives/2004_06_01_archive.html">http://www.findinglisp.com/blog/archives/2004_06_01_archive.html</a><br /></p><p>- <a href="https://www.industriallogic.com/patterns/P22.pdf">https://www.industriallogic.com/patterns/P22.pdf</a></p><p>- <a href="https://hillside.net/plop/plop2003/Papers/Adamczyk-State-Machine.pdf">https://hillside.net/plop/plop2003/Papers/Adamczyk-State-Machine.pdf</a><br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-24791466727094198912023-03-26T23:17:00.004+01:002023-03-26T23:17:19.353+01:00 Finding Approximately Repeated Patterns in Time Series I found this talk about time series which seems super cool, about smart things people can do with time series<br /><p>https://www.youtube.com/watch?v=BYjOp2NoDdc</p><p>And here are some more details in <a href="https://matrixprofile.org/posts/what-are-time-series-discords/">time series discords </a><br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-69451847361627639882023-03-12T23:14:00.003+00:002023-03-12T23:14:16.305+00:00Johnny.Decimal<p>From this <a href="https://news.ycombinator.com/item?id=35122780">HN thread</a> I just discovered a file/info organization system called <a href="https://johnnydecimal.com/">Johnny Decimal</a>.</p><p>And it looks very very sane. And I think it can be easily combined with the person-action-object memory system I'm already using.</p><p>Going to try it and report back.<br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-77379174663358999082023-02-16T12:21:00.000+00:002023-02-16T12:21:52.298+00:00SQL window functions<p>SQL's window functions are such a huge topic that some see them as a DSL inside SQL itself. </p><p>You can do amazing things in sql without window functions, but when you need sophisticated rankings, or sorting subgroups, or you feel you are struggling with `group by + having + order by`, window functions are usually the answer.</p><p> To me, <a href="https://www.youtube.com/watch?v=XO1WnmJs9RI">Bruce Momjian's talk about window functions</a> has been my default resource whenever I need a refresher.</p><p>But today I found a teaser of a <a href="https://antonz.org/sql-window-functions-book/">book on window functions</a> that's coming that looks very very good. I'll keep an eye on this page. Also, the pic is from that site, so it deserves a link from my high-traffic blog (you're welcome).<br /></p><p style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQ858LDxTkX0GyYVbCZrMJYkTXHwToYP84zbuVtpBqVB9Qq_8XmucNaTqlWtvIYURfZlhYDU9tmQDX26xKwuk7W5L3UiscLyYGlIzjbU-4xhOQuzYmf3qGYzeEDQh0s_Bl8KuQT687MKSMNEDQtywd6fgg5vDvOj4CccmhGB-J5F-nM4DsucjZDmoYqg/s1080/how-i-see-complex-queries.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1080" data-original-width="1080" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQ858LDxTkX0GyYVbCZrMJYkTXHwToYP84zbuVtpBqVB9Qq_8XmucNaTqlWtvIYURfZlhYDU9tmQDX26xKwuk7W5L3UiscLyYGlIzjbU-4xhOQuzYmf3qGYzeEDQh0s_Bl8KuQT687MKSMNEDQtywd6fgg5vDvOj4CccmhGB-J5F-nM4DsucjZDmoYqg/s320/how-i-see-complex-queries.png" width="320" /></a></p><br />Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-79856719980400049682023-02-13T08:27:00.004+00:002023-02-13T16:11:29.953+00:00Duckdb news and posts, early 2023<p>Motherduck is lately increasing their activity on the net, and they recently published a couple of interesting blogposts:</p><p>One, with the provocative-cliche of "<a href="https://motherduck.com/blog/big-data-is-dead/">Big Data is dead</a>", which had quite a bit of traction in HN, and the other, a guest post, much more practical and "fun": "<a href="https://motherduck.com/blog/solving-advent-code-duckdb-dbt/">solving aoc with duckdb</a>".</p><p>If you follow what happens in duckdbland, you'll know that the duckcon happened during FOSDEM2023. <a href="https://www.youtube.com/@duckdblabs1237/videos">Here are the videos</a>. </p><p>One of the talks (not in duckcon, but in FOSDEM), talks about pytohn+duckdb. It's very nice to be able to use pandas dataframes from duckdb with the 0 copy approach.</p><p>Another talk is about duckdb extensions. Very DWIM, and in general, the practical approach the team has is quite refreshing. Unfortunately, I don't do C++, so writing extensions is out of my league. But I started thinking what would it take to be able to write extensions in Lua(JIT?). Probably a low level wizard could make it happen quite fast. But performance wise... no idea.</p><p>Also, the biggest news: <a href="https://duckdb.org/2023/02/13/announcing-duckdb-070.html">DuckDB 0.7.0 is out!</a><br /></p><p><br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-34050556227136279892023-01-17T21:54:00.003+00:002023-05-04T17:19:37.182+01:00DuckDB's arg_max<p>In kdb+ there's a great way of showing aggregated information called `aj` for <a href="https://code.kx.com/q/ref/aj/">asof join</a>. This way of joining data is a neat way to get the "latest record" in a join. </p><p>These kinds of operations are already quite painful to do in plain sql when grouping by. Ever had to get the latest invoice of each customer? probably you had to nest a query on top of another one, or use a fancy "distinct on".</p><p>I realized though, that some analytical engines have something called `<a href="https://duckdb.org/docs/sql/aggregates.html">arg_max</a>`, that can alleviate the pain when doing nested group by. And Duckdb already implements it! (discussion <a href="https://github.com/duckdb/duckdb/discussions/3207">here</a>)<br /></p><p>First, let's create a table with a few values ready to group and get the max. Then, we'll want to get the id of the max of every group. At that point, arg_max just does the right thing.</p><p> </p><p>Btw, look at that nice Duckdb's <a href="https://duckdb.org/2022/05/04/friendlier-sql.html#group-by-all">group by all</a>. <br /></p>
<script src="https://gist.github.com/kidd/97cfcd7fb42fc2b03c8c812559bb01d2.js"></script>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-29308549713807174082022-12-21T17:49:00.008+00:002022-12-22T14:51:13.269+00:00Trying things out with duckdb's dbgen<p>REPLs are great, but they are only a part of the "have fun" experience.</p><p>Sometimes, you need data to work on a repl and to be able to simulate the "real world".</p><p>In clojure, I've used <a href="https://sweet-tooth.gitbook.io/specmonstah/">specmonstah</a> and I quite like it. It can build nested structures nicely and once you configured the db schema and the specs of the individual properties, you can use it as a building block from your code, to generate trees of data.<br /></p><p>For sql, I got pretty wild with generate_series, and `create table XXX as select` and you can get quite far with those. </p><p>But recently I discovered `dbgen` function in DuckDB and I'm pretty happy with it: this function (that comes bundled in any duckdb binary) can generate TPC-H sample databases, including (scale factor as a parameter!).</p><p>I find myself using it more and more in order to test some sql I have in mind that I'm not sure if it would work or not. It's really good that it gets you from "I wonder what would happen if...." to "aha, I see" in a shorter time than any of my previous approaches to scaffolding sample data for sql tests.</p><p>The usage is pretty simple:</p><p>`call dbgen(sf=0.1)`, and off you go!<br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-86029049789704072232022-12-18T19:48:00.009+00:002022-12-21T17:39:53.602+00:00Going meta until it hurts, with Brian Cantwell Smith<p>I realized today I haven't mentioned Brian Cantwell Smith in this blog before. I'm not intimately familiar with his work, but I've read some parts of his papers, thesis, and talks.</p><p>And I can tell you, dear reader... if you're into meta/circular concepts, you'll have a great time reading his work.</p><p>Here are some links. The first one is a new talk by him, and later are works I've read and caused some impression on me. Look for more of his talks. All of them are pretty deep, and not " run on the mill" talk you find in youtube.<br /></p><ul style="text-align: left;"><li>https://student.cs.uwaterloo.ca/~cs492/11public_html/p18-smith.pdf <- limits of correctness . I loved it. it's the most light of the links here<br /></li><li>https://www.youtube.com/watch?v=0eE-CTX96v8</li><li>https://www.ics.uci.edu/~jajones/INF102-S18/readings/17_Smith84.pdf <br /></li><li>https://dspace.mit.edu/handle/1721.1/15961</li></ul><p> I find these themes pretty dense, but it's enjoyable to have a peek from time to time. Similar to Dennet's, <a href="https://www.youtube.com/watch?v=NNnIGh9g6fA">Spolski's</a>, and Hofstadter's </p><p><br /></p><p>Have fun<br /></p><p><br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-61438258090042431832022-12-18T11:49:00.000+00:002022-12-18T11:49:01.287+00:00Update primary key and associated foreign keys in sql <p>If you have two tables with a foreign_key relationship, and you want to update the value of a primary key, postgresql will fail, telling you that there are associated tables with foreign keys to that value, so you can't break the consistency.</p><p><br /></p><p><b>update subscription set cus_id=2 where cus_id=1;</b><br /></p>ERROR: 23503: update or
delete on table "subscription" violates foreign key constraint
"xxxxxx_id_fkey" on table
"xxxxx"<br />DETAIL: Key (id)=(1) is still referenced from table "xxxxx".<br />LOCATION: ri_ReportViolation, ri_triggers.c:2797<p>Oh, I know what I'll do (you think), I'll change both inside a transaction:</p><p><b>begin; update subscription set cus_id=2 where cus_id=1;...</b><br />ERROR: 23503: update or
delete on table "subscription" violates foreign key constraint
"xxxxxx_id_fkey" on table
"xxxxx"<br />DETAIL: Key (id)=(1) is still referenced from table "xxxxx".<br />LOCATION: ri_ReportViolation, ri_triggers.c:2797</p><p>Oh, the same :( So even inside a transaction, we can't have inconsistent states.<br /></p><p>So.... The trick to let postgres update both columns of the two tables at once is to use a cte. Common Table Expressions have many different uses, and one of them is to bundle multiple queries in a single one. When used with update/inserts it allows these kinds of tricks.</p><p><b>with c as (update customer set id=42 where id=1),<br /> s as (update subscription set cus_id=42 where cus_id=1)<br />select 1;</b></p><p>As a_horse_with_no_name explains <a href="https://stackoverflow.com/questions/34383412/how-to-update-rows-of-two-tables-that-have-foreign-key-restrictions">here</a>:</p><blockquote><p>As (non-deferrable) foreign keys are evaluated on statement level and both the primary and foreign key are changed in the same <em>statement</em>, this works.</p></blockquote>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0tag:blogger.com,1999:blog-8696405790788556158.post-79960514267962376922022-12-12T20:29:00.004+00:002023-02-10T09:56:09.005+00:00Sum scan in sql<p>`scan` is an adverb in <a href="https://en.wikipedia.org/wiki/APL_(programming_language)">apl</a>/<a href="https://en.wikipedia.org/wiki/J_(programming_language)">j</a>/<a href="https://en.wikipedia.org/wiki/K_(programming_language)">k</a> which is used to reduce an array over an operation, while keeping all the intermediate results. It's used in lots of very creative ways in the array programming languages. Here's some sample examples of "scan (\)". <br /></p><p>:x:1+!9<br />1 2 3 4 5 6 7 8 9<br />+\x<br />1 3 6 10 15 21 28 36 45<br />*\x<br />1 2 6 24 120 720 5040 40320 362880<br /><br /></p><p>In most of those programming languages, scan acts as an adverb, which is a higher order function that modifies a verb. In clojure, you can get similar results with `<a href="https://clojuredocs.org/clojure.core/reductions">reductions</a>`.</p><p>Anyway, the topic of today is how to accumulate intermediate results like these, in sql.</p><p>Well, in the AoC 2022 (day 1), the first part was about summing numbers by paragraphs. </p><p>Some people at duckdb discord are sharing their solutions, and we see it's kinda normal to use `row_number() over() as idx` idiom to add ascending numbers to any projection.</p><p>Another trick, to indicate the paragraph number in each line (for future group_by purposes), this is what <a href="https://db.cs.uni-tuebingen.de/team/members/torsten-grust/">Teggy</a> writes:</p><p> `select sum(i.value is null :: int) over (order by num)`</p><p>Here I show generate_series, row_number, and the sum scan based on "is null". </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgr_7Er4bxx34ww9HCpq84aoEPjiexq7DkJlSB_kSNzDTGOr0GeQC_JLUusLY3f7Q7e5wbsEUSlxVYmk0FAH2MApZoeredLV5-9s4GTj0KjOaaWWC2BLwejZDehsOYp0K31xbGbKD5xKIm8GfhB7Z7zwuyxjnljYxcVUuaaoHQGujLK6y446g9jybrGUA/s936/sshot--2022-12-12--202730.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="699" data-original-width="936" height="478" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgr_7Er4bxx34ww9HCpq84aoEPjiexq7DkJlSB_kSNzDTGOr0GeQC_JLUusLY3f7Q7e5wbsEUSlxVYmk0FAH2MApZoeredLV5-9s4GTj0KjOaaWWC2BLwejZDehsOYp0K31xbGbKD5xKIm8GfhB7Z7zwuyxjnljYxcVUuaaoHQGujLK6y446g9jybrGUA/w640-h478/sshot--2022-12-12--202730.png" width="640" /></a></div><p></p><p><br /></p>Raimon Grauhttp://www.blogger.com/profile/15545476302081532235noreply@blogger.com0