Outils pour utilisateurs

Outils du site


postgresql

Formation PostgreSQL

Biblio:

Les pages

Notes

PRÉSENTATION

Moi : formateur travaillant pour le compte d'AlterWay,

Double activité de formation et de prestations de services autour des logiciels libres, d'où le coté pratique, et (je l'espère) proche de la réalité, de mes formations

LE LANGAGE SQL

introduction

Syntaxe

Notion de mots-clés, d'identifieur.

Ces éléments sont insensibles à la casse ⇒ attention.

Pour forcer le respect de la casse de caractère, et utiliser un mot-clé comme

identifieur, il faut l'encadrer par des guillemets doubles “.

Les constantes :

  • de chaîne - il s'agit d'une chaîne encadrée par des guillemets simples. Il
  • est possible d'inclure des séquences d'échappements C (newline par ex).
  • de chaîne avec guillemet dollar $balise$ texte mis en forme $balise$ permet d'implémenter des chaînes de caractères contenant caractères spéciaux, antislash et guillemets, sans se préoccuper d'une quelconque interprétation.

Les commentaires sont – et /* */

fonction

La syntaxe de CREATE FUNCTION implique que la définition soit une chaîne.

Par simplicité on utilise la syntaxe guillemet dollars qui évite d'avoir à

échapper guillemets et antislash.

Les arguments passés à la fonction sont récupérés via les variables

positionnelles $1, $2, $n. On peut aussi utiliser, pour les paramètres, la

syntaxe suivante: CREATE FUNCTION ajoute (IN x int, IN y int, OUT sum int)

Exemple: language_sql}

Définition des données

Voir la partie table, mais on est peut-être HS ? Si c'est le cas, faire un

rapide résumé:

  • Création, modification, suppression d'une table Définition d'une colonne: nom
  • type valeur par défaut contrainte

Colonnes système:

  • oid - identifiant de la ligne. Généralement non présente.
  • tableoid - identifiant de la table. cf système de fichier.
  • xmin - identifiant de la transaction à l'origine de l'insertion de la ligne
  • cmin - identifiant de la commande au sein de la transaction d'insertion
  • xmax - identifiant de la transaction de suppression
  • cmax - identifiant de la commande au sein de la transaction de suppression
  • ctid - localisation physique de la ligne. Attention elle peut varier sur le long terme → à ne pas utiliser comme identifiant unique.
===== Schémas   =====

Ils seront abordés en détail dans la partie 'administration'. Également appelé

Espace de nom. permet de faire coexister plusieurs utilisateurs /

application de manière pacifique, d'éviter les collisions de nommage.

Les rôles sont communs à un groupe de base de donnée. Une base de donnée

contient 1 ou plusieurs schémas, contenant chacun différents objets, notamment

des tables, des fonctions, des triggers, des séquences, …

Manipulation des données

Via INSERT, UPDATE et DELETE. Noter la syntaxe pour insérer plusieurs lignes:

 INSERT INTO table(c1, c2) VALUES (v1, v2), (v3, v4) ; }}}



Pour l'insertion d'une grand quantité de donnée, voir la commande COPY 



 ===== Extraction et aggrégats  =====

Voir cet exemple: 

SELECT produit_id, p.nom, (sum(v.unite)  * (p.prix - p.cout)) AS profit 

    FROM produits p LEFT JOIN ventes v USING (produit_id) 

    WHERE v.date > CURRENT_DATE - INTERVAL '4 weeks' 

    GROUP BY produit_id, p.nom, p.prix, p.cout 

    HAVING sum(p.prix  * s.unite) > 5000; 



 //LIMIT et OFFSET// 



permettent de restreindre le nombre de ligne renvoyée par une requête. 



Attention: comme sql ne garantit pas l'ordre dans lequel les lignes d'une table

seront renvoyées, il est nécessaire de couplet ces directives avec une clause de

//tri// //ORDER BY//. 



 Requête WITH Voir cet exemple: WITH ventes_regionales AS ( SELECT region,

 sum(montant) AS ventes_totales FROM commandes GROUP BY region ),

 meilleures_regions AS ( SELECT region FROM ventes_regionales WHERE

 ventes_totales > (SELECT sum(ventes_totales)/10 FROM ventes_regionales) )

 SELECT region, produit, sum(quantite) AS unites_produit, sum(montant) AS

 ventes_produit FROM commandes WHERE region IN (SELECT region FROM

 meilleures_regions) GROUP BY region, produit; 



 ===== Les jointures =====



   La principale différence entre AS et USING apparaît dans l'utilisation de

   plusieurs colonnes. par exemple USING( a, b) est équivalent à ON (t1.a = t2.a

   AND t1.b = t2.b) à une différence d'affichage près dans le résultat 



( en utilisant ON, les colonnes a et b apparaitront 2 fois) 



   La jointure réflexive permet par exemple de connaître le nom du responsable

   de chaque prof.  ex: select G.nomprof,D.nomprof as responsable from profs G,

   profs D where G.numresponsable=D.numprof ; 



 ===== les combinaisons =====

   REQUETE 1 COMBINATION_OP REQUETE 2 

   COMBINATION_OP in union, intersect, difference 



   R1 et R2 doivent avoir le même nombre / type de colonne. 



   //union//: ajoute les résultats de la 2eme requete à la premiere, en supprimante les doublons (sauf si UNION ALL). 

   ex1: //select  * from vente_enligne union select * from vente_magasin order by date ;// permet de connaître les dates où il y aeu des ventes en ligne ou en magasin 



   //intersect//: renvoie toutes les lignes présentes dans requete 1 et dans requete2, en supprimant les doublons, sauf si INTERSECT ALL 

   ex2 : // select date from vente_enligne intersect select date from vente_magasin order by date ;// 



   //EXCEPT//: renvoie les lignes présentes dans la 1ère requete mais pas dans la 2eme. 

   ex3 :  select date from vente_enligne except select date from vente_magasin order by date ; 



 ===== Opérateurs et fonctions =====



  * logiques 

  * de comparaison. noter (NOT) BETWEEN et IS (NOT) [NULL|FALSE|TRUE|UNKNOWN] et IS (NOT) DISTINCT FROM 

  * de traitement de chaîne (|| lower, upper, substring (REGEX from chaine), length, md5, quote_ident, quote_literral 

      **Correspondance de chaînes ** 



       LIKE _ % 



       ILIKE 



       SIMILAR TO - mélange de like et de regexp posix 



       Ces dernières peuvent être utilisées avec les opérateurs  ~ ~* !~ et !~* 

  * fonctions de formattage. Permettent d'afficher des dates ou des valeurs

  * numériques sous forme de chaîne. Elles prennent en 1er paramètre la valeur à

  * convertir et en 2eme paramètre le format à utiliser.  fonction de date et

  * heure 

   current_date , current_time, 



  * expression conditionnelles 

   CASE WHEN condition THEN ACTION 

        WHEN ... 

	ELSE 

   END 

 



   COALESCE( arg, ...) renvoir le 1er argument non null. utile dans les

   fonctions d'affichage. 

 

  * fonctions de génération de séries - renvoie un type SETOF 

   generate_series( debut, fin, pas ) generate_series( timestamp1, timestamp2,

   interval ) 



  * fonction d'information système 

     current_database() 

     current_user() 

     has_OBJECT_privileges( object, privileges) 



  * fonction de calcul de la taille d'objets 

   pg_database_size(name|oid) 

   pg_relation_size('commerciaux') 

   pg_size_pretty permet de formatter //joliment// une taille 





 



 ==== LE LANGAGE PLPGSQL ====

Langage de procédures 



[[:postgresql|Postgresql]] parcequ'il s'appuie largement sur son //catalogue système//, est

facilement extensible: 

  * fonctions - de 4 types: fonction en SQL, en langage procédural (pl/PgSQL),

  * interne, ou en C.  (agrégats) types de données - types composite (CREATE TYPE

  * personne AS( nom varchar(25), numsecu char(14)))  domaine  type de donnees

  * avec contrainte de verification.  (opérateurs) ... 

4 langages: perl, [[:windows:python|python]], tcl et PL/pgSQL 



Le support d'un langage (postgresql-plpython-8.3) peut demander l'installation

de paquets supplémentaires. 



Le langage doit être installé pour une base de donnée particulière: CREATE

LANGUAGE [[:windows:python|python]] BDD 



Il est ensuite possible d'écrire des fonctions en utilisant son langage favori.

Le principal soucis du langage SQL vient de la nécessité de faire exécuter

chaque requête individuellement par le serveur. 



Dans le cas de requêtes complexes, la quantité d'information à transmettre entre

le client et le serveur peut devenir non négligeable. 



Utiliser une //procédure stockée// coté serveur, permet de limiter ces échanges,

voire de mettre en place une //interface// entre client et serveur. 





D'un point de vue sécurité, les fonctions s'exécute avec les droits systèmes de

l'utilisateur sous l'identité duquel tourne le serveur. Le privilège de création

de procédures et de fonction est un privilèges important, qu'il convient de ne

pas donnée à n'importe qui. 



Il existe également des langages //non surs//, permettant de réaliser des accès

mémoires assez //conséquents//, classés comme tel par postgres, et disponible

uniquement pour les administrateurs. 



Toutes les informations liées aux fonctions sont dans le catalogue système

//pg_proc//. 





===== Privilèges associés aux fonctions =====



Le privileges //EXECUTE// donne le droit d'exécuter la fonction référencée. 



Le privilèges //CREATE//, sur le schéma, donne le droit de créer (entre autres)

des fonctions. 



La directive //SECURITY DEFINER|INVOKER// permet de spécifier les privilèges

accordées à la fonction lors de son exécution, soit du propriétaire, soit de

l'appelant (par défaut). 



Il n'est pas possible de créer une fonction avec //SECURITY DEFINER// à l'aide

de pgamdin ? 



===== //requête préparée// =====



<code> 

   PREPARE ma_requete_preparee( integer ) AS 

      INSERT INTO numero values( $1 ) ; 



   EXECUTE ma_requete_preparee( 10) ; EXECUTE ma_requete_preparee( 13) ; EXECUTE

   ma_requete_preparee( 8) ; 



   DEALLOCATE ma_requete_preparee ; }}} 



On peut aussi faire un //DEALLOCATE ALL//, qui est fait de toute manière dès la

fin de la session. 



Une requête préparée est un objet coté serveur mis en place dans le but

d'améliorer les performances. Les étapes d'analyse, de réécriture et de

planification sont réalisées à ce moment là, une fois pour toute. 



Contrairement aux fonctions, les RP ne sont pas des objets permanents: ils sont

propres à une //session//, et ne peuvent être ni réutilisées, ni partagées. 



Les RP sont intéressantes dans le cas où on a besoin d'exécuter un grand nombre

d'instructions similaires complexes, par exemple dans le cas de jointures

multiples, ou mettant en jeu un nombre important de rêgles. 

 

Les informations sur les RP de la session en cours sont disponibles à partir du

catalogue système //pg_prepared_statements//.  Structure du langage 



Les commentaires -- ou /* ...  * 



===== Les labels de bloc =====

<<monbloc>> sont surtout intéressant pour l'instruction //EXIT//. 

DECLARE 

   compteur := 100 ; 

BEGIN 

   raise notice 'compteur: %', compteur ; 

   compteur := 1000 ; 

   DECLARE 

      compteur = 10 ; 

   BEGIN 

      raise notice 'compteur: %', compteur ; 

      raise notice 'compteur monbloc: %', monbloc.compteur ; 

   END; 

END; 

===== Contrôles conditionnels =====

<code> 

   IF ... THEN 

   IF ... THEN ... ELSE 

   IF ... THEN ... ELSE IF 

   IF ... THEN ... ELSIF ... THEN ... ELSE 

   IF ... THEN ... ELSEIF ... THEN ... ELSE 

Boucles

 

   <<Label>> 

   LOOP 

      instructions 

   END LOOP Label ; 
 

   <<Label>> 

   WHILE condition LOOP 

      instructions 

   END LOOP Label; 
 

   <<Label>> 

   FOR nom IN [REVERSE] start..stop BY step LOOP 

      instructions 

   END LOOP Label ; 

Travailler sur chaque ligne du résultat d'une requête:

 

   DECLARE cible table%ROWTYPE ; 

   <<myloop>> 

   FOR cible in SELECT  * from pg_authid ; LOOP 

      instructions 

   END LOOP myloop 

Idem, la requête (sous forme de chaîne) étant réévaluée et replanifier à chaque

itération: for_cible_in_execute_string_loop_instructions_end_loop}

Contrôles de boucles

EXIT Label permet de quitter une boucle donnée. Il est possible de rajouter une condition de sortie:

 

   EXIT WHEN condition 

On peut utiliser EXIT dans une bloc BEGIN…END. Dans ce cas on sort du

bloc et on passe au suivant.

CONTINUE Label permet de passer directement à l'itération suivante, en ignorant les instructions suivantes. De la même manière, la clause WHEN permet de spécifier une condition.

Paramètres en entrée et sortie

TODO: parler de SETOF

paramètres de fonctions

Les paramètres en entrée sont désignés par les variables $1, $2, $3, etc.

Il peuvent être aliasé par un nom. Il suffit de spécifier ce dernier dans les parenthèse:

 

   CREATE FUNCTION printf( message text ) RETURNS void AS $$ ... 

L'argument de la fonction de cet exemple pourra être référencé indifféremment par $1 ou par message.

Attention: Tous les noms de variables sont substitués. Donner un nom d'objet existant (une table par exemple) à une variable est une très mauvaise idée, qui peut être source d'erreur difficile à débugguer.

Astuce utiliser des noms d'objets variables. Ex:

 

   SELECT  * from v_table ; 

Cet exemple ne fonctionnera pas, Il faut créer la reqête sous forme de chaîne,

et la passer à EXECUTE. RETURN termine la fonction, et renvoie la

valeur de l'expression qui lui est passé en paramètre. Cette instruction est

obligatoire dans le cas d'une fonction avec paramètres de sortie, ou

renvoyant autre chose que void.

un RETURN sans expressions permet de quitter rapidement une fonction

renvoyant void, ou renvoyant plusieurs valeurs. (càd avec des paramètres en

sortie).

Dans le cas de fonctions renvoyant un ensemble de résultat on peut utiliser:

  • RETURN QUERY query : exécute query et recupère son résultat en
  • interne. Cet instruction ne termine pas la fonction: elle peut continuer son
  • traitement. Il est donc possible de combiner le résultat de plusieurs
  • requêtes.
 CREATE OR REPLACE FUNCTION obtenirTousLesTrucs() RETURNS SETOF truc AS

$BODY$ DECLARE r truc%rowtype; BEGIN return query select  * from truc ; RETURN;

END $BODY$ 

  * // RETURN NEXT// : permet d'itérer sur le résultat d'une requête. L'exemple

  * ci-dessus pourrait s'écrire: 

<code> FOR r IN SELECT  * FROM truc WHERE id_truc > 0 LOOP -- quelques traitements

RETURN NEXT r; -- renvoie la ligne courante du SELECT END LOOP;*/ }}} 



