Custom database functions


I’m currently reviewing a very nice change which adds support for Debian errata in Katello[1]. There’s one aspect of the PR that I’m looking for feedback on which is the inclusion of a postgresql function that compares versions of Debian packages[2].

The problem is that Rails uses db/schema.rb to migrate the test database but ActiveRecord does not place this new function into schema.rb

One potential workaround is to change Foreman’s config/application.rb to include this setting: config.active_record.schema_format = :sql This will use pg_dump to create a .sql file containing actual SQL queries to create the schema which will reflect the custom function. I’m not sure if this has any negative implications for us. Beyond that I don’t really see a good way to go about this.

Any thoughts?


1 Like

My question is do we ever load schema besides during unit testing?

Tests usually run (locally) on sqlite, not on postgres. How do you imagine this in an environment without Katello?

My question is do we ever load schema besides during unit testing?

That is a good extension of my question :slight_smile:

I am currently looking at which adds SQL-function and -trigger handling to rails.
Has anybody got any experience with that?

Not sure if this would solve the problem with the tests using :schema.

Update: fx gem seems to have problems, with functions that need parameters ( they can be applied fine, but droping them is currently not possible :frowning:

It looks like there’s a fix in f(x) to support dropping of functions w/ parameters however it requires postgresql 10 :

Maybe a dumb question but is it possible to take debian version and convert it into (human unreadable, machine friendly) format that sorts out correctly? Numbers could be represented as fixed-length hex numbers, strings as strings and special characters as different characters or short strings which follow sorting behavior. It does not matter if this form would be 3x longer because this could take advantage of RDBM indices and I would expect this to be faster than full table scan which you always need to do for custom function. For presentation purposes, a copy of the original NVRE would be saved.

I quite like that. Not sure I can comment on whether there are pitfalls there, though. @m-bucher what do you think about @lzap’s suggestion? Perhaps the errata service could emit those searchable values.

Interesting idea, I am pretty sure it’s possible, but I need to think about it. @x9c4 you originally implemented the db-functions, what is your opinion?

I do not think the errata-service should supply the converted versions. We would need the conversion-service/-method in Katello nevertheless, because the package-versions received from the hosts need to be converted as well. It might be better to keep it in one place.

The conversion adds complexity and maybe redundant data (if we would also decide to save the plain version). That could be regarded as negative, though I am not sure whether using db-functions is worse :wink:.

Just for reference, here is the definition of dpkg-versions:

We do something very similar for rpms:

which we use to sort rpms by. It seems like a hack to me and db
functions are the better solution, but are so uncommon in a rails app
that i’m okay with it.


I like that, however I am not sure if this catches all special cases. Quick look at rpm -qa on my Fedora 30 reveals few candidates which are worth being added to the unit test to verify:


But these all can be fixed. Probably it is worth extracting this into some kind of service so debian could provide its own implementation.

Yeah, i’ve always suspected it didn’t fully cover all cases, this came
from the pulp team and is what they use for applicability. Their use
case needs to be a lot more accurate than ours (simple list sorting) :slight_smile:

I’ll open an issue to look into those test cases. Thanks lzap!



In Debian’s versioning system, the character “~” has a special meaning, as it evaluates to being prior to anything else, including the end of string. I do not see how this can be mapped into a lexical ordering.

For example, the versions should be ordered as follows:
0.99 < 1.0~~ < 1.0~ < 1.0~x9c4 < 1.0 < 1.0-4~x9c4 < 1.0-4

There is also a software project to provide a debversion field in postgres. Not sure, whether foreman can supply the dependencies (libapt-pkg-dev), but it would serve to completely offload the responsibility.

1 Like

I started to package postgresql-debversion in

A few questions come up:

  • Should this package be built with the postgres scl foreman is using?
  • Can this package be shared by pulp and katello?
  • Is foreman-packaging the right place for this extension, because pulp should be able to use it independently?
1 Like

Bringing this thread back to life…
The postgresql-extension is now available through the foreman-repos.
I created a draft-PR for using it in katello:

However, the extension has to be ‘enabled’ by executing the following in the database-console:


We probably do not want to do this within the migration-script as this will probably result in the same problems we had with the pgsql-functions.


We’ve actually solved this as part of our evr extension:


Thanks for the tip.

Another concern I have is, how to make sure the extension’s rpm is installed.
Do we need a n additional Requires: in katello.spec?
And if so, what should be in there, because the extensions rpm is currently named rh-postgresql12-postgresql-debversion

We handled it in the puppet-katello module (since some users may run an
external database):

It also has to be installed on jenkins slaves:

Also don’t forget puppet-katello-devel: