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

[Thomas Lamprecht <t.lamprecht@proxmox.com>]

Am 22/02/2024 um 13:40 schrieb Stoiko Ivanov:
> 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(-)
> 


applied both patches with Dominik's T-b & R-b, thanks!