===== Erreurs  =====



La fonction //RAISE NIVEAU format expression// permet 



Les niveaux sont: DEBUG, LOG, INO, NOTICE, WARNING et EXCEPTION. 



Ce dernier niveau provoque l'annulation de la transaction en cours. Suivants le

niveau du message et la valeur des directives //log_min_message// et

//client_min_message//, le message pourra se retrouver dans les logs, ou être

renvoyé vers le client. 



On peut passer une variable au message à l'aide de la syntaxe //%// : {{{ RAISE

NOTICE '% did a % on %!', current_user, TG_OP, TG_TABLE_NAME ; }}} 



On peut rajouter une clause //EXCEPTIONS// à un bloc //BEGIN..END//, de manière

à //intercepter// et //traiter// les cas d'erreurs possibles, sans terminer

l'exécution de la fonction. 



Les cas d'erreurs sont disponibles dans l'annexe de la documentation en ligne:

http://docs.postgresqlfr.org/8.3/errcodes-appendix.html. Exemple: {{{ BEGIN

instructions ...  EXCEPTION WHEN null_value_not_allowed THEN raise NOTICE

'Tentative d\'insertion valeur NULL' ; ...  END }}} 



Dans le(s) test(s), il faut utiliser la colonne //constante// du tableau des

erreurs.  ===== Déclaration  ===== Toutes les variables doivent être déclarées. 



LA syntaxe est: nom [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression

]; 



Récupérer le type d'un élément - dans cet exemple, //id// est déclaré comme

étant du même type que le champ //id// de la table //utilisateurs//.  {{{ id =

utilisateurs.id%TYPE ; }}} 



le type de donnée //row// permet de recevoir une ligne d'une table (par exemple

suite à un //SELECT INTO//). On pourra accéder aux valeurs des différents

champs. Ex: {{{ DECLARE ligne utilisateurs%ROWTYPE ; BEGIN SELECT  * INTO ligne

from utilisateurs where id = 1 ; RAISE NOTICE ligne.name ; }}} 

      



===== Types de données  =====



Le //type// d'une donnée contrôle la valeur qu'il est possible d'y affecter, et

les opérations qu'il est possible de réaliser avec cette variable. 



Certains sont conformes à la norme SQL. d'autres sont propres à PostreSQL. 



il est important de dimensionner correctement les tailles de variables, car cela

peut avoir un impact conséquent sur les performances. 



Ni trop grand pour ne pas gaspiller mémoire et espace disque, ni trop petit afin

de prévoir une extension future de nos informations. 



Les //types numériques//

http://docs.postgresqlfr.org/8.3/datatype.html#datatype-numeric-table permettent

de stocker des données numériques entières ou non, d'une taille plus ou moins

importante. 



Le type //NUMERIC( précision, échelle)// permet de stocker des données

flottantes, en garantissant la précision des calculs réalisés. En revanche, le

coût d'utilisation de ce type, en terme de performance, est élevé, par rapport

au type //FLOAT//. 



La //échelle// désigne le nombre totale de chiffre. Ce paramètre est optionnel. 



La //précision// désigne le nombre chiffre après la virgule. 



Si le nombre de chiffres à gauche du point décimal est supérieur à la différence

entre la précision déclarée et l'échelle déclarée, une erreur est levée. 



//Remarque//: la précision et l'échelle ne constitue pas une allocation, mais une limite, de la même manière que le type //varchar//. 





Le type //FLOAT(précision)// représente des valeurs floattantes en simple ou

double précision. Le paramètre //précision//, optionnel spécifie la précision

acceptable utilisable. 



Ce type n'est pas conseillé pour des calculs précis (valeurs monétaires par

exemple). 



Les types //Sériés// sont des raccourcis, équivalent de l'attribut

//AUTOINCREMENT// de [[:mysql|mysql]]. Cela permet de créer des colonnes de valeurs

entières uniques, auto-générées. 



//Types caractères//. 



On a principalement //character(taille)// et //character varying(taille max)//.

On a également le type //text//, propre à [[:postgresql|postgreSQL]]. 



Contrairement à d'autres SGBD, l'utilisation de chaîne à longueur longue ou fixe

n'a pas d'incidence notable de performance. 



[[:postgresql|PostgreSQL]]autorise l'utilisation du type varchar sans spécifier de taille

maximale. 



Attention, il existe un type //interne// //name//, utilisé dans les catalogues

systèmes. Il est déconseillé de les utiliser. 



//Types binaires// 



//bytea// 



Ce type permet de stocker des données //brutes//. Contrairement aux chaînes, le

type binaire permet de stocker n'importe quel caractère. 



Les opérations se font sur les données brutes, et  ne dépendent pas de la

//localisation//. 



Certains caractères spéciaux doivent être //échappés// lors de la saisie. Leur

valeur décimale est la suivante: 

* 0		E'\\000' 39		E'\\047' 92		E'\\134' 0 - 31

* et 127 - 255	E'\\XXX' 



//Types date// 



[[:postgresql|postgreSQL]]propose des types permettant de stocker date, temps et timestamp,

dépendant ou non d'un timezone. 



Le timestamp est calculé en seconde, à partir du 01.01.2001 00h00 . Elles sont

stockées en tant que //float//, donc attention au manque de précision inhérent à

ce type de donnée. 



LA saisie de date se fait sous forme de chaîne. Attention, suivant le format

utilisé, et la valeur de la directive de configuration //datetime//, il peut y

avoir erreur. Par exemple, 1/8/2009 équivaut au 1er aout en mode //dmy// et au 8

janvier en mode //mdy//. 



De même, la saisie des heures se fait sous forme de chaîne, et peut dépendre de

la directive //timezone//. Pour connaître l'heure à Los Angeles, voir: {{{ set

timezone='America/Los_Angeles' ; select current_time ; ou select current_time

AT TIME ZONE 'America/Los_Angeles' ; }}} 



Pour info, en été nous sommes (en France métropolitaine) à UTC +2, et UTC +1 en

hiver. 



Enfin il existe des dates et des heures spéciales pour simplifier la saisie: 

  * now		date et heure actuelles today	date courante tomorrow	date de

  * demain yesterday	date d'hier epoch	01/01/1970 ... 



