createAuditEntryDhcp4 failing if dhcp4_audit_revision is empty
When running createAuditEntryDhcp4 function having audits enabled the function fails, as far as the dhcp4_audit_revision table is empty.
To Reproduce
I stepped into this adding the first subnet to dhcp4_subnet table on a freshly installed postgres DB (https://kea.readthedocs.io/en/latest/arm/admin.html#postgresql): the AFTER INSERT trigger ends up calling the createAuditEntryDHCP4 function, which fails as below:
ERROR: insert or update on table "dhcp4_audit" violates foreign key constraint "fk_dhcp4_audit_revision"
DETAIL: Key (revision_id)=(0) is not present in table "dhcp4_audit_revision".
CONTEXT: SQL statement "INSERT INTO dhcp4_audit (object_type, object_id, modification_type, revision_id)
VALUES (object_type_val, object_id_val,
(SELECT id FROM modification WHERE modification_type = modification_type_val),
audit_revision_id)"
PL/pgSQL function createauditentrydhcp4(character varying,bigint,character varying) line 16 at SQL statement
SQL statement "SELECT createAuditEntryDHCP4('dhcp4_subnet', NEW.subnet_id, 'create')"
PL/pgSQL function func_dhcp4_subnet_ains() line 3 at PERFORM
Expected behavior
Subnet to be added
Environment:
- Kea version: 2.4.1-3build3
- OS: Ubuntu 24.04 x64
- PostgreSQL: 16+257build1, schema 18.0
Additional Information
The following workaround allowed me to finalize the insert:
DROP FUNCTION createauditentrydhcp4;
CREATE OR REPLACE FUNCTION public.createauditentrydhcp4(object_type_val character varying, object_id_val bigint, modification_type_val character varying)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
audit_revision_id BIGINT;
disable_audit BOOLEAN := false;
BEGIN
-- Fetch session value for disable_audit.
disable_audit := get_session_boolean('kea.disable_audit');
IF disable_audit IS NULL OR disable_audit = false THEN
PERFORM createauditrevisiondhcp4(CURRENT_TIMESTAMP, 'all', 'created by createauditentrydhcp4', false);
-- Fetch session value most recently created audit_revision_id.
audit_revision_id := get_session_big_int('kea.audit_revision_id');
INSERT INTO dhcp4_audit (object_type, object_id, modification_type, revision_id)
VALUES (object_type_val, object_id_val,
(SELECT id FROM modification WHERE modification_type = modification_type_val),
audit_revision_id);
END IF;
RETURN;
END;$function$
I did not test them explicitly, but suspect that also inserts from other tables ending up calling the same CreateAuditEntryDhcp4 can fail similarly; moreover the same issue may affect DHCP6 related tables as well