Uninstall and reinstall for upgrades

[andreasscherbaum]

Currently the documentation states:

If you are upgrading from a previous version of pg_repack or pg_reorg, just drop the old version from the database as explained above and install the new version.

Figure out if that is necessary, or still necessary. Or if it can be removed.

[andreasscherbaum]

@dvarrazzo @Melkij @za-arthur Do you have any insight into why this statement is in the documentation, or if it can be removed?

[Melkij]

Since we don't support scripts for alter extension .. update, I think this sentence is still appropriate.

[andreasscherbaum]

Looked through the changes. Are there any fundamental incompatible changes which prevent an upgrade? Or can the next upgrade start providing update scripts.

[za-arthur]

What would be use case for an update script? It could be necessary in case if pg_repack would create types or functions, on which other DB objects could depend on, or if pg_repack would create relations with data, which need to be preserved.

[andreasscherbaum]

The update script doesn't have to do anything, if there is no changes in the data types or views or tables.

[Melkij]

Only upgrade scripts? (some releases will be no-op because the sql interface rarely changed) Or do you mean to relax the requirement that the versions of the loaded lib and the control program match?

I don't mind making support for alter extension .. update if we continue to require repack.version() check. Otherwise, support and somehow test with another version of the extension (SQL representation) or another version of the loadable module (with SQL representation from another version)... Many surprises without obvious benefit.

[andreasscherbaum]

Or do you mean to relax the requirement that the versions of the loaded lib and the control program match?

No, that's a lot of work, and not necessary at all.

Just enabling upgrades, and not forcing a reinstall.

[Melkij]

Ok, soons good.

I just realized that we won’t have empty scripts: we need to update CREATE FUNCTION repack.version_sql() with each release, even if nothing has changed on the SQL side. Hm, maybe replace it by select extversion from pg_extension where extname=?

[dvarrazzo]

There were reasons, which now I don't remember, for which it was advisable to get the version from a version in the schema rather than from the extension metadata.

Making the extension upgradeable requires work because you need to provide an upgrade path, even if the diff is nil. If there is no change between 1.0.0 and 1.0.1 and a change in 1.0.2, you still need to provide a 1.0.0--1.0.1 file otherwise you can't go from 1.0.0 to 1.0.2.

pg_repack is a stateless extension and no object will depend on it. Dropping and re-creating it is the easiest way to upgrade it.

[dvarrazzo]

There were reasons, which now I don't remember, for which it was advisable to get the version from a version in the schema rather than from the extension metadata.

Well, one obvious-ish consideration is that we want to check if the sql is consistent with the c, and the extension version is neither of the two...

[Melkij]

There were reasons, which now I don't remember, for which it was advisable to get the version from a version in the schema rather than from the extension metadata.

The main reason for version_sql(), I think, was that pg_extension and create extension as such did not exist at all.

repack.version() is indeed needed to check the actual version of the library.

[andreasscherbaum]

Making the extension upgradeable requires work because you need to provide an upgrade path, even if the diff is nil. If there is no change between 1.0.0 and 1.0.1 and a change in 1.0.2, you still need to provide a 1.0.0--1.0.1 file otherwise you can't go from 1.0.0 to 1.0.2.

We can provide these version upgrades for compatible upgrades, going forward. Would not bother doing the testing for older versions.

As long as the functions don't change, it's a relatively small file anyway. And we can integrate upgrade tests, I think I have some code for that somewhere.

[dvarrazzo]

What would be the advantage of providing an upgrade feature for this extension?

[andreasscherbaum]

Not forcing users to uninstall and reinstall the extension. Got asked this by two different persons during pgconf.de 2024.

[dvarrazzo]

In my opinion it is a viable upgrade path: it has the advantage that it can't go wrong, whereas allowing upgrade can make the installed resources diverge in many ways. It is easier to maintain, and it causes no problem for end users.

[andreasscherbaum]

Except that users need to uninstall and reinstall it. And that is not how other extensions work in PostgreSQL.

This is not easy to maintain.