//Affichage// 



Le format de sortie est contrôlé par la directive //datestyle//. 



// Type Booléens // 



TRUE et FALSE, représentés en interne par t ou f. 



peut aussi prendre comme valeur, respectivement, 1, yes, y, true. 



// Type énumération // 



Ce sont des ensembles de valeurs fixes. Ils sont générés à l'aide de la commande

CREATE TYPE: {{{ CREATE TYPE jours AS ENUM( 'lundi', 'mardi', 'mercredi',

'jeudi', 'vendredi', 'samedi', 'dimanche' ) ; }}} 



Ce type permet notamment de limiter les valeurs possibles pour une colonne. D'un

point de vue interface, il sera typiquement représenté par une liste déroulante. 



2 ENUM, même s'ils ont les même valeurs, sont réellement différents, et la comparaison peut ne pas être possible. Dans ce cas, il convient de faire une //conversion// en text. 



 // Type UUID  // 



cE type de donnée permet de garantir (pratiquement) l'unicité de la valeur. Il

peut être intéressant de l'utiliser dans le cadre de systèmes distribués. 



un modules //ossp-contrib// apporte les fonctions nécessaires à la génération de

ce type de valeur. 



 // Type XML  // 



Ce type permet de stocker des données dans ce format, en garantissant la

conformité des données. 



 // Type tableaux  // 



Un tableau se définit en suffixant au type du contenu des crochets: {{{ liste

integer[] }}} 



On peut spécifier la taille du tableau dans les [], mais cela n'a qu'une valeur

purement indicative. 



La saisie de valeur se fait en encadrant chaque //ligne// du tableau par des {},

chaque //colonne// étant séparée par une , les valeurs textes étant encadrées

par des ". 



On accède aux valeurs par leur indice (qui commence à **1**). 



On peut également spécifier une étendue {{{ liste[1:3] }}} 



La fonction //array_dims// permet de connaître la taille du tableau. 



Modification : on peut modifier une valeur d'un tableau par son indice, ou

l'ensemble du tableau. 



On peut également rajouter des données, en utilisant des indices libres.

Attention, les //espaces vides// entre éléments existants et nouvellement

insérés seront initialisés à NULL. 



Les fonctions array_append, array_prepend, et array_cat permettent aussi de

construire des tableaux. 



Recherche dans un tableau. 



Il faut tester chaque valeur du tableau, ce qui peut se révéler un peu lourd. La

syntaxe suivante simplifie la chose: {{{ SELECT col FROM table WHERE valeur =

[ALL|ANY] ( colonne_tableau_test ) ; }}} 



Avec //ALL//, on recupère les lignes pour lesquelles //colonne_tableau_test//

n'a que des valeurs égalles à //valeur//. 



Avec ALL, on réupère les lignes pour lesquelles //colonne_tableau_test// a au

moins une valeur égale à //valeur//. 



Remarque: IMHO, l'utilisation dans une base de donnée est souvent le signe d'une

mauvaise conception. On se rapproche en effet de l'attribut multivalué. 



// Types composites// 



Ces types correspondent aux structures en C. Il peut également servir à

représenter une ligne de table. 



Ce type se créé à l'aide de la commande //CREATE TYPE//: {{{ CREATE TYPE

complexe AS ( r float, i float ); }}} 



Lors de la création d'une table, un type composite est systématiquement créé,

afin de représenter une ligne de la table. 



Grosse différence: ce type de donnée n'implémente pas la notion de contrainte. 



Pour renseigner une valeur de type composite: 

  * la valeur de chaque champ est séparé par une , l'ensemble est entre '()' les

  * chaîne sont entre "" 



On peut également utiliser la syntaxe {{{ ROW( 'chaine', num0, num1 ) ; }}} 



ce qui évite de se prendre les pieds dans les guillements. 





La sélection se fait avec la syntaxe //([table].nom_variable).nom_champ// 



Modification: {{{ UPDATE matab SET col_complexe = ROW(1.1,2.2) WHERE ...; }}} 



<code> 

   UPDATE matab SET col_complexe.r = (col_complexe).r + 1 WHERE ...; 
 

   INSERT INTO matab (col_complexe.r, col_complexe.i) VALUES(1.1, 2.2); 

Conseil: privilégiez au maximum la syntaxe ROW, c'est beaucoup plus simple.

Type OID'

Ce type correspond à un identifiant d'objet, utilisé comme clé primaire par

postgreSQLdans certaines tables systèmes.

Il ne sont généralement pas ajoutés aux tables utilisateurs, sauf s'il on

spécifie l'option de création WITH OIDS.

Il est codé en interne sur 4 octets, donc il ne permet pas de garantir l'unicité

d'un objet dans le cadre de référentiels de taille importante.

À ce type de donnée correspond des alias (http://docs.postgresqlfr.org/8.3/datatype-oid.html#datatype-oid-table) qui propose des opérateurs permettant notamment d'obtenir l'oid d'un objet à partir de son nom:

 

   SELECT  * FROM pg_attribute WHERE attrelid = 'ma_table'::regclass; 

D'autres type de données identifiant des objets existent:

  • cid - identifiant de commande xid - identifiant de transaction tid -
  • identifiant de ligne

Pseudo-types

Il s'agit de types génériques utilisables pour les arguments et les valeurs de

retours de fonctions complexes.

Les langages procéduraux n'autorise pas de pseudo-types en entrée, et ne peuvent

que retourner les PT void, record, et éventuellement trigger.

Domain

Un domaine est un type de donnée contraint.

CREATE DOMAIN us_postal_code AS TEXT CHECK( VALUE ~ '^\\d{5}$' OR VALUE ~

'^\\d{5}-\\d{4}$');

Conversion de type

SQL est un langage fortement typé. Son implémentation par postgreSQL apporte

beaucoup de souplesse.

La plupart du temps, la conversion d'une valeur d'un type vers un autre se fait

de manière implicite. Il existe certains cas où la conversion doit être

explicite:

  • sélection de fonction et d'opérateurs. En effet, une fonction, et un
  • opérateur, sont identifiés par le nom ET par le type des arguments
  • acceptés. insertion de données dans une table. Les constructions impliquant
  • des comparaisons entre valeurs doivent s'assurer que ces valeurs soient du
  • même type.

SEQUENCES

Fonctions associées:

currval(regclass)

nextval(regclass)

lastval()

setval( regclass, bigint )

Valeurs spéciales

  • Nan pour les types numériques.
  • (-)Infinity pour les float.

Les types de base (par exemple integer, text) sont proposés par le système. Ils en existent 2 sortes : les scalaires et les tableaux. À chaque scalair correspond un tableau.

Les types composites ou types lignes sont

triggers

Les triggers peuvent être écrit dans n'importe quel langage de procédures

disponible avec postgresql.

Un trigger permet de faire le lien entre:

  • une table une fonction, qui sera exécutée lors de l'arrivée de cet évènement.
  • un évènement survenant sur cette table

L'interface pgadmin3 met clairement en valeur les 2 étapes nécessaires à la

mise en place d'un trigger.

Les événements peuvent être INSERT, DELETE ou UPDATE et TRUNCATE

pour la version 8.4. le trigger peut être déclenché avant ou après l'événement.

Il peut se déclencher soit pour chaque ligne modifiée, soit pour chaque

expression déclenchante. Notamment, dans le 2ème cas, le trigger sera exécuté

même dans le cas où aucune ligne n'est modifiée.

La fonction doit obligatoirement être définie avant la mise en place du trigger.

Elle ne prend pas de paramètre et renvoie un type trigger. Elles peuvent

cependant renvoyer autre chose: généralement NULL dans le cas d'un trigger type

instruction, ou un ligne dans le cas d'un trigger type ligne. Un trigger

ligne, avant peut renvoyer NULL. Dans ce cas, l'opération ayant

déclenché le trigger est annulée, pour cette ligne. Les éventuels triggers

supplémentaires ne sont pas déclenchés. Elle reçoit des paramètres via une

structure de donnée interne triggerdata.

Le squelette d'une fonction trigger est donc: create_function_f_trigger_returns_trigger_as_body}

La création du trigger (association table | événement | exécution d'une

fonction) se fait grâce à l'appel CREATE TRIGGER: AFTER } { evenement [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE nomfonc ( arguments ) }

À noter que cette syntaxe permet de lier plusieurs événements à la fonction.

La possibilité de créer un trigger est lié au privilège du même nom. Les

informations sur les triggers sont disponibles dans le catalogue système

pg_trigger. La reqûete suivante permet de retrouver la triple association:

 select tgname,relname,proname from pg_trigger t left join  pg_class c on

t.tgrelid = c.oid left join pg_proc p on p.oid= tgfoid ; }}} 



 // Visibilité des données  // 



  *  //instruction// //avant//: pas d'accès aux données modifiées 

  * //instruction// //après//: accès aux données modifiées. 

  * //ligne// //après// : les modifications sont visibles. 

  * //ligne// //avant// : la ligne modifiée n'est pas visible 

 

 Dans ce dernier cas, les lignes modifiées par les précédentes instructions

 d'une fonction trigger seront //visibles//. Attention, car l'ordre des

 modifications n'est pas prévisible, dans le cas de la modification de plusieurs

 lignes par une même instruction. 



