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 à examiner
  • hits par résultat pour le connaitre sans attendre le retour de la fonction
  • all pour avoir tables et hits 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.