PostgreSQL Extension Hot-Reload Pattern (Using a Dummy "noop" Version)

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.