Writing Postgres extensions in Sqitch

[Erik Wienhold]

Hi Sqitchers,

has anybody tried writing Postgres extensions[1] in Sqitch? Just SQL and
maybe PL/pgSQL.

My gut feeling tells me that it should be possible to generate the extension
update scripts from the deploy scripts and the information in the plan file,
assuming that changes are from a single Sqitch project. Just dump the deploy
scripts of all changes between two tags into a single update script with the
version numbers from the tags.

The BEGIN and COMMIT statements need to be stripped from the deploy script
because update scripts are already transactional.

Handling @extschema@ can be addressed with a placeholder and some sed magic
after dumping the deploy scripts.

Why do I want to do this? Because I want to write extensions with the benefit
of version control, e.g. git diff, for definitions of individual database
objects.

For example this change[2] from pgTAP: function _runner gets changed in
version 1.2.1. The update script for 1.2.0--1.2.1 replaces the function but
information about the actual change, i.e. the diff, is kind of lost because
there is no single file for function _runner that gets reworked over time.

You can of course still diff the update scripts but that is not ideal in my
opinion because they are an aggregation of changes and you have to know the
update script containing the most recent version of a particular database
object.

--
Erik


[1] https://www.postgresql.org/docs/current/extend-extensions.html
[2] https://github.com/theory/pgtap/commit/335e3187422c5359df5b297b219d4dd832750af9#diff-79a1ea47ef83613155b3a0020a7c71bfdc37517152d97f20723b4968b95b40f8

[David E. Wheeler]

On Aug 3, 2022, at 10:55, 'Erik Wienhold' via Sqitch Users <sqitch...@googlegroups.com> wrote:

> Hi Sqitchers,

👋🏻

> has anybody tried writing Postgres extensions[1] in Sqitch? Just SQL and
> maybe PL/pgSQL.

I haven’t, no, mostly because my extensions tend to be pretty simple and have few Changs (aside from pgTAP). I do think it could work as you describe, though you’d probably want to use some build tooling (a Makefile at least) to handle the various parts. You can also create custom templates without the BEGIN/COMMIT statements, as described in this post:

https://justatheory.com/2013/09/sqitch-templating/

Let us know how it works out if you decide to pursue this plan? Could be interesting and perhaps point to the need for additional features (e.g., concatenating scripts).

Best,

David