Long lived rdkit postgres cartridge management/maintenance in a cloud-based, client-facing web application context (for managed db like AWS RDS Postgres or self-managed DB)

[MichelML]

See here https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html

It says the rdkit version of the extension is 3.8. Yet, it doesn't seem correlated with the rdkit cartridge version as the sql SELECT rdkit_version(); seems to return 0.74.0 .

Do we know how AWS maintains or update the RDKit extension for AWS RDS Postgres?

[greglandrum]

Hi @MichelML, I'm afraid I don't know how often they update their packages.
As you've discovered, the version information returned by rdkit_version() isn't at all useful. That's something we need to fix.
The best way I'm aware of to get a halfway reliable version with older versions of the catridge is to directly ask postgresql to show you which extensions are installed:

chembl_30=# \dx
                          List of installed extensions
  Name   | Version |   Schema   |                  Description
---------+---------+------------+-----------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 rdkit   | 4.1.1   | public     | Cheminformatics functionality for PostgreSQL.
(2 rows)

You can get some sense of which version of the RDKit that corresponds to by finding that version number in the history of the file rdkit.control:
https://github.com/rdkit/rdkit/commits/master/Code/PgSQL/rdkit/rdkit.control

Sorry that none of this is easy. This is why we added the rdkit_toolkit_version() function, but that's only available in recent versions.

[MichelML]

Thank you @greglandrum, this is helpful.

Speaking with my team, we were wondering how to do maintenance on the db when a new rdkit extension is released. For example, we currently are stuck (i think) on rdkit postgres extension 3.8, and on the app side stuck on django-rdkit using rdkit 2021.03.5, because when we upgrade rdkit in our django app we get this rdkit/django-rdkit#22 . I think for things to keep working we need to upgrade both sides (the postgres rdkit extension and the rdkit version of the app)?

To recap my question in other words: Any idea how the upgrade/migration should happen in this context:

We have a live, client-facing app (a django app using django-rdkit and rdkit 2021.03.5) interfacing with an already populated db (the db is using rdkit cartridge extension 3.8), and we want to upgrade both the rdkit version in the app, and the extension of the db.

How should we proceed?

(I can log another question if this follow up question goes beyond the scope of the initial question)

[greglandrum]

@MichelML I believe that the compatibility problems can arise because django-rdkit uses the RDKit's binary format to efficiently communicate send/receive molecules from the database (@rvianello can confirm this, I don't know that much about the inner workings for django-rdkit).
The RDKit is always backwards compatible w.r.t. the pickle format (i.e. newer versions can read pickles from older versions), but it's not necessarily forward compatible (i.e. older versions may not be able to read pickles from newer versions). The pickle format doesn't change frequently, so reading from a newer version of the RDKit does often work, but you can't rely on it.

There's no easy way to look this stuff up, but you can check when the format last changed in the commit history of lines 36 and 37 of this file:
https://github.com/rdkit/rdkit/blame/master/Code/GraphMol/MolPickler.cpp#L36

[rvianello]

Sorry about the slow reply, but yes, the django-rdkit code transfers the molecules in binary form, which has the side-effect of requiring compatible versions (ideally the same version) of RDKit on the client and server side. The migration workflow I usually applied when upgrading the rdkit cartridge just consisted in performing a dump/restore of the database, this most often implies a downtime, but it should generally work (I never tried an ALTER EXTENSION UPDATE even though some update scripts are available with the most recent versions).

[MichelML]

Thanks guys, I tried ALTER EXTENSION UPDATE and it didn't work on my rds instance

I'll post and close the discussion here once we're (my team and I) clear about how we'll maintain this over time

[xrl]

What error did you get from the ALTER EXTENSION UPDATE or output? When upgrading an RDS instance remember to check the preupgrade or similar log file that is automatically created.

[MichelML]

@xrl sorry for the delay, here is the output:

ebdb=> ALTER EXTENSION rdkit UPDATE;
NOTICE:  version "3.8" of extension "rdkit" is already installed
ALTER EXTENSION
ebdb=> ALTER EXTENSION rdkit UPDATE TO '4.0';
ERROR:  extension "rdkit" has no update path from version "3.8" to version "4.0"
ebdb=> ALTER EXTENSION rdkit UPDATE TO '4.1';
ERROR:  extension "rdkit" has no update path from version "3.8" to version "4.1"
ebdb=> ALTER EXTENSION rdkit UPDATE TO '4.1.1';
ERROR:  extension "rdkit" has no update path from version "3.8" to version "4.1.1"
ebdb=> ALTER EXTENSION rdkit UPDATE TO '4.2';
ERROR:  extension "rdkit" has no update path from version "3.8" to version "4.2"

if you run select * from pg_available_extensions; you'll see that the default rdkit extension is 3.8

And the docs of AWS doesn't specify support for a version of the rdkit extension > 3.8, see https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html

[MichelML]

I will write to the AWS guys about this, but before that, I'd like to know:

• In a self-managed db context, what is the standard way (if no standard, what would be a way?) of upgrading the cartridge over time as new version of the cartridge (and new version of the main rdkit package) come out, without breaking existing data in the db? Consider my question being in the context of a deployed client-facing application with existing data in the db.

cc @greglandrum @rvianello @xrl

[rvianello]

A dump/restore of a logical backup is the documented upgrade path across major PostgreSQL versions. The main limitation is that it may be slower than other strategies, and - depending on the changes in the cartridge - it may be not always required, but it should generally work. As I wrote I have no experience using the update scripts with ALTER EXTENSION, I guess this approach would still require a short downtime to upgrade the cartridge, run the update and maybe sometimes perform a reindex or recompute other data if needed?

[MichelML]

Did you have to upgrade the rdkit cartridge extension in an already existing database @rvianello ? If so, how did you do it?

[greglandrum]

I have run ALTER EXTENSION on local ChEMBL installs without problems.

Given that backend changes can (and often do) result in minor changes to the chemistry, and those changes can lead to differences in fingerprints, I would suggest regenerating fingerprints and indices after doing an update. If you have stored descriptors, you should probably update those as well.

[harryjubb]

@MichelML, did you have any luck contacting AWS on upgrading the RDKit cartridge within RDS?