On 04.01.2016 14:28, Shane Kerr wrote: > I spent a short time looking at the schema for the PostgreSQL and I > have a few questions before I go further. > > ---- > > First, a question for both MySQL and PostgreSQL. There are certain > values which are basically enumerated data types: > > - lease6_types > - lease_hwaddr_source > - lease_state > > I guess it was an intentional choice not to use enum types here, but > both MySQL and PostgreSQL support enum types, so I'm not sure of the > motivation. I guess the reason is that the Kea C++ code uses constants > defined elsewhere to match these (or perhaps these come from the RFC > documents)? It's not a big deal, but in the interests of consistency I > would generally put a foreign key constraint there. Good point. Those tables are not used explicitly by the Kea code. We just thought it would be useful to have them for making queries more human readable.
I have no idea why they're not enums. I presume you can assign specific, explicit values to enums, right? We do have those constants defined in .h files and I'd like to keep them explicitly defined. > Second, there is support in the MySQL schema that is missing in the > PostgreSQL schema beyond the host reservations. For example, the MySQL > schema includes information about hardware/MAC address in the lease6 > database. This was added to the MySQL schema in 2.0 of the schema. Yup. I think it was never implemented in PostgreSQL. Our excuse is the usual one - lack of time. Actually, there is more to that. We miss hardware address in PostgeSQL, but also missing hwaddress source (in both mysql and pgsql). Getting MAC address in DHCPv6 is a tricky business, so depending on where the address was extracted from it may be less or more trustworthy. Also, there's a pull request that attempts to address some of the issue in pgsql. Sadly, it's incomplete and abandoned: https://github.com/isc-projects/kea/pull/9 > Does it make sense to try to get the PostgreSQL schema revised to > include all of the missing bits from 2.0 of the schema before moving on > to 3.0? Yes, definitely. > Speaking of schema versions... I assume that the schemas use something > like semantic versioning? So 3.0 to 3.1 is a compatible change that > adds new features but 3.1 to 4.0 is incompatible? You guessed right. But you didn't have to. It's actually documented: http://git.kea.isc.org/~tester/kea/guide/kea-guide.html#kea-database-version We do have a backend version as well. This can be checked with using -V on command-line. The original idea was to do a check whether a database is correct and refuse to run if inconsistency between the code and the database versions are detected. This is partially implemented and currently not working as far as I can tell. > What is the idea when it comes to supporting multiple databases? I ask > because I assume that schemas cannot go backwards, so presumably the > changes to add hardware/MAC address to lease6 would change the > PostgreSQL from 3.0 to 4.0, even though there would be no change at all > for the MySQL schema. They're independent and don't have to match each other. > I guess for simplicity it makes sense to have a single schema version, > but it also means that administrators may be scared of updates because > a 3.0 to 4.0 seems more serious than a 3.0 to 3.1... even if it does > not affect their install at all. Nope, we keep them separate for each backend. No need to scare PostgreSQL folks when we're messing with MySQL :) > Speaking of updating schemas... :) > > In PostgreSQL it is possible to use support for the poorly-named > SCHEMA abstraction along with triggers to perform transparent, in-place > modification of schemas. > > Basically you can do something like: > > 1. start with a schema named something like kea_db_3.0 (default for the > kea user) > 2. when a new version is created, make kea_db_4.0 along with a set of > triggers to keep data in sync between the two versions > 3. migrate the data to the new version > 4. change the default of the kea user to go to kea_db_4.0 > 5. sometime when everything has been working for a while drop the > kea_db_3.0 schema > > http://www.postgresql.org/docs/9.4/static/ddl-schemas.html No way. This is definitely too complicated to be robust. The only real advantage of this approach is if you really, really want your downtime to be minimal, so you're essentially running both old and new version at the same time. > I don't know if there is any support for such things in MySQL > databases, but there are a lot of advantages to this approach, even > though it involves more developer work. Yup. And testing it would be a major task on its own. Our engineering resources are limited, so I think they're better spent on making sure that the upgrade procedure is working correctly vs. making it possible to migrate back. On a related note, it seems that so far all the changes we introduced were always adding new columns or tables. If there's a case where user determines that he absolutely must revert back to the old version, it should be possible to drop the new columns and manually revert schema version. This is purely a speculation, I don't recommend doing such a thing. > It won't be top of my list, but this may be something I want to explore > as well. It's certainly an interesting concept. I suppose it could have benefits for the case I mentioned - when you want to minimize upgrade downtime to sub-seconds level, this could be a viable way to do it. But the engineering overhead for this would be substantial, especially in the testing area, so this is not something I'm eager to do. Oh, one final thought before we start changing the schemas. One of the important aspects here should be performance. I'm sure there are better, safer ways of doing things with tons of triggers, constraints, foreign keys etc., but we should understand what's their impact on performance is. Fortunately, we do have perfdhcp, which can measure backend performance reliably and repetitively, so it's easy to assess the impact. Couple leases less per second is a fair price to pay for better self-consistency, but if the performance goes down by a lot, we may have second thoughts whether to go ahead with a change or not. Thanks a lot for running Kea and for your willingness to improve it! Tomek _______________________________________________ Kea-users mailing list [email protected] https://lists.isc.org/mailman/listinfo/kea-users
