La syntaxe DELETE avec un JOIN est typique de MySQL et n'existe pas dans PostgreSQL. Pour éliminer les lignes qui ont le même theme_lib en gardant le theme_id le plus petit, ça pourrait être écrit avec une auto-jointure comme ça dans PostgreSQL:

DELETE FROM theme t1
USING theme t2
WHERE t1.theme_lib = t2.theme_lib
AND t1.theme_id > t2.theme_id;

C'est aussi vérifiable dans le détail via le dépôt git des sources de pgapt:

https://git.postgresql.org/gitweb/?p=pg … a=shortlog

Le nom de code de 14.04 est "trusty" et on voit qu'il a été éliminé des versions supportées le 14 mai précisément:

https://git.postgresql.org/gitweb/?p=pg … 6ab5074232

Ca je n'en doutais pas mais ce n'est pas ce que je voulais dire en disant que le contenu temporaire à durée de quelques secondes ne va pas forcément physiquement sur les disques.

Prenons un exemple sous Linux avec 32Go de RAM, et un FS ext4 basique.

Je créé un fichier random de 100Mo:
$ dd if=/dev/urandom of=file bs=1024 count=$((100*1024))

Mettons maintenant que je le copie en regardant à côté avec vmstat les accès sur l'interface disque.

$ vmstat -n 1
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  0   8256 382924 1133884 30858356    0    0     0     0   68   85  0  0 100  0  0
0  0   8256 382924 1133884 30858356    0    0     0     0   46   56  0  0 100  0  0
... pas mal de secondes plus tard...
0  0   8256 382180 1133884 30858356    0    0     0 102555  310  219  0  0 99  1  0
0  0   8256 382180 1133884 30858360    0    0     0     6  238   93  0  0 100  0  0
1  0   8256 382180 1133884 30858360    0    0     0     0   66   69  0  0 100  0  0
0  0   8256 382312 1133884 30858360    0    0     0    19   98  138  0  0 100  0  0

Au bout d'un moment on voit passer les 100Mo en colonne "bo" qui correspond à l'écriture physique, c'est le 102555 en l'occurrence.

Mais qu'est-ce qui se passe si on fait "cp file file2 ; sleep 3; rm file2" c.a.d que le fichier est détruit au bout de quelques secondes?
Et bien typiquement cette ligne dans vmstat n'apparait jamais, car les données ont été abandonnées avant que le cache soit vidé sur disque. En vrai tout s'est passé en mémoire.

Dans le cas d'un petit fichier de 100Mo écrits dans base/pgsql_tmp, vu que postgres n'a pas de raison de demander un "fsync" sur ce fichier qui n'a pas à survivre à la transaction, je ne vois pas pourquoi l'OS se précipiterait pour l'écrire physiquement sur disque.

Si on regarde exactement le noeud de tri dans les 2 cas, via le explain analyze:
Cas 1:

work_mem = '8MB';

GroupAggregate  (cost=2023043.46..2063380.33 rows=407047 width=85) (actual time=6157.856..7899.641 rows=467497 loops=1)
   Group Key: r.colonne2, r.colonne4, r.colonne3
   Buffers: shared hit=574976, temp read=11850 written=11869
   ->  Sort  (cost=2023043.46..2026340.41 rows=1318778 width=81) (actual time=6157.823..7029.107 rows=988089 loops=1)
         Sort Key: r.colonne2, r.colonne4, r.colonne3
         Sort Method: external merge  Disk: 94800kB

7029 - 6157 = 870 ms pour faire le tri

Cas 2:

  set work_mem = '4GB';

GroupAggregate  (cost=1987074.38..2027412.90 rows=407047 width=85) (actual time=8889.551..10203.305 rows=467497 loops=1)
   Group Key: r.colonne2, r.colonne4, r.colonne3
   Buffers: shared hit=574976
   ->  Sort  (cost=1987074.38..1990371.48 rows=1318838 width=81) (actual time=8889.511..9224.192 rows=988089 loops=1)
         Sort Key: r.colonne2, r.colonne4, r.colonne3
         Sort Method: quicksort  Memory: 163527kB
         Buffers: shared hit=574976

9224 - 8889 = 335 ms pour faire le tri

Le tri lui-même est plus rapide en mémoire que sur disque, conformément à ce qu'on peut attendre.

Au moment de commençer le tri, la requête avec le work_mem à 4GB semble déjà bien en retard par rapport à l'autre configuration, et c'est ce retard là qui semble jouer dans la différence totale entre les deux requêtes, plus que l'étape de tri.

