######octothorp.es

#postgres

  • POC: PGXN Binary Distribution Format

    https://justatheory.com/2024/06/trunk-poc

    A proof of concept for “trunk”, the proposed binary distribution format for Postgres extensions.

  • Mini Summit Five

    https://justatheory.com/2024/05/mini-summit-five

    Links, notes, and commentary from Yurii Rashkovskii’s presentation, “Universally Buildable Extensions: Dev to Prod,” at the fifth Postgres Extension Ecosystem Mini-Summit on May 1, 2024.

  • Dynamic OFFSETs and LIMITs

    https://justatheory.com/2009/01/postgres-dynamic-limit

  • JSON Path Operator Confusion

    https://justatheory.com/2023/10/sql-jsonpath-operators

  • PGXN v2: Go or Rust?

    https://justatheory.com/2024/03/pgxn-language-choices

  • Compiling libreadline on Mac OS X

    https://justatheory.com/2004/10/compile-libreadline

  • Bricolage 1.8.3 Released

    https://justatheory.com/2004/11/bricolage-1.8.3

    The Bricolage development team is pleased to announce the release of Bricolage 1.8.3. This maintenance release addresses a number of issues in Bricolage 1.8.2. The most important changes eliminate or greatly reduce the number of deadlocks caused during bulk publishes of many documents. Other changes include new contributed scripts for importing contributors and for generating thumbnail images, Russian localization, and various fixes for database transaction, template formatting, and various user interface fixes. Here are the other highlights of this release:

  • Committed: pgTAP Result Set Assertion Functions

    https://justatheory.com/2009/07/pgtap-results-eq

    Regular readers will know that I’ve been thinking a lot about testing SQL result sets and how to how to name result testing functions, and various implementation issues. I am very happy to say that I’ve now committed the first three such test functions to the Git repository. They’ve been tested on 8.4 and 8.3. Here’s what I came up with.

  • Fixing Foreign Key Deadlocks in PostgreSQL

    https://justatheory.com/2010/11/postgres-fk-locks-project

  • Release: pgxn_meta v0.1.0

    https://justatheory.com/2024/08/pgxn_meta_release

    Today I released pgxn_meta v0.1.0, a Rust crate and executable for validating PGXN Meta v1 and v2 META.json files.

  • PGXN V2 Architecture

    https://justatheory.com/2024/04/pgxn-v2-architecture

    I’ve written and published a document outlining the vision and strategy for the next iteration of PGXN. It includes diagrams. Everybody loves diagrams.

  • Patch: Postgres ABI and API Guidance

    https://justatheory.com/2024/06/abi-api-guidance

    Dear Postgres extension developers: Please review and give feedback on the proposed patch adding ABI and API guidance to the C language documentation.

  • Unicode Normalization in SQL

    https://justatheory.com/2009/09/postgres-unicode-normalization

  • Introducing MyTAP

    https://justatheory.com/2010/07/introducing-mytap

    After some prodding from the MySQL Community Manager, some OSCON hacking yields tangible results.

  • pgenv

    https://justatheory.com/2018/08/pgenv

    I wrote a simple PostgreSQL binary manager. You should try it.

  • RFC: A Simple Markdown Table Format

    https://justatheory.com/2009/02/markdown-table-rfc

    I’ve been thinking about markdown tables a bit lately. I’ve had in mind to follow up on my definition list proposal with a second proposal for the creation and editing of simple tables in Markdown. For better or for worse, an aside on the markdown-discuss mail list led to a longish thread about a syntax for continuing lines in tables (not to mention a long aside on the use of monospaced fonts, but I digress), wherein I realized, after an open-minded post from MultiMarkdown’s Fletcher Penney, that I needed to set to working up this request for comments sooner rather than later.

  • Mini Summit Four

    https://justatheory.com/2024/04/mini-summit-four

    Links, notes, and commentary from Jonathan Katz’s presentation at the fourth Postgres Extension Ecosystem Mini-Summit, “Trusted Language Extensions for PostgreSQL”.

  • Managing Key/Value Pairs in PostgreSQL

    https://justatheory.com/2010/08/postgres-key-value-pairs

  • PGXN Language Poll Result

    https://justatheory.com/2024/05/pgxn-language-poll-result

    Results of the Postgres community poll for building PGXN v2 in Go, Rust, or both.

  • Higher-Order PL/pgSQL

    https://justatheory.com/2006/05/higher-order-plpgsql

    Well, it’s not really higher-order PL/pgSQL, since the language doesn’t support closures, as far as I know. But I have been working on a series of articles for O’Reilly Databases site, drawing inspiration from Mark Jason Dominus’s Higher-Order Perl, and specifically using the Fibonacci sequence to create example PL/pgSQL functions. It turns out that, while the Fibonacci sequence may not be of much use in day-to-day database work, it makes for great pedagogy. And I learned a fair bit along the way, as well.

  • Thoughts on Testing SQL Result Sets

    https://justatheory.com/2009/06/pgtap-comparing-relations

    pgTAP: The Critique I’ve been continuing hacking on pgTAP in order to add a lot more schema-testing functionality and a few other niceties. But back when I started the project, I using it to write tests for CITEXT, which was great for my sanity as I developed it, but proved a bit controversial. In a pgsql-hackers post, Tom Lane wrote:

  • 🏔 Extension Ecosystem Summit 2024

    https://justatheory.com/2024/06/extension-ecosystem-summit

    A brief report on the PostgreSQL Extension Ecosystem Summit at PGConf.dev 2024 in Vancouver, Canada.

  • PostgreSQL + OSSP UUID on Mac OS X

    https://justatheory.com/2009/04/postgres-ossp-uuid

  • PL/pgSQL Talk Slides Posted

    https://justatheory.com/2006/07/plpgsql-talk-slides

  • Bricolage Now has PHP 5 Templating

    https://justatheory.com/2005/08/bricolage-1.9.0

  • POSETTE 2024

    https://justatheory.com/2024/06/posette

    I attended and gave a presentation at POSETTE, an event for Postgres. This post highlights some talks and the slides for my own.

  • Mini Summit Three

    https://justatheory.com/2024/04/mini-summit-three

    A rough transcript of my Devrim Gündüz’s talk, “Overview of {yum,zypp}.postgresql.org, and the challenges around RPMifying extensions”, along with interspersed chat comments.

  • Introducing Go SQL/JSON Path and Playground

    https://justatheory.com/2024/07/go-sqljson-path

    Introducing the SQL/JSON Path package, a PostgresSQL-compatible jsonpath parser and executor in Go. Also: a Wasm-based playground!

  • PostgreSQL Development: Lessons for Perl?

    https://justatheory.com/2009/07/pg-vs-perl-dev

    Pondering Conservatism I’ve been following chromatic’s new blog since it launched, and have read with particular interest his posts on the Perl 5 development and release process. The very long time between releases of stable versions of Perl has concerned me for a while, though I hadn’t paid much attention until recently. There has been a fair amount of discussion about what it means for a release to be “stable,” from, among others, now-resigned Perl Pumpking Rafael Garcia-Suarez and Perl 5 contributor chromatic. Reading this commentary, I started to ponder what other major open-source projects might consider “stable,” and how they manage stability in their development and release processes. And it occurred to me that the Perl 5 code base is simultaneously treated too conservatively and – more importantly – not conservatively enough. What open-source projects treat their code highly conservatively?

  • My Adventures with Mac OS X

    https://justatheory.com/2002/11/my-osx-adventures

    I recently decided to make the leap from Yellow Dog Linux to Mac OS X on my Titanium PowerBook. Getting everything to work the way I wanted proved to be a challenge, but well worth it. This document outlines all that I learned, so that neither you nor I will have to experience such pain again. The overall goal was to get Bricolage up and running, figuring that if it worked, then just about any mod_perl based solution would run. I’m happy to say that I was ultimately successful. You can be, too.

  • I'm a Postgres Extensions Tembonaut

    https://justatheory.com/2024/01/tembonaut

    Near year, new job. I accepted a new position at Tembo to work on improving the PostgreSQL extension ecosystem full time.

  • Test and Release pgrx Extensions with pgxn-tools

    https://justatheory.com/2024/04/pgxn-tools-pgrx

    The v1.6.0 release of the pgxn-tools Docker image adds a new command to efficiently build and test pgrx extensions on a wide variety of Postgres versions.

  • Contemplating Decentralized Extension Publishing

    https://justatheory.com/2024/02/decentralized-extension-publishing

    The Go package ecosystem uses distributed publishing to release modules without authentication or uploads. Could we do something similar for Postgres extensions?

  • How to Generate Recurring Events in the Database

    https://justatheory.com/2008/01/postgres-recurring-events

    Going deep on using Postgres functions to create efficient recurring events.

  • Enable CSV Logging in PostgreSQL

    https://justatheory.com/2009/08/enable-postgres-csvlogging

    One of the cooler features of recent versions of PostgreSQL is support for CSV-formatted logging. I’ve never had a chance to use it, but after reading Josh’s cool hack for determining sums of concurrent queries using windowing functions in PostgreSQL 8.4 to query a table generated from a CSV log, I just had to give it a try. But while there is decent documentation for loading up the contents of a CSV-formatted log file, there I couldn’t find simple information on how to set it up. So I figured it out and record it here for posterity.

  • RFC: Extension Packaging & Lookup

    https://justatheory.com/2024/11/rfc-extension-packaging-lookup

    A proposal to modify the PostgreSQL core so that all files required for an extension live in a directory named for the extension, along with a search path to find extension directories.

  • Bootstrapping Bucardo Master/Master Replication

    https://justatheory.com/2013/02/bootstrap-bucardo-mulitmaster

    Let’s say you have a production database up and running and you want to set up a second database with Bucardo-powered replication between them. Getting a new master up and running without downtime for an existing master, and without losing any data, is a bit fiddly and under-documented. Having just figured out one way to do it with the forthcoming Bucardo 5 code base, I wanted to blog it as much for my own reference as for yours.

  • Catalyst with DBIx::Connector and Template::Declare

    https://justatheory.com/2009/11/catalyst-tutorial-continued

    Following up on my post yesterday introducing Catalyst::View::TD, today I’d like to continue with the next step in chapter 3 of the Catalyst tutorial. The twist here is that I’m going to use PostgreSQL for the database back-end and start introducing some database best practices. I’m also going to make use of my DBIx::Connector module to interact with the database.

  • Extension Ecosystem Summit 2025

    https://justatheory.com/2025/03/extension-ecosystem-summit

    We’re doing it again! The PostgreSQL Extension Ecosystem Summit returns to PGConf.dev. We’ll again be looking at indexing, discovery, packaging, and core support. And we’ll once again be hosting a series of “Mini Summits” leading up to the event. Join us!

  • Validating Time Zones in PostgreSQL

    https://justatheory.com/2007/11/postgres-timezone-validation

  • Compiling Postgres with LLVM

    https://justatheory.com/2024/06/compile-postgres-llvm

  • Mini Summit One

    https://justatheory.com/2024/03/mini-summit-one

    A rough transcript of my talk “State of the Extension Ecosystem”, along with interspersed chat comments and appearances by Bagel.

  • Always Use TIMESTAMP WITH TIME ZONE

    https://justatheory.com/2012/04/postgres-use-timestamptz

  • PGXN Challenges

    https://justatheory.com/2024/01/pgxn-challenges

    Some thoughts on the challenges for PGXN’s role in the ideal PostgreSQL extension ecosystem of the future.

  • Enforcing a Set of Values

    https://justatheory.com/2010/01/enforce-set-of-postgres-values

  • My Catalyst Tutorial: Add Authors to the View

    https://justatheory.com/2009/11/sql-view-aggregate-magic

  • The History and Future of Extension Versioning

    https://justatheory.com/2024/02/extension-versioning

    What versioning standard should be used for Postgres extension distribution? Some context from PostgreSQL and PGXN, a survey of the version standard landscape today, and a recommendation.

  • Introducing pgTAP

    https://justatheory.com/2008/06/introducing-pgtap

    So I started working on a new PostgreSQL data type this week. More on that soon; in the meantime, I wanted to create a test suite for it, and wasn’t sure where to go. The only PostgreSQL tests I’ve seen are those distributed with Elein Mustain’s tests for the email data type she created in a PostgreSQL General Bits posting from a couple of years ago. I used the same approach myself for my GTIN data type, but it was rather hard to use: I had to pay very close attention to what was output in order to tell the description output from the test output. It was quite a PITA, actually.

  • PGConf.dev 2024

    https://justatheory.com/2024/06/pgconf-future-ecosystem

    At PGConf.dev, I attended some great talks, made one of my own, and enjoyed the commearderie of fellow PostgreSQL extension authors and core developers. A brief report.

  • ⛰️ Postgres Ecosystem Summit EU

    https://justatheory.com/2024/10/postgres-ecosystem-summit-eu

    The sequel to the successful PGConf.dev event, the Extension Ecosystem Summit EU will showcases some exemplary extension use cases.

  • RFC: PGXN Metadata Sketch

    https://justatheory.com/2024/03/rfc-pgxn-metadata-sketch

    Request for comments on a sketch of a new metadata standard for Postgres extension packaging, distribution, and delivery, building on the PGXN Meta Spec to address its shortcomings and emerging use cases 12 years on.

  • SQL/JSON Path Playground Update

    https://justatheory.com/2024/12/playground-update

    The Go SQL/JSON Playground has been updated with a fresh design and PostgreSQL 17 compatibility.

  • Extension Ecosystem Summit 2024

    https://justatheory.com/2024/02/extension-ecosystem-summit

    Some pals and I organized a summit at PGConf.dev on May 28 to work together as a community toward comprehensive indexing, discovery, and binary distribution, as well as a speaker series “mini summit” leading up to the event.

  • Mini Summit Two

    https://justatheory.com/2024/03/mini-summit-two

    A rough transcript my Ian Stanton’s talk, “Building Trunk: A Postgres Extension Registry and CLI”, along with interspersed chat comments.

  • CipherDoc: A Searchable, Encrypted JSON Document Service on Postgres

    https://justatheory.com/2023/10/cipherdoc

    I gave a talk at PGCon this year on a privacy-first data storage service I designed and implemented. Perhaps the encryption and searching patterns will inspire others.

  • Benchmarking PostgreSQL Functions

    https://justatheory.com/2006/05/benchmarking-functions

    Update 2006-05-19: I realized that there was a nasty error in my algorithm for determining the runtime of a function: It was only fetching the milliseconds part of the runtime, without adding in seconds and minutes! This led to getting negative runtimes then the milliseconds part of the end time was less than the milliseconds part of the start time. Ugh. But with the help of yain on IRC, I’ve switched to calculating the number of seconds by converting the start and end times to epoch seconds (which have subsecond precision in PostgreSQL, and now things are just dandy. While I was at it, I reorganized the function so that it was a bit easier to read, by constructing the created function in the order it would be executed, and fixed the caching problem, as suggested by Aidan in a comment below.

  • Extension Registry Namespacing RFC

    https://justatheory.com/2024/03/extension-namespace-rfc

    A proposal for an additional level of name uniqueness for Postgres extension packaging and distribution, based on URIs.

  • POC: Distributing Trunk Binaries via OCI

    https://justatheory.com/2024/06/trunk-oci-poc

    Would it be possible to distribute Postgres extension binaries via Open Container Registries? Tune in to find out!

  • Mini Summit Six

    https://justatheory.com/2024/05/mini-summit-six

    A rough transcript of the sixth and final Extension Ecosystem Mini-Summit, in which we review potential topics for the in-person summit at PGConf.dev and discuss how to organize it.

    Follow #postgres on RSS or use the

    JSON API 
    
curl -X GET \
  -H "Content-type: application/json" \
  -H "Accept: application/json" \
  "https://octothorp.es/~/postgres"