#postgres

• Always use the C Locale with PostgreSQL

  https://justatheory.com/2004/08/postgres-always-use-c-locale/

• Validating Time Zones in PostgreSQL

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

• pgenv

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

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

• 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.

• 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.

• 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”.

• RFC: PGXN Meta Spec v2

  https://justatheory.com/2024/07/rfc-pgxn-meta-v2/

  PGXN Meta Spec v2 represents a significant reworking of the original spec for PGXN distributions. It would very much benefit from feedback from Postgres extension and developers.

• 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.

• 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.

• My First C: A GTIN Data Type for PostgreSQL

  https://justatheory.com/2006/09/postgres-gtin-0.01/

• 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:

• 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.

• pgTAP 0.22: Test Your Results

  https://justatheory.com/2009/07/pgtap-test-your-results/

  I’m pleased to announce that, after much thinking, committing, and not an insignificant amount of hair-pulling, pgTAP 0.22 has finally landed. Download it here. Many, many thanks to all who commented on my previous posts, made suggestions, and helped me on IRC to figure out how to get all this stuff to work. The crazy thing is that it does, quite well, all the way back to PostgreSQL 8.0.

• Dynamic OFFSETs and LIMITs

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

• No more USE_PGXS=1?

  https://justatheory.com/2010/03/no-more-use-pgxs/

  I’m tired of having to remember to set USE_PGXS=1 when building third-party PostgreSQL extensions like pgTAP. Aren’t you?

• Compiling libreadline on Mac OS X

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

• Bricolage Now has PHP 5 Templating

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

• 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.

• Always Use TIMESTAMP WITH TIME ZONE

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

• 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.

• 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.

• 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.

• 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.

• Enforcing a Set of Values

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

• DBIx::Connector and Serializable Snapshot Isolation

  https://justatheory.com/2011/09/dbix-connector-and-ssi/

• Unicode Normalization in SQL

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

• JSON Path Operator Confusion

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

  The relationship between the Postgres SQL/JSON Path operators @@ and @? confused me. Here’s how I figured out the difference.

• My Catalyst Tutorial: Add Authors to the View

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

• PGConf & Extension Ecosystem Summit EU 2024

  https://justatheory.com/2024/10/pgconf-extension-summit-eu/

  Notes and links from the Extension Ecosystem Summit EU 2024 and my first time at PGConf EU. Plus thoughts and photos from ancient sites and archeological museums I visited.

• 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.

• 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.

• PGXN Development Project

  https://justatheory.com/2010/06/pgxn-development-project/

  I’m pleased to announce the launch of the PGXN development project. I’ve written a detailed specification and pushed it through general approval on pgsql-hackers. I’ve written up a detailed project plan and estimated things at a highly reduced PostgreSQL Experts rate to come up with a fundraising goal: $25,000. And now, thanks to founding contributions from myYearbook.com, and PostgreSQL Experts, we have started the fundraising phase of the project.

• PGXN v2: Go or Rust?

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

  What programming language(s) should we use to build new and revamp existing PGXN services and tools: Rust or Go? Vote your preference!

• 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.

• 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.

• 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.

• 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.

• 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.

• Compiling Postgres with LLVM

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

  I decided to compile Postgres with LLVM this week but ran into failing tests due to server crashes. Here’s how to avoid the issue.

• CITEXT Patch Submitted to PostgreSQL Contrib

  https://justatheory.com/2008/06/citext-patch-submitted/

  On Friday, I submitted a patch to add a locale-aware case-insensitive text type as a PostgreSQL contrib module. This has been among my top requests as a feature for PostgreSQL ever since I started using it. And as I started work on yet another application recently, I decided to look into what it would take to just make it happen myself. I’m hopeful that everyone will be able to benefit from this bit of yak shaving.

• 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.

• 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.

• ⛰️ 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.

• 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: 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.

• 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?

• Automate Postgres Extension Releases on GitHub and PGXN

  https://justatheory.com/2020/10/release-postgres-extensions-with-github-actions/

  Go beyond testing and fully automate the release of Postgres extensions on both GitHub and PGXN using GitHub actions.

• 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:

• Managing Key/Value Pairs in PostgreSQL

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

  Let’s say that you’ve been following the latest research in key/value data storage and are interested in managing such data in a PostgreSQL database. You want to have functions to store and retrieve pairs, but there is no natural way to represent pairs in SQL. Many languages have hashes or or data dictionaries to fulfill this role, and you can pass them to functional interfaces. SQL’s got nothin’. In PostgreSQL, have two options: use nested arrays (simple, fast) or use a custom composite data type (sugary, legible).

• 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.

• 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.

• 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.

• MySQL's REPLACE Considered Harmful

  https://justatheory.com/2005/03/mysql-replace-considered-harmful/

• 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?

• 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.

• 🏔 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.

Follow #postgres on RSS or use the

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