PLV8 breaks with some recent postgres versions

[lauri865]

Describe the bug
Updated project link: postgres updated to 15.6.1.127 locally

Getting errors on every plv8 function:

PostgresError: could not access file "$libdir/plv8-3.1.5": No such file or directory

Downgraded to: 15.6.1.120

Everything works again.

Any idea what we could do to solve this?
We also tried v15.6.1.139, but it didn't work either.

[sweatybridge]

Hi @lauri865, there is indeed a version bump for plv8 on the platform side since 15.6.1.120. You can update your local setup by running supabase stop --no-backup before supabase start. Hope it helps.

[lauri865]

All would be well and good, but the way I got stuck in this situation was different.

I was setting up a new cloud project (from an existing local project):

• Supabase service was running
• Ran supabase link to set up a custom domain/hostname on a new project
• 1 week passed since I did that
• Had some ongoing work (backed up), but needed to restart the supabase service for whatever reason
• Restart triggers postgres update due to the unrelated command I ran 1 week ago, updates postgres to a new version
• Now I'm in this state, where I have uncommitted work, nothing really works, I don't yet know what the cause is, as I didn't explictly update anything at this point in time.
• After figuring out it's likely postgres, I couldn't figure out what the previous working version was, because the version is stored in a temp folder that is not committed to git.

I'm not sure what the learning here is for either party, but I lost hours of productivity, because of an innocent supabase stop / supabase start sequence.

Sure, there's many other coincidences here, but maybe suggestions would be:

• Enable checking local versions into git, so that our whole team runs on the same page, and if something breaks, we can easily revert back to a previously working state
• Don't ship breaking updates in minors or at least make it seamless for the developer (or leave it for the developer to upgrade plv8, if possible)? 🙈 I'm not sure how AWS RDS ships plv8 updates, but I'm sure it's not like that.
• Maybe confirm whether the developer wants to upgrade when running supabase start. Or have an explicit command for upgrading versions. supabase link is used for many things, so it might not be obvious that a command I ran a week ago will trigger an update sequence later.

[sweatybridge]

Yup agree that checking the versions into git would be nice going forward #2435

We will probably add a flag other commands to specify different remotes. That would reduce the reliance on repeatedly running supabase link to activate different projects.

[lauri865]

Great!

Seems like Supabase cloud prevents postgres upgrade when plv8 version changes, which is great. But it only says to disable plv8 in order to upgrade the postgres version, without offering any other instructions or why this needs to be done – confusing. And disabling plv8 is far from being a straightfoward thing to do if you're actually using it.

It can make for a very difficult upgrade path, if there's a deep dependency tree on plv8. Do we need to recreate the whole database was my first instinct?! In that case, we're gonna be stuck on this version for a while, because that's a pain to do manually.

I still think minor version upgrades of plv8 could be handled better, as they don't ship any breaking updates, but tearing down and rebuilding the database is a lot of work.

Luckily, I found a simpler workaround for the time being

1. Replace all plv8 functions with temporary plpsql functions, e.g.:

CREATE OR REPLACE FUNCTION has_access()
RETURNS BOOLEAN
IMMUTABLE
AS $$
BEGIN
    RAISE EXCEPTION 'database maintenance in progress';
    RETURN false;
END
$$ LANGUAGE plpgsql;

2. Then you can run DROP EXTENSION plv8;, without having any cascading effects
3. Upgrade infra
4. CREATE EXTENSION plv8;
5. Replace temporary plpgsql functions back to original plv8 functions, and you're all set.

No need to mess with tearing down the whole database in our case – a few minutes work instead of hours of pain that we'd rather not take on. In our case, we only have 2 plv8 functions, but every RLS rule depends on them, and some views. So, cascading effects are very real.

[lauri865]

@pcnc – does the below PR fix the above issue in the future?
supabase/postgres#1259

If so, I think there's more documentation / information needed to share with developer, as many of your customers may be stuck on older versions because of the cumbersome upgrade path.