Lors de l'appel de la fonction trigger, plusieurs variables spéciales sont à

notre disposition: 

  * [[:old|OLD]] (record) NEW (record) TG_NAME TG_WHEN TG_LEVEL TG_OP TG_TABLE_NAME 



 // Comparaison rules vs. trigger  // 



1 trigger "insert update delete" sur une vue ne sera jamais appelé, contrairement à une règle. 



Si l'on veut vérifier la validité d'une valeur, et avertir en cas d'invalidité,

alors il faut utiliser une trigger. 



De la même manière il est déconseillé d'utiliser des règles pour implémenter des

contraintes. 



Une règle pourra être plus performante dans le cas où la requête qui l'a

déclenche renvoie un grand nombres de lignes, pour lesquelles un trigger

s'exécutera à chaque fois, alors que la règle s'exécutera pour l'ensemble. 



'' Création d'un trigger avec pgAdmin :  '' 



Il faut dans un 1er temps créer la fonction à appeler. Pour la syntaxe, voir

l'onglet //SQL// en cours de rédaction du corps de la fonction. Démarrer et

finir la définition par un saut de ligne. 



On peut ensuite créer le trigger à partir de la table voulue, qui permet

d'associer table, évenement(s) et fonctions. 



Se poser la question, ensuite, de savoir pourquoi le trigger ne fonctionne pas

... Tout simplement parce que l'interface est quand même un peu bugguée, et

qu'il ne faut pas oublier d'abuser du bouton //refresh//. Ne pas oublier non

plus de s'assurer que notre trigger soit bien //activé//. 



'' Questions annexes: qu'est-ce que le coût ?  '' 



'' Qu'est-ce que //VOLATILE// ?  '' 

Ce terme renseigne l'optimiseur de requête sur le comportement de la fonction. 

VOLATILE: (valeur par défaut). La fonction peut faire ce qu'elle veut, y compris modifier la base. Le résultat peut être différent, même si la fonction est appelée avec les même paramètres. L'optimiseur n'essaie pas de prévoir le comportement de ces fonctions. 

STABLE: la fonction ne peut pas modifier la base, et le résultat est le même si elle est appelé avec les même arguments, pour toutes les lignes d'une même instruction. Cela permet d'optimiser plusieurs appels d'une même fonction au sein d'une requète. 

IMMUTABLE: une fonction de ce type ne modifie pas la base, et, si elle est appelée avec les mêmes arguments, renverra le même résultat. l'optimiseur peut donc évaluer par avance son comportement. exemple: la fonction d'adition. 



Ces 2 dernières catégories n'acceptent donc que des requètes SELECT. 



 

Il est conseillé d'utiliser la //classe de volatilité// la plus strict lors de

la définition d'une fonction. 





=====  Les transactions  =====



Que ce passe-t'il lorsque plusieurs sessions tentent d'accéder aux même données

au même moment ? 



À la base: MVCC, qui évite en grande partie l'utilisation des verrous. En effet, ce système permet de ne vérrouiller que les lignes en cours de modification. Ce mécanisme permet d'éviter qu'une lecture bloque une écriture et réciproquement. 



3 problèmes peuvent survenir lors d'une transaction: 

  * lecture sale (lecture de données écrites par une tx non validée) 

  * lecture non reproductible (relecture de donnée entretemps modifiées par une tx) 

  * lecture fantôme (rééxecution d'une requète renvoyant un autre ensemble de ligne, car les conditions de recherches ont été modifiées par une autre tx) 



 niveau d'isolation correspondant: 

  * uncommitted read - committed read - repeateble read serializable 



 PostgreSQL ne propose que les niveau //read committed// et //serializable//.   



 ===== Les sous-requêtes  =====



Syntaxe: doivent être entre () et aliasée. 



Expressions de sous-requêtes: EXISTS( SELECT 1 FROM ...) IN 



===== Importation et exportation de données  =====



	Il est possible de rassembler une série de commandes SQL dans un

	fichier.  COPY table (colonnes...) FROM 'nomFichier' WITH DELIMITER AS

	'char' CSV HEADER QUOTE AS 'guill' ESCAPE AS 'esc' FORCE NOT NULL

	colonne 



		Ce mécanisme peut être utilisé pour l'insertion de gros volumes

		de données.	 le fichier doit être lisible par l'utilisateur

		postgres.  Il est possible de spécifier 'STDIN' comme nom de

		fichier, et d'écrire les données à la suite, terminées par

		\lstinline{\.}:



COPY cours (numcours, nomcours) FROM stdin; 1	Bash 2	Linux 3	PostgreSQL \. 















===== Les logs de transactions  =====

Il y a conflit entre performance et fiabilité. la première demande à ce que l'on travaille au maximum avec les données en mémoire. La seconde implique que les données en mémoire soient rapidement écrites sur disque. 



À cela s'ajoute les nombreux caches (pg, OS, controleur de disque et  disque) qui risquent de conserver dans des endroits non protégés, volatiles, des données que l'on croient être en sureté sur le disque. C'est notamment le cas des controleurs disques, sur lesquels nous avons peu de moyen de forcer une écriture. 



Commit asynchrones : on considère la transaction comme effectuée avant que le

log ait été écrit sur le disque. Il existe un (faible) risque de perte de

donnée, entre le moment ou le client est averti du succès de la transaction et

celui où la transaction est effectivement commitée. Dans le pire des cas, on a

perte de donnée, mais pas de corruption. 



La durée de risque est de 3 fois la valeur de //wal_writer_delay// (qui est de

200ms par défaut ) . 