Mais il y a une chose qui n'est pas claire pour moi, c'est si vous comparez uniquement des work_mem différents ou si vous comparez toujours deux configurations différentes sur de multiples paramètres comme au début du post.

Oui il est tout à fait possible d'avoir plusieurs versions en même temps. Effectivement il faut un port différent pour chaque, un répertoire de données différent pour chaque, et il faut faire attention à ce que shared_buffers (cache mémoire global par instance) ne soit pas trop élevé, puisque c'est une zone de mémoire contigüe qui est allouée par instance et libérée seulement quand l'instance  quitte. Si vous n'avez pas besoin des différentes instances en même temps, vous pouvez les mettre en démarrage manuel dans le gestionnaire de services.

Il semble que le besoin de votre fonction soit schématiquement:

Tant qu'il y a des enregistrements qui vérifient certains critères
     faire certains traitements susceptibles de modifier ces enregistrements

Et donc la condition "tant que" serait à réévaluer à chaque fois.

La boucle FOR n'est pas spécialement adaptée à ça, mais il y a d'autres types de boucle
et structures de contrôle qui peuvent être plus appropriées:

https://docs.postgresql.fr/11/plpgsql-c … tures.html

Ce n'est toujours pas clair parce que l'expression "pris en compte" reste à interpréter. Il serait peut être utile d'ajouter des raise notice dans ce code pour pouvoir dire à tel moment la valeur de telle variable est X alors qu'elle devrait être Y.

Je suspecte qu'il y a une confusion sur le fait que le SELECT derrière une boucle FOR n'est exécuté qu'une seule fois, et que les valeurs qu'il délivre sont pré-établies à ce moment là indépendamment ce qui se passe dans la boucle. Voici un exemple qui illustre ça:

create table list(x int);

insert into list values (1),(2),(3),(4);

do $$
declare i integer;

begin
  for i in select x from list
  loop
   raise notice 'i=%', i;
   update list set x=10;
   i:=12;
  end loop;
end
$$ language plpgsql;

Ici dans la boucle la variable de boucle est modifiée, ainsi que l'intégralité du contenu de la table sur laquelle on boucle.
Et ca n'a aucun effet sur les valeurs de i au tour suivant de la boucle, dans le sens où la sortie est :

NOTICE:  i=1
NOTICE:  i=2
NOTICE:  i=3
NOTICE:  i=4

Et si ce select avait une clause WHERE dont les valeurs changent dans la boucle, ce serait pareil. La clause WHERE ne serait pas réévaluée.

Je ne suis pas certain que ça fonctionne (et je trouve ce redémarrage automatique un peu surprenant)
mais personnellement j'aurais tendance à voir plutôt par là:

(extrait de man pg_ctlcluster):

et mettre provisoirement "disabled" dans le fichier, vu que (cf man pg_createcluster):

La sécurité par défaut sous Debian est en principe que l'utilisateur système postgres peut se connecter sans mot de passe avec l'utilisateur de base de données du même nom. Ensuite il peut créer d'autres utilisateurs avec CREATE USER ou d'autres bases avec CREATE DATABASE et mettre des droits à ces utilisateurs avec GRANT. La gestion de tout cet aspect est assez différente de mysql et les connaissances mysql ne servent pas à grand chose, il faut regarder la doc de PostgreSQL ou trouver des tutos si la doc vous paraît trop peu didactique.

En tout cas pour se connecter, en partant d'un compte root, faire "su - postgres" puis simplement "psql"

Cette commande CREATE PROFILE existe dans Oracle mais pas dans PostgreSQL.

Elle peut exister dans le fork commercial de PostgreSQL fait par EntrepriseDB qui sert justement à être plus proche d'Oracle.

Voir https://www.enterprisedb.com/compare-postgres-databases

Le modèle de PostgreSQL c'est qu'il y a un processus par connexion, donc si N instances psql sont en parallèle, il y a N processus côté serveur (sauf s'il y a un pooler de connexion qui dit le contraire).

Après ce que font ces processus c'est une autre affaire. S'il se trouve que des sessions en verrouillent d'autres, il peut y avoir N-1 processus qui attendent et un seul qui travaille. Il faut regarder dans le détail, via la vue pg_stat_activity par exemple.

Un UPDATE sans clause WHERE met à jour toute la table.
Donc ici toutes les lignes sont mises à jour à chaque tour de boucle, écrasant toutes les valeurs précédentes.

En base de données les lignes des tables n'ont pas de numéro intrinsèque, donc il n'y a pas de ligne 1, ligne 2, etc. sauf à les numéroter de manière complètement arbitraire. Il faudrait plutôt utiliser la clef primaire de la table pour indiquer que telle ligne doit être mise à jour avec telle valeur.