Recherche globale dans une base
Quand on cherche une donnée stockée dans un modèle relationnel, en principe
on sait dans quelle table et colonne elle se trouve, on l’indique
en SQL via une requête simple de la forme
SELECT colonne FROM table WHERE condition
, et le moteur
se charge de trouver les lignes qui correspondent.
Mais quand on travaille sur une base qu’on ne connait pas, il arrive qu’on cherche à l’inverse dans quelle table et colonne (voire dans quel schéma) une valeur donnée peut bien avoir été stockée.
Le modèle relationnel n’est pas prévu pour faire ça efficacement, mais c’est quand même possible en cherchant dans tous les champs de toutes les tables de tous les schémas. Voyons comment y arriver avec PostgreSQL de la manière la moins coûteuse possible, et en permettant quand même de filtrer un peu la liste des tables où chercher.
Chercher via une fonction plpgsql
La question est comment faire l’équivalent d’un grep -F chaine *
en SQL, où *
représente toutes les tables?
Itérer sur les tables
D’abord il faut itérer sur le contenu de information_schema.tables
, qui est une vue sortant notamment:
- table_schema
- table_name
- table_type
Ici on va filtrer sur table_type = 'BASE TABLE'
pour chercher
uniquement dans les tables persistantes locales.
Désigner une ligne particulière
Généralement, une ligne d’une table est désignée par la valeur de la clef primaire. Mais pour un affichage homogène applicable à toute table, ressortir cette valeur n’est pas forcément la meilleure idée, car d’une table à l’autre les clefs primaires peuvent être très hérérogènes, avec des tailles, type de contenu et nombres de colonnes variables. En plus il peut ne pas y en avoir, puisqu’il n’est pas interdit à une table de n’avoir aucune clef primaire.
Dans le cadre de cette recherche, on va plutôt retourner le
pointeur interne de tuple
ctid
qui indique la position physique de la ligne.
Cette valeur n’est pas durable mais sera suffisante le temps d’une recherche.
En cas de crainte sur des modifications concurrentes, il est toujours possible
d’initier la recherche dans une transaction avec le mode d’isolation repeatable read
,
ce qui garantira la stabilité de ces données dans le cadre de cette transaction.
Utiliser la représentation textuelle des lignes
En principe, on pourrait
fabriquer et exécuter la requête SELECT nomcolonne,ctid FROM nomtable
WHERE nomcolonne='valeur'
pour chaque colonne de chaque table. Mais ça ferait
beaucoup de requêtes et de comparaison.
Pour réduire ce nombre, on utilise une astuce consistant à faire une pré-recherche
dans la ligne entière au format texte, obtenue par CAST(table.* AS text)
, qui
sort les colonnes dans format similaire à celui du constructeur ROW()
tel que
décrit dans la syntaxe SQL
En effet quand le terme cherché est égal à une valeur d’une colonne,
il est forcément aussi présent dans la ligne considérée en entier.
La requête pour préfiltrer va être la suivante, avec %I.%I remplacés par schemaname.tablename et %L par le terme à chercher:
SELECT ctid FROM %I.%I AS t WHERE strpos(cast(t.* as text), %L) > 0
Pour chaque ctid
trouvé par cette requête, il reste à faire le test pour chaque
colonne:
SELECT true FROM %I.%I WHERE cast(%I /*colonne*/ as text)=%L AND ctid=%L
Faire état des progrès
Une recherche globale en base risque d’être longue, et une information
vers l’utilisateur sur la progression de l’opération est toujours bonne à prendre.
On utilise RAISE INFO
, qui a l’avantage d’être transmis immédiatement
au client, notamment psql
qui l’affichera immédiatement,
contrairement aux résultats du RETURN NEXT
de la fonction, qui en
interne sont accumulés et ne sont envoyés effectivement au client
qu’une fois la fonction terminée.
Code complet
Voici une fonction complète qui prend les tables et schémas optionnels en paramètre, pour affiner là où on veut chercher, sous forme de tableaux. Elle offre également une option pour savoir quel type d’information de progression on souhaite:
- null (défaut) pour aucune
tables
pour savoir quelle table la fonction commence à examinerhits
par résultat pour le connaitre sans attendre le retour de la fonctionall
pour avoirtables
ethits
combiné
Enfin elle retourne en résultat un ensemble d’enregistrements
comportant le nom du schéma, le nom de la table, le nom et la
colonne et le ctid
de la ligne où la valeur à chercher a été
trouvée.
CREATE OR REPLACE FUNCTION global_search(
search_term text,
param_tables text[] default '{}',
param_schemas text[] default '{public}',
progress text default null -- 'tables','hits','all'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid tid)
AS $$
declare
query text;
hit boolean;
begin
FOR schemaname,tablename IN
SELECT table_schema, table_name
FROM information_schema.tables t
WHERE (t.table_name=ANY(param_tables) OR param_tables='{}')
AND t.table_schema=ANY(param_schemas)
AND t.table_type='BASE TABLE'
LOOP
IF (progress in ('tables','all')) THEN
raise info '%', format('Searching globally in table: %I.%I',
schemaname, tablename);
END IF;
query := format('SELECT ctid FROM %I.%I AS t WHERE strpos(cast(t.* as text), %L) > 0',
schemaname,
tablename,
search_term);
FOR rowctid IN EXECUTE query
LOOP
FOR columnname IN
SELECT column_name
FROM information_schema.columns
WHERE table_name=tablename
AND table_schema=schemaname
LOOP
query := format('SELECT true FROM %I.%I WHERE cast(%I as text)=%L AND ctid=%L',
schemaname, tablename, columnname, search_term, rowctid);
EXECUTE query INTO hit;
IF hit THEN
IF (progress in ('hits', 'all')) THEN
raise info '%', format('Found in %I.%I.%I at ctid %s',
schemaname, tablename, columnname, rowctid);
END IF;
RETURN NEXT;
END IF;
END LOOP; -- for columnname
END LOOP; -- for rowctid
END LOOP; -- for table
END;
$$ language plpgsql;
Exemples d’utilisation
Pour chercher ‘foo’ dans toutes les tables du schéma public :
SELECT * FROM global_search('foo');
Pour chercher ‘foo’ dans toutes les tables des schémas s1, s2 :
SELECT * FROM global_search('foo', param_schemas:=array['s1','s2']);
Pour chercher ‘foo’ dans toutes les tables sauf bigtable :
SELECT * FROM global_search('foo', param_tables:=array(
select table_name::text from information_schema.tables where table_name<>'bigtable')
);
Chercher dans la sortie de pg_dump
Une autre possibilité parfois plus simple ou plus rapide à faire est
d’exporter toute la base en fichier texte avec pg_dump
, puis
d’utiliser grep
sur le résultat. Ca a le mérite qu’on n’a pas à
écrire de code, mais il y a quand même quelques ombres au tableau:
- si pg_dump n’est pas en local sur le serveur, la totalité de la base va devoir transiter par le réseau, la recherche se faisant côté client.
- si on stocke temporairement l’export sur disque, notamment pour faire des recherches multiples, il faut estimer à l’avance combien d’espace disque ça va prendre, et évidemment avoir la place libre en question.
Indépendamment de ces difficultés potentielles, pour exploiter les résultats de grep,
il faut plutôt extraire les tables avec les options --column-inserts
,
de façon à retrouver sur chaque ligne le nom de la
table et des colonnes, ce qui au passage augmente encore le volume de
l’export. En effet, les données sont au format COPY par défaut, donc en
l’absence de cette option, si les lignes
sorties par grep ont bien la donnée cherchée, elles n’ont ni le nom de la table ni
celui de la colonne. Ces informations se trouvent au début de la commande
COPY correspondant à la table en question, lequel peut très bien
se trouver plusieurs millions de lignes plus haut.
Le filtrage par grep
pose aussi des problèmes plus subtils sur des
cas d’école, qui rendent vraiment difficile une solution robuste:
-
s’il y a des sauts de ligne dans une colonne de la ligne qu’on cherche,
grep
sera incapable de distinguer un saut de ligne entre deux INSERT d’un saut de ligne à l’intérieur de champ, et pourra toujours sortir un morceau d’INSERT sans table ni colonne, malgré l’option--column-inserts
sur l’extraction. -
l’extraction n’a pas les contenus verbatim, elle a nécessairement un format d’encapsulation, même léger, dans lequel certains caractères spéciaux seront forcément échappés. Si la chaîne à chercher contient ces caractères, il peut être compliqué de formuler l’expression exacte à chercher: il faut maîtriser à la fois les échappements syntaxiques de PostgreSQL et ceux des expressions régulières pour le grep, sans oublier ceux du shell via lequel on exprime la commande.
-
s’il se trouve que le contenu à localiser est aussi un nom de colonne ou quelque chose d’autre dans l’export comme un mot utilisé dans le SQL courant, les résultats auront des faux positifs qu’il faudra post-filtrer manuellement.