Mode curseur et transactions avec JDBC (PostgresQL)

Lorsque l'on travaille en mode curseur sous JDBC, le mode auto-commit du connecteur est nécessairement dévalidé:

connection.setAutoCommit(false);

La conséquence est que le postmaster garde en mémoire le log des requêtes.
Si l'on fait plusieurs requêtes avec le même connecteur JDBC, on reste connecté au même Postmaster et ce dernier explose en mémoire.

J'ai essayé d'insérer des commits entre les requêtes, mais cela a produit l'erreur suivante:

DECLARE CURSOR pourrait seulement être utilisé dans des blocs de transaction

J'avoue en pas très bien comprendre le sens de ce message et je me suis résolu à créer un nouveau connecteur à chaque requête en mode curseur:

   public static ResultSet runLargeQuerySQL(String sql) throws  FatalException {
        try {
           closeLargeQueryConnection();
           jdbc_large_connection = DriverManager.getConnection(Database.getConnector().getJdbc_url(),Database.getConnector().getJdbc_reader(), Database.getConnector().getJdbc_reader_password());
           Statement _stmts =jdbc_large_connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
           jdbc_large_connection.setAutoCommit(false);
           _stmts.setFetchSize(1000);
           if( Messenger.debug_mode ) Messenger.printMsg(Messenger.DEBUG, "Select large query: " + sql);
           /*
            * Trailing semicolumns make the statement to run in non-cursor mode
            */
           return   _stmts.executeQuery(sql.replaceAll(";", ""));
       } catch (Exception e) {
           Messenger.printMsg(Messenger.ERROR, "Query: " + sql);
           Messenger.printStackTrace(e);
           FatalException.throwNewException(SaadaException.DB_ERROR, e);
       }
       return null;
   }
 
   /**
    * @throws SQLException
    */
   public static void closeLargeQueryConnection() throws SQLException {
       if( jdbc_large_connection != null ) {
           if( Messenger.debug_mode ) Messenger.printMsg(Messenger.DEBUG, "Close connection for large queries ");          
           jdbc_large_connection.close();
           jdbc_large_connection = null;
       }
     
   }

Quand le mode curseur de JDBC ne fonctionne pas sur PostgresQL

Imaginer la requete suivante sur une grosse table (10.000.000 lignes):

SELECT oidprimary,oidsecondary FROM Counterparts

Comme vous ne voulez pas que votre application Java vous fasse un OutOfMemoryError, vous traitez votre requête en mode curseur (voir ici)):

connection().setAutoCommit(false);
Statement _stmts = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
_stmts.setFetchSize(1000);
ResultSet rs = _stmts.executeQuery("SELECT oidprimary,oidsecondary FROM Counterparts");
....

Et ca marche, mais attention, si à la suite d'un couper/coller malicieux par exemple, votre requête se voit suivie d'un ";", vous risquez de vous retrouver avec cette erreur:

Exception in thread "Thread-0" java.lang.OutOfMemoryError: Java heap space

En effet, le pilote JDBC de PostgresQL considère alors que votre requête comporte une suite de requêtes séparées par des ";" et dans ce cas il quitte le mode curseur et tente de recopier tout les résultat en mémoire.

PostgresQL vs MySQL

Mon application a été construite sur PostgresQL, système de base de données auquel on accède par JDBC. Dès le départ il a été envisagé d'utiliser d'autres SGDBs. Nous avons toutefois attendu une demande formelle avant de passer à l'acte.
C'est ce qui s'est passé récemment avec un utilisateur confessant que notre Saada serait encore plus génial s'il pouvait utiliser MySQL en lieu et place de Postgres et que autrement il ne lui serait d'aucun intérêt.
Vous trouverez plus loin un bestiaire des incompatibilités SQL entre les deux systèmes. Il ne s'agit pas ici de comparer mais simplement de pointer les différences.

Les transactions

L'ouverture de transaction permet de rendre atomique l'exécution d'un ensemble de requêtes. Si l'une plante, la base retombe dans son état initial (rollback) quelque soit les modifications déjà effectuées avant le plantage.

	Postgres	MySQL