Ce comportement peut être changé à tout moment via un ''SET LOCAL

synchronous_commit TO OFF'' 



Lecture et écriture 



Tous les //checkpoint_segment// ou les //checkpoint_timeout//, le WAL est flushé

sur disque. 



Si la fréquence de création de log parce que la taille de segment max est

atteinte est inférieur à la valeur de//checkpoint_warning//, un avertissement

sera écrit dans les logs => il faut augmenter la valeur de

//checkpoint_segment//. 



* checkpoint_segment http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm. un //checkpoint// est un moment pendant lequel les données //sales// en mémoire sont assurées d'être écrite sur disque. Sur un serveur féquemment modifié, cela peut engendrer des baisses de performances. 



 Les checkpoints sont générés par 2 choses: 

  * la taille du segment (//checkpoint_segment//) est atteinte.  un laps de temps

  * (//checkpoint_timeout//) s'est écoulé. 



Il convient de trouver un compromis entre des CP fréquents (et donc peu de

données modifiées à écrire) et des CP"moins fréquents, mais avec beaucoups de

données à écrire. 



De même, en augmentant la fréquence de CP, on augmente la durée de récupération

suite à un crash. 



La version 8.3 introduit //checkpoint_completion_target// qui permet d'étendre

l'écriture des données sales sur une fraction du temps nécessaire à un

checkpoint. 



Donc en augmentant la fréquence des CP, on allonge la distribution des IO dans

le temps. 



Les logs peuvent être archivé. La directive de configuration //archive_command//

permet de faire des choses intéressantes. 



voir //les fonctions de contrôle de la sauvegarde//. 



===== Archivage et PITR  =====



Il s'agit de combiner le précédent type de sauvegarde, sans se soucier de

l'intégrité des données avec le WAL. 



Lorsque l'on //rejoue// le WAL, on s'arrêt quand on veut => PITR. 



En fournissant à un serveur nos WAL de manière continue, on fait de la

réplication à bon marché et l'on dispose d'un serveur disposant presque des même

données que le serveur principal, prêt à le remplacer en cas de soucis. C'est le

principe du //warm standby//. 



L'inconvénient réside dans le fait que l'on travaille sur l'ensemble du groupe

de base de donnée, et qu'il n'est pas possible de ne travailler que sur une base

de donnée particulière. 



Le principe est le suivant: 

  * mise en place de l'archivage. (//archive_(mode|command|timeout//) sauvegarde

  * à chaud (select pg_start_backup( 'label'))	il est conseiller de spécifier

  * le chemin d'accès vers le répertoire de destination comme //label//

  * sauvegarde  FS , cp rsync, ..., fin sauvegarde à chaud select

  * pg_stop_backup('label') ; 

 

Cette dernière opération va générer un fichier ''d'historique de sauvegarde''

contenant les WAL de la sauvegarde, le label (donc normalement le répertoire

dans lequel les WAL sont archivés) et des informations de date de début et de

fin de la sauvegarde 



La restauration se fait de la manière suivante: 

  * arrêt du serveur si possible, sauvegarde du DATADIR et de pg_xlog

  * restauration du FS supprimer tous les fichiers de pg_xlog y copier tous les

  * log non archivés récupérés créer une commande de restauration dans un fichier

  * //reconvery.conf//. A noter les paramètres //recovery_target// permettant

  * d'arréter la restauration en temps voulu.  redémarrer le serveur. Celui-ci

  * devrait entamer la restauration des données, en relisant les différens

  * fichiers WAL 







===== tablespace =====

À spécifier lors de la création, y compris pour les index 



CREATE table //def...// TABLESPACE //tablespace// ... USING INDEX TABLESPACE

//index tablespace// 















 ===== pgfouine  =====



pgfouine est un analyseur de log, il est principalement utilisé pour générer des

statistiques sur les requètes traitées par votre serveur, et permet de détecter

les requètes les plus lentes, les plus souvent traitées, etc. C'est un outil

appréciable pour peaufiner application et configuration serveur. 





===== Explain  =====



C'est le meilleur moyen de mesurer les performances d'une requête. 



Pour l'utiliser, il suffit de préfixer la requëte par l'instruction //EXPLAIN//.

Cela permet de déterminer: 

  * la méthode choisie pour parcourir les tables.  la méthode choisie pour lier

  * les tables entre elles 



<code> 

[[:postgresql:analyse_de_requetes_avec_explain|EXPLAIN]]SELECT  * FROM tenk1; 



			 QUERY PLAN

			 -------------------------------------------------------------

			 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000

			 width=244) }}} La commande indique le temps estimé

			 avant l'affichage du 1er résultat (cf exemple sort), et

			 le second, le temps total mesurés en unité de

			 récupération de page disque. Les autres données sont:

			 le nombre de lignes renvoyées si l'exécution se

			 poursuit jusqu'au bout, et la taille des données. 



Si le nombre de ligne est erroné, c'est signe qu'il y a besoin de faire un

vacuum analyze 



Le coût d'un noeud supérieur inclut les coûts des noeuds fils. Il se calcule

comme suit: (nb pages lue  * seq_page_cost (=1.0)) + (lignes parcourues *

cpu_tuple_cost(0.01)) ; 



nb_pages_lue et lignes_parcourues sont récupérables à partir de //pg_class//. 



L'option ANALYZE impose l'exécution réelle de la reqête. 



 Optimisation des tables Espace de table 





FILLFACTOR - permet de spécifier le taux de remplissage de la table. Un taux de

remplissage faible permet de stocker les lignes modifiées à proximité des lignes

originales, ce qui est plus efficace. Utiliser le //fillfactor// à sa valeur par

défaut (100) dans le cas de tables rarement mises à jour. 



===== Partitionnement horizontal - sharding  =====



''Qu'est-ce que c'est ?" : des lignes d'une même table sont réparties //physiquement//  à des endroits différents. 



À utiliser dans le cas de grandes tables. (en général quand la taille de la table dépasse la quantité de mémoire disponible). 



Le partitionnement se fait par le mécanisme d'héritage des tables partitionnées

à partir d'une unique table-mère. 



Le partitionnement peut se faire par //liste// ou par //étendue//. 



