Improve documentation about TLE upgrades

[point-source]

Improve documentation

I do not see a page which addresses or explains how TLE updates are supposed to be written and applied

Describe the problem

As a developer, I cannot figure out how to safely write a new version:

1. Do I have to assume that the users may already have a previous version installed? This means using defensive statements like "create table if not exists" and "create or replace view". What if I have multiple previous versions? Do I need to account for all of these? (this would be increasingly unmaintainable over time)
2. What if my users have data that they have stored in tables my extension has created? How can I know the right way to migrate such data? Is there some way I can warn users before "resetting" things?
3. What if the new version tries to modify something that is restricted? For instance, a constraint/primary key that needs modifying may have to be dropped before being recreated but cannot be done while there is data that references it. In this case, I have no way to modify the constraint other than to drop table data.

As a user, I cannot tell how to upgrade a TLE or what to expect when I do. For example:

1. Do I have to update in sequence or can I skip versions and jump from 1.0 to 6.0 and let dbdev figure it out?
2. Is any of my data at risk? What if the TLE created tables when I first installed it? Will those table persist or be destroyed/recreated? Is this up to the author? If so, how do I protect myself from bad updates?

Describe the improvement

I would like extensive documentation and examples of how devs are expecting to write and provide TLE updates. I would also like a list of best practices, warnings, errors, and dangers that could result from not following the guidelines. Overall this system currently feels opaque and risky for both devs and users.

Additional context

I was asked if I could provide my package as a TLE, which I did but I am now unsure how to update it. I asked a while ago but didn't get a response and now my users are asking me. I also have an update I'd like to push. This also overlaps the user-focused issue here.

[imor]

Hi @point-source,

We are still working on improving the docs, but to get you unblocked I will try to answer your questions here.

Do I have to assume that the users may already have a previous version installed?

Yes, users may have any previous version already installed when they upgrade to a new one.

This means using defensive statements like "create table if not exists" and "create or replace view".

In general yes, if you don't then the upgrade will fail with errors like ERROR: relation "<tablename>" already exists.

What if I have multiple previous versions? Do I need to account for all of these? (this would be increasingly unmaintainable over time)

You don't need to write an upgrade from every lower version to current version. For example, let's say you published hello_world version 1.0:

hello_world--1.0.sql contains the following SQL:

create table greetings (name text);

Your users have created this extension with create extension "hello_world" version '1.0'; which will create a table named greetings in their db. Now you publish version 2.0;

hello_world--1.0--2.0.sql contains the following SQL:

alter table greetings add column kind text;

Then your users will be able to do an alter extension "hello_world" update to '2.0'; which will create a new column called kind to the greetings table. If on the other hand, a user never had version 1.0 installed and they do create extension "hello_world" version '2.0'; then hello_world--1.0.sql and hello_world--1.0--2.0.sql will be run in sequence with the same end result. Having said that, there's a bug in the current pg_tle (1.0.4) we have on our platform and we will soon upgrade to pg_tle 1.3.2 with a fix.

Now when you are ready to publish version 3.0 you similarly add a file hello_world--2.0--3.0.sql which contains just the code to upgrade from 2.0 to 3.0.

What if my users have data that they have stored in tables my extension has created? How can I know the right way to migrate such data? Is there some way I can warn users before "resetting" things?

You write any code to migrate the data in the new version file. The data will stay in the tables unless the new version code deletes them or the extension is dropped. Do you have any specific example in mind which you think will be hard to migrate?

What if the new version tries to modify something that is restricted? For instance, a constraint/primary key that needs modifying may have to be dropped before being recreated but cannot be done while there is data that references it. In this case, I have no way to modify the constraint other than to drop table data.

In general you treat a new version as a normal database migration and use similar techniques e.g. by copying data into temp tables etc. Again do you have a specific example in mind that you can share?

Do I have to update in sequence or can I skip versions and jump from 1.0 to 6.0 and let dbdev figure it out?

Users can upgrade directly from 1.0 to 6.0 and when they do all the intermediate upgrade sql files will be run automatically.