Ouvrir la transaction	BEGIN TRANSACTION	START TRANSACTION
Terminer la transaction	COMMIT	COMMIT
Annuler la transaction	ABORT	ROLLBACK

Une différence plus importante concerne le verrouillage des tables. Sous Postgres, le verrouillages des tables durant une transaction est implicite. Il n'y a pas à s'en occuper.
Sous MySQL, il faut verrouiller explicitement toutes les tables auxquelles on accède durant la transaction.

• Si au cours de la transaction une requête modifie la table TABLE_W il faut la faire précéder par LOCK TABLE TABLE_W WRITE


• Si au cours de la transaction une requête lit la table TABLE_R il faut la faire précéder par LOCK TABLE TABLE_R READ


• Si au cours de la transaction une requête lit les tables TABLE_R1 et TABLE_R2 et qu'elle modifie la table TABLE_W, il faut la faire précéder par LOCK TABLE TABLE_R1, TABLE_R2 READ, TABLE_W WRITE

Les tables temporaires

Un petit bonnet d'âne à MySQL: La partie Web des bases Saada utilise un rôle (compte) d'accès à la base avec des droits réduits (le reader) de manière a éviter à des requêtes malicieuses d'altérer les données. Seulement voila, certaines requêtes compliquées utilisent des tables temporaires. Cela nous permet d'éviter des jointure compliquées dont on ne sait jamais comment l'optimiseur du moteur de requête se sortira.
Sous Postgres, il n'y a rien à faire de particulier, le reader peut créer ses tables temporaires.
Sous MySQL, la musique est bien différente. Il faut donner explicitement au reader le droit de créer des tables temporaires:GRANT CREATE TEMPORARY TABLES ON database TO reader

Le chargement de données à partir de fichiers

Lors du chargement de donnée dans MySQL à partir d'un fichiers ASCII (LOAD DATA INFILE...) les contraintes de clés primaires sont vérifiées ligne par ligne, et ça rame vraiment beaucoup. La commande SQL suivante permer de dévalider temporairement les contraintes sur la clé primaire.
ALTER TABLE tbl_name DISABLE KEYS

Tout a changé le 4 Novembre 2008

Il est des convictions tellement fortes qu'elles ne peuvent s'ancrer que dans la matière malléable et fertile d'un esprit d'enfant. Mais alors elles peuvent rester des décades durant rangées dans notre cerveau au rayon des évidences, des axiomes devrais-je dire:
Depuis ma naissance et jusqu'à un certain 4 Novembre 2008, j'étais totalement certain que jamais je ne serai plus vieux que le président des USA.

Interrompre proprement une transaction JDBC

Un client lance une requête trop longue sur ma base (servlet + JDBC). Je veux l'interrompre au bout de quelques heures car je peux raisonnablement estimer que mon utilisateur n'a pas eu la patience d'attendre et que de plus, mon serveur est chargé inutilement pendant ce temps.
Si mon architecture repose sur un middleware (intergiciel ca me plait) lançant un processus par requête, je peux toujours tuer les processus les plus vieux.
Si maintenant les requêtes JDBC sont lancées directement depuis une application monolithique (même threadées) les choses sont plus difficiles.
Une solution élégante à ce problème est proposée par cet article (en anglais).

JConsole ne parvient pas à se connecter sur votre process

JConsole est un utilitaire inclus dans jdk (à partir de 1.5) et basé sur JMX permettant de faire un audit d'un processus Java en cours d'exécution.

JConsole ne peut être connecté qu'à une application tournant sous une JVM 1.6 ou alors sous une JVM 1.5 mais avec la condition que la ligne de commande possède l'option suivante: -Dcom.sun.management.jmxremote

Une doc très didactique est fournie par SUN, mais elle est en anglais.

Pourquoi Astro-Saada

Et bien c'est parce que Saada tout seul était déjà pris.
Je fais dans les bases de données astronomiques (Java, Linux, Postgres et tous ces genres de choses) et je me suis attelé au développement et à la diffusion d'un outil capable de générer automatiquement des bases de données astronomiques à partir de l'analyse d'un ensemble de fichiers de données (FITS et VOTables pour les connaisseurs). Cet outil s'appelle Saada.