D'un point de vue pratique: 

  * définition de la table-mère, sans contrainte.  création des tables-filles, en

  * rajoutant des //contraintes// garantissant l'unicité des clés.

  * éventuellement créer un trigger  qui redirige les opérations d'écritures sur

  * la bonne table. 



 On peut prendre l'exemple d'une table utilisé pour le logging: plutôt que de

 tout mettre dans une table, on créé une table fille par période, on redirige

 l'écriture des données sur la table la plus récente. La rotation des logs peut

 être assurée par: 

   * suppression de la table la plus ancienne (drop) création d'une nouvelle

   * table redéfinition du trigger de redirection d'écriture. 



  directive //constraint_exclusion// : lors d'une requête, cette directive

  indique au //planner// de vérifier les contraintes des tables filles afin de

  s'assurer de la présence possible ou pas. Cela évite la recherche sur

  l'ensemble des tables. 



 

===== Optimisation du serveur =====



   le minimum vital: 

  * effective_cache_size : quantité de cache disque disponible pour une requête.

  * 128mo par défaut. utilisé par l'optimiseur / planner. une valeur importante

  * permet de privilégier l'utilisation des index. Valeur optimale: free -> free

  * + cached.  shared_buffers : c'est la taille du terrain de jeu de postgres.

  * C'est la quantité de mémoire Valeur idéale: 25 à 30% RAM 



valeur 128MO - kernel.shmmax 128mo //ps aux // après redémarrage serveur:

shared_buffers	128M	32M	16M VZS 		150696	50808	34160

RSZ 		6228	4836	4600 SIZE		37674	12000	8500 





  * default_statistics_target - taille de //pg_stats.most_common_vals//, peut améliorer les performances dans le cas de données distribuées de manière hétérogène. 

  * work_mem - voir section spécifique du programme 



et aussi: 

  * maintenance_work_mem - quantité de mémoire disponible pour les opérations de

  * création et de maintenance des tables et des index.. Peut être plus important

  * que //work_mem// car il est rare d'avoir de telles requêtes exécutées en même

  * temps.  max_prepared_transaction update_process_title max_fsm_pages et

  * max_fsm_relation ne sont plus utilisés en 8.4 La free space map n'est plus

  * implémenté sous la forme d'une zone de shm de taille fixe, mais sous la forme

  * d'un fichier de donnée, (oidtable_FSM) synchronous_commit 



- Optimisation de l'environnement 

   Voir les variables noyau 

   shmmax = shared_buffers  * 8ko 



   Voir les limites utilisateurs 



    disques: 



   WAL on own raid1 data on raid 10 more spindles more controller -> tablespace

   controller avec batterie, et cache actif. 



   La limitation d'un disque, c'est plus le nb d'opérationd IO par secondes que

   le débit 



    à ne pas faire: 



   raid5 cheap disk / controller 







===== Options de gestion des écritures : FSYNC  =====

Si la directive de configuration //fsync// est activée, le serveur s'assure que chaque résultat de transaction soit effectivement écrite sur le disque, en utilisant l'appel système //fsync//, ou équivalent. 



Cela implique malheureusement une perte de performance non négligeable. 



En revanche, la désactivation de ce paramètre entraîne un risque important de

//corruption de données//, dans le cas d'un crash du système d'exploitation. Il

est donc déconseillé de désactiver ce paramètre, à moins d'avoir une entière

confiance dans le système d'exploitation, et le matériel. 



Le type d'appel système réellement est contrôlé par la directive

//wal_sync_method//. Il dépend surtout du système d'exploitation. 



Un paramètre offrant des gains de performance comparables, avec un risque moins

élevé de corruption de données est proposé par //synchronous_commit//. S'il est

à Off, une transaction est considérée comme validée lorsque elle est écrite dans

le WAL, avant que celui-ci soit écrit sur disque. Il existe donc un délai

(infime) pendant lequel un crash du serveur peut entraîner une perte des

dernières transaction, mais pas de corruption des données. Mais cela permet de

combiner plusieurs transactions dans une seule opération d'io. 



L'intérêt de ce paramètre est qu'il peut être spécifié pour une transaction

donnée: {{{ SET LOCAL synchronous_commit TO OFF ; BEGIN ...  }}} 



//full_page_writes//' active ou désactive l'écriture de l'ensemble de la 1ère page (8k) après un checkpoint dans le WAL lorsqu'elle est modifiée. Risque de corruption de donnée dans le cas d'un crash. 



//commit_delay (ms)// délai d'attente entre écriture dans le WAL et flush disques. Permet de réunir plusieurs transactions en une seule opération d'IO. 



- Contrôle de l'utilisation de la mémoire vive : WORK_MEM 

 quantité de mémoire utilisé de manière interne lors des requêtes impliquant des clauses ORDER BY, DISTINCT, merge join et hash tables. Si la requête dépasse cette valeur, il y a création d'une table temporaire, sur disque. //Attention//: il s'agit de la quantité de mémoire allouable pour //chaque// opération de tri. La directive //log_temp_files N// permet de logguer la création / suppression des tables temporaires d'une taille >= N ko. 







Optimisation de l'OS



   echo 175726755840 >/proc/sys/kernel/shmmax echo 655360

   >/proc/sys/kernel/shmmni echo 20971520 >/proc/sys/kernel/shmall 



   voir /sys/block/DISKDEV/queue/scheduler 



   Voir l'ordonnanceur IO Deadline, qui marche apparemment bien

   http://archives.postgresql.org/pdxpug/2007-08/msg00015.php 



















Vérification des performances ( [[:postgresql:pgbench|pgBench]] ) 



//pgbench// permet de déterminer le nombre de transactions simples que votre serveur est capable de traiter. Il utilise des tables de tests, qu'il nous faut préalablement créer. 



plusieurs facteurs sont à prendre en compte pour rendre le test cohérent, car il

est facile d'obtenir des nombres sans réelles significations. 

 

l'option -s permet de déterminer le facteur d'échelle, c'est à dire la taille

des tables. par exemple une valeur de 10 entraîne la création de la table

//accounts// avec  1 000 000 de lignes. ''Cette valeur doit être supérieure aux

nombres de [[:clients|clients]] que vous allez tester'', faute de quoi on ne va mesurer que

la contention. En effet, la valeur de s détermine la taille de la table

//branches//, qui est mise à jour par chaque transaction. 



En jouant sur le facteur d'échelle, on va aussi pouvoir choisir l'élément à

tester, parmi: la mémoire, le système d'entrée sortie, la capacité de réaction

des disques. 



Le principe des tests est le suivant: 



  * 3 tests avec les même paramètres, en prenant la moyenne des trois. 

  * on augmente successivement le facteur d'échelle. 

  * après chaque réinitialisation de la base, on fait un //vacuum analyze//, et un //checkpoint//. 

  * on récupère la taille de la base de données, celle de la table //account// et de l'index associé à cette table. 

  * on récupère les valeurs du bench. 



on obtient une courbe en "Z écrasé" qui s'effondre en 2 endroits: une 1\ere

baisse du tps causé par le passage d'un mode "travail exclusif en mémoire" à un

début d'utilisation des disques. Et une 2eme lorsque la taille des données à

manipuler entraîne l'utilisation intensive des disques. 



Ce schéma se retrouve sur n'importe quelle machine. C'est bien évidemment à

mettre en parallèle avec //work_mem//, effective_cache_size, ainsi que la

quantité de mémoire RAM, et cache contrôleur disponible. 





ASPECTS AVANCÉS 



Window functions 