Is any of my data at risk? What if the TLE created tables when I first installed it? Will those table persist or be destroyed/recreated? Is this up to the author? If so, how do I protect myself from bad updates?

The objects created by a previous version of the extension will not be dropped during an upgrade unless the upgrade sql file explicitly does so or the extension is dropped by the user.

TLEs are not different from normal Postgres extensions when it comes to updates. So you can more read about how Postgres handles updates in the docs.

Hope this answers all of your questions.

[point-source]

@imor thank you so much! I actually didn't realize that TLEs were built on top of existing (and already documented) extension infrastructure. Supabase was my intro to postgres and as such, supabase and database.dev are my intro to TLEs, which I now realize are built on native postregres extensions. So because of this, I didn't know about update scripts at all. I didn't know you could add the from and to versions in the file name like that.

As I have already released at least one update, my files are currently called supabase_rbac--0.0.1.sql and supabase_rbac--0.0.2.sql. Am I correct in assuming then that since there is no supabase_rbac--0.0.1--0.0.2.sql that it will throw a missing update script error if someone tries to upgrade from 0.0.1 to 0.0.2? Or will it attempt to update them by just running the 0.0.2 file on top of the 0.0.1?

Asked another way, are files with only a single version in the name (not update scripts) only useful for initial installs or are they ever going to be used during the upgrade process as well?

[imor]

TLEs are like normal extensions in many ways and most of the information in the docs applies to them as much as to normal extensions. A few differences worth noting are:

• A Trusted Language Extension can only be written in a trusted language. See this blog post for details about what is a trusted language.
• dbdev uses pg_tle to manage TLEs in a database. When a normal extension is created, its .control and .sql files are read from the disc. pg_tle doesn't save the .control and .sql files on disc but in appropriately named functions. E.g. a hello_world--1.0.sql will be returned when a function named hello_world--1.0.sql is called. Similarly its control file is saved in a function hello_world.control. When a user runs a create extension "hello_world", pg_tle intercepts it and if it is an extension it manages reads the sql and control data from the functions instead of from the disc. It falls back to reading from disc if it doesn't manage the extension.

As I have already released at least one update, my files are currently called supabase_rbac--0.0.1.sql and supabase_rbac--0.0.2.sql. Am I correct in assuming then that since there is no supabase_rbac--0.0.1--0.0.2.sql that it will throw a missing update script error if someone tries to upgrade from 0.0.1 to 0.0.2? Or will it attempt to update them by just running the 0.0.2 file on top of the 0.0.1?

If there is no upgrade file then a user can't run an alter extension "supabase_rbac" update to '0.0.2'; command but will have to drop the old version and create the new version again. But I'd like to point out that the current version of pg_tle on our platform (version 1.0.4) has a bug due to which upgrade path files are not being used during installs. We are working on updating pg_tle to a newer version which has fixed this bug.

Asked another way, are files with only a single version in the name (not update scripts) only useful for initial installs or are they ever going to be used during the upgrade process as well?

They are only useful only during the initial installs. During an upgrade only upgrade files are used, although these can also be used during an initial install if the version file is missing. E.g. if there are hello_world--0.0.1.sql and hello_world--0.0.1--0.0.2.sql files then a create extension "hello_world" version '0.0.2'; will result in first hello_world--0.0.1.sql being run and then the upgrade file hello_world--0.0.1--0.0.2.sql will be run. If, on the other hand, there was a hello_world--0.0.2.sql then only hello_world--0.0.2.sql would be run.

[point-source]

Perfect. Thanks, that all makes sense. Would you recommend I hold off on releasing updates then until the pg_tle tool is fixed?

And shall I leave this ticket open awaiting improved docs or close it now that my questions are answered?

[imor]

If you published the upgrade files the users wouldn't be able make use of them due to the bug so It's best to wait. Yes, please leave the ticket open as it contains a lot of useful information and we still need to document all this. I'll close other tickets requesting the same information redirecting them here instead.