public inbox for [email protected]  

help / color / mirror / Atom feed

From: Orwa Diraneyya <[email protected]>
To: [email protected] <[email protected]>
Subject: PostgreSQL Extension Hot-Reload Pattern (Using a Dummy "noop" Version)
Date: Thu, 28 Aug 2025 09:38:26 +0000
Message-ID: <F4emWrwnsCzHyuEtdWVAtv8PJdcI4B5PFpyv8gaj5MoN1ovAnFGTvNlYxZFVN9Ec1YXsszbNVS-wA_PAtOqLzlTj1Rv8rzOvvffFIKwJ2eA=@orwa.tech> (raw)

Hi pgsql SQL community,

I wanted to share a development pattern that's been very useful during PostgreSQL SQL extension development (the topic of this mailing list).

The Problem: Traditional extension development requires drop extension + create extension cycles, destroying all data in extension-managed tables.

The Solution: A "noop" (no-operation, dummy) version that allows moving the bulk of create or replace statements from the install script to an update script:

alter extension myextension update to 'noop'; -- does nothing
alter extension myextension update to '1.0'; -- reloads from file

How it Works: When reloading the extension, simply update to noop, then back to your working version (e.g., 1.0).

File Structure:
- extension--1.0.sql: Contains any 'drop-y' statements and convenience functions
- extension--noop--1.0.sql: Contains the rest of the implementation (mostly create or replace statements)
- extension--1.0--noop.sql: Empty stub

Convenient Setup: Store all three files as hard links in the extension directory, making repo changes instantly visible to PostgreSQL.

Convenience Functions:
create or replace function myextension_reload() returns text as $$
begin
alter extension myextension update to 'noop';
alter extension myextension update to '1.0';
return 'reloaded successfully';
end $$ language plpgsql;

Development Workflow:
1. Edit source code
2. select myextension_reload(); (hot-reload without data loss)
3. Test and repeat

Benefits:
- Fast iteration with instant updates
- Preserves extension table data when schema unchanged
- Perfect for experimentation and iterative development

I hope this pattern proves useful to others. If you've used similar approaches, or know of better patterns or existing extensions using this technique, I'd love to hear about them.

Best,Orwa

Sent with [Proton Mail](https://proton.me/mail/home) secure email.

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: PostgreSQL Extension Hot-Reload Pattern (Using a Dummy "noop" Version)
  In-Reply-To: <F4emWrwnsCzHyuEtdWVAtv8PJdcI4B5PFpyv8gaj5MoN1ovAnFGTvNlYxZFVN9Ec1YXsszbNVS-wA_PAtOqLzlTj1Rv8rzOvvffFIKwJ2eA=@orwa.tech>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox