public inbox for pmg-devel@lists.proxmox.com
 help / color / mirror / Atom feed
From: Stoiko Ivanov <s.ivanov@proxmox.com>
To: pmg-devel@lists.proxmox.com
Subject: [pmg-devel] [PATCH pmg-api 0/2] fix table order on backup and add foreign key constraint for new tables
Date: Thu, 22 Feb 2024 13:40:49 +0100	[thread overview]
Message-ID: <20240222124051.324714-1-s.ivanov@proxmox.com> (raw)

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





             reply	other threads:[~2024-02-22 12:41 UTC|newest]

Thread overview: 5+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2024-02-22 12:40 Stoiko Ivanov [this message]
2024-02-22 12:40 ` [pmg-devel] [PATCH pmg-api 1/2] backup: reorder tables in order of dependency Stoiko Ivanov
2024-02-22 12:40 ` [pmg-devel] [PATCH pmg-api 2/2] database: use foreign keys for rule and object group attributes Stoiko Ivanov
2024-02-22 13:22 ` [pmg-devel] [PATCH pmg-api 0/2] fix table order on backup and add foreign key constraint for new tables Dominik Csapak
2024-02-22 13:29 ` [pmg-devel] applied: " Thomas Lamprecht

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20240222124051.324714-1-s.ivanov@proxmox.com \
    --to=s.ivanov@proxmox.com \
    --cc=pmg-devel@lists.proxmox.com \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox
Service provided by Proxmox Server Solutions GmbH | Privacy | Legal