Custom database functions

#1

Hello,

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] https://github.com/Katello/katello/pull/7961
[2] https://github.com/Katello/katello/pull/7961/files#diff-8ea9be1576c2fa2ee791f4949f23018b

1 Like
#2

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

#3

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

#4

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

That is a good extension of my question :slight_smile:

#5

I am currently looking at https://github.com/teoljungberg/fx 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.

#6

Update: fx gem seems to have problems, with functions that need parameters (https://github.com/teoljungberg/fx/issues/7) they can be applied fine, but droping them is currently not possible :frowning:

#7

It looks like there’s a fix in f(x) to support dropping of functions w/ parameters however it requires postgresql 10 : https://github.com/teoljungberg/fx/commit/53f8b3fb651e7574f98f6ff3685361a13de0981c

#8

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.

#9

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.

#10

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: https://www.debian.org/doc/debian-policy/ch-controlfields.html#version

#11

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.

Justin

#12

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:

containers-common-0.1.37-0.gite079f9d.fc30.x86_64
rubygem-asciidoctor-pdf-1.5.0-0.9.alpha.16.fc30.noarch
texlive-index-svn24099.4.1beta-25.fc30.noarch
libedit-devel-3.1-27.20190324cvs.fc30.x86_64
google-noto-fonts-common-20181223-2.fc30.noarch
dpkg-1.18.25-8.fc30.x86_64
tbb-2019.6-1.fc30.x86_64
pocl-1.2-4.20190221gita0b083a1b47a738.fc30.x86_64

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

#13

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!

Justin

2 Likes
#14

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

#15

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
#16

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