--- title: "A new monster of an SQL query" publication: "Postgres Weekly" issue: 538 published_at: "2024-01-17" canonical_url: "https://postgresweekly.com/issues/538" --- Plus a big pg_query update, Tembo Cloud goes live, and altering generation expressions. | #​538 — January 17, 2024 [Web Version](https://postgresweekly.com/issues/538) Together with [![Crunchydata\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,w_430,h_100/e_make_transparent/co_white,e_outline:7/xyflkz7n00elwtgja9ne.png)](https://www.crunchydata.com/products/crunchy-bridge?utm_source=cppg) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/efoegvdhvcam6sdo1f6h.jpg)](https://explainextended.com/2023/12/31/happy-new-year-15/) [Implementing GPT in 500 Lines of SQL](https://explainextended.com/2023/12/31/happy-new-year-15/) — When an article pitches itself as demonstrating _‘a complete GPT2 implementation as a single SQL query in PostgreSQL’_, you know you’re in for a serious ride. Can the algorithms involved in a large language model be reimplemented in SQL? _(It's not going to replace ChatGPT any time soon, but the eventual query in this post is a sight to behold..)_ Quassnoi [Postgres 17's `ALTER TABLE` Command to Change Generation Expressions](https://www.depesz.com/2024/01/15/waiting-for-postgresql-17-alter-table-command-to-change-generation-expression/) — When you `CREATE` a table, you can define [_generated columns_](https://www.postgresql.org/docs/current/ddl-generated-columns.html) that are automatically generated by way of an expression. If you want to _change_ such expressions later on, Postgres 17 will have an answer in the form of `ALTER COLUMN ... SET EXPRESSION`. Hubert depesz Lubaczewski [![\](https://copm.s3.amazonaws.com/43e2f1bb.png)](https://www.crunchydata.com/products/crunchy-bridge?utm_source=cppg) [❤️ Postgres](https://www.crunchydata.com/products/crunchy-bridge?utm_source=cppg) — You need a database provider that loves Postgres as much as you do. We'll take care of all the hassle - monitoring, backups, HA, disaster recovery so you don't have to. Want amazing support? We'll be there when you have questions. Crunchy Bridge sponsor [Introducing `pg_query` for Postgres 16](https://pganalyze.com/blog/pg-query-postgres-16) — [pg_query](https://github.com/pganalyze/pg_query) is a long standing _standalone_ library (with Ruby, Rust and Go bindings) for turning SQL queries into parse trees. This new version is based on Postgres 16, supports SQL/JSON, and now works on Windows too. Fittl & Mijalkovic (pganalyze) [Optimizing Postgres Functions with Preloaded Libraries](https://www.cybertec-postgresql.com/en/optimizing-postgresql-functions-with-preloaded-libraries/) — Thanks to extensions, it’s possible to write Postgres functions in numerous different languages, but those extensions need to be loaded when needed – the `session_preload_library` can be used to preload libraries at connection start, shaving off precious milliseconds. Hans-Jürgen Schönig _IN BRIEF:_ - 📘 [Henrietta Dombrovskaya has announced](https://hdombrovskaya.wordpress.com/2024/01/11/the-optimization-book-second-edition-is-here/) the publication of the second edition of _PostgreSQL Query Optimization_ by Apress. - It's a bit of an 'info dump' for now, but Takayuki Tsunakawa has [been working](https://postgres-road.blogspot.com/2024/01/introducing-new-community-wiki-page.html) on a new Postgres wiki page: '[Operations cheat sheet.](https://wiki.postgresql.org/wiki/Operations_cheat_sheet)' It's a collection of wisdom and links from a variety of Postgres blogs on topics as broad as security, scalability, and database structure. - 📅 The CFP for PgConf․dev 2024 has closed, but [you can now register to attend](https://2024.pgconf.dev/registration/) this May 28-31 in Vancouver, Canada. - 📅 In other event news, [FOSSASIA PGDay 2024](https://www.postgresql.org/about/news/fossasia-pgday-2024-takes-place-in-hanoi-vietnam-on-april-9-2788/) will be taking place in Hanoi, Vietnam this April. [Its CFP is open](https://eventyay.com/e/55d2a466/cfs) for two more weeks. - [React + Postgres](https://react-postgres-components.vercel.app/) = 🤯 - Pavel Luzanov [skims through the latest PG Commitfest](https://postgrespro.com/blog/pgsql/5970508) if you want a peek at some of the many tweaks coming to future Postgres versions. - Tembo has announced [the general availability of Tembo Cloud,](https://tembo.io/blog/ga/) their managed Postgres platform. ▶[Waiting for Postgres 17: Incremental Base Backups](https://pganalyze.com/blog/5mins-postgres-17-incremental-backups) — Walking through the incremental backup feature committed to the Postgres 17 development branch by Robert Haas. _(6-minute video with transcript.)_ Lukas Fittl [Password Encryption Without Leaking Credentials](https://launchbylunch.com/posts/2024/Jan/16/postgres-password-encryption/) — Logs and proxies can sometimes open up unexpected holes into your system’s security, but you can work with user passwords over the wire using pre-hashed versions. Sehrope Sarkuni [The `postmaster.pid` File Explained](https://www.crunchydata.com/blog/postgres-postmaster-file-explained) — A short file with a long history. Greg Sabino Mullane 🛠 Code and Tools [Procrastinate 1.1: Postgres-Based Task Queue for Python](https://procrastinate.readthedocs.io/en/stable/) — A distributed task processing library for Python 3.8+ that uses Postgres to store task definitions, manage locks, and dispatch tasks. It can be used in both sync and async code, as well as in Django apps. [GitHub repo.](https://github.com/procrastinate-org/procrastinate) Jablon and Lemoine [Steampipe: Query Your Cloud Services with SQL](https://steampipe.io/) — Use SQL to query and even join across [over 100 APIs](https://hub.steampipe.io/plugins) from providers like Airtable, AWS, Heroku, Slack and Stripe, as well as social media sites like Reddit and Hacker News. It’s [open source](https://github.com/turbot/steampipe) (AGPL) and **there are now [extensions to use it](https://steampipe.io/blog/2023-12-postgres-extensions) from _within_Postgres.** Steampipe [pg_back 2.2.0: A Simple, Thorough Backup Tool for Postgres](https://github.com/orgrim/pg_back) — A tool for dumping your databases to files, including roles, server parameters, and more, in the format of your choice. This new version adds support for public and private keys for encryption with AGE, some bugfixes and an example `Dockerfile` plus other configs to ease deployment on Kubernetes. Nicolas Thauvin - [pgcopydb 0.15](https://github.com/dimitri/pgcopydb) – Turbocharged `pg_dump|pg_restore` between servers. - [GoBackup 2.8](https://github.com/gobackup/gobackup) – Go-powered CLI tool for cloud database backups. - [pg-mem 2.8.1](https://github.com/oguimbal/pg-mem) – Experimental in-memory Postgres emulation in JS. - [Hasql 1.6.4](https://github.com/nikita-volkov/hasql) – Fast Postgres client library for Haskell. - [plpgsql_check 2.7.2](https://github.com/okbob/plpgsql_check) – PL/pgSQL linter. - [pg_partman / PostgreSQL Partition Manager 5.0.1](https://github.com/pgpartman/pg_partman/releases/tag/v5.0.1) **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. _Thanks!_ --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*).