From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: <s.ivanov@proxmox.com> Received: from firstgate.proxmox.com (firstgate.proxmox.com [212.224.123.68]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by lists.proxmox.com (Postfix) with ESMTPS id 48F6294883 for <pmg-devel@lists.proxmox.com>; Thu, 22 Feb 2024 13:41:11 +0100 (CET) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id 32BAF8891 for <pmg-devel@lists.proxmox.com>; Thu, 22 Feb 2024 13:41:11 +0100 (CET) Received: from proxmox-new.maurer-it.com (proxmox-new.maurer-it.com [94.136.29.106]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by firstgate.proxmox.com (Proxmox) with ESMTPS for <pmg-devel@lists.proxmox.com>; Thu, 22 Feb 2024 13:41:09 +0100 (CET) Received: from proxmox-new.maurer-it.com (localhost.localdomain [127.0.0.1]) by proxmox-new.maurer-it.com (Proxmox) with ESMTP id 6B75944B12 for <pmg-devel@lists.proxmox.com>; Thu, 22 Feb 2024 13:41:09 +0100 (CET) From: Stoiko Ivanov <s.ivanov@proxmox.com> To: pmg-devel@lists.proxmox.com Date: Thu, 22 Feb 2024 13:40:49 +0100 Message-Id: <20240222124051.324714-1-s.ivanov@proxmox.com> X-Mailer: git-send-email 2.39.2 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.084 Adjusted score from AWL reputation of From: address BAYES_00 -1.9 Bayes spam probability is 0 to 1% DMARC_MISSING 0.1 Missing DMARC policy KAM_DMARC_STATUS 0.01 Test Rule for DKIM or SPF Failure with Strict Alignment SPF_HELO_NONE 0.001 SPF: HELO does not publish an SPF Record SPF_PASS -0.001 SPF: sender matches SPF record T_SCC_BODY_TEXT_LINE -0.01 - Subject: [pmg-devel] [PATCH pmg-api 0/2] fix table order on backup and add foreign key constraint for new tables X-BeenThere: pmg-devel@lists.proxmox.com X-Mailman-Version: 2.1.29 Precedence: list List-Id: Proxmox Mail Gateway development discussion <pmg-devel.lists.proxmox.com> List-Unsubscribe: <https://lists.proxmox.com/cgi-bin/mailman/options/pmg-devel>, <mailto:pmg-devel-request@lists.proxmox.com?subject=unsubscribe> List-Archive: <http://lists.proxmox.com/pipermail/pmg-devel/> List-Post: <mailto:pmg-devel@lists.proxmox.com> List-Help: <mailto:pmg-devel-request@lists.proxmox.com?subject=help> List-Subscribe: <https://lists.proxmox.com/cgi-bin/mailman/listinfo/pmg-devel>, <mailto:pmg-devel-request@lists.proxmox.com?subject=subscribe> X-List-Received-Date: Thu, 22 Feb 2024 12:41:11 -0000 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