Limitez les lignes de la table Postgresql de manière FIFO

Question

Je possède une table avec un ID PK, deux FK (deux autres ID) et un timestamp. J'aimerais avoir une limite sur cette table afin qu'il y ait seulement un nombre limité de lignes pour chaque combinaison de FK. Par exemple, s'il y a plus de 10 lignes avec les mêmes FK, la plus ancienne en fonction du timestamp devrait être supprimée.

Actuellement, l'idée de solution est un déclencheur avant l'insertion qui devrait vérifier s'il y a plus d'un certain nombre de lignes dans la table avec les mêmes FK que la nouvelle ligne qui va être insérée. Si c'est le cas, la plus ancienne en fonction du timestamp devrait être supprimée.

Est-ce que quelqu'un peut m'aider à implémenter cela?

Answer 1

Une solution alternative à ce problème consiste à utiliser des colonnes de tableau au lieu de lignes et le support INSERT INTO ... ON CONFLICT UPDATE ....

Exemple en ligne : https://www.db-fiddle.com/f/2y46V6EEVJLQ5cPNTDAUPy/0

Structure:

CREATE TABLE test_rr (
    id serial primary key,
    fk_1 integer not null,
    fk_2 integer not null,
    latest timestamptz[] not null
);

CREATE UNIQUE INDEX idx_unique_rr ON test_rr (fk_1, fk_2);

Insertion des données:

INSERT INTO test_rr (fk_1, fk_2, latest)
    VALUES (1, 2, array[current_timestamp])
    ON CONFLICT (fk_1, fk_2) DO UPDATE SET latest = (array_cat(EXCLUDED.latest, test_rr.latest))[:10];

Sélection des entrées:

SELECT id, fk_1, fk_2, unnest(latest) AS ts FROM test_rr WHERE fK_1 = 1 AND fk_2 = 2;

...résultant en :

 id  | fk_1 | fk_2 |             ts
-----+------+------+-------------------------------
 652 |    1 |    2 | 2019-03-10 13:28:57.806489+01
 652 |    1 |    2 | 2019-03-10 13:28:56.670678+01
 652 |    1 |    2 | 2019-03-10 13:28:55.470668+01
 652 |    1 |    2 | 2019-03-10 13:28:54.174111+01
 652 |    1 |    2 | 2019-03-10 13:28:52.878719+01
 652 |    1 |    2 | 2019-03-10 13:28:51.3748+01
 652 |    1 |    2 | 2019-03-10 13:28:49.886457+01
 652 |    1 |    2 | 2019-03-10 13:28:48.190317+01
 652 |    1 |    2 | 2019-03-10 13:28:46.350833+01
 652 |    1 |    2 | 2019-03-10 13:11:50.506323+01
(10 rows)

Au lieu de timestamptz[], vous pouvez également créer votre propre type pour supporter plus de colonnes :

CREATE TYPE my_entry_data AS (ts timestamptz, data varchar);

CREATE TABLE test_rr (
    id serial primary key,
    fk_1 integer not null,
    fk_2 integer not null,
    latest my_entry_data[] not null
);

CREATE UNIQUE INDEX idx_unique_rr ON test_rr (fk_1, fk_2);

-- ...
INSERT INTO test_rr (fk_1, fk_2, latest)
  VALUES (1, 2, array[(current_timestamp,'L')::my_entry_data])
  ON CONFLICT (fk_1, fk_2) DO UPDATE
    SET latest = (array_cat(EXCLUDED.latest, test_rr.latest))[:10];

SELECT id, fk_1, fk_2, tmp.ts, tmp.data
FROM test_rr, unnest(latest) AS tmp -- Appel de fonction LATERAL
WHERE fK_1 = 1 AND fk_2 = 2;

Cependant, des tests de charge doivent montrer si cela est effectivement plus rapide que les déclencheurs ou d'autres approches. Au moins, cela a l'avantage que les lignes seront simplement mises à jour et non insérées + supprimées, ce qui pourrait économiser quelques E/S.

Answer 2

Vous devriez utiliser une table de résumé distincte, maintenue avec un déclencheur, qui contiendra les colonnes fk1, fk2 et count avec un check count<=N et un index sur (fk1, fk2).

Avant d'insérer une ligne dans votre table d'origine, vous devriez vérifier la valeur de count et, si elle est à la limite, supprimer d'abord la ligne la plus ancienne. Cela peut être fait avec un déclencheur si vous ne voulez pas le faire dans l'application.

Vous devez vous rappeler que :

• si vous changez les valeurs fk1 ou fk2 ou supprimez plusieurs lignes avec le même fk1 et fk2 dans la même transaction, vous devez vous assurer de le faire dans un ordre spécifié (par exemple trié par id) ou vous pouvez obtenir des impasses;
• vous ne pouvez pas ajouter plus de N lignes avec le même (fk1, fk2) dans une seule transaction - il n'y aura pas assez de lignes à supprimer en premier;
• il y aura une pénalité de performance (moins de parallélisme) pour l'ajout de plusieurs lignes avec le même (fk1, fk2).

Un déclencheur simple qui ne fait que vérifier le nombre de lignes et supprime les plus anciennes avant l'insertion, peut être lent, s'il y a beaucoup (comme 100+) de lignes avec le même (fk1, fk2). Il peut également permettre trop de lignes pendant que plusieurs insertions sont faites en parallèle.