Mentat

Removing relation to #7052, as it was dealt with differently, based on relational model.

Is this really related to #7215 and #7220? In both issues the column in question has type varchar[] and the data are unique.

Rajmund Hruska wrote in #note-6:

Is this really related to #7215 and #7220? In both issues the column in question has type varchar[] and the data are unique.

This issue is about referential integrity of values in an array, effectively that an array can only contain values from some set.
In case of #7215 and #7220, the values in mail_to must exist in some set of all abuse contacts.
There is a complication here as the set of all abuse contacts is not represented very well in the database. It can be currently obtained by:

SELECT DISTINCT trim(unnest(string_to_array(unnest(emails),','))) FROM settings_reporting;

To sum it up, this issue is related to #7215 and #7220 in the sense, that it would have prevented them. Also, it is not related in the sense that it does not need to be solved, for those issues to be fixed. Take your pick.

Does that answer your question?

Data in column mail_to can be a subset of all emails from column emails. But that's not always true. An abuse group can change its emails and it that case the previous reports of that group will have original values, which is correct. That's the reason why mail_to exists. And in that case I don't see how referential integrity can be enforced.

Rajmund Hruska wrote in #note-8:

Data in column mail_to can be a subset of all emails from column emails. But that's not always true. An abuse group can change its emails and it that case the previous reports of that group will have original values, which is correct. That's the reason why mail_to exists. And in that case I don't see how referential integrity can be enforced.

Yes, in that case it cannot. An argument can be made that that's a bad design. While a relational database should not be used as simple object storage, it certainly can be used that way. In other words, if we don't care about referential integrity, it might not be possible to and definitely does not make sense to try to enforce it.

To go back to your original question, this issue stemmed from #7215 and #7220 as a possible solution, the one most compatible with the general idea of relational databases. It is however certainly not the only solution and it might not be a viable solution, if the requirements are not met (there is no set of possible values defined). That is why this issue is categorized as "Research and analysis" and prioritized as "low". As for the relation status, I do not have a strong opinion against removing it. Please, feel free to do so. Of course then this might be a void issue, not worth spending effort on. I do not think we have other cases in Mentat, where an array referential integrity might be helpful.