[pmg-devel] [PATCH pmg-api 0/2] fix table order on backup and add foreign key constraint for new tables

[Stoiko Ivanov <s.ivanov@proxmox.com>]

This follows the RFC from
https://lists.proxmox.com/pipermail/pmg-devel/2024-February/002776.html

huge thanks to Dominik for catching the issue upon backup+restore with
the constraints added, and to Stefan Hanreich, for looking through the
table create statements with me!

For the creation of indexes - I think we are covered - as there is an
explicit index on the referenced row (e.g. rule.id for rule_attributes) -
it was just not part of the diff-context.
Stefan correctly pointed out that the primary-key index (rule_id, name),
should already be enough - see [0], but in this case (rather little
rows, few writes) I think having the index in addition as we do for the
existing attribut table has its merits

changes from the RFC:
* added a patch to create our rule-db backup in the correct order (tables
  referencing columns in other tables coming after) - while this did work,
  because the schema does not use foreign keys and other constraints
  currently I'd still think it has its merits - e.g. should we ever
  introduce constraints for other tables old backups could not get
  restored anymore. The better approach would probably be to replace our
  own dumping logic by an appropriate call to pg_dump - but this can
  happen as a follow-up
* tested a restore of a backup created with the patches applied (more
  testing would still be much appreciated
* formated the patches with `-U6` so that the create index statements are
  visible, without applying the patch


[0] https://www.postgresql.org/docs/current/indexes-multicolumn.html

Stoiko Ivanov (2):
  backup: reorder tables in order of dependency
  database: use foreign keys for rule and object group attributes

 src/PMG/Backup.pm  | 6 +++---
 src/PMG/Cluster.pm | 2 --
 src/PMG/DBTools.pm | 6 ++----
 src/PMG/RuleDB.pm  | 6 ------
 4 files changed, 5 insertions(+), 15 deletions(-)

-- 
2.39.2