@@ -553,6 +553,28 @@ END
ON h.taskid = t.taskid
WHERE h.hostname = ?
ORDER BY h.lcseq;
+END
+ # We simulate two foreign key constraints which can't be in the
+ # db schema, by checking the values we are going to insert.
+ #
+ # For "resources" we would need a foreign key constraint
+ # with a literal value as part of the foreign key, which is
+ # not supported until PostgreSQL 13.
+ #
+ # For "tasks" we only want to apply the constraint on inserts into
+ # "host_lifecycle" - in particular, we want to allow delet6ions
+ # from "tasks" to render the taskid foreign key unresolvable.
+ # This could be done with a trigger, but since here is the only
+ # place we do insertions into host_lifecycle, this seems easier.
+ my $constraintsq = $dbh_tests->prepare(<<END);
+ SELECT * FROM
+ (SELECT 1 AS ok
+ FROM resources where restype='host' and resname=?)
+ hostname_ok
+ NATURAL JOIN
+ (SELECT 1 AS ok
+ FROM tasks where taskid=? AND live)
+ taskid_ok;
END
my $insertq = $dbh_tests->prepare(<<END);
INSERT INTO host_lifecycle
@@ -632,6 +654,9 @@ END
push @lifecycle, "$omarks$otj:$o->{stepno}$osuffix";
}
}
+ $constraintsq->execute($hostname, $ttaskid);
+ $constraintsq->fetchrow_array() or confess "$hostname ?";
+
if (defined $flight) {
$insertq->execute($hostname, $ttaskid,
$flight, $job,