Une fonction window est une fonction qui travaille sur un (sous)-ensemble de

lignes en relation avec l'enregistrement courant. C'est à rapprocher des

fonctions d'aggrégation, à ceci près qu'il n'y a pas de regroupement des données

calculées. 



Exemple de la table commerciaux.  Afficher pour chaque commercial le ca moyen de

sa division.  {{{ select nom, region, ca_moyen from commerciaux join ( select

avg(ca) as ca_moyen,region from commerciaux group by region) as temp using

(region) ; }}} 



ou, en utilisant les WF: 





Rappels sur les fonctions d'agrégation: Elles calculent un unique résultat à

partir de plusieurs lignes. 





===== XML =====



Alors ...  Il  y a des //fonctions// permettant de: 

  * générer du xml (xmlcomment, xmlconcat, xmlelement, ...) traiter du xml

  * (xpath) exporter des objects (tables, requètes, curseur) au format xml

  * (table_to_xml) 



 La documentation

 [http://www.[[:postgresql|postgresql]].org/docs/8.3/interactive/functions-xml.html suivante]

 propose une feuille xlst permettant de convertir le résultat des fonctions

 précédentes au format html. 

 

 Il y a un //type de donnée// xml, dont l'avantage, par rapport au type //text//

 est qu'il s'occupe de la vérification syntaxe / conformance de la donnée. 



 XMLPARSE permet de créer une variable de type xml à partir d'une chaîne. 



 XMLSERIALIZE fait l'inverse. 



 Attention, il n'existe pas d'opérateurs de comparaison sur ce type de donnée ! 



===== Recherche FULL Text ( FTS )  =====

 Qu'est-ce que c'est 

Recherche dans un document, avec un tri par similarité des résultats. 



On peut utiliser les opérateurs de recherche de textes classiques (like, ~)

mais: 

  * pas de notion linguistique pas de support des index pas de tri 



 types de donnée utilisés: 

  * tsvector - liste trié de //jetons normalisés// (lexernes) auquels s'ajoute la

  * position dans le document, permettant de faire des recherches par proximité. 



On peut également affecter un poids  (1, B, C ou D) à chaque jetons. On l'affect

à l'aide de la fonction //setweigth//. 



On peut générer une liste de jetons normalisés à partir d'une chaîne à l'aide de

la fonction //to_tsvector//. 



  * //tsquery// - liste de //tsvector// combinés par des opérateurs logiques, afin de représenter une RPT. On l'obtient à partir de la fonction //to_tsquery//, qui prend en paramètre des //jetons// combinés entre eux par les opérateurs logiques //|//, //&// ou //!//. 



<code> 

    SELECT to_tsquery( 'french', 'Le & rouge & et & le & noir ) 

      'noir' & 'roug' 

Il est également possible d'associer un poids à un jeton: a}

La fonction plain_totsquery prend une chaîne en entrée, la normalise et

combine ses éléments à l'aide de &, pour la convertir en tsquery.

Indéxation L'indexation full text se fait à priori, en 3 temps:

  • analyse du document en jeton (mot, nombre, mot composé, …) normalisation
  • des jetons (lexernes) (suppression des mots courants, suppression des
  • suffixes et préfixes, passage en minuscule, . L'objectif étant de pouvoir
  • trouver à partir d'un seul critère toutes les variantes du mot demandé.
  • stockage du document indexé, sous la forme d'un tableau de jetons normalisés.

Un dictionnaire permet de contrôler la normalisation des jetons.

Exemple: mot -------+----------- 10095 | atteint 10096 | atteinte 10097 | atteintes 10098 | atteints </code> la //configuration// (1er paramètre) est défini par default_text_search_config. Type d'index: **GIN** * plus rapide plus long à construire bcps plus lent à mettre à jour plus gros que les index //GIST//, donc à conseiller pour les données statiques <code> CREATE INDEX idx_mot ON words USING gin( to_tsvector( 'french', mot ) ); </code> Comme on le voit, on utilise les types //tsvector// et //tsquery//. On peut avantageusement stocker la (ou les) colonnes sur le(s)quelle(s) on veut générer l'index dans une autre colonne de type //tsvector//, qui sera utilisé comme index. Cela permet notamment de stocker //en dur// la configuration de la recherche. Dans ce cas-là, il est nécessaire de mettre en place un trigger, qui met à jour ce champs lors d'une modification / insertion. <code> template1=# select to_tsvector( 'Le test en français.' ) ; to_tsvector -------------------- 'test':2 'franc':4 (1 ligne) </code> Attribuer un score au résultat Les fonctions de //ranking// proposées par [[:postgresql|postgreSQL]] se basent sur le nombre d'occurences, la proximité des occurences avec les termes recherchés, et l'importance (le poids) du passage contenant les termes recherchés. ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 Le premier paramètre permet de définir le poids de chaque classe de jetons ( D C B A) par défaut: {0.1, 0.2, 0.4, 1.0} Le 4ème indique la manière de prendre en compte la longueur du document. par défaut 0: on n'en tient pas compte. exemple imparfait car les données ne sont pas organisées en document: {{{ select id, mot, ts_rank( ts_mot,query) as rang from words, to_tsquery( 'franc | noir' ) query where query @@ ts_mot order by rang desc limit 10 ; }

Mise en valeur des résultats

Utilisation d'une sous-requète pour surligner les termes recherchés: select_id_ts_headline_body_q_rank_from_select_id_body_q_ts_rank_cd_ti_q_as_rank_from_apod_to_tsquery_stars_q_where_ti_q_order_by_rank_desc_limit_10_as_foo}

FTS : divers

Fonction tsvector_update_trigger( colonne tsvector, config, colonne text1, 2, …)

Fonction ts_stat( requète texte, poids)

renvoie: word (valeur du lexerne), ndoc (nombre de documents répondant à la

requète), nentry (nombre d'occurences) ;

- Héritage

L'héritage de table permet de définir une table (fille) à partir d'une table

existante (père). Il est bien sur possible d'y rajouter des attributs

propres à la table héritière.

Les contraintes CHECK et NOT NULL sont héritées. Les contraintes

d'unicité ne le sont pas.

Une requète se fait par défaut sur la table parent et toutes les tables

héritères, sauf en utilisant le mot clé ONLY dans la requète: select_nom_from_only_table}

La modification d'une table entraîne la modification des tables héritières.

La modification d'une colonne héritée entraîne une erreur.

La suppresion d'une table parente n'est pas possible tant qu'il existe des

tables héritières, sauf en utilisant l'option CASCADE. La relation de parenté

peut être rompue à l'aide de la commande ALTER TABLE fille NO INHERIT parent.

Les droits ne sont pa hérités.

Le principal problème réside dans le fait que les contraintes d'unicité ne sont

pas propagées.

De même pour les clés étrangères: Elles ne sont pas propagées, et doivent donc

être recrées sur les tables filles.

Lorsque une table fait référence à une colonne de la table parente, elle ne

pourra pas accéder à la table fille.

- Partitionnement Horizontal ( DBLink et PL/Proxy )

# vim: set filetype=dokuwiki:

postgresql.txt · Dernière modification: 2016/11/30 19:50 (